Final Project

Reviewed & published by Brayan K

This is your capstone. You'll build BookNook — a small but complete bookstore database — from an empty editor to indexed, transactional, report-ready SQL. Every concept from the course shows up here, assembled into one real system you design and query yourself.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Build

• A 4-table schema with PKs, FKs, and CHECK constraints
• Seed data inserted in the correct FK order
• Core reports: revenue per category and top customers
• Indexes for the hot queries (and reading an EXPLAIN plan)
• A reusable view plus a safe place-an-order transaction
• A window-function best-seller ranking using a CTE

The Plan: 6 Milestones

You're building a bookstore. Customers place orders; each order contains one or more books (the line items). Here's the path from empty database to finished system:

• Design the schema — four tables wired together with keys and constraints.
• Seed sample data — insert rows in the right order.
• Core queries — revenue per category and top customers.
• Indexes — speed up the hot paths, then read an EXPLAIN plan.
• A view + a transaction — reuse a report, place an order safely.
• Advanced touch — rank best-sellers with a window function + CTE.

Milestone 1 — Design the Schema

A good schema is the foundation everything else stands on. You'll create four tables. A primary key (PK) is the column that uniquely identifies a row; a foreign key (FK) is a column that points at another table's PK, which is how the database knows an order belongs to a customer.

Think of order_items as the receipt lines. The order is the receipt; each line says "1 copy of Dune". The line links the receipt (order_id) to the book (product_id) — exactly what a foreign key does.

Notice the guardrails: NOT NULL forbids blanks, UNIQUE stops duplicate emails, CHECK rejects nonsense like a negative price, and REFERENCES enforces that every order points at a real customer.

-- MILESTONE 1 — Build the BookNook bookstore schema
-- Four tables, linked by primary keys (PK) and foreign keys (FK).

-- customers: one row per shopper. The PK uniquely identifies each row.
CREATE TABLE customers (
    customer_id   INTEGER PRIMARY KEY,          -- PK: unique id for each customer
    full_name     TEXT    NOT NULL,             -- NOT NULL = required, no blanks
    email         TEXT    NOT NULL UNIQUE,       -- UNIQUE = no two customers share one
    joined_on     DATE    NOT NULL
);

-- products: the books you sell. category groups them for reporting.
CREATE TABLE products (
    product_id    INTEGER PRIMARY KEY,
    title         TEXT    NOT NULL,
    category      TEXT    NOT NULL,
    price         REAL    NOT NULL CHECK (price >= 0),  -- CHECK rejects bad data
    stock         INTEGER NOT NULL DEFAULT 0            -- DEFAULT fills it in for you
);

-- orders: one row per order. customer_id is a FK back to customers.
CREATE TABLE orders (
    order_id      INTEGER PRIMARY KEY,
    customer_id   INTEGER NOT NULL REFERENCES customers(customer_id),  -- FK
    ordered_on    DATE    NOT NULL,
    status        TEXT    NOT NULL DEFAULT 'pending'
                  CHECK (status IN ('pending','shipped','delivered'))   -- only these 3
);

-- order_items: the line items. This is the JOIN table linking orders to products.
CREATE TABLE order_items (
    order_id      INTEGER NOT NULL REFERENCES orders(order_id),
    product_id    INTEGER NOT NULL REFERENCES products(product_id),
    quantity      INTEGER NOT NULL CHECK (quantity > 0),
    PRIMARY KEY (order_id, product_id)           -- a composite PK: one row per book per order
);

-- ✅ Expected result: 4 tables created, no rows yet. Seed them in Milestone 2.

Milestone 2 — Seed Sample Data

Empty tables can't be queried meaningfully, so add rows. The one rule that trips everyone up: insert parents before children. A foreign key can only point at a row that already exists, so customers and products must exist before the orders that reference them, and orders before their items.

-- MILESTONE 2 — Insert sample rows so we have something to query.
-- Insert PARENTS before CHILDREN: customers + products first, then orders,
-- then order_items (a FK can only point at a row that already exists).

INSERT INTO customers (customer_id, full_name, email, joined_on) VALUES
    (1, 'Ada Lovelace',   '[email protected]',   '2025-01-10'),
    (2, 'Alan Turing',    '[email protected]',  '2025-02-02'),
    (3, 'Grace Hopper',   '[email protected]', '2025-02-20');

