1. Creating a View
CREATE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;
2. Querying a View
SELECT * FROM view_name;
3. Updating Data Through a View
-- Only works if the view is updatable
UPDATE view_name
SET column = value
WHERE condition;
4. Dropping a View
DROP VIEW view_name;
5. Replacing an Existing View
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2
FROM table_name
WHERE condition;
6. WITH CHECK OPTION
CREATE VIEW active_users AS
SELECT * FROM users
WHERE status = 'active'
WITH CHECK OPTION;
-- Prevents updates that violate the WHERE condition
7. Read-Only Views
CREATE VIEW summary AS
SELECT department, COUNT(*) AS total
FROM employees
GROUP BY department;
-- Views with aggregates or joins are typically read-only
8. Materialized Views (if supported)
CREATE MATERIALIZED VIEW sales_summary AS
SELECT region, SUM(sales) AS total_sales
FROM orders
GROUP BY region;
-- Not supported in all SQL dialects
9. View vs Table
- A view is a virtual table generated by a
SELECT query.
- No physical data is stored (except in materialized views).
10. Best Practices
- Use views to simplify complex queries and encapsulate logic.
- Name views clearly (e.g.,
active_users_view).
- Avoid deep nesting of views.
- Use for read abstraction and fine-grained data access control.
