Capstone: Query Drills

lesson 10-3 · ~16 min · 29/29

The bookstore, start to finish

Everything you have learned, on one schema:

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

Four drills. Each one names the lessons it draws on. Write the query yourself before touching the hints; viewing the solution after a real attempt is how this course is meant to be used, not a defeat.

Code exercise · sql

Drill 1 (lessons 4-2, 5-3, 3-1). Revenue per book: for each book that has sales, print title and total revenue (SUM of qty * price), highest revenue first. Dune sold 3+2 copies at 10, so it leads with 50.

Code exercise · sql

Drill 2 (lesson 6-1). Which books have NEVER been sold? Print just the title. This is the find-the-missing pattern.

Code exercise · sql

Drill 3 (lessons 6-2 and 5-3 combined). Revenue per AUTHOR, highest first. This needs all three tables: sales know quantities, books know prices and authors, authors know names.

Quiz

Course finale. Your production app is slow. Users complain a report page takes 30 seconds. Which order of investigation matches what you learned?

Code exercise · sql

Drill 4 (lessons 4-4 and 5-3). Label each sold book: print title, revenue, and 'hit' if revenue is 20 or more, otherwise 'slow'. Highest revenue first. Same join and grouping as drill 1, plus a CASE column.