Hack University - Capstone: Query Drills

The bookstore, start to finish

Everything in this course, applied to one schema:

authors(id, name)
books(id, author_id, title, price), where one author has many books, per lesson 5-1
sales(id, book_id, qty), where each row is one sale of qty copies

Note that revenue is never stored anywhere. It has to be computed from qty in sales and price in books, which is normalization doing its job: a price lives in one place, and totals are derived on demand.

Four drills follow, each naming the lessons it draws on. Reading the query and then predicting its output before checking is the way to get the most out of them.

Drill 1: revenue per book

Drawing on lessons 4-2, 5-3, and 3-1. For each book that has sales, the query reports the title and the total revenue, highest first.

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

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  author_id INTEGER,
  title TEXT,
  price INTEGER
);

CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  book_id INTEGER,
  qty INTEGER
);

INSERT INTO authors VALUES (1, 'Frank Herbert');
INSERT INTO authors VALUES (2, 'Isaac Asimov');
INSERT INTO authors VALUES (3, 'Ursula K. Le Guin');

INSERT INTO books VALUES (1, 1, 'Dune', 10);
INSERT INTO books VALUES (2, 2, 'Foundation', 9);
INSERT INTO books VALUES (3, 3, 'The Dispossessed', 12);
INSERT INTO books VALUES (4, 2, 'I, Robot', 8);
INSERT INTO books VALUES (5, 1, 'Dune Messiah', 11);

INSERT INTO sales VALUES (1, 1, 3);
INSERT INTO sales VALUES (2, 2, 1);
INSERT INTO sales VALUES (3, 1, 2);
INSERT INTO sales VALUES (4, 4, 5);
INSERT INTO sales VALUES (5, 3, 1);
INSERT INTO sales VALUES (6, 2, 2);

SELECT books.title, SUM(sales.qty * books.price) AS revenue
FROM sales
JOIN books ON books.id = sales.book_id
GROUP BY books.title
ORDER BY revenue DESC;

Output

Dune|50
I, Robot|40
Foundation|27
The Dispossessed|12

Dune sold 3 copies and then 2 more at a price of 10, which is 5 × 10 = 50 and puts it on top. SUM(sales.qty * books.price) shows that an aggregate can wrap arithmetic, and the multiplication happens per row before the sum.

ORDER BY revenue DESC sorts by the alias declared in the SELECT list, which most databases allow and which reads better than repeating the whole expression.

Only four books appear because the inner join drops any book with no sales, which is the subject of the next drill.

Drill 2: books never sold

Drawing on lesson 6-1, the find-the-missing pattern.

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

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  author_id INTEGER,
  title TEXT,
  price INTEGER
);

CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  book_id INTEGER,
  qty INTEGER
);

INSERT INTO authors VALUES (1, 'Frank Herbert');
INSERT INTO authors VALUES (2, 'Isaac Asimov');
INSERT INTO authors VALUES (3, 'Ursula K. Le Guin');

INSERT INTO books VALUES (1, 1, 'Dune', 10);
INSERT INTO books VALUES (2, 2, 'Foundation', 9);
INSERT INTO books VALUES (3, 3, 'The Dispossessed', 12);
INSERT INTO books VALUES (4, 2, 'I, Robot', 8);
INSERT INTO books VALUES (5, 1, 'Dune Messiah', 11);

INSERT INTO sales VALUES (1, 1, 3);
INSERT INTO sales VALUES (2, 2, 1);
INSERT INTO sales VALUES (3, 1, 2);
INSERT INTO sales VALUES (4, 4, 5);
INSERT INTO sales VALUES (5, 3, 1);
INSERT INTO sales VALUES (6, 2, 2);

SELECT books.title
FROM books
LEFT JOIN sales ON sales.book_id = books.id
WHERE sales.id IS NULL
ORDER BY books.title;

Output

Dune Messiah

The LEFT JOIN from books keeps unsold books in the result, and every column that came from sales is NULL on those rows, so sales.id IS NULL isolates them.

sales.id is the right column to test because it is a primary key and can never legitimately be NULL. Testing sales.qty would also match any real sale whose quantity was left blank.

And it must be IS NULL, never = NULL, as lesson 2-3 established. The wrong version returns an empty result that reads as "every book has sold at least once".

Drill 3: revenue per author

Drawing on lessons 6-2 and 5-3 together. All three tables are needed, because sales know quantities, books know prices and their author, and authors know names.

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

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  author_id INTEGER,
  title TEXT,
  price INTEGER
);

CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  book_id INTEGER,
  qty INTEGER
);

