lesson 63 Database engineering · advanced

Views: reusable query definitions

Define a reusable query as a view.

the idea

A normal view is a named query definition. It is reusable without storing a snapshot of the result. A view can simplify business logic, but it is not automatically faster or slower than the equivalent query. SQLite views are read-only unless INSTEAD OF triggers support writes.

tables in this lesson

The challenges use this data. Each column shows its type underneath; → marks a link to another table.

orders6 columns · 12 rows

idinteger · key	customer_idinteger	product_idinteger	quantityinteger	statustext	ordered_attext
101	1	1	2	paid	2025-01-01
102	2	3	1	paid	2025-01-02
103	1	2	1	paid	2025-01-05
104	3	5	2	pending	2025-01-08
105	4	4	5	paid	2025-02-01

showing 5 of 12 rows

products4 columns · 6 rows

idinteger · key	nametext	categorytext	pricereal
1	Notebook	Stationery	12
2	Desk lamp	Home	45
3	Keyboard	Tech	80
4	Pen set	Stationery	8
5	Headphones	Tech	120

showing 5 of 6 rows

example

The idea in one query. Press Run to see what it returns; running examples never affects your progress.

example.sql

CREATE VIEW paid_orders AS
SELECT id,customer_id,quantity
FROM orders
WHERE status='paid';
SELECT *
FROM paid_orders;

Watch for: A view is an abstraction, not a guarantee of materialization or caching.

build it step by step

Each step changes one thing. Run them in order and watch the result change.

1
Look at the rows a view will read. A view does not copy these rows. It saves a query, and selecting the view reads the current orders.
step-1.sql
sql›
```
SELECT id, customer_id, quantity, status
FROM orders;
```

Execute a complete script. All setup and the final result run together in one disposable database.

step-2.sql

CREATE VIEW paid_orders AS
SELECT id,customer_id,quantity
FROM orders
WHERE status='paid';
SELECT *
FROM paid_orders;