INSERT INTO products (product_id, title, category, price, stock) VALUES
    (10, 'The SQL Cookbook',     'Tech',    39.00, 120),
    (11, 'Clean Code',          'Tech',    32.50, 60),
    (12, 'Dune',                'SciFi',   18.00, 200),
    (13, 'Project Hail Mary',   'SciFi',   22.00, 75),
    (14, 'The Pragmatic Coder', 'Tech',    41.00, 40);

INSERT INTO orders (order_id, customer_id, ordered_on, status) VALUES
    (100, 1, '2025-03-01', 'delivered'),
    (101, 1, '2025-03-15', 'shipped'),
    (102, 2, '2025-03-18', 'delivered'),
    (103, 3, '2025-04-02', 'pending');

INSERT INTO order_items (order_id, product_id, quantity) VALUES
    (100, 10, 1),
    (100, 12, 2),
    (101, 11, 1),
    (102, 13, 3),
    (102, 10, 1),
    (103, 14, 1);

-- ✅ Expected result: 3 customers, 5 products, 4 orders, 6 order items.

Milestone 3 — Core Queries

Now the payoff: answering real business questions. Both reports below JOIN tables back together and use an aggregate (SUM, COUNT) with GROUP BY to collapse many rows into one summary row per group.

-- MILESTONE 3a — Revenue per category
-- JOIN three tables, multiply price x quantity, then total it per category.

SELECT
    p.category,
    SUM(p.price * oi.quantity)  AS revenue   -- money earned per book line
FROM order_items oi
JOIN products p ON p.product_id = oi.product_id   -- attach the book's price + category
GROUP BY p.category                               -- one output row per category
ORDER BY revenue DESC;

-- ✅ Expected result:
-- category | revenue
-- Tech | 151.5
-- SciFi | 102

-- MILESTONE 3b — Top customers by amount spent
-- Walk customers → orders → order_items → products to add up each spend.

SELECT
    c.full_name,
    COUNT(DISTINCT o.order_id)   AS orders,      -- how many orders they placed
    SUM(p.price * oi.quantity)   AS total_spent  -- their lifetime spend
FROM customers c
JOIN orders       o  ON o.customer_id = c.customer_id
JOIN order_items  oi ON oi.order_id   = o.order_id
JOIN products     p  ON p.product_id  = oi.product_id
GROUP BY c.customer_id, c.full_name
ORDER BY total_spent DESC;

-- ✅ Expected result:
-- full_name | orders | total_spent
-- Ada Lovelace | 2 | 107.5
-- Alan Turing | 1 | 105
-- Grace Hopper | 1 | 41

Your Turn #1: average per customer

The joins are written for you. Fill in the two ___ blanks: the aggregate that means "average", and the column to group by. The expected shape is in the comments.

-- 🎯 YOUR TURN #1 — average order value per customer
-- Goal: for each customer show their name and the AVERAGE money per order.
-- The joins are done for you. Fill in the two blanks.

SELECT
    c.full_name,
    ___(p.price * oi.quantity)   AS avg_per_line   -- 👉 the aggregate that means "average"
FROM customers c
JOIN orders       o  ON o.customer_id = c.customer_id
JOIN order_items  oi ON oi.order_id   = o.order_id
JOIN products     p  ON p.product_id  = oi.product_id
GROUP BY c.customer_id, ___;                        -- 👉 group by the same name column you selected

-- ✅ Expected result: 3 rows, one per customer, each with an avg_per_line value.
--    (Hint: the missing aggregate is AVG.)

Milestone 4 — Add Indexes for the Hot Queries

As data grows, the queries you run most often need to be fast. An index is a sorted lookup structure — like the index at the back of a book — that lets the database jump straight to matching rows instead of scanning every one. Index the columns you filter on (WHERE) and join on (ON).

EXPLAIN (or EXPLAIN QUERY PLAN in SQLite) shows you the database's plan for a query without running it. Seeing "USING INDEX" instead of "SCAN" tells you the index is doing its job.

