SovranCode
SQL: Query, Model, and Analyze Data Production data project
This device
Course contentsProduction data project · 32 topics

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsSelf joins and many-to-many dataSubqueries and correlated subqueriesUNION, INTERSECT, and EXCEPT

Designing reliable schemas

CREATE TABLE and schema designConstraints and data integrityNormalization and intentional denormalizationViews and materialized views

Writing and protecting data

INSERT, UPDATE, and DELETEUpserts and conflict handlingTransactions and savepointsIsolation, locks, and concurrency

Performance and administration

Indexes and access pathsEXPLAIN and query plansQuery tuning patternsBackups, restores, and migrations

Security and production workflow

Users, roles, and least privilegePreventing SQL injectionStored procedures, functions, and triggersProduction data project
SQL: Query, Model, and Analyze Data32 complete · 0 planned

Relational foundations

Databases, SQL, and the relational modelTables, rows, columns, and schemasData types and NULLPrimary keys, foreign keys, and relationships

Reading and filtering data

Your first SELECT queryConditions with WHERESorting, limiting, and distinct valuesText, dates, patterns, and conditional results

Reports, aggregation, and analytics

Aggregate functionsGROUP BY and HAVINGWindow functionsCommon table expressions

Combining related data

INNER, LEFT, RIGHT, and FULL joinsSelf joins and many-to-many dataSubqueries and correlated subqueriesUNION, INTERSECT, and EXCEPT

Designing reliable schemas

CREATE TABLE and schema designConstraints and data integrityNormalization and intentional denormalizationViews and materialized views

Writing and protecting data

INSERT, UPDATE, and DELETEUpserts and conflict handlingTransactions and savepointsIsolation, locks, and concurrency

Performance and administration

Indexes and access pathsEXPLAIN and query plansQuery tuning patternsBackups, restores, and migrations

Security and production workflow

Users, roles, and least privilegePreventing SQL injectionStored procedures, functions, and triggersProduction data project
PREVIOUS LESSONStored procedures, functions, and triggers
COURSE COMPLETESQL: Query, Model, and Analyze Data
Security and production workflow · Lesson 32 155 min

Production data project

This is one shop, not a new language. You already have tables, keys, joins, views, transactions, indexes, EXPLAIN, backups, sessions, binds, and triggers. The project is to put them on the same catalog and refuse to ship a checkout that only works in the happy-path screenshot.

Definition of done

A clerk can list the storefront without costs. Ada can see Ada's orders, not Grace's. A sale writes a header, lines, audit, and stock in one transaction. Lookups bind values. WHERE sku = SEARCH-es. Schema version is recorded. This runner is still SQLite: no GRANT, no file copy, no host binds—use the view, session_customer, params, and user_version as stand-ins.

Product brief

Workshop desk sells notebooks, pens, and mugs. Customers have a stable id and a unique email. The catalog has a sku, a display name, a live list price, and on-hand stock. An order has a customer and a date. Each line names a sku, a quantity, and the unit price at sale time. The website login may read names, not costs. Checkout is one all-or-nothing write.

SQL BROWSER RUNNER

Create the shop and list stored objects

Four tables, seed orders, user_version 1. Triggers come later so history is not replayed.

PRAGMA foreign_keys = ON;
PRAGMA user_version = 1;

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL
);

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  listed_cents INTEGER NOT NULL CHECK (listed_cents >= 0),
  stock INTEGER NOT NULL CHECK (stock >= 0)
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  placed_on TEXT NOT NULL
);

CREATE TABLE order_lines (
  order_id INTEGER NOT NULL REFERENCES orders(order_id),
  sku TEXT NOT NULL REFERENCES products(sku),
  qty INTEGER NOT NULL CHECK (qty > 0),
  unit_cents INTEGER NOT NULL CHECK (unit_cents >= 0),
  PRIMARY KEY (order_id, sku)
);

CREATE TABLE session_customer (
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id)
);

CREATE TABLE order_audit (
  order_id INTEGER NOT NULL,
  customer_id INTEGER NOT NULL
);

INSERT INTO customers VALUES
  (1, 'ada@example.com', 'Ada'),
  (2, 'grace@example.com', 'Grace'),
  (3, 'ada.obrien@example.com', 'Ada O''Brien'),
  (4, 'alonzo@example.com', 'Alonzo');

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 1200, 5),
  ('PEN-2', 'Gel pen', 400, 2),
  ('MUG-9', 'Mug', 900, 8);

