Description

Login
Back

Advanced · 18 min

Views and query boundaries

Name reusable queries and keep application input separate from SQL.

Sample database

CREATE TABLE groups (id INTEGER PRIMARY KEY, label TEXT NOT NULL);
CREATE TABLE members (id INTEGER PRIMARY KEY, name TEXT NOT NULL, city TEXT, group_id INTEGER REFERENCES groups(id));
CREATE TABLE sessions (id INTEGER PRIMARY KEY, member_id INTEGER NOT NULL REFERENCES members(id), topic TEXT NOT NULL, minutes INTEGER NOT NULL CHECK(minutes > 0), completed INTEGER NOT NULL CHECK(completed IN (0, 1)), day TEXT NOT NULL);
INSERT INTO groups VALUES (1, 'Morning'), (2, 'Evening');
INSERT INTO members VALUES (1, 'Ada', 'Oslo', 1), (2, 'Bo', 'Rome', 1), (3, 'Cy', NULL, 2), (4, 'Dee', 'Oslo', 2);
INSERT INTO sessions VALUES
  (1, 1, 'Travel', 20, 1, '2026-01-01'),
  (2, 1, 'Food', 10, 0, '2026-01-02'),
  (3, 2, 'Travel', 30, 1, '2026-01-01'),
  (4, 2, 'Music', 15, 1, '2026-01-03'),
  (5, 3, 'Music', 25, 0, '2026-01-02'),
  (6, 3, 'Travel', 15, 1, '2026-01-04');

Create a reusable view

A view stores a query definition, not a snapshot of its rows. Querying it reads the current underlying data. Views can make reporting logic reusable, but they do not automatically improve performance or establish an authorization boundary.

CREATE VIEW completed_sessions AS SELECT id, member_id, minutes FROM sessions WHERE completed = 1;
SELECT COUNT(*) AS completed FROM completed_sessions;

Output

completed
4

Observe current data

The view reflects an update to its source table. In application code, bind user input as parameters instead of concatenating it into SQL. The editor deliberately accepts whole SQL programs; it is not an example of building an application search endpoint.

CREATE VIEW completed_sessions AS SELECT id FROM sessions WHERE completed = 1;
UPDATE sessions SET completed = 1 WHERE id = 2;
SELECT COUNT(*) AS completed FROM completed_sessions;

Output

completed
5

Put it into practice

  1. Read the sample tables and predict the result before running the query.
  2. Solve the task, compare the returned rows, and explain why the solution works.

Try it yourself

Code runs on this device. When you are signed in, drafts sync to your account.

Run queries on a fresh sample database. Table changes last for this run only; your query draft is saved separately. Results show column names followed by rows. NULL means a missing value.

Apply what you learned

Create a view containing only completed sessions and count its rows.

Show solution
CREATE VIEW completed_sessions AS SELECT id, member_id, minutes FROM sessions WHERE completed = 1;
SELECT COUNT(*) AS completed FROM completed_sessions;

The view exposes the intended subset, and its count includes only completed sessions.

Practice