SQL chapters

Chapter 14 of 17

Views and common table expressions

Try this in our SQLite Compiler →

The idea

A view stores a query definition rather than a copied result set. It is useful for naming a repeated read. A common table expression is local to a single statement. Neither is a package manager or an imported source file.

Worked example

DROP VIEW IF EXISTS passing;
DROP TABLE IF EXISTS scores;
CREATE TABLE scores (name TEXT, score INTEGER);
INSERT INTO scores VALUES ('Asha', 82), ('Ravi', 60);
CREATE VIEW passing AS SELECT name, score FROM scores WHERE score >= 70;
SELECT * FROM passing;

What to check

The view returns Asha/82. A later update to scores affects subsequent reads from the view because it evaluates the stored query.

Practice task

Update Ravi to 75 and query the view again. Explain why no refresh of a copied table is needed.

Check your understanding

Explain the result in your own words before changing the example. Change one thing at a time, compare the result with your prediction, and keep the version that demonstrates the concept clearly.

Try it yourself

Output
Run your project to see output here.
Ctrl/Cmd+Enter runs · Ctrl/Cmd+S downloads the project · Local auto-save is always on