Capstone Project

What You'll Build

You will build an e-commerce warehouse: a small normalized database that records what the shop sells, who bought it, and what each order contained — and then the four reports a business actually asks of it. The work is done in stages, and each stage is a graded build you run in the page. Each one opens with the starter and its expected output, and asks for a plan before it hands you an editor — answer "Before you run — what's your plan for the first TODO?" and press Commit hypothesis, and the editor and its Run fix button appear. Then you write the SQL and compare your output against the specification before moving on.

This is the milestone every core lesson has been feeding. Designing tables, joining them without losing rows, rolling many rows up into one, guarding against missing data, wrapping a write in a transaction, and reading a query plan are each one lesson's worth of work; here they become one artifact. You arrive able to do all six separately (Database Design, Joins and Relationships, Aggregation and Grouping, Null Handling, Transactions, and Query Optimization). What is new is holding them at once, and noticing when one of them is quietly lying to you.

How this lesson runs

Every Run starts a fresh, empty database. Nothing survives between blocks, so every fence and every build block below creates its own tables and its own data in front of you. That is not repetition for its own sake — it is what makes each stage independently runnable, and it is why you can restart any stage without redoing the ones before it.

The data is deliberately small: six customers, seven products, seven orders. A report that is wrong is easier to catch when you can count the right answer by hand, and several of the stages below depend on you doing exactly that.

Money is stored as integer cents. A column declared REAL holds a float, and floats do not hold 19.99 exactly; totals built out of them drift. The whole warehouse therefore stores price_cents and unit_price_cents as integers, and formats for display only at the very end.

The milestone map

Each stage of this build is a capability an earlier lesson taught. If a stage fights you, the lesson named beside it is where the answer lives.

Required, and optional

Required — this is the project. Stages 1 through 4 below: the constrained schema, the revenue report, the reusable view with its ranked rollup and its index and plan note, and the transaction-safe stock deduction. Each has a graded block or a milestone check, and the definition of done at the end of the lesson is written against these four.

Optional — extensions for when the required four are solid. The five Challenge Exercises at the end, and anything from the four extension lessons that follow this one (recursive queries, JSON, and the two trigger lessons). None of them is needed to call this project finished.

Stage 1: The Schema

A warehouse needs four things: what categories exist, what products sit in them, what orders were placed, and what line items each order contained. Splitting them this way is the normalization decision — each fact is stored once, and the joins put it back together.

One design choice is worth stating before you write it, because it looks redundant and is not. order_items stores unit_price_cents even though products already has price_cents. Prices change; an order must always show what the customer actually paid. Copying the price at order time is not duplication, it is a historical record — and it is why a revenue report built from order_items stays correct after a price change, while one built from products silently rewrites history.

Here is the full schema, with every constraint it needs:

PRAGMA foreign_keys = ON;

CREATE TABLE categories (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL UNIQUE
);

CREATE TABLE customers (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    email TEXT NOT NULL UNIQUE,
    country TEXT NOT NULL DEFAULT 'US',
    joined_date TEXT NOT NULL
);

CREATE TABLE products (
    id INTEGER PRIMARY KEY,
    name TEXT NOT NULL,
    category_id INTEGER NOT NULL REFERENCES categories(id),
    price_cents INTEGER NOT NULL CHECK (price_cents > 0),
    stock INTEGER NOT NULL DEFAULT 0 CHECK (stock >= 0)
);

CREATE TABLE orders (
    id INTEGER PRIMARY KEY,
    customer_id INTEGER NOT NULL REFERENCES customers(id),
    order_date TEXT NOT NULL,
    status TEXT NOT NULL DEFAULT 'pending' CHECK (status IN ('pending','shipped','delivered','cancelled'))
);

CREATE TABLE order_items (
    id INTEGER PRIMARY KEY,
    order_id INTEGER NOT NULL REFERENCES orders(id),
    product_id INTEGER NOT NULL REFERENCES products(id),
    quantity INTEGER NOT NULL CHECK (quantity > 0),
    unit_price_cents INTEGER NOT NULL CHECK (unit_price_cents > 0)
);

INSERT INTO categories VALUES (1,'Electronics'),(2,'Furniture'),(3,'Books'),(4,'Clothing');
INSERT INTO customers VALUES
    (1,'Alice Johnson','alice@example.com','US','2024-03-15'),
    (2,'Bob Smith','bob@example.com','UK','2024-05-20'),
    (3,'Carol Williams','carol@example.com','FR','2024-06-10'),
    (4,'David Brown','david@example.com','JP','2024-08-01'),
    (5,'Eva Martinez','eva@example.com','US','2024-09-12'),
    (6,'Frank Lee','frank@example.com','DE','2024-11-05');
