Final Project
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
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 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 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.
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.
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 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.
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.
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.
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.
📘 Quick Reference — what this project used
Syntax
Purpose
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.
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.
🎉 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
What does a PRIMARY KEY do?
- Points at another table's key
- Allows duplicate values
- Uniquely identifies each row in a table
- Stores the row's timestamp
Answer: Uniquely identifies each row in a table. The PK is the column that uniquely identifies a row, like customer_id in customers.
What is a foreign key (FK)?
- A column that points at another table's primary key
- A column that must be unique
- An index on a text column
- A computed column
Answer: A column that points at another table's primary key. An FK links tables: orders.customer_id REFERENCES customers(customer_id).
Why must you insert parents before children when seeding data?
- Children are alphabetically first
- The database sorts inserts randomly
- Parents have no constraints
- A foreign key can only point at a row that already exists
Answer: A foreign key can only point at a row that already exists. Insert customers and products before orders, and orders before order_items, to satisfy the FKs.
What does the CHECK (price >= 0) constraint do?
- Sets a default price of 0
- Rejects any row whose price is negative
- Indexes the price column
- Makes price required
Answer: Rejects any row whose price is negative. CHECK rejects bad data; here it forbids a negative price.
What does the composite PRIMARY KEY (order_id, product_id) on order_items guarantee?
- One row per book per order (no duplicate pairings)
- Orders can have only one product
- Products can appear in one order only
- Quantities must be unique
Answer: One row per book per order (no duplicate pairings). A composite PK of both keys means each book appears once per order.
In Milestone 3, why route revenue through the order_items table?
- order_items stores the prices
- It is the only indexed table
- Joining orders straight to products without it multiplies rows and breaks totals
- Products have no category
Answer: Joining orders straight to products without it multiplies rows and breaks totals. The line-items table is the bridge; skipping it fans out rows and produces wrong sums.
What does CREATE INDEX on a join/filter column achieve?
- Stores a copy of the whole table
- Lets the database jump to matching rows instead of scanning every one
- Encrypts the column
- Makes writes faster
Answer: Lets the database jump to matching rows instead of scanning every one. Index the columns you filter (WHERE) and join (ON); reads speed up, writes pay a small cost.
What is a VIEW?
- A cached copy of query results
- A second physical table
- An index on multiple columns
- A stored SELECT that holds no data and re-runs each time you query it
Answer: A stored SELECT that holds no data and re-runs each time you query it. A plain view stores only the query, so it is always up to date when queried.
What property does a transaction (BEGIN ... COMMIT) provide?
- Faster individual inserts
- Atomicity: all statements succeed together or all roll back
- Automatic indexing
- Encryption of the rows
Answer: Atomicity: all statements succeed together or all roll back. A transaction is all-or-nothing; ROLLBACK throws away everything since BEGIN.
What does RANK() OVER (PARTITION BY category ORDER BY revenue DESC) do?
- Sums revenue per category
- Deletes lower-ranked rows
- Numbers rows 1, 2, 3 within each category without collapsing them
- Groups every category into one row
Answer: Numbers rows 1, 2, 3 within each category without collapsing them. A window function ranks within each partition while keeping every row, unlike GROUP BY.
Continue this course
- Previous: Big Data SQL
- Next: SQL vs NoSQL: When to Use Each