INSERT INTO authors VALUES (1, 'Frank Herbert');
INSERT INTO authors VALUES (2, 'Isaac Asimov');
INSERT INTO authors VALUES (3, 'Ursula K. Le Guin');

INSERT INTO books VALUES (1, 1, 'Dune', 10);
INSERT INTO books VALUES (2, 2, 'Foundation', 9);
INSERT INTO books VALUES (3, 3, 'The Dispossessed', 12);
INSERT INTO books VALUES (4, 2, 'I, Robot', 8);
INSERT INTO books VALUES (5, 1, 'Dune Messiah', 11);

INSERT INTO sales VALUES (1, 1, 3);
INSERT INTO sales VALUES (2, 2, 1);
INSERT INTO sales VALUES (3, 1, 2);
INSERT INTO sales VALUES (4, 4, 5);
INSERT INTO sales VALUES (5, 3, 1);
INSERT INTO sales VALUES (6, 2, 2);

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

Output

Isaac Asimov|67
Frank Herbert|50
Ursula K. Le Guin|12

Two joins chain outward from sales, first to books on sales.book_id and then to authors on books.author_id. This is drill 1 with one extra hop and a different grouping column.

Asimov leads with Foundation at 3 copies for 9 each, which is 27, plus I, Robot at 5 copies for 8 each, which is 40, giving 67. He overtakes Herbert by having two selling titles rather than one strong one, which the per-book report in drill 1 could never have shown.

A checklist for a slow report page

When a production report page takes 30 seconds, the order of investigation that matches this course is: look for N+1 query loops, then inspect the slow query's plan for full scans, then add the missing index on the filtered column.

That sequence is deliberate, cheapest and most common cause first:

Step	Tool	Lesson
count the round trips	application query log	10-2
read the plan for `SCAN` on a big table	`EXPLAIN QUERY PLAN`	9-1
index the `WHERE` or `JOIN` column	`CREATE INDEX`	9-2

Rewriting the service in a faster language or moving to a bigger machine rarely beats fixing the query, because a full scan of 20 million rows is slow for reasons no CPU can argue with.

Deleting data to go faster is a joke with a grain of truth in it. Less data genuinely does scan faster, and archiving old rows is a legitimate technique, but an index gets you the same speed while keeping the history.

Drill 4: labeling each book by revenue

Drawing on lessons 4-4 and 5-3. The same join and grouping as drill 1, with a CASE column added.

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

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  author_id INTEGER,
  title TEXT,
  price INTEGER
);

CREATE TABLE sales (
  id INTEGER PRIMARY KEY,
  book_id INTEGER,
  qty INTEGER
);

INSERT INTO authors VALUES (1, 'Frank Herbert');
INSERT INTO authors VALUES (2, 'Isaac Asimov');
INSERT INTO authors VALUES (3, 'Ursula K. Le Guin');

INSERT INTO books VALUES (1, 1, 'Dune', 10);
INSERT INTO books VALUES (2, 2, 'Foundation', 9);
INSERT INTO books VALUES (3, 3, 'The Dispossessed', 12);
INSERT INTO books VALUES (4, 2, 'I, Robot', 8);
INSERT INTO books VALUES (5, 1, 'Dune Messiah', 11);

INSERT INTO sales VALUES (1, 1, 3);
INSERT INTO sales VALUES (2, 2, 1);
INSERT INTO sales VALUES (3, 1, 2);
INSERT INTO sales VALUES (4, 4, 5);
INSERT INTO sales VALUES (5, 3, 1);
INSERT INTO sales VALUES (6, 2, 2);

SELECT books.title, SUM(sales.qty * books.price) AS revenue,
  CASE WHEN SUM(sales.qty * books.price) >= 20 THEN 'hit' ELSE 'slow' END AS tier
FROM sales
JOIN books ON books.id = sales.book_id
GROUP BY books.title
ORDER BY revenue DESC;

Output

Dune|50|hit
I, Robot|40|hit
Foundation|27|hit
The Dispossessed|12|slow

The CASE test uses SUM(...) directly, which works because the labeling happens after the buckets are built. Repeating the whole expression is a little verbose, and a CTE from lesson 6-4 could compute revenue once and label it in an outer query instead.

Unlike a HAVING COUNT(*) >= 20, this keeps every sold book in the result and merely marks it. Filtering removes rows and labeling annotates them, which is the distinction to carry forward.

That is the course. A schema where each fact lives once, joins to bring the pieces back together, aggregates to turn rows into answers, transactions to keep writes honest, and indexes to make it all fast.