INSERT INTO session_customer VALUES (1);

INSERT INTO orders VALUES (1, 1, '2026-09-01');
INSERT INTO order_lines VALUES (1, 'NB-1', 1, 1200);

INSERT INTO orders VALUES (2, 2, '2026-09-02');
INSERT INTO order_lines VALUES (2, 'PEN-2', 1, 400);

SELECT type, name
FROM sqlite_master
WHERE type IN ('table', 'view', 'trigger', 'index')
  AND name NOT LIKE 'sqlite_%'
ORDER BY type, name;

PRAGMA user_version;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Which tables exist, and what is user_version?

Read the shop the way a report does

Alonzo never ordered. A LEFT JOIN keeps him with spent 0. Ada spent 1200 on one notebook. Grace spent 400 on a pen. COUNT(DISTINCT o.order_id) stays an order count when a header has several lines. Revenue uses qty * unit_cents from the line, not today's list price.

SQL BROWSER RUNNER

Spend by customer, including Alonzo

LEFT JOIN orders and lines. COALESCE spent to 0.

PRAGMA foreign_keys = ON;
PRAGMA user_version = 1;

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL
);

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  listed_cents INTEGER NOT NULL CHECK (listed_cents >= 0),
  stock INTEGER NOT NULL CHECK (stock >= 0)
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  placed_on TEXT NOT NULL
);

CREATE TABLE order_lines (
  order_id INTEGER NOT NULL REFERENCES orders(order_id),
  sku TEXT NOT NULL REFERENCES products(sku),
  qty INTEGER NOT NULL CHECK (qty > 0),
  unit_cents INTEGER NOT NULL CHECK (unit_cents >= 0),
  PRIMARY KEY (order_id, sku)
);

CREATE TABLE session_customer (
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id)
);

CREATE TABLE order_audit (
  order_id INTEGER NOT NULL,
  customer_id INTEGER NOT NULL
);

INSERT INTO customers VALUES
  (1, 'ada@example.com', 'Ada'),
  (2, 'grace@example.com', 'Grace'),
  (3, 'ada.obrien@example.com', 'Ada O''Brien'),
  (4, 'alonzo@example.com', 'Alonzo');

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 1200, 5),
  ('PEN-2', 'Gel pen', 400, 2),
  ('MUG-9', 'Mug', 900, 8);

INSERT INTO session_customer VALUES (1);

INSERT INTO orders VALUES (1, 1, '2026-09-01');
INSERT INTO order_lines VALUES (1, 'NB-1', 1, 1200);

INSERT INTO orders VALUES (2, 2, '2026-09-02');
INSERT INTO order_lines VALUES (2, 'PEN-2', 1, 400);

SELECT
  c.display_name,
  COUNT(DISTINCT o.order_id) AS order_count,
  COALESCE(SUM(ol.qty * ol.unit_cents), 0) AS spent_cents
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
LEFT JOIN order_lines AS ol ON ol.order_id = o.order_id
GROUP BY c.customer_id, c.display_name
ORDER BY c.customer_id;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: What is Alonzo's order_count and spent_cents, and what did Ada spend?

The sold price is a different fact from the shelf price

Bump NB-1 to 1500. Order 1 still says 1200. That is the normalization lesson: do not store the live list price as if it were the receipt. If you reported revenue from products.listed_cents, yesterday's sale would change when marketing edits the catalog.

SQL BROWSER RUNNER

Raise the list price without rewriting the receipt

UPDATE products; SELECT the line.

PRAGMA foreign_keys = ON;
PRAGMA user_version = 1;

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL
);

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  listed_cents INTEGER NOT NULL CHECK (listed_cents >= 0),
  stock INTEGER NOT NULL CHECK (stock >= 0)
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  placed_on TEXT NOT NULL
);

CREATE TABLE order_lines (
  order_id INTEGER NOT NULL REFERENCES orders(order_id),
  sku TEXT NOT NULL REFERENCES products(sku),
  qty INTEGER NOT NULL CHECK (qty > 0),
  unit_cents INTEGER NOT NULL CHECK (unit_cents >= 0),
  PRIMARY KEY (order_id, sku)
);

CREATE TABLE session_customer (
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id)
);

CREATE TABLE order_audit (
  order_id INTEGER NOT NULL,
  customer_id INTEGER NOT NULL
);

INSERT INTO customers VALUES
  (1, 'ada@example.com', 'Ada'),
  (2, 'grace@example.com', 'Grace'),
  (3, 'ada.obrien@example.com', 'Ada O''Brien'),
  (4, 'alonzo@example.com', 'Alonzo');

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 1200, 5),
  ('PEN-2', 'Gel pen', 400, 2),
  ('MUG-9', 'Mug', 900, 8);

INSERT INTO session_customer VALUES (1);

INSERT INTO orders VALUES (1, 1, '2026-09-01');
INSERT INTO order_lines VALUES (1, 'NB-1', 1, 1200);

INSERT INTO orders VALUES (2, 2, '2026-09-02');
INSERT INTO order_lines VALUES (2, 'PEN-2', 1, 400);

UPDATE products SET listed_cents = 1500 WHERE sku = 'NB-1';

SELECT sku, listed_cents FROM products WHERE sku = 'NB-1';
SELECT order_id, sku, unit_cents FROM order_lines WHERE sku = 'NB-1';
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: What is listed_cents after the UPDATE, and what is unit_cents on order 1?

Storefront columns and a session are two locks

catalog_store is sku and name. The clerk SELECT does not need cost. The second query asks for order 2 (Grace) while the session row is Ada. The join to session_customer returns no rows. Bound ids without that join would still leak. PostgreSQL could enforce this with row-level security; here you write the join every time.

SQL BROWSER RUNNER

List the storefront, then refuse Grace's order

View without listed_cents. Order 2 plus session 1 should count as 0.

PRAGMA foreign_keys = ON;
PRAGMA user_version = 1;

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL
);

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  listed_cents INTEGER NOT NULL CHECK (listed_cents >= 0),
  stock INTEGER NOT NULL CHECK (stock >= 0)
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  placed_on TEXT NOT NULL
);

CREATE TABLE order_lines (
  order_id INTEGER NOT NULL REFERENCES orders(order_id),
  sku TEXT NOT NULL REFERENCES products(sku),
  qty INTEGER NOT NULL CHECK (qty > 0),
  unit_cents INTEGER NOT NULL CHECK (unit_cents >= 0),
  PRIMARY KEY (order_id, sku)
);

CREATE TABLE session_customer (
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id)
);

CREATE TABLE order_audit (
  order_id INTEGER NOT NULL,
  customer_id INTEGER NOT NULL
);

INSERT INTO customers VALUES
  (1, 'ada@example.com', 'Ada'),
  (2, 'grace@example.com', 'Grace'),
  (3, 'ada.obrien@example.com', 'Ada O''Brien'),
  (4, 'alonzo@example.com', 'Alonzo');

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 1200, 5),
  ('PEN-2', 'Gel pen', 400, 2),
  ('MUG-9', 'Mug', 900, 8);

INSERT INTO session_customer VALUES (1);

INSERT INTO orders VALUES (1, 1, '2026-09-01');
INSERT INTO order_lines VALUES (1, 'NB-1', 1, 1200);

INSERT INTO orders VALUES (2, 2, '2026-09-02');
INSERT INTO order_lines VALUES (2, 'PEN-2', 1, 400);

CREATE VIEW catalog_store AS
SELECT sku, name FROM products;

SELECT sku, name FROM catalog_store ORDER BY sku;

SELECT COUNT(*) AS grace_order_visible
FROM orders AS o
JOIN session_customer AS s ON s.customer_id = o.customer_id
WHERE o.order_id = 2;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Which catalog columns appear, and what is grace_order_visible?

Checkout is one transaction plus reactions

Ada buys a mug. You start a transaction, insert order 3, insert the line with unit_cents copied from the catalog at that moment (900), and commit. The audit trigger records the header. The stock trigger subtracts qty from MUG-9 (8 → 7). If the line insert failed, ROLLBACK would drop the header too. Seed orders were inserted before the triggers so historical lines do not decrement stock a second time.

SQL BROWSER RUNNER

Sell Ada a mug in one COMMIT

Triggers after seed. BEGIN, order 3, mug line, COMMIT.

PRAGMA foreign_keys = ON;
PRAGMA user_version = 1;

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL
);

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  listed_cents INTEGER NOT NULL CHECK (listed_cents >= 0),
  stock INTEGER NOT NULL CHECK (stock >= 0)
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  placed_on TEXT NOT NULL
);

CREATE TABLE order_lines (
  order_id INTEGER NOT NULL REFERENCES orders(order_id),
  sku TEXT NOT NULL REFERENCES products(sku),
  qty INTEGER NOT NULL CHECK (qty > 0),
  unit_cents INTEGER NOT NULL CHECK (unit_cents >= 0),
  PRIMARY KEY (order_id, sku)
);

CREATE TABLE session_customer (
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id)
);

CREATE TABLE order_audit (
  order_id INTEGER NOT NULL,
  customer_id INTEGER NOT NULL
);

INSERT INTO customers VALUES
  (1, 'ada@example.com', 'Ada'),
  (2, 'grace@example.com', 'Grace'),
  (3, 'ada.obrien@example.com', 'Ada O''Brien'),
  (4, 'alonzo@example.com', 'Alonzo');

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 1200, 5),
  ('PEN-2', 'Gel pen', 400, 2),
  ('MUG-9', 'Mug', 900, 8);

INSERT INTO session_customer VALUES (1);

INSERT INTO orders VALUES (1, 1, '2026-09-01');
INSERT INTO order_lines VALUES (1, 'NB-1', 1, 1200);

INSERT INTO orders VALUES (2, 2, '2026-09-02');
INSERT INTO order_lines VALUES (2, 'PEN-2', 1, 400);

CREATE TRIGGER trg_audit_order AFTER INSERT ON orders
BEGIN
  INSERT INTO order_audit (order_id, customer_id)
  VALUES (NEW.order_id, NEW.customer_id);
END;

CREATE TRIGGER trg_dec_stock AFTER INSERT ON order_lines
BEGIN
  UPDATE products
  SET stock = stock - NEW.qty
  WHERE sku = NEW.sku;
END;

BEGIN;

INSERT INTO orders (order_id, customer_id, placed_on)
VALUES (3, 1, '2026-09-19');

INSERT INTO order_lines (order_id, sku, qty, unit_cents)
VALUES (3, 'MUG-9', 1, 900);

COMMIT;

SELECT order_id, customer_id FROM order_audit;
SELECT sku, stock FROM products WHERE sku = 'MUG-9';
SELECT order_id, sku, qty, unit_cents FROM order_lines WHERE order_id = 3;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Who is on order_audit, what is MUG-9 stock, and which unit_cents landed?

Lookups still bind

Ada O'Brien's email is data. params stands in for the driver bind from the injection lesson. Join it. Do not build WHERE email = ' plus the form. Identifiers you cannot bind (sort keys) still go through an allowlist; this lookup only needs a value.

SQL BROWSER RUNNER

Find O'Brien through a params row

params.email is the bound form value.

PRAGMA foreign_keys = ON;
PRAGMA user_version = 1;

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL
);

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  listed_cents INTEGER NOT NULL CHECK (listed_cents >= 0),
  stock INTEGER NOT NULL CHECK (stock >= 0)
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  placed_on TEXT NOT NULL
);

CREATE TABLE order_lines (
  order_id INTEGER NOT NULL REFERENCES orders(order_id),
  sku TEXT NOT NULL REFERENCES products(sku),
  qty INTEGER NOT NULL CHECK (qty > 0),
  unit_cents INTEGER NOT NULL CHECK (unit_cents >= 0),
  PRIMARY KEY (order_id, sku)
);

CREATE TABLE session_customer (
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id)
);

CREATE TABLE order_audit (
  order_id INTEGER NOT NULL,
  customer_id INTEGER NOT NULL
);

INSERT INTO customers VALUES
  (1, 'ada@example.com', 'Ada'),
  (2, 'grace@example.com', 'Grace'),
  (3, 'ada.obrien@example.com', 'Ada O''Brien'),
  (4, 'alonzo@example.com', 'Alonzo');

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 1200, 5),
  ('PEN-2', 'Gel pen', 400, 2),
  ('MUG-9', 'Mug', 900, 8);

INSERT INTO session_customer VALUES (1);

INSERT INTO orders VALUES (1, 1, '2026-09-01');
INSERT INTO order_lines VALUES (1, 'NB-1', 1, 1200);

INSERT INTO orders VALUES (2, 2, '2026-09-02');
INSERT INTO order_lines VALUES (2, 'PEN-2', 1, 400);

CREATE TABLE params (
  email TEXT NOT NULL
);

INSERT INTO params VALUES ('ada.obrien@example.com');

SELECT c.customer_id, c.email, c.display_name
FROM customers AS c
JOIN params AS p ON p.email = c.email;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Which customer_id returned, and where would the host language put that email?

Prove the access path before you ship the lookup

sku is the primary key. EXPLAIN QUERY PLAN should SEARCH, not SCAN, for WHERE sku = 'NB-1'. Do not ANALYZE this tiny catalog; on a handful of rows the planner may prefer a scan after statistics. Production still needs the index (the PK is one) and a plan you re-read when the table grows.

SQL BROWSER RUNNER

EXPLAIN the sku lookup, then return the row

QUERY PLAN first so a failed SELECT cannot hide it.

PRAGMA foreign_keys = ON;
PRAGMA user_version = 1;

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL
);

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  listed_cents INTEGER NOT NULL CHECK (listed_cents >= 0),
  stock INTEGER NOT NULL CHECK (stock >= 0)
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  placed_on TEXT NOT NULL
);

CREATE TABLE order_lines (
  order_id INTEGER NOT NULL REFERENCES orders(order_id),
  sku TEXT NOT NULL REFERENCES products(sku),
  qty INTEGER NOT NULL CHECK (qty > 0),
  unit_cents INTEGER NOT NULL CHECK (unit_cents >= 0),
  PRIMARY KEY (order_id, sku)
);

CREATE TABLE session_customer (
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id)
);

CREATE TABLE order_audit (
  order_id INTEGER NOT NULL,
  customer_id INTEGER NOT NULL
);

INSERT INTO customers VALUES
  (1, 'ada@example.com', 'Ada'),
  (2, 'grace@example.com', 'Grace'),
  (3, 'ada.obrien@example.com', 'Ada O''Brien'),
  (4, 'alonzo@example.com', 'Alonzo');

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 1200, 5),
  ('PEN-2', 'Gel pen', 400, 2),
  ('MUG-9', 'Mug', 900, 8);

INSERT INTO session_customer VALUES (1);

INSERT INTO orders VALUES (1, 1, '2026-09-01');
INSERT INTO order_lines VALUES (1, 'NB-1', 1, 1200);

INSERT INTO orders VALUES (2, 2, '2026-09-02');
INSERT INTO order_lines VALUES (2, 'PEN-2', 1, 400);

EXPLAIN QUERY PLAN
SELECT sku, name, listed_cents
FROM products
WHERE sku = 'NB-1';

SELECT sku, name FROM products WHERE sku = 'NB-1';
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Does the plan SEARCH products, and which name comes back?

Version the schema and keep a logical copy

This box cannot copy a .db file. It can snapshot rows and bump user_version. CREATE TABLE products_backup AS SELECT * FROM products is a logical copy you could dump. integrity_check should return ok. In PostgreSQL you would use pg_dump and a migrations table; the idea is the same: know which schema you restored, and prove the copy.

SQL BROWSER RUNNER

Snapshot products and bump user_version

Backup table, version 2, counts, integrity_check.

PRAGMA foreign_keys = ON;
PRAGMA user_version = 1;

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL
);

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  listed_cents INTEGER NOT NULL CHECK (listed_cents >= 0),
  stock INTEGER NOT NULL CHECK (stock >= 0)
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  placed_on TEXT NOT NULL
);

CREATE TABLE order_lines (
  order_id INTEGER NOT NULL REFERENCES orders(order_id),
  sku TEXT NOT NULL REFERENCES products(sku),
  qty INTEGER NOT NULL CHECK (qty > 0),
  unit_cents INTEGER NOT NULL CHECK (unit_cents >= 0),
  PRIMARY KEY (order_id, sku)
);

CREATE TABLE session_customer (
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id)
);

CREATE TABLE order_audit (
  order_id INTEGER NOT NULL,
  customer_id INTEGER NOT NULL
);

INSERT INTO customers VALUES
  (1, 'ada@example.com', 'Ada'),
  (2, 'grace@example.com', 'Grace'),
  (3, 'ada.obrien@example.com', 'Ada O''Brien'),
  (4, 'alonzo@example.com', 'Alonzo');

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 1200, 5),
  ('PEN-2', 'Gel pen', 400, 2),
  ('MUG-9', 'Mug', 900, 8);

INSERT INTO session_customer VALUES (1);

INSERT INTO orders VALUES (1, 1, '2026-09-01');
INSERT INTO order_lines VALUES (1, 'NB-1', 1, 1200);

INSERT INTO orders VALUES (2, 2, '2026-09-02');
INSERT INTO order_lines VALUES (2, 'PEN-2', 1, 400);

CREATE TABLE products_backup AS
SELECT * FROM products;

PRAGMA user_version = 2;

SELECT COUNT(*) AS live_products FROM products;
SELECT COUNT(*) AS backup_products FROM products_backup;
PRAGMA user_version;
PRAGMA integrity_check;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: Do live and backup counts match, and what does integrity_check say?

A new column is a migration, not a surprise

reorder_at is the stock level that should page a clerk. ADD COLUMN ... DEFAULT 2 fills existing rows. PEN-2 is tighter, so backfill 3. Bump user_version so the next deploy knows this catalog is on 2. Rename-style changes still need a table rebuild, as in the backups lesson.

SQL BROWSER RUNNER

Add reorder_at and backfill pens

ALTER, UPDATE PEN-2, user_version 2.

PRAGMA foreign_keys = ON;
PRAGMA user_version = 1;

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL
);

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  listed_cents INTEGER NOT NULL CHECK (listed_cents >= 0),
  stock INTEGER NOT NULL CHECK (stock >= 0)
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  placed_on TEXT NOT NULL
);

CREATE TABLE order_lines (
  order_id INTEGER NOT NULL REFERENCES orders(order_id),
  sku TEXT NOT NULL REFERENCES products(sku),
  qty INTEGER NOT NULL CHECK (qty > 0),
  unit_cents INTEGER NOT NULL CHECK (unit_cents >= 0),
  PRIMARY KEY (order_id, sku)
);

CREATE TABLE session_customer (
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id)
);

CREATE TABLE order_audit (
  order_id INTEGER NOT NULL,
  customer_id INTEGER NOT NULL
);

INSERT INTO customers VALUES
  (1, 'ada@example.com', 'Ada'),
  (2, 'grace@example.com', 'Grace'),
  (3, 'ada.obrien@example.com', 'Ada O''Brien'),
  (4, 'alonzo@example.com', 'Alonzo');

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 1200, 5),
  ('PEN-2', 'Gel pen', 400, 2),
  ('MUG-9', 'Mug', 900, 8);

INSERT INTO session_customer VALUES (1);

INSERT INTO orders VALUES (1, 1, '2026-09-01');
INSERT INTO order_lines VALUES (1, 'NB-1', 1, 1200);

INSERT INTO orders VALUES (2, 2, '2026-09-02');
INSERT INTO order_lines VALUES (2, 'PEN-2', 1, 400);

ALTER TABLE products ADD COLUMN reorder_at INTEGER NOT NULL DEFAULT 2;

UPDATE products SET reorder_at = 3 WHERE sku = 'PEN-2';

PRAGMA user_version = 2;

SELECT sku, stock, reorder_at FROM products ORDER BY sku;
PRAGMA user_version;
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: What is reorder_at for NB-1 and PEN-2, and which user_version remains?

What you should be able to do

  • Draw customers, products, orders, and order_lines with keys and CHECKs.
  • Report with LEFT JOIN so missing orders stay visible.
  • Keep unit_cents as a snapshot.
  • Hide cost behind a view and filter by session.
  • Checkout in one transaction with audit and stock triggers.
  • Bind values, EXPLAIN a PK lookup, and version a backup or ALTER.

Independent lab: Ada buys a pen

PEN-2 has stock 2 and listed 400. Attach the decrement trigger, start a transaction, insert order 3 for session customer 1, insert one line at the live list price, commit. Show only Ada's new line (join session_customer), remaining stock, and PRAGMA integrity_check. Optional: BEFORE INSERT on order_lines that RAISEs when NEW.qty exceeds stock—run a failing qty last so this runner does not hide the successful SELECT.

SQL BROWSER RUNNER

Complete Ada's pen checkout

Seed shop is ready. You add trigger, transaction, and proof SELECTs.

PRAGMA foreign_keys = ON;
PRAGMA user_version = 1;

