✏️ Explanatory Question
Level: Advanced — Views simplify complex queries and add a security layer over your tables.
A view is a virtual table based on the result of a stored SELECT query. It does not store data itself (unlike a real table) — instead, it dynamically pulls data from the underlying "base" tables each time it is queried.
| Aspect | View | Table |
|---|---|---|
| Stores data? | No (virtual) | Yes (physical) |
| Based on | A SELECT query | Actual storage |
| Always current? | Yes (live data) | Yes |
| Storage cost | Minimal (definition only) | Full data storage |
Some views allow INSERT/UPDATE/DELETE that flow through to the base table. A view is not updatable if it contains: aggregate functions, GROUP BY, DISTINCT, UNION, or subqueries in the SELECT list.
-- Create a view that hides complexity and sensitive columns
CREATE VIEW active_employees AS
SELECT e.emp_id, e.name, d.dept_name
FROM employees e
JOIN departments d ON e.dept_id = d.dept_id
WHERE e.status = 'Active';
-- Query it just like a table
SELECT * FROM active_employees WHERE dept_name = 'Engineering';
-- Updatable view (simple, no aggregates)
CREATE VIEW kolkata_emps AS
SELECT emp_id, name, salary FROM employees WHERE city = 'Kolkata'
WITH CHECK OPTION; -- prevents inserts/updates that break the WHERE
-- WITH CHECK OPTION ensures new rows still match city = 'Kolkata'
UPDATE kolkata_emps SET salary = salary + 1000 WHERE emp_id = 1;
-- Drop a view
DROP VIEW active_employees;WHERE condition — preventing rows from "disappearing" out of the view after an update.