Lesson 19 of 20
In progress
Views: naming a query
Save a query under a name so reports stay consistent, understand what a view does not do, and know when a materialised view is worth it.
A view is a stored query with a name. Selecting from it runs the query, which means the definition lives in one place instead of being copied into five reports.
What you will learn
- Create and use a view.
- Recognise what a view cannot do.
- Decide between a view, a CTE and a materialised view.
A view in practice
Editable. Runs in your browser.
INSERT INTO orders (customer, status, amount_paise, placed_on) VALUES
('Ana', 'paid', 125000, '2025-07-01'),
('Ana', 'refunded', 40000, '2025-07-04'),
('Ravi', 'paid', 89000, '2025-07-05'),
('Meera', 'pending', 240000, '2025-07-06');
CREATE VIEW paid_orders AS
SELECT id, customer, amount_paise, placed_on
FROM orders
WHERE status = 'paid';
SELECT customer, SUM(amount_paise) / 100.0 AS paid_rupees
FROM paid_orders
GROUP BY customer;
Press Run to see the output.
The rule "a paid order is one with status paid" now exists once. When the definition changes — say refunds move to their own table — every report that uses the view changes with it.
Views also document a domain term. paid_orders tells a reader what the business means by paid, which a WHERE buried in a dashboard query does not.
Views do not store rows
Selecting from a view runs the underlying query every time. There is no copy to go stale, and there is no copy to make it fast.
That has two consequences. First, a view over a slow query is still slow. Second, an index on the base table helps the view, but there is no such thing as an index on a view in SQLite.
Because the query is expanded at planning time, the engine can often push filters from your query into the view’s definition. SELECT * FROM paid_orders WHERE customer = 'Ana' can use an index on customer, even though the filter is outside the view.
Updating through a view
Simple views — one table, no aggregation, no DISTINCT — are updatable in many databases. SQLite does not support UPDATE on a view directly; use an INSTEAD OF trigger if you need that behaviour.
For anything with a join or an aggregate, treat the view as read-only. The database cannot know which base row an update to an averaged column should change.
A view for a join
Editable. Runs in your browser.
INSERT INTO customers (id, name, city) VALUES
(1, 'Ana', 'Pune'), (2, 'Ravi', 'Delhi'), (3, 'Meera', 'Mumbai');
ALTER TABLE orders ADD COLUMN customer_id INTEGER;
UPDATE orders SET customer_id = 1 WHERE customer IN ('Ana');
UPDATE orders SET customer_id = 2 WHERE customer = 'Ravi';
UPDATE orders SET customer_id = 3 WHERE customer = 'Meera';
CREATE VIEW order_details AS
SELECT o.id, c.name AS customer, c.city, o.status, o.amount_paise, o.placed_on
FROM orders AS o
JOIN customers AS c ON c.id = o.customer_id;
SELECT city, COUNT(*) AS orders
FROM order_details
GROUP BY city;
Press Run to see the output.
order_details turns a three-line join into a name. Reports read like questions about orders rather than about o and c.
Views versus CTEs
A CTE lives for one query. A view lives in the schema and can be used by any query, any report and any colleague.
Use a CTE when the step is internal to one query. Use a view when the step is a concept the organisation talks about: active subscriptions, overdue loans, paid orders.
Nesting views is allowed and worth doing sparingly. A view on a view on a view is hard to debug, and the query plan becomes hard to read. Two levels is usually enough; beyond that, a table or a scheduled summary is clearer.
Materialised views
A materialised view stores the result. Reads are fast because nothing is computed, and the data is as fresh as the last refresh.
SQLite has no materialised views. PostgreSQL and Oracle do; MySQL emulates them with a table and a scheduled refresh. The trade-off is always the same: speed at read time against staleness and refresh cost.
A practical pattern in SQLite is a summary table rebuilt on a schedule inside a transaction:
Editable. Runs in your browser.
INSERT INTO daily_totals (day, orders, revenue_paise)
SELECT placed_on, COUNT(*), SUM(amount_paise)
FROM orders
GROUP BY placed_on;
Press Run to see the output.
The table is explicit, the refresh is visible in code, and nothing pretends to be automatic.
Naming and review
Prefix or suffix views so they are recognisable: v_paid_orders or paid_orders, applied consistently. A schema where views and tables share a naming style is confusing when someone tries to insert into a view.
Review a view like code: it is a query with a name, and the name is a promise about its contents. A view called active_users that includes deactivated accounts is worse than no view, because people trust the name.
Creating a view that selects * from a table. When the table gains a column, the view’s shape changes silently and downstream reports may break. List the columns.
Create a view of overdue orders — pending for more than seven days against a fixed reference date of 2025-07-10 — and use it to list each customer’s overdue amount.
Show solution
Editable. Runs in your browser.
SELECT customer, SUM(amount_paise) / 100.0 AS overdue_rupees
FROM overdue_orders
GROUP BY customer;
Press Run to see the output.
Meera’s 2025-07-06 order is not yet seven days old on the reference date, so the view returns no rows. The fixed date keeps the example reproducible; in production the comparison uses date('now'), and the view definition stays the single source of the rule.
Recap
- A view stores a query, not data, and runs it on every select.
- Use views for shared definitions, CTEs for steps inside one query.
- List columns explicitly, and use a summary table when you need stored results.
The capstone brings constraints, indexes, transactions and views together in a small library database.
Do views have permissions?
Can a view reference another view?
Progress is saved in this browser only. No account, no email.