Checkpoint: Building a Schema

Reviewed & published by Brayan K

You've learned data types, CREATE/ALTER TABLE, constraints, and DROP/TRUNCATE/DELETE — let's design a real schema. This checkpoint pulls every recent lesson together into one hands-on build: a small, properly normalized bookshop database, populated and queried end to end.

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.

No new syntax here — just the satisfying part where it all clicks. Work through the three build steps, reveal the solutions to check yourself, then test your understanding on the quiz.

What You've Learned

Everything in this checkpoint draws on the lessons you just finished. Here's the toolkit you're bringing to the build:

• Data types — INTEGER, VARCHAR, TEXT, DECIMAL, DATE, BOOLEAN
• CREATE TABLE — defining columns and their types
• ALTER TABLE — adding, dropping, and renaming columns
• PRIMARY KEY — a unique, NOT NULL identity per row
• FOREIGN KEY — linking tables with referential integrity
• UNIQUE & CHECK — no duplicates, custom value rules
• DROP / TRUNCATE / DELETE — removing data and tables safely
• Why DECIMAL (not FLOAT) for money

The Plan: A Bookshop in Three Tables

We'll model a tiny bookshop with two one-to-many relationships: one author writes many books, and one book has many sales.

authors (1) ───< (many) books (1) ───< (many) sales

authors:  id (PK) · name
books:    id (PK) · title · price · author_id (FK -> authors.id)
sales:    id (PK) · book_id (FK -> books.id) · qty · sold_on

Notice each fact lives in exactly one place — an author's name is stored once, and books point to it by id. That's normalization.

Step 1 — Design the Tables

Fill in the blanks to define all three tables. Use NOT NULL for required fields, DECIMAL(8,2) for the price, a CHECK so quantities are at least 1, and FOREIGN KEYs to wire the tables together.

-- 🎯 CHECKPOINT BUILD — STEP 1: design the tables
--
-- We're building a tiny bookshop. You need THREE tables:
--   authors(id PK, name required)
--   books(id PK, title required, price money, author_id -> authors)
--   sales(id PK, book_id -> books, qty CHECK >= 1, sold_on date)
--
-- Fill in the structure below using everything you've learned:
-- PRIMARY KEY, NOT NULL, DECIMAL, FOREIGN KEY, CHECK.

CREATE TABLE authors (
    id   INTEGER PRIMARY KEY,
    name VARCHAR(100) ____            -- 👉 required
);

CREATE TABLE books (
    id        INTEGER PRIMARY KEY,
    title     VARCHAR(200) NOT NULL,
    price     ____ NOT NULL,          -- 👉 exact money, 2 decimals
    author_id INTEGER NOT NULL,
    FOREIGN KEY (author_id) ____ authors(id)   -- 👉 link to authors
);

CREATE TABLE sales (
    id      INTEGER PRIMARY KEY,
    book_id INTEGER NOT NULL,
    qty     INTEGER ____ (qty >= 1),  -- 👉 no zero/negative sales
    sold_on DATE DEFAULT CURRENT_DATE,
    FOREIGN KEY (book_id) REFERENCES books(id)
);

The completed three-table schema:

CREATE TABLE authors (
    id   INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL
);

CREATE TABLE books (
    id        INTEGER PRIMARY KEY,
    title     VARCHAR(200) NOT NULL,
    price     DECIMAL(8,2) NOT NULL,
    author_id INTEGER NOT NULL,
    FOREIGN KEY (author_id) REFERENCES authors(id)
);

CREATE TABLE sales (
    id      INTEGER PRIMARY KEY,
    book_id INTEGER NOT NULL,
    qty     INTEGER CHECK (qty >= 1),
    sold_on DATE DEFAULT CURRENT_DATE,
    FOREIGN KEY (book_id) REFERENCES books(id)
);

Key points: price is DECIMAL(8,2) (never FLOAT), each child table carries the foreign key, and the CHECK (qty >= 1) blocks zero or negative sales.

Step 2 — Populate It (Order Matters!)

Foreign keys mean order is everything: insert authors first, then books (which reference authors), then sales (which reference books). Try it, then reveal to confirm.

-- 🎯 CHECKPOINT BUILD — STEP 2: populate the schema
--
-- Insert in the RIGHT ORDER so no foreign key fails:
-- parents (authors) first, then books, then sales.