INSERT INTO products VALUES
    (1,'Laptop Pro',1,129999,45),
    (2,'Wireless Mouse',1,2999,200),
    (3,'Mechanical Keyboard',1,8999,120),
    (4,'Standing Desk',2,49999,25),
    (5,'Desk Lamp',2,3999,100),
    (6,'SQL Mastery',3,4499,500),
    (7,'Tech T-Shirt',4,2499,8);
INSERT INTO orders VALUES
    (1,1,'2025-01-05','delivered'),
    (2,2,'2025-01-08','delivered'),
    (3,1,'2025-01-12','shipped'),
    (4,3,'2025-01-15','delivered'),
    (5,5,'2025-02-01','delivered'),
    (6,2,'2025-02-08','shipped'),
    (7,1,'2025-02-14','pending');
INSERT INTO order_items VALUES
    (1,1,1,1,129999),
    (2,1,2,2,2999),
    (3,2,4,1,49999),
    (4,2,5,2,3999),
    (5,3,3,1,8999),
    (6,4,6,3,4499),
    (7,5,7,4,2499),
    (8,5,2,1,2999),
    (9,6,1,1,129999),
    (10,7,6,2,4499);

SELECT 'categories' AS tbl, COUNT(*) AS n FROM categories
UNION ALL SELECT 'customers', COUNT(*) FROM customers
UNION ALL SELECT 'products', COUNT(*) FROM products
UNION ALL SELECT 'orders', COUNT(*) FROM orders
UNION ALL SELECT 'order_items', COUNT(*) FROM order_items;

That closing count is the verification query for this stage: five named tables, five numbers, and if one of them is not what you expect the problem is in the seed, not in the reports you build next.

Two facts about this schema that the rest of the lesson depends on. David Brown and Frank Lee placed no orders at all — they are here so that a report which drops them is visibly wrong. And Clothing holds exactly one product, the Tech T-Shirt, with only 8 in stock; stage 4 uses that shortage.

The pragma is not decoration

PRAGMA foreign_keys = ON; is the first line for a reason. In this environment foreign-key enforcement starts off on every single Run, and a REFERENCES clause on a switched-off connection is documentation rather than a constraint: the orphan row goes in, no error, no warning. Every fence in this lesson that declares a foreign key turns enforcement on first, and so should every script you write outside it.

Build it: the integrity audit

The starter below is the same warehouse with the constraints stripped out of three tables, and it has already been fed four rows that should never have been accepted: a duplicate category name, a product priced at zero, a product filed under a category that does not exist, and an order whose status is not one of the four the business recognizes. The script runs, exits cleanly, and the five audit queries at the bottom report the damage.

Work TODO 1 through TODO 4 so that every audit count comes back 0 and only the two real categories survive. Note what the shape of the fix is: you add the constraint and you remove the row that only ever existed because the constraint was missing — with the rule in place, that row can no longer be written at all.

PRAGMA foreign_keys = ON;

CREATE TABLE categories (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL
);
CREATE TABLE products (
  id INTEGER PRIMARY KEY,
  name TEXT NOT NULL,
  category_id INTEGER NOT NULL,
  price_cents INTEGER NOT NULL,
  stock INTEGER NOT NULL DEFAULT 0
);
CREATE TABLE orders (
  id INTEGER PRIMARY KEY,
  customer TEXT NOT NULL,
  order_date TEXT NOT NULL,
  status TEXT NOT NULL DEFAULT 'pending'
);

-- TODO 1: no two categories may share a name. Electronics is currently in twice.
-- TODO 2: a product must cost something, and stock may not go below zero.
--         Ghost Chair is priced at 0 and must not be insertable.
-- TODO 3: a product's category_id must name a real category row.
--         Ghost Chair claims category 9, which does not exist.
-- TODO 4: an order's status must be one of pending, shipped, delivered, cancelled.
--         Bob's order is 'beamed-up'.
-- Then delete the three inserts below the marker that your new rules would refuse.
INSERT INTO categories VALUES (1,'Electronics'),(2,'Furniture');
INSERT INTO products VALUES (1,'Laptop Pro',1,129999,45),(2,'Desk Lamp',2,3999,100);
INSERT INTO orders VALUES (1,'Alice','2025-01-05','delivered');

INSERT INTO categories VALUES (3,'Electronics');
INSERT INTO products VALUES (4,'Ghost Chair',9,0,5);
INSERT INTO orders VALUES (2,'Bob','2025-01-08','beamed-up');

SELECT COUNT(*) AS categories FROM categories;
SELECT COUNT(*) AS duplicate_names FROM (SELECT name FROM categories GROUP BY name HAVING COUNT(*) > 1);
SELECT COUNT(*) AS bad_price FROM products WHERE price_cents <= 0;
SELECT COUNT(*) AS orphan_product FROM products p LEFT JOIN categories c ON c.id = p.category_id WHERE c.id IS NULL;
SELECT COUNT(*) AS bad_status FROM orders WHERE status NOT IN ('pending','shipped','delivered','cancelled');

