Learn SQL Database Programming

Creating a generated column

Let's say you regularly need to get a player's batting average, so you want to store it in a generated column instead of calculating it each time with an expression. The following is a query that calculates the batting average:

USE lahmansbaseballdbSELECT playerid, yearid, teamid,   h, ab, h/ab AS batavg FROM batting;

To create a virtual generated column with the batting average in the batting table, execute the following query:

USE lahmansbaseballdb;ALTER TABLE battingADD COLUMN batavg DECIMAL(4,3) GENERATED ALWAYS AS (h/ab) AFTER lgID; 

To create a stored generated column with the batting average in the batting table, execute the following query:

USE lahmansbaseballdb;ALTER TABLE battingADD COLUMN batavgstored ...