-- MILESTONE 4 — Speed up the hot queries with indexes.
-- An index is a lookup structure (like a book's index) the database can scan
-- instead of reading every row. Index the columns you FILTER or JOIN on.

-- We JOIN order_items to orders on order_id and to products on product_id a LOT:
CREATE INDEX idx_items_order   ON order_items(order_id);
CREATE INDEX idx_items_product ON order_items(product_id);

-- We frequently look up orders by customer and filter by status:
CREATE INDEX idx_orders_customer ON orders(customer_id);
CREATE INDEX idx_orders_status   ON orders(status);

-- Did it help? Ask the planner with EXPLAIN — it shows HOW the query runs
-- without changing your data. Look for an index scan instead of a full scan:
EXPLAIN QUERY PLAN
SELECT * FROM orders WHERE customer_id = 1;

-- ✅ Expected: the plan now mentions "USING INDEX idx_orders_customer"
--    instead of "SCAN orders" (a slow full-table read).
--
-- Read the LAST column, "detail" — that is the part SQLite documents and
-- the part this lesson is about. The third column is literally called
-- "notused": it is internal scratch space, and different SQLite builds
-- put different numbers in it. Pressing Run here shows one value; the
-- sqlite3 command line on your machine may show another. Neither is
-- wrong, so this block claims no exact row.

Milestone 5 — A View and a Transaction

A view saves a SELECT under a name so you can reuse a complex report as if it were a simple table — it stores no data, just the query. A transaction bundles several writes so they either all succeed or all get undone; that all-or-nothing property is called atomicity.

Placing an order touches three tables (create the order, add the items, lower the stock). Without a transaction, a crash halfway through leaves you with an order that sold phantom stock. BEGIN … COMMIT makes those three changes one indivisible unit; ROLLBACK throws them all away.

-- MILESTONE 5a — A VIEW: save a query under a name and reuse it.
-- A view is a stored SELECT. It holds no data of its own — it re-runs each
-- time you query it, so it's always up to date.

CREATE VIEW order_totals AS
SELECT
    o.order_id,
    o.customer_id,
    o.status,
    SUM(p.price * oi.quantity)  AS order_total
FROM orders o
JOIN order_items oi ON oi.order_id   = o.order_id
JOIN products    p  ON p.product_id  = oi.product_id
GROUP BY o.order_id, o.customer_id, o.status;

-- Now query it like any table:
SELECT * FROM order_totals WHERE status = 'delivered';

-- ✅ Expected result: order 100 and order 102 with their totals.

-- MILESTONE 5b — A TRANSACTION: place an order atomically.
-- A transaction groups statements so they ALL succeed or ALL roll back.
-- "Atomic" = no half-finished state. Perfect for "take stock + record sale".

BEGIN TRANSACTION;

-- 1) Create the order header
INSERT INTO orders (order_id, customer_id, ordered_on, status)
VALUES (104, 3, '2025-04-10', 'pending');

-- 2) Add a line item: 2 copies of "Clean Code" (product 11)
INSERT INTO order_items (order_id, product_id, quantity)
VALUES (104, 11, 2);

-- 3) Decrement stock to match the sale
UPDATE products SET stock = stock - 2 WHERE product_id = 11;

COMMIT;   -- make all three changes permanent together.
-- If any step had failed, you'd run ROLLBACK and the order never happened.

-- ✅ Expected result: order 104 exists, has 1 line item, and "Clean Code"
--    stock dropped from 60 to 58 — all or nothing.

Your Turn #2: undo with ROLLBACK

Fill in the two ___ blanks so the bad insert is thrown away and order 999 never exists. One blank starts the transaction, the other cancels it.

-- 🎯 YOUR TURN #2 — undo a mistake with ROLLBACK
-- You start a transaction, realise the quantity is wrong, and want to abandon it.
-- Fill in the two blanks so NOTHING is saved.

___;   -- 👉 the keyword that STARTS a transaction (two words)

INSERT INTO orders (order_id, customer_id, ordered_on, status)
VALUES (999, 1, '2025-04-11', 'pending');

-- Oops — wrong order. Throw the whole thing away:
___;   -- 👉 the keyword that CANCELS everything since BEGIN

-- ✅ Expected result: order 999 does NOT exist afterwards.
--    SELECT * FROM orders WHERE order_id = 999;  -- returns 0 rows.

Milestone 6 — Advanced Touch: Best-Sellers per Category

Time to flex. A CTE (the WITH block) names an intermediate result so the final query reads cleanly. A window function like RANK() OVER (...) computes a value across a set of rows without collapsing them the way GROUP BY does — so you keep every book and get its rank.

PARTITION BY category restarts the ranking for each category, so every category gets its own #1, #2, #3 — exactly what a "best-sellers by section" report needs.

-- MILESTONE 6 — Advanced touch: rank best-sellers WITHIN each category.
-- A CTE (the WITH block) names a sub-result. A WINDOW FUNCTION (RANK() OVER...)
-- computes a value across a group of rows WITHOUT collapsing them with GROUP BY.