2 row(s) affected
2 row(s) affected
1 row(s) affected
categories
----------
2
duplicate_names
---------------
0
bad_price
---------
0
orphan_product
--------------
0
bad_status
----------
0

Once it passes, try two variations and predict the result before you run each one. First, put back the INSERT INTO products VALUES (4,'Ghost Chair',9,0,5); line with all four constraints in place — it should now fail with Error: CHECK constraint failed: price_cents > 0, because the CHECK is evaluated before the foreign key. Second, delete the PRAGMA foreign_keys = ON; line and re-run with Ghost Chair's price corrected to 19999 — the orphan row goes back in and orphan_product reports 1 again, even though the REFERENCES clause is still sitting right there in the schema.

Stage 2: The Revenue Report

The first question any shop asks is where the money came from. That is a rollup over the line items: join order_items up to products and on to categories, multiply quantity by the price paid, and group.

The trap in this query is not the arithmetic. It is that a join decides which rows exist before the aggregate decides what they add up to, and the two mistakes look nothing alike in the output.

CREATE TABLE categories (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, category_id INTEGER NOT NULL, price_cents INTEGER NOT NULL);
CREATE TABLE order_items (id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL, product_id INTEGER NOT NULL, quantity INTEGER NOT NULL, unit_price_cents INTEGER NOT NULL);

INSERT INTO categories VALUES (1,'Electronics'),(2,'Furniture'),(3,'Books'),(4,'Clothing');
INSERT INTO products VALUES (1,'Laptop Pro',1,129999),(2,'Wireless Mouse',1,2999),(3,'Mechanical Keyboard',1,8999),(4,'Standing Desk',2,49999),(5,'Desk Lamp',2,3999),(6,'SQL Mastery',3,4499),(7,'Tech T-Shirt',4,2499);
INSERT INTO order_items VALUES (1,1,1,1,129999),(2,1,2,2,2999),(3,2,4,1,49999),(4,2,5,2,3999),(5,3,3,1,8999),(6,4,6,3,4499),(7,5,7,4,2499),(8,5,2,1,2999),(9,6,1,1,129999),(10,7,6,2,4499);

SELECT c.name AS category,
       COUNT(oi.id) AS line_items,
       COALESCE(SUM(oi.quantity * oi.unit_price_cents), 0) AS revenue_cents
FROM categories c
LEFT JOIN products p ON p.category_id = c.id
LEFT JOIN order_items oi ON oi.product_id = p.id
GROUP BY c.id, c.name
ORDER BY revenue_cents DESC, c.name;

Three things in that query are doing defensive work, and each one is the fix for a specific way the report goes wrong.

LEFT JOIN rather than JOIN, twice, because a category that sold nothing this period is still a category — and a report that drops it does not look broken, it looks like the shop has three categories. COUNT(oi.id) rather than COUNT(*), because a LEFT JOIN with nothing on the right still emits one row, and COUNT(*) counts that manufactured row as 1 while counting a column counts only the values that are really there. And COALESCE(..., 0), because SUM over no rows at all returns not zero but nothing — which prints as None and breaks every consumer downstream that expected a number.

ORDER BY revenue_cents DESC, c.name has a second sort key for a reason that is easy to skip: without it, two categories with the same revenue come back in whatever order the engine found convenient, and the report is not reproducible even though it looks stable.

CREATE TABLE categories (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE products (id INTEGER PRIMARY KEY, category_id INTEGER NOT NULL, name TEXT NOT NULL);
CREATE TABLE order_items (id INTEGER PRIMARY KEY, product_id INTEGER NOT NULL, quantity INTEGER NOT NULL, unit_price_cents INTEGER NOT NULL);

INSERT INTO categories VALUES (1,'Electronics'),(2,'Clothing'),(3,'Garden');
INSERT INTO products VALUES (1,1,'Laptop Pro'),(2,2,'Tech T-Shirt');
INSERT INTO order_items VALUES (1,1,1,129999),(2,2,4,2499);

SELECT c.name AS category,
     COUNT(*) AS line_items,
     SUM(oi.quantity * oi.unit_price_cents) AS revenue_cents
FROM categories c
JOIN products p ON p.category_id = c.id
JOIN order_items oi ON oi.product_id = p.id
GROUP BY c.id, c.name
ORDER BY c.name;

Debug it: the inflated order count

Here is the same warehouse from the customer's side: how many orders did each customer place, and how much did they spend? The query below runs, exits cleanly, and returns a table that looks entirely reasonable.

It is wrong in two separate ways, and one of them has the right number sitting next to it.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, order_date TEXT NOT NULL);
CREATE TABLE order_items (id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL, quantity INTEGER NOT NULL, unit_price_cents INTEGER NOT NULL);

