🧮 SQL Virtual Columns Explained

Virtual columns in SQL are computed columns that do not store data physically, but calculate values based on other columns. They are useful for derived or auto-calculated data like age, tax, bonus, etc.

📌 Syntax (Oracle Style)

CREATE TABLE employees (
  id NUMBER PRIMARY KEY,
  name VARCHAR2(50),
  salary NUMBER(8, 2),
  bonus_percentage NUMBER(3),
  bonus_amount GENERATED ALWAYS AS (salary * bonus_percentage / 100) VIRTUAL
);

Explanation:

bonus_amount is a virtual column
It automatically calculates: salary × bonus_percentage ÷ 100
It’s not stored — just calculated when you query it

📋 Sample Data (Bangladeshi Employees)

id	name	salary	bonus_percentage	bonus_amount (Virtual)
1	Arif	50000	10	5000
2	Nasrin	60000	5	3000
3	Faruk	55000	12	6600

🔍 Query the Table

SELECT name, salary, bonus_percentage, bonus_amount
FROM employees;

Output will show: calculated bonus amount for each employee — no need to insert it manually.

💡 Use Cases

🎯 Automatically calculate age from birthdate
💰 Calculate tax or discount values
📊 Create dynamic fields like full name (first + last)

⚠️ Notes

❌ You cannot insert or update data in a virtual column directly
✅ Virtual columns can be used in indexes
🚫 Not all databases support `VIRTUAL` keyword — mainly available in Oracle

📝 Bonus: MySQL Generated Column

CREATE TABLE employees (
  id INT PRIMARY KEY,
  name VARCHAR(50),
  salary DECIMAL(8,2),
  bonus_percentage INT,
  bonus_amount DECIMAL(8,2) GENERATED ALWAYS AS (salary * bonus_percentage / 100) STORED
);

In MySQL:

STORED — stores the result in the table
VIRTUAL — calculates on demand (default if not stored)

📌 Summary:
Virtual columns are auto-calculated fields that make your table smarter and reduce manual data entry. They're especially useful in reporting and automation.

Trust Coding : Programming

📌 SQL Virtual Columns Tutorial