VIRTUAL

MySQL

Example

CREATE TABLE employee (
    id INT,
    first_name VARCHAR(100),
    last_name VARCHAR(100),
    full_name VARCHAR(255) AS (CONCAT(first_name, ' ', last_name)) VIRTUAL
);

INSERT INTO employee (id, first_name, last_name)
VALUES (1, 'John', 'Doe');

SELECT * FROM employee;

Output

+------+------------+-----------+--------------+
| id   | first_name | last_name | full_name    |
+------+------------+-----------+--------------+
| 1    | John       | Doe       | John Doe     |
+------+------------+-----------+--------------+

Explanation

The above example demonstrates the usage of VIRTUAL in MySQL. It creates a VIRTUAL column full_name in the employee table. This column is generated by concatenating first_name and last_name. When a new record is inserted into the table, MySQL automatically calculates and stores the value for the full_name column.

SQLite

Example

CREATE TABLE Students (
    StudentID INTEGER,
    FirstName TEXT,
    LastName TEXT,
    Age INTEGER,
    GradeLevel INTEGER,
    GPA REAL,
    TotalScore AS (GPA * GradeLevel) VIRTUAL
);

Output

Table Students created.

Explanation

In the above SQL code, a table called Students is created with columns for StudentID, FirstName, LastName, Age, GradeLevel, and GPA. In addition, there is a VIRTUAL column, TotalScore, which is calculated on the fly and doesn’t store any data permanently. It’s computed as GPA multiplied by GradeLevel each time data is read out of the table.