INSERT INTO customers VALUES (1,'Alice Johnson'),(2,'Bob Smith'),(3,'Carol Williams');
INSERT INTO orders VALUES (1,1,'2025-01-05'),(2,2,'2025-01-08'),(3,1,'2025-01-12');
INSERT INTO order_items VALUES (1,1,1,129999),(2,1,2,2999),(3,2,1,49999),(4,2,2,3999),(5,3,1,8999);

SELECT cu.name AS customer,
     COUNT(*) AS orders,
     SUM(oi.quantity * oi.unit_price_cents) AS spend_cents
FROM customers cu
JOIN orders o ON o.customer_id = cu.id
JOIN order_items oi ON oi.order_id = o.id
GROUP BY cu.id, cu.name
ORDER BY cu.name;

3 row(s) affected
3 row(s) affected
5 row(s) affected
customer | orders | spend_cents
-------------------------------
Alice Johnson | 2 | 144996
Bob Smith | 1 | 57997
Carol Williams | 0 | 0

The monthly trend

The other half of a revenue report is time. The dates here are stored as YYYY-MM-DD text, so grouping by month is a prefix: substr(order_date, 1, 7) gives 2025-01, and text in that layout sorts correctly as text.

CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, order_date TEXT NOT NULL);
CREATE TABLE order_items (id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL, quantity INTEGER NOT NULL, unit_price_cents INTEGER NOT NULL);

INSERT INTO orders VALUES (1,1,'2025-01-05'),(2,2,'2025-01-08'),(3,1,'2025-01-12'),(4,3,'2025-01-15'),(5,5,'2025-02-01'),(6,2,'2025-02-08'),(7,1,'2025-02-14');
INSERT INTO order_items VALUES (1,1,1,129999),(2,1,2,2999),(3,2,1,49999),(4,2,2,3999),(5,3,1,8999),(6,4,3,4499),(7,5,4,2499),(8,5,1,2999),(9,6,1,129999),(10,7,2,4499);

SELECT substr(o.order_date, 1, 7) AS month,
       COUNT(DISTINCT o.id) AS orders,
       SUM(oi.quantity * oi.unit_price_cents) AS revenue_cents
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
GROUP BY substr(o.order_date, 1, 7)
ORDER BY month;

Note COUNT(DISTINCT o.id) again: the same row multiplication from the debug block is present here too, because this query also joins orders to their line items. Seven orders across two months, ten line items between them — count the orders per month by hand against the seed above and confirm the query agrees with you. That habit, predicting a row count and then checking it, is the single most useful debugging routine in SQL, because the language so rarely tells you when a count is wrong.

Now try two changes on the fence above — open it with its Open in Playground pill, then edit and Run Code — and say what each will do before you run it. Change one: drop DISTINCT from the count and rerun — each month's order count should rise to the number of line items in that month (6 and 4), while the revenue stays identical. Change two: change substr(o.order_date, 1, 7) to substr(o.order_date, 1, 4) in both the SELECT and the GROUP BY — the two months collapse into one 2025 row whose revenue is the sum of both.

Stage 3: A Reusable View, and What the Planner Does With It

A report you will run more than once belongs behind a name. A CTE names a query for the length of one statement; a view names it permanently, so the next query can select from category_revenue and not care how it is computed.

CREATE TABLE categories (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, category_id INTEGER NOT NULL, price_cents INTEGER NOT NULL);
CREATE TABLE order_items (id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL, product_id INTEGER NOT NULL, quantity INTEGER NOT NULL, unit_price_cents INTEGER NOT NULL);

INSERT INTO categories VALUES (1,'Electronics'),(2,'Furniture'),(3,'Books'),(4,'Clothing');
INSERT INTO products VALUES (1,'Laptop Pro',1,129999),(2,'Wireless Mouse',1,2999),(3,'Mechanical Keyboard',1,8999),(4,'Standing Desk',2,49999),(5,'Desk Lamp',2,3999),(6,'SQL Mastery',3,4499),(7,'Tech T-Shirt',4,2499);
INSERT INTO order_items VALUES (1,1,1,1,129999),(2,1,2,2,2999),(3,2,4,1,49999),(4,2,5,2,3999),(5,3,3,1,8999),(6,4,6,3,4499),(7,5,7,4,2499),(8,5,2,1,2999),(9,6,1,1,129999),(10,7,6,2,4499);

CREATE VIEW category_revenue AS
SELECT c.name AS category,
       COUNT(oi.id) AS line_items,
       COALESCE(SUM(oi.quantity * oi.unit_price_cents), 0) AS revenue_cents
FROM categories c
LEFT JOIN products p ON p.category_id = c.id
LEFT JOIN order_items oi ON oi.product_id = p.id
GROUP BY c.id, c.name;

SELECT category, revenue_cents FROM category_revenue WHERE revenue_cents > 20000 ORDER BY revenue_cents DESC;

The view holds no data. It is a stored query, re-evaluated every time you select from it, so it is always current and never stale — and it is read-only: you cannot insert through it, because the database has no way to know which of the three underlying tables a new row belongs in.

