Chapter 16: Views
A VIEW is like a saved query that you can treat almost exactly like a real table. You write a complex SELECT once, give it a name, and then you can query that view forever — just like querying a normal table.
Why are views so useful?
- Hide complexity from users (show only the columns they need)
- Simplify repeated reports (e.g., monthly sales summary)
- Improve security (users can query the view but not the underlying tables)
- Make code cleaner and more maintainable
- Can sometimes be updatable (you can INSERT/UPDATE/DELETE through the view!)
Today we’ll cover the three main things you need to know about views:
- CREATE VIEW – Making a new view
- Updatable views – When you can modify data through a view
- DROP VIEW – Deleting a view
We’ll use our bookshop database with the books and authors tables.
1. CREATE VIEW – Creating Your First View
Syntax:
|
0 1 2 3 4 5 6 7 8 9 10 |
CREATE VIEW view_name AS SELECT column1, column2, ... FROM table_name WHERE condition ORDER BY ...; -- ORDER BY is allowed but ignored in most databases unless TOP/LIMIT is used |
Important: A view does not store data — it’s just a saved query. Every time you query the view, the underlying SELECT runs fresh.
Example 1 – Simple view: All books with author names
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 |
CREATE VIEW book_catalog AS SELECT b.title AS book_title, a.name AS author_name, a.country, b.price, b.stock, ROUND(b.price * 1.18, 2) AS price_with_gst FROM books b INNER JOIN authors a ON b.author_id = a.author_id ORDER BY b.title; |
Now you can query it like a table:
|
0 1 2 3 4 5 6 7 8 9 |
SELECT * FROM book_catalog WHERE price_with_gst > 300 ORDER BY price_with_gst DESC LIMIT 5; |
Example 2 – View with aggregation: Books per author summary
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
CREATE VIEW author_performance AS SELECT a.name AS author_name, a.country, COUNT(b.book_id) AS books_published, ROUND(AVG(b.price), 2) AS avg_price, SUM(b.stock) AS total_stock, MIN(b.price) AS cheapest_book, MAX(b.price) AS most_expensive_book FROM authors a LEFT JOIN books b ON a.author_id = b.author_id GROUP BY a.author_id, a.name, a.country HAVING COUNT(b.book_id) > 0; |
Usage:
|
0 1 2 3 4 5 6 7 8 |
SELECT * FROM author_performance WHERE total_stock > 200 ORDER BY books_published DESC; |
Example 3 – View with CASE and functions (fancy catalog)
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE VIEW fancy_book_catalog AS SELECT CONCAT(UPPER(title), ' by ', a.name) AS book_info, price, stock, CASE WHEN price < 200 THEN 'Budget Gem' WHEN price < 350 THEN 'Worth It' ELSE 'Premium Pick' END AS recommendation, DATEDIFF(CURDATE(), published_date) AS days_since_published FROM books b INNER JOIN authors a ON b.author_id = a.author_id; |
Best Practice Tips for Creating Views:
- Use clear, descriptive names: vw_book_sales_summary, vw_active_customers
- Prefix with vw_ or view_ so everyone knows it’s a view
- Add OR REPLACE to update existing view:
SQL0123456CREATE OR REPLACE VIEW book_catalog AS ...
2. Updatable Views – When You Can Modify Data Through a View
Some views allow you to INSERT, UPDATE, or DELETE data — and those changes go to the underlying table(s).
Rules for a view to be updatable (most databases):
- It must be based on one single table (no JOINs, no aggregations)
- No DISTINCT, GROUP BY, HAVING, UNION, subqueries in SELECT
- No calculated columns (e.g., price * 1.18)
- Must include the primary key of the underlying table
Example – Updatable view: Simple view on books table
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
CREATE VIEW affordable_books AS SELECT book_id, -- Must include PK title, price, stock, is_available FROM books WHERE price <= 300.00; |
Now you can update through the view:
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 |
-- This actually updates the real books table! UPDATE affordable_books SET price = price * 0.95 -- 5% discount WHERE book_id = 1; -- Or insert new book through the view INSERT INTO affordable_books (title, price, stock, is_available) VALUES ('New Budget Book', 150.00, 100, 1); -- Delete through view DELETE FROM affordable_books WHERE stock < 10; |
Non-updatable view example (will fail):
|
0 1 2 3 4 5 6 7 8 9 |
CREATE VIEW book_summary AS SELECT author_id, COUNT(*) AS book_count, AVG(price) AS avg_price FROM books GROUP BY author_id; |
|
0 1 2 3 4 5 6 |
UPDATE book_summary SET avg_price = 300; -- ERROR! Not updatable |
Pro Tip: Even if a view is updatable, many teams avoid using updatable views in production — they can be confusing. Use them mainly for SELECT-only purposes.
3. DROP VIEW – Deleting a View
Very simple — and safe! Dropping a view does NOT delete the underlying data — just the saved query.
Syntax:
|
0 1 2 3 4 5 6 |
DROP VIEW view_name; |
Safe version:
|
0 1 2 3 4 5 6 |
DROP VIEW IF EXISTS book_catalog; |
Example:
|
0 1 2 3 4 5 6 7 |
-- We don’t need this anymore DROP VIEW IF EXISTS fancy_book_catalog; |
What happens to queries that use the view? They will fail with “View does not exist” — so be careful in production!
Full Realistic Workflow Example
|
0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 |
-- 1. Create a useful view for the sales team CREATE OR REPLACE VIEW vw_book_sales_report AS SELECT b.title, a.name AS author, b.price, b.stock, ROUND(b.price * b.stock, 2) AS potential_revenue, CASE WHEN b.stock > 100 THEN 'High Demand' WHEN b.stock > 50 THEN 'Medium Demand' ELSE 'Low Demand' END AS demand_level FROM books b INNER JOIN authors a ON b.author_id = a.author_id; -- 2. Use it like a table SELECT * FROM vw_book_sales_report WHERE demand_level = 'High Demand' ORDER BY potential_revenue DESC; -- 3. If we want to update prices through a simple view CREATE VIEW vw_books_basic AS SELECT book_id, title, price, stock FROM books; -- 4. Give 10% discount on all books via the view UPDATE vw_books_basic SET price = price * 0.90; -- 5. Clean up when no longer needed DROP VIEW IF EXISTS vw_book_sales_report; DROP VIEW IF EXISTS vw_books_basic; |
