Views
A view is a saved query that you use like a table.
In this page:
Syntax
CREATE VIEW view_name AS
SELECT columns FROM table_name WHERE condition;
SELECT * FROM view_name;Views
CREATE VIEW name AS SELECT ... stores the query, not the data. Views simplify complex joins, hide columns for security and give a stable interface when tables change.
Simple views can be updatable.
CREATE OR REPLACE VIEW updates a definition, and DROP VIEW removes it.
Note:
Grant access to a view instead of the underlying table to expose only some columns.
Example: Views
CREATE TABLE employees (id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER);
INSERT INTO employees VALUES (1,'Ada','eng',100),(2,'Bob','eng',80),(3,'Cy','ops',60);
CREATE VIEW dept_summary AS SELECT dept, COUNT(*) AS staff, AVG(salary) AS avg_salary FROM employees GROUP BY dept;
SELECT * FROM dept_summary ORDER BY dept;
UPDATE employees SET salary = 120 WHERE id = 2;
SELECT * FROM dept_summary ORDER BY dept;
-- Output:
-- dept | staff | avg_salary
-- eng | 2 | 90.0
-- ops | 1 | 60.0
-- dept | staff | avg_salary
-- eng | 2 | 110.0
-- ops | 1 | 60.0Login to try C/C++/Java/PHP code in the editor
Related Topics
Common Mistakes
- Assuming views store data
- Stacking many views and hurting performance
- Forgetting a view reflects the table's current data
Chapter Summary
- A view is a saved query
- No data is stored
- Simplifies joins and limits column access
- Always shows current data
🔒
Chapter Quiz — Complete all 7 topics to unlock
0/7 topics done
Complete these topics first: