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.
Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.
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.
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:
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.