Ranking the rollup

A revenue table answers "how much"; a business asks "which is biggest". That is a window function: RANK() assigns a position across the rows the query already produced, without collapsing them the way a GROUP BY would.

CREATE TABLE categories (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, category_id INTEGER NOT NULL, price_cents INTEGER NOT NULL);
CREATE TABLE order_items (id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL, product_id INTEGER NOT NULL, quantity INTEGER NOT NULL, unit_price_cents INTEGER NOT NULL);

INSERT INTO categories VALUES (1,'Electronics'),(2,'Furniture'),(3,'Books'),(4,'Clothing');
INSERT INTO products VALUES (1,'Laptop Pro',1,129999),(2,'Wireless Mouse',1,2999),(3,'Mechanical Keyboard',1,8999),(4,'Standing Desk',2,49999),(5,'Desk Lamp',2,3999),(6,'SQL Mastery',3,4499),(7,'Tech T-Shirt',4,2499);
INSERT INTO order_items VALUES (1,1,1,1,129999),(2,1,2,2,2999),(3,2,4,1,49999),(4,2,5,2,3999),(5,3,3,1,8999),(6,4,6,3,4499),(7,5,7,4,2499),(8,5,2,1,2999),(9,6,1,1,129999),(10,7,6,2,4499);

WITH category_revenue AS (
    SELECT c.id AS category_id,
           c.name AS category,
           COALESCE(SUM(oi.quantity * oi.unit_price_cents), 0) AS revenue_cents
    FROM categories c
    LEFT JOIN products p ON p.category_id = c.id
    LEFT JOIN order_items oi ON oi.product_id = p.id
    GROUP BY c.id, c.name
)
SELECT category,
       revenue_cents,
       RANK() OVER (ORDER BY revenue_cents DESC, category) AS revenue_rank
FROM category_revenue
ORDER BY revenue_rank;

Two details are the required work here, and both are about determinism rather than about ranking.

The window's own ORDER BY carries two keys, revenue_cents DESC, category. Without the second, two categories on the same revenue would be ranked in whatever order the engine happened to produce them, and the numbers would be unstable across runs while looking perfectly stable in any one of them. A rank is an assertion about order, so the order it reads has to be total.

And the outer ORDER BY revenue_rank is separate from the window's. A window function computes a value; it does not sort the result. Leave the outer sort off and you get correct ranks in arbitrary row order, which is one of the easiest ways to ship a report that is right and unreadable.

Plan notes

The last required piece is knowing whether a query is doing something sensible. EXPLAIN QUERY PLAN prints how the engine intends to find the rows, and the difference an index makes is visible in one word.

CREATE TABLE order_items (id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL, product_id INTEGER NOT NULL, quantity INTEGER NOT NULL, unit_price_cents INTEGER NOT NULL);
INSERT INTO order_items VALUES (1,1,1,1,129999),(2,1,2,2,2999),(3,2,4,1,49999),(4,2,5,2,3999),(5,3,3,1,8999),(6,4,6,3,4499),(7,5,7,4,2499),(8,5,2,1,2999),(9,6,1,1,129999),(10,7,6,2,4499);

EXPLAIN QUERY PLAN SELECT id, quantity FROM order_items WHERE order_id = 5;

CREATE INDEX idx_order_items_order ON order_items(order_id);

EXPLAIN QUERY PLAN SELECT id, quantity FROM order_items WHERE order_id = 5;

Run it and read the detail column of the two plans. The first says SCAN order_items — with no index on order_id, the only way to find the matching rows is to look at every row in the table. The second says SEARCH order_items USING INDEX idx_order_items_order (order_id=?): the engine now goes straight to the matching rows through the index. SCAN means every row; SEARCH means a lookup.

Two honest caveats. On ten rows this is not a speed difference you can measure — the table fits in memory and both plans finish instantly; what changes is how the work grows as the table does, and the plan is where you see that before it hurts. And an index is not free: it is a second structure the database maintains, so every insert and update into order_items now has more work to do. Index the columns you filter and join on, not every column you have.

Stage 4: Changing Data Safely

Every report so far only read. The last required stage writes — and a write that touches more than one table is where a database either keeps its promises or quietly breaks them.

Where the values come from

This is also the first stage whose values would not be yours. Every literal in the statements below — the customer id, the product id, the quantity — is something a real checkout receives from outside: a cart, a form, an API request. In a real warehouse those never reach the query as text. The statement is written once with ? placeholders and the values are handed to the database driver separately, exactly as Filtering and Sorting and Inserting and Updating set out. The two caveats from those lessons apply here unchanged and are worth restating because this stage is where they bite: the binding happens inside the driver, so a helper that escapes a value and pastes it into your string is still string-building; and a placeholder stands for a value and never for an identifier, so a sort column or table name that genuinely comes from outside has to be checked against an allow-list you wrote by hand. This lane cannot demonstrate any of it. The Run button sends a script of literal SQL with no program around it, so there is no driver here to bind with — the literals below are how the statement looks after the driver has done its work, not how you would write it in an application.

