Views
A VIEW is a saved SQL query with a name - it acts like a virtual table. Views simplify complex queries for end users, enforce column visibility (security), and provide a stable interface even when underlying tables change.
View Characteristics
- CREATE VIEW name AS SELECT ... - virtual table based on a query
- No data is stored - VIEW is executed at query time
- Supports SELECT (usually), limited UPDATE/INSERT/DELETE
- Updatable views: writable if view maps 1:1 to a single table (no GROUP BY, DISTINCT, JOIN)
- Use views to: simplify complex joins, hide sensitive columns, provide named query templates
- Changes to underlying tables are immediately reflected in view queries
View Examples on ecommerce_db
-- Create a view: customer order summary
CREATE VIEW customer_orders AS
SELECT
u.id AS user_id,
u.name AS customer_name,
u.email,
COUNT(o.id) AS total_orders,
ROUND(SUM(o.total_amount), 2) AS lifetime_value,
MAX(o.created_at) AS last_order_date
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.email;
-- Use view like a table
SELECT customer_name, lifetime_value FROM customer_orders
WHERE lifetime_value > 500
ORDER BY lifetime_value DESC;
-- Create view hiding sensitive payment data
CREATE VIEW order_summary AS
SELECT
o.id, o.status, o.total_amount,
u.name AS customer,
CASE WHEN p.paid_at IS NOT NULL THEN 'Paid' ELSE 'Unpaid' END AS payment_status
FROM orders o
JOIN users u ON o.user_id = u.id
LEFT JOIN payments p ON o.id = p.order_id;
-- Use the simplified view
SELECT * FROM order_summary WHERE status = 'pending';
-- Drop view
DROP VIEW customer_orders;
-- Replace existing view (PostgreSQL)
CREATE OR REPLACE VIEW customer_orders AS
SELECT u.name, COUNT(o.id) AS orders FROM users u LEFT JOIN orders o ON u.id=o.user_id GROUP BY u.id, u.name;Quick Quiz
Tip
Tip
Practice Views in small, isolated examples before integrating into larger projects. Breaking concepts into small experiments builds genuine understanding faster than reading alone.
JOINs combine rows from two tables based on related columns
Common Mistake
Warning
A common mistake with Views is skipping edge case testing - empty inputs, null values, and unexpected data types. Always validate boundary conditions to write robust, production-ready sql code.
Practice Task
Note
Practice Task - (1) Write a working example of Views from scratch without looking at notes. (2) Modify it to handle an edge case (empty input, null value, or error state). (3) Share your solution in the Priygop community for feedback.
Key Takeaways
- A VIEW is a saved SQL query with a name - it acts like a virtual table.
- CREATE VIEW name AS SELECT ... - virtual table based on a query
- No data is stored - VIEW is executed at query time
- Supports SELECT (usually), limited UPDATE/INSERT/DELETE