INSERT INTO authors (id, name) VALUES
    (1, 'Ursula K. Le Guin'),
    (2, 'Terry Pratchett');

INSERT INTO books (id, title, price, author_id) VALUES
    (10, 'A Wizard of Earthsea', 12.99, 1),
    (11, 'The Left Hand of Darkness', 14.50, 1),
    (12, 'Mort', 9.99, 2);

INSERT INTO sales (id, book_id, qty, sold_on) VALUES
    (100, 10, 3, '2026-05-01'),
    (101, 12, 1, '2026-05-02'),
    (102, 10, 2, '2026-06-10');

-- ✅ All inserts succeed because parents exist before children,
--    and every qty is >= 1.

Step 3 — Query It: Revenue per Author

The payoff: a single query that joins all three tables, multiplies price by quantity, and totals revenue per author. This is exactly the kind of report a well-designed schema makes easy.

-- 🎯 CHECKPOINT BUILD — STEP 3: the report query
--
-- Write ONE query that, for each author, shows their name and the
-- TOTAL revenue from their books (price * qty), highest first.
-- You'll need: JOINs across all three tables, SUM, GROUP BY, ORDER BY.

SELECT
    a.name AS author,
    SUM(b.price * s.qty) AS revenue
FROM authors a
JOIN books b  ON b.author_id = a.id
JOIN sales s  ON s.book_id = b.id
GROUP BY a.name
ORDER BY revenue DESC;

-- ✅ Expected:
-- author              | revenue
-- Ursula K. Le Guin   | 64.95   (3*12.99 + 2*12.99 = 38.97 + 25.98)
-- Terry Pratchett     | 9.99

-- Same query, fully worked, with the arithmetic spelled out.
SELECT
    a.name                 AS author,
    SUM(b.price * s.qty)   AS revenue
FROM authors a
JOIN books b ON b.author_id = a.id     -- author -> their books
JOIN sales s ON s.book_id  = b.id      -- books  -> their sales
GROUP BY a.name                        -- one row per author
ORDER BY revenue DESC;                 -- biggest earner first

-- Walkthrough of the numbers:
--   Le Guin: sale 100 = 3 x 12.99 = 38.97
--            sale 102 = 2 x 12.99 = 25.98  -> total 64.95
--   Pratchett: sale 101 = 1 x 9.99 = 9.99
--
-- Result:
-- author              | revenue
-- Ursula K. Le Guin   | 64.95
-- Terry Pratchett     | 9.99

Reorder Challenge

A teammate scrambled the build script. Put the statements in an order that runs without a single foreign-key error:

A.   INSERT INTO books ... (references authors)
B.   CREATE TABLE authors ...
C.   CREATE TABLE sales ... (FK -> books)
D.   INSERT INTO authors ...
E.   CREATE TABLE books ... (FK -> authors)
F.   INSERT INTO sales ... (references books)

Quick Recall

1) Which table carries the author_id foreign key — authors or books?

books. The FK lives on the "many" side: one author has many books, so each book points back to its author.

2) Why is price DECIMAL(8,2) and not FLOAT?

DECIMAL stores exact base-10 money; FLOAT is approximate and accumulates rounding errors that corrupt revenue totals.

3) What does CHECK (qty >= 1) prevent?

Inserting a sale with a quantity of 0 or a negative number — nonsensical data the database now refuses.

🎯 Your turn: put the module together

Steps 1–3 above build the bookshop one piece at a time. This exercise is the whole thing in one snippet: the two constraints that keep bad data out, real rows, and the query a shop owner actually wants — revenue per author, which needs all three tables. The expected rows were produced by running exactly this SQL in SQLite.

-- 🎯 YOUR TURN — the bookshop schema, end to end: a constraint that rejects
-- bad data, rows that respect it, and a JOIN with an aggregate.
-- Fill in the three ___ blanks. (Everything needed is created here.)

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

CREATE TABLE books (
    id        INTEGER PRIMARY KEY,
    title     TEXT NOT NULL,
    price     INTEGER NOT NULL,             -- whole pounds
    author_id INTEGER NOT NULL,
    -- 1) a book must point at a real author
    FOREIGN KEY (author_id) ___ authors(id)
    -- 👉 replace ___ with REFERENCES
);