CREATE TABLE customers (
  customer_id INTEGER PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  display_name TEXT NOT NULL
);

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  listed_cents INTEGER NOT NULL CHECK (listed_cents >= 0),
  stock INTEGER NOT NULL CHECK (stock >= 0)
);

CREATE TABLE orders (
  order_id INTEGER PRIMARY KEY,
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id),
  placed_on TEXT NOT NULL
);

CREATE TABLE order_lines (
  order_id INTEGER NOT NULL REFERENCES orders(order_id),
  sku TEXT NOT NULL REFERENCES products(sku),
  qty INTEGER NOT NULL CHECK (qty > 0),
  unit_cents INTEGER NOT NULL CHECK (unit_cents >= 0),
  PRIMARY KEY (order_id, sku)
);

CREATE TABLE session_customer (
  customer_id INTEGER NOT NULL REFERENCES customers(customer_id)
);

CREATE TABLE order_audit (
  order_id INTEGER NOT NULL,
  customer_id INTEGER NOT NULL
);

INSERT INTO customers VALUES
  (1, 'ada@example.com', 'Ada'),
  (2, 'grace@example.com', 'Grace'),
  (3, 'ada.obrien@example.com', 'Ada O''Brien'),
  (4, 'alonzo@example.com', 'Alonzo');

INSERT INTO products VALUES
  ('NB-1', 'SQL notebook', 1200, 5),
  ('PEN-2', 'Gel pen', 400, 2),
  ('MUG-9', 'Mug', 900, 8);

INSERT INTO session_customer VALUES (1);

INSERT INTO orders VALUES (1, 1, '2026-09-01');
INSERT INTO order_lines VALUES (1, 'NB-1', 1, 1200);

INSERT INTO orders VALUES (2, 2, '2026-09-02');
INSERT INTO order_lines VALUES (2, 'PEN-2', 1, 400);

-- Checkout: Ada (session 1) buys 1 PEN-2 at today's listed_cents.
-- Add the stock trigger (after this seed, so history is not replayed).
-- BEGIN / INSERT order 3 / INSERT the line / COMMIT.
-- SELECT session-scoped lines, PEN-2 stock, and integrity_check.

SELECT sku, stock, listed_cents FROM products WHERE sku = 'PEN-2';
QUERY OUTPUT
Edit the query, predict the rows it will return, then run it.

Each run starts a fresh SQLite database in an isolated browser worker. Your edited SQL is saved on this device, but the database and its rows are discarded after the run.

Try this next: What is PEN-2 stock after qty 1, which order_id is Ada's, and did integrity_check return ok?

Common mistakes to avoid

  • Storing only listed_cents and calling it revenue.
  • One products row with repeating sku1, sku2 columns instead of order_lines.
  • Checkout as two autocommit statements so a crash leaves a header without lines.
  • Creating stock triggers before loading history, then wondering why seed stock went negative.
  • SELECT * for the website login, including cost.
  • Filtering by order_id from the URL without the session.
  • Pasting emails into SQL text because “it is just this form.”
  • Shipping a SCAN because the table is small today.
  • ALTER without user_version or a backup of the old shape.

Lesson review

  • I can model a shop with keys, CHECKs, and a line-item grain.
  • I can report with joins and keep sold prices as snapshots.
  • I can hide cost and enforce a session on customer reads.
  • I can checkout in one transaction with trigger side effects.
  • I can bind parameters and read a PK access path.
  • I can snapshot data and version a schema change.
KNOWLEDGE CHECK

Check the production workshop store

Answer all ten questions, then reopen the runner whose schema, session, checkout, or plan still feels unclear.

01What is the grain of order_lines?
02Why keep listed_cents on products and unit_cents on the line?
03What does the storefront view exist for?
04Why join session_customer on every customer-facing SELECT?
05How should the app send Ada's email into a lookup?
06Why wrap a new order header and its lines in one transaction?
07When does the stock trigger run?
08What should EXPLAIN QUERY PLAN show for WHERE sku = 'NB-1' on a PRIMARY KEY?
09What is a logical backup in this runner?
10What does a production SQL change include besides the new column?
PREVIOUS LESSONStored procedures, functions, and triggers
COURSE COMPLETESQL: Query, Model, and Analyze Data
ON THIS PAGEProduct briefReportsSold vs shelfView and sessionCheckoutBindsQuery planBackupMigrationIndependent labCommon mistakesKnowledge check
Course contents