Placing an order is three changes that must all happen or none: the order row, its line items, and the stock deduction. Here is what happens when they are not held together. The script below is a single transaction, and the stock deduction violates the stock >= 0 CHECK because there are only 8 T-shirts and the order is for 12:

PRAGMA foreign_keys = ON;
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, stock INTEGER NOT NULL CHECK (stock >= 0));
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer TEXT NOT NULL);
INSERT INTO products VALUES (7,'Tech T-Shirt',8);

BEGIN;
INSERT INTO orders VALUES (8,'David Brown');
UPDATE products SET stock = stock - 12 WHERE id = 7;
COMMIT;

SELECT id, name, stock FROM products WHERE id = 7;
SELECT COUNT(*) AS orders_after FROM orders;

The failing UPDATE reports an error:

Error: CHECK constraint failed: stock >= 0

Now read what the two SELECTs printed anyway. The stock is unchanged at 8, which is correct — the UPDATE was refused. But orders_after is 1: the order row was written, the COMMIT persisted it, and the warehouse now contains an order for twelve shirts that were never deducted from anything.

That is the fact worth taking from this stage. A failing statement inside BEGIN ... COMMIT aborts only itself; everything else in the transaction is still there when COMMIT runs, and COMMIT keeps it. All-or-nothing is not something the engine does for you here — it is something your script gets by issuing an explicit ROLLBACK when a check fails.

The order of a reservation

Before building one, get the sequence right. Reserving stock for an order is four steps that each depend on the one before: take the stock down, record what the pick left behind, check whether the shelf went too low, and then decide whether to keep the reservation or undo it.

The frame below opens the transaction and takes a savepoint, so the lines you order are only the work inside it.

PRAGMA foreign_keys = ON;
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, stock INTEGER NOT NULL CHECK (stock >= 0));
CREATE TABLE picks (id INTEGER PRIMARY KEY, product_id INTEGER NOT NULL REFERENCES products(id), stock_left INTEGER NOT NULL);
INSERT INTO products VALUES (7,'Tech T-Shirt',8);
BEGIN;
SAVEPOINT reserve;

Eight T-shirts are on the shelf, the transaction is open, and the savepoint is taken.

Build it: the guarded stock deduction

The starter below does the same three-part write, but the UPDATE now carries its own guard — AND stock >= 12 — so it cannot violate the CHECK. That turns a loud failure into a silent one: the UPDATE matches no rows, reports 0 row(s) affected, and the COMMIT writes the order anyway. Nothing errors. The warehouse ends up with 8 orders, 11 line items, and 8 shirts still on the shelf.

Your job is to make the script refuse to keep a write it could not complete.

PRAGMA foreign_keys = ON;
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, stock INTEGER NOT NULL CHECK (stock >= 0));
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL, order_date TEXT NOT NULL);
CREATE TABLE order_items (id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES orders(id), product_id INTEGER NOT NULL REFERENCES products(id), quantity INTEGER NOT NULL);

INSERT INTO products VALUES (7,'Tech T-Shirt',8);
INSERT INTO orders VALUES (1,1,'2025-01-05'),(2,2,'2025-01-08'),(3,1,'2025-01-12'),(4,3,'2025-01-15'),(5,5,'2025-02-01'),(6,2,'2025-02-08'),(7,1,'2025-02-14');
INSERT INTO order_items VALUES (1,1,7,1),(2,2,7,1),(3,3,7,1),(4,4,7,1),(5,5,7,1),(6,6,7,1),(7,7,7,1),(8,1,7,1),(9,2,7,1),(10,3,7,1);

-- TODO: the UPDATE below is guarded, so it changes 0 rows when stock is short.
--       The COMMIT then keeps the order and the line item anyway.
--       Replace the COMMIT so the whole attempt is discarded instead.
--       Expected after your change: stock 8, orders_after 7, items_after 10.
BEGIN;
INSERT INTO orders VALUES (8, 4, '2025-02-20');
INSERT INTO order_items VALUES (11, 8, 7, 12);
UPDATE products SET stock = stock - 12 WHERE id = 7 AND stock >= 12;
COMMIT;

SELECT id, name, stock FROM products WHERE id = 7;
SELECT COUNT(*) AS orders_after FROM orders;
SELECT COUNT(*) AS items_after FROM order_items;

1 row(s) affected
7 row(s) affected
10 row(s) affected
1 row(s) affected
1 row(s) affected
0 row(s) affected
id | name | stock
-----------------
7 | Tech T-Shirt | 8
orders_after
------------
7
items_after
-----------
10

