Views
CREATE VIEW — named saved queries and virtual tables
Skills taught: sql.create-view, sql.drop-view, sql.materialized-view
Creating and using views
-- View: full order details (replaces repetitive 3-table join)
CREATE VIEW IF NOT EXISTS order_details AS
SELECT
o.id AS order_id,
u.name AS customer,
u.city,
p.name AS product,
p.category,
o.quantity,
o.total,
o.ordered_at
FROM orders o
JOIN users u ON o.user_id = u.id
JOIN products p ON o.product_id = p.id;
-- Now query the view like a table
SELECT * FROM order_details;
-- Filter on the view
SELECT customer, product, total
FROM order_details
WHERE category = 'Electronics'
ORDER BY total DESC;
-- Aggregate on the view
SELECT customer, SUM(total) AS total_spent
FROM order_details
GROUP BY customer
ORDER BY total_spent DESC;
More view patterns
-- View: customer summary (hides the raw orders table complexity)
CREATE VIEW IF NOT EXISTS customer_summary AS
SELECT
u.id,
u.name,
u.city,
COUNT(o.id) AS order_count,
COALESCE(SUM(o.total), 0) AS total_spent,
MAX(o.ordered_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.city;
SELECT * FROM customer_summary ORDER BY total_spent DESC;
-- List all views
SELECT name FROM sqlite_master WHERE type = 'view';
-- Drop a view
-- DROP VIEW IF EXISTS order_details;
Frequently Asked Questions
Are views faster than writing the same query directly?
Not inherently — a view is expanded inline at query time, so the database runs the same JOIN. However, views encourage writing the query once correctly (with proper indexes), and then it's reused everywhere. For performance, PostgreSQL's materialized views store the result physically and are much faster to read — at the cost of potentially stale data until refreshed.