CREATE TABLE sales (
    id      INTEGER PRIMARY KEY,
    book_id INTEGER NOT NULL,
    -- 2) a sale of zero or negative copies makes no sense
    qty     INTEGER NOT NULL ___ (qty >= 1),
    -- 👉 replace ___ with CHECK
    FOREIGN KEY (book_id) REFERENCES books(id)
);

INSERT INTO authors VALUES (1, 'Octavia Butler'), (2, 'Ursula Le Guin');
INSERT INTO books VALUES (1, 'Kindred', 12, 1), (2, 'Parable of the Sower', 14, 1), (3, 'The Dispossessed', 11, 2);
INSERT INTO sales (book_id, qty) VALUES (1, 3), (2, 1), (3, 4), (1, 2);

-- 3) revenue per author: join all three tables and multiply price by quantity
SELECT a.name AS author, SUM(b.price * s.qty) AS revenue
FROM sales s
JOIN books b   ON b.id = s.book_id
JOIN authors a ON a.id = ___
-- 👉 replace ___ with b.author_id
GROUP BY a.name
ORDER BY revenue DESC;

-- ✅ Expected result:
-- author | revenue
-- Octavia Butler | 74
-- Ursula Le Guin | 44

📝 Checkpoint Quiz

Six questions covering the whole DDL block. Think first, then reveal.

1) You try to drop the books table but it errors. Why?

The sales table has a foreign key referencing books. Drop sales first, or use DROP TABLE books CASCADE (carefully).

2) You want to empty sales for a fresh test run and reset its ids. DELETE or TRUNCATE?

TRUNCATE TABLE sales — it's fast and resets the auto-increment counter, which DELETE does not.

3) You need to add a genre column to books without recreating it. Which command?

ALTER TABLE books ADD COLUMN genre VARCHAR(50); — existing rows get NULL unless you add a DEFAULT.

4) Inserting a book with author_id = 99 fails. What constraint caught it?

The FOREIGN KEY on books.author_id. No author 99 exists, so referential integrity rejects the insert.

5) Two authors accidentally share the same id in your INSERT. What stops it?

The PRIMARY KEY on authors.id — it's unique and NOT NULL, so the duplicate is rejected.

6) Why store authors in a separate table instead of repeating the author name on every book row?

Normalization: storing each author once avoids duplication and inconsistency, and lets you update a name in a single place. Books reference the author by id.

🎉 Checkpoint Complete

• ✅ You designed a normalized three-table schema from scratch
• ✅ You used PRIMARY KEY, FOREIGN KEY, CHECK, NOT NULL, and DECIMAL together
• ✅ You populated it in dependency order so no foreign key failed
• ✅ You joined all three tables for a revenue-per-author report
• ✅ You understand why each fact lives in exactly one place (normalization)
• ✅ Next: SQL Joins — combining tables side by side in depth

Practice quiz

Continue this course

• Previous: DROP vs TRUNCATE vs DELETE
• Next: JOIN Operations — Combine data from multiple tables using INNER, LEFT, RIGHT, and FULL JOINs
• Quick reference: SQL cheat sheet

Frequently asked questions

What makes a schema 'normalized'?

A normalized schema stores each fact once. Instead of repeating an author's name on every book row, you keep authors in their own table and reference them by id with a foreign key. This avoids duplication, prevents inconsistent data (one misspelled copy of a name), and makes updates trivial — change the name in one place and every book reflects it.

How do I decide which table holds the foreign key?

The foreign key goes on the 'many' side of a one-to-many relationship. One author has many books, so book rows carry author_id pointing back to the single author. One book has many sales, so sale rows carry book_id. The child (many) always references the parent (one), never the other way around.

Why insert parent rows before child rows?

A foreign key requires the referenced parent to already exist. If you insert a book with author_id 1 before author 1 exists, the constraint rejects it. So the safe order is parents first (authors), then their children (books), then the children's children (sales).

Should every table have a primary key?

Yes — practically always. A primary key gives each row a stable, unique identity that foreign keys can reference and that lets you reliably update or delete a single row. A table without one makes relationships and precise edits much harder, so add a surrogate id PRIMARY KEY even when no natural key is obvious.

Related lessons

• Constraints
• CREATE TABLE