Once it passes, try two variations and predict each before running. First, change the order quantity from 12 to 4 in both the line item and the UPDATE, and keep ROLLBACK — the UPDATE now matches its row and reports 1 row(s) affected, but the rollback still throws the whole thing away, so the final numbers are unchanged at 8, 7 and 10. That is the check you want: a correct transaction must also be able to commit. Second, make that same quantity change and switch back to COMMIT — now the deduction is real and the three final queries report stock 4, 8 orders and 11 items.

Transfer: the same report in another engine

CREATE TABLE categories (id INTEGER PRIMARY KEY, name TEXT NOT NULL);
CREATE TABLE products (id INTEGER PRIMARY KEY, category_id INTEGER NOT NULL, name TEXT NOT NULL);
CREATE TABLE order_items (id INTEGER PRIMARY KEY, product_id INTEGER NOT NULL, quantity INTEGER NOT NULL, unit_price_cents INTEGER NOT NULL);

INSERT INTO categories VALUES (1,'Electronics'),(2,'Clothing'),(3,'Garden');
INSERT INTO products VALUES (1,1,'Laptop Pro'),(2,2,'Tech T-Shirt');
INSERT INTO order_items VALUES (1,1,1,129999),(2,2,4,2499);

SELECT c.name AS category,
     p.name AS product,
     COALESCE(SUM(oi.quantity * oi.unit_price_cents), 0) AS revenue_cents
FROM categories c
LEFT JOIN products p ON p.category_id = c.id
LEFT JOIN order_items oi ON oi.product_id = p.id
GROUP BY c.id;

Challenge Exercises

These are optional. Each is a question the warehouse can answer that the required stages did not ask, and each needs a fresh database — so start from the starter fence below, which is a complete, runnable warehouse you can paste your query underneath.

PRAGMA foreign_keys = ON;
CREATE TABLE categories (id INTEGER PRIMARY KEY, name TEXT NOT NULL UNIQUE);
CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT NOT NULL, country TEXT NOT NULL, joined_date TEXT NOT NULL);
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT NOT NULL, category_id INTEGER NOT NULL REFERENCES categories(id), price_cents INTEGER NOT NULL CHECK (price_cents > 0), stock INTEGER NOT NULL CHECK (stock >= 0));
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER NOT NULL REFERENCES customers(id), order_date TEXT NOT NULL, status TEXT NOT NULL);
CREATE TABLE order_items (id INTEGER PRIMARY KEY, order_id INTEGER NOT NULL REFERENCES orders(id), product_id INTEGER NOT NULL REFERENCES products(id), quantity INTEGER NOT NULL CHECK (quantity > 0), unit_price_cents INTEGER NOT NULL CHECK (unit_price_cents > 0));

INSERT INTO categories VALUES (1,'Electronics'),(2,'Furniture'),(3,'Books'),(4,'Clothing');
INSERT INTO customers VALUES (1,'Alice Johnson','US','2024-03-15'),(2,'Bob Smith','UK','2024-05-20'),(3,'Carol Williams','FR','2024-06-10'),(4,'David Brown','JP','2024-08-01'),(5,'Eva Martinez','US','2024-09-12'),(6,'Frank Lee','DE','2024-11-05');
INSERT INTO products VALUES (1,'Laptop Pro',1,129999,45),(2,'Wireless Mouse',1,2999,200),(3,'Mechanical Keyboard',1,8999,120),(4,'Standing Desk',2,49999,25),(5,'Desk Lamp',2,3999,100),(6,'SQL Mastery',3,4499,500),(7,'Tech T-Shirt',4,2499,8);
INSERT INTO orders VALUES (1,1,'2025-01-05','delivered'),(2,2,'2025-01-08','delivered'),(3,1,'2025-01-12','shipped'),(4,3,'2025-01-15','delivered'),(5,5,'2025-02-01','delivered'),(6,2,'2025-02-08','shipped'),(7,1,'2025-02-14','pending');
INSERT INTO order_items VALUES (1,1,1,1,129999),(2,1,2,2,2999),(3,2,4,1,49999),(4,2,5,2,3999),(5,3,3,1,8999),(6,4,6,3,4499),(7,5,7,4,2499),(8,5,2,1,2999),(9,6,1,1,129999),(10,7,6,2,4499);

-- The query below confirms the warehouse loaded: it should report
-- 4 categories, 6 customers, 7 products, 7 orders and 10 line items.
-- Replace it with your challenge query once you have seen those numbers.
SELECT (SELECT COUNT(*) FROM categories)  AS categories,
       (SELECT COUNT(*) FROM customers)   AS customers,
       (SELECT COUNT(*) FROM products)    AS products,
       (SELECT COUNT(*) FROM orders)      AS orders,
       (SELECT COUNT(*) FROM order_items) AS line_items;