WITH sales AS (                                  -- CTE: revenue per book
    SELECT
        p.category,
        p.title,
        SUM(p.price * oi.quantity) AS revenue
    FROM order_items oi
    JOIN products p ON p.product_id = oi.product_id
    GROUP BY p.category, p.title
)
SELECT
    category,
    title,
    revenue,
    RANK() OVER (                                -- 1, 2, 3... restarting per category
        PARTITION BY category
        ORDER BY revenue DESC
    ) AS rank_in_category
FROM sales
ORDER BY category, rank_in_category;

-- ✅ Expected result: each category's books numbered 1, 2, 3...
--    The #1 row in each category is its best-seller by revenue.

Common Pitfalls (and the fix)

• "FOREIGN KEY constraint failed" on INSERT: you inserted a child before its parent. Insert customers and products first, then orders, then order_items.
• "no such table: products": you ran a query before Milestone 1/2. Run the schema and seed blocks first, in the same session.
• Wrong totals from a JOIN: joining orders straight to products with no order_items in between multiplies rows. Always route through the line-items table.
• Column in SELECT but not in GROUP BY: every non-aggregated column you select must appear in GROUP BY, or the query is ambiguous and errors.
• Forgetting COMMIT: changes inside BEGIN aren't permanent until you commit. Close the session first and they vanish.
• Index seems ignored: on tiny tables the planner may still choose a full scan because it's faster — that's correct. Indexes pay off as rows grow.

Frequently Asked Questions

Q: Why store line items in a separate order_items table?

Because an order can contain many books, and a book can appear in many orders — a many-to-many relationship. The line-items table is the bridge that makes that possible cleanly.

Q: Do I need indexes on such a tiny database?

Not for speed — with a handful of rows a full scan is instant. You add them here to learn the workflow; on a table with millions of rows they're the difference between milliseconds and minutes.

Q: Is a view slower than a real table?

A plain view re-runs its query each time, so it's exactly as fast as that query. If you need to cache the result, that's a materialised view — a different tool.

Q: What happens if a statement fails mid-transaction?

Nothing is saved until COMMIT. You run ROLLBACK (or the session ends) and the database returns to exactly how it was before BEGIN.

Q: My SQL playground rejected SERIAL or JSONB — why?

Those are PostgreSQL-specific. This project uses portable SQLite-friendly types (INTEGER, TEXT, REAL) so it runs nearly anywhere. Swap dialects if you target a specific engine.

🎯 Stretch Challenge: Add Product Reviews

No answer this time — just a brief and a comment outline. Extend BookNook with a reviews feature, wire its foreign keys, and write an aggregate report. This is the faded, build-it-yourself rung. Sketch it here, then run it in a playground.

-- 🎯 STRETCH CHALLENGE — extend BookNook with product reviews.
-- No answer given. Design it yourself using everything above.
--
-- 1) Create a "reviews" table:
--      review_id   INTEGER PRIMARY KEY
--      product_id  INTEGER  -> FK to products(product_id)
--      customer_id INTEGER  -> FK to customers(customer_id)
--      rating      INTEGER  -> CHECK it's between 1 and 5
--      comment     TEXT
--      created_on  DATE NOT NULL
--
-- 2) Seed 4–5 reviews across a couple of books.
--
-- 3) Write a query: each product's title, its AVG(rating) rounded to 1 decimal,
--    and COUNT(*) of reviews. Use a LEFT JOIN so books with NO reviews still show.
--
-- 4) BONUS: a view "top_rated" listing only products with avg rating >= 4.
--    Use HAVING to filter on the aggregate.
--
-- ✅ Expected: every product appears; unreviewed books show NULL/0;
--    "top_rated" lists only the crowd favourites.

-- your schema + queries here

🎉 Project Complete

• ✅ You designed a normalised schema with PKs, FKs, and constraints
• ✅ You seeded data in foreign-key-safe order
• ✅ You answered business questions with JOINs, aggregates, and GROUP BY
• ✅ You indexed hot columns and read an EXPLAIN plan
• ✅ You built a reusable view and placed an order inside a transaction
• ✅ You ranked best-sellers per category with a CTE + window function
• ✅ Where to go next: take the Stretch Challenge further — add authentication tables, write triggers to keep stock in sync, or load a real public dataset and rebuild these reports against it. You now have the full loop: design → seed → query → optimise → secure.

Practice quiz

Continue this course

• Previous: Advanced Queries
• Next: Advanced Relational Database Theory & Normalization (BCNF, 4NF, 5NF) — Eliminate data anomalies with higher normal forms beyond 3NF
• Quick reference: SQL cheat sheet

Related lessons

• SELECT
• JOINs
• GROUP BY & aggregates