1. Products bought together: find every pair of distinct products that appear in the same order, with how many orders each pair shares. A self-join of order_items to itself on order_id, with a condition that keeps each pair only once.
2. Signup cohorts: group customers by the month they joined and report how many customers and how many orders each cohort has produced. Customers with no orders must still be counted in their cohort.
3. Running total by day: report daily revenue alongside a cumulative total across the whole period, using a window function with a running frame.
4. Stock cover: for each product that has sold at least one unit, report its remaining stock, its total units sold across the two months in the data, and its stock cover in months — stock divided by the monthly sales rate, which is units sold divided by 2. Keep only the products whose cover is below 6 months. Expected result: exactly one row, the Tech T-Shirt, with stock 8, 4 units sold and a cover of 4.0 months — everything else in this dataset is at 45 months or more, because the shop sells a handful of units of each and the warehouse is deliberately overstocked. Watch the divisor, because integer division does not fail loudly here: the two products with a single unit sold have a rate of 1 / 2, which integer arithmetic evaluates to 0, and stock / 0 returns None rather than raising — so those products come back with a null cover and quietly fall out of any threshold comparison. Writing the rate as SUM(oi.quantity) / 2.0 keeps it a float and the problem disappears.
5. Top customer per country: for each country, the customer who spent the most, using a ranking window function partitioned by country. Decide deliberately what your query should do about a country whose customers have spent nothing.

Definition of done

The project is finished when all seven of these hold, checked by running them rather than assumed:

1. The schema refuses bad data. Adding a product priced at 0, a product in a category that does not exist, or an order with an invented status all fail rather than being stored.
2. The revenue report has as many rows as the business has categories. Four categories in, four rows out, with the one that sold nothing reporting 0 and not None.
3. No count is a line-item count in disguise. For every report that joins orders to their line items, the order count matches the number you get counting the seed data by hand.
4. The view answers the same as the query it replaces. Selecting from category_revenue and running the underlying query give the same rows.
5. The ranking is reproducible. The ranked rollup returns the same ranks on every run, because the window's own ORDER BY carries a tiebreak and the outer query sorts by the rank it produced.
6. An index shows up in the plan. EXPLAIN QUERY PLAN reports SEARCH with the index named where before it reported SCAN.
7. A failed write leaves nothing behind. After a transaction whose deduction could not apply, the order count and the line-item count are exactly what they were before it started.

If any of the seven fails, that is the next thing to fix — not a new report.

Reflect before you extend

Spend ten minutes on these before adding anything. Writing the answers down is worth more than thinking them, because the vague ones become visible on the page.

• Where did you put each rule, and why there? The stock floor is enforced by a CHECK in the schema and again by a WHERE on the UPDATE. Is that duplication or defence in depth — and what breaks if you remove either one?
• Which tradeoff would you make differently now? Storing unit_price_cents on every line item duplicates a number that is usually the same as the product's current price. What does that buy, what does it cost, and what would have to be true for the other choice to be right?
• What was hardest to get right? For most people it is the inflated order count, because it has no symptom. Was the difficulty in the concept or in noticing there was a problem at all — and which check would have caught it sooner?
• What would you change to support two shops? Follow a shop_id through the five tables and notice which reports need it in their GROUP BY. The ones that do not move are the ones whose boundaries you drew well.

Key Takeaways

• A normalized schema stores each fact once and puts it back together with joins; the exception worth making deliberately is a price copied at order time, which is a historical record rather than duplication
• Constraints are cheaper than defensive queries: a rule in the schema refuses the bad row, while a missing rule means every later report has to work around it
• REFERENCES enforces nothing unless PRAGMA foreign_keys = ON; runs first, and in this environment it starts off on every Run
• A join decides which rows exist and an aggregate decides what they say, so a wrong join type and a wrong count function produce completely different wrong reports — check a report's row count against the table it claims to describe
• A view names a query permanently and holds no data; an index changes how the engine reaches rows, and EXPLAIN QUERY PLAN is where you see SCAN become SEARCH
• Inside BEGIN and COMMIT, a failing statement aborts only itself — atomicity comes from your script issuing ROLLBACK, and a conditional write that matched nothing is a success the database will happily keep

Pro Tip: Real warehouses also carry shipping addresses, payment methods, discount codes, reviews and inventory movements. The schema here is a foundation to extend, and the same three questions apply to every new table: which facts belong in it, which constraints make its bad rows impossible, and which existing report's row count changes when it arrives.

Congratulations!

You've completed the core SQL curriculum. From your first SELECT to a warehouse you designed, constrained, seeded, reported on, indexed and wrote to safely, you now have the skills to build a database and get real answers out of it. The best way to solidify them is to keep building — pick a dataset, design the schema, seed it, and start asking questions.

Next Steps

With the capstone complete, the remaining lessons are optional extensions: specialized techniques you can take in any order, as the work demands them. The core path is finished without them. The next one covers recursive queries — traversing hierarchical data like org charts, category trees, and threaded comments with a single statement.