Part 5 of 5 · 3 min read
Exercises
Easy
Easy 1: Count all rows (15 minutes)
Write a query counting the total number of books in books.
Done when: a correct SELECT COUNT(*) FROM books; is given.
Hint
SELECT COUNT(*) FROM books;
Easy 2: Average by group (15 minutes)
Write a query computing the average book price for each author.
Done when: a correct GROUP BY author_id with AVG(price) is given.
Hint
SELECT author_id, AVG(price) FROM books GROUP BY author_id;
Easy 3: Where does the condition go (15 minutes)
State whether "price above 500" and "average price above 500, per author" each belong in WHERE or HAVING.
Done when: the first is correctly placed in WHERE and the second in HAVING.
Hint
A plain row-level condition goes in WHERE; a condition on an aggregate goes in HAVING.
Medium
Medium 1: Customers with more than one order (25 minutes)
Write a query returning every customer_id with more than one order, using GROUP BY and HAVING.
Done when: a correct GROUP BY customer_id HAVING COUNT(*) > 1 query is given.
Hint
SELECT customer_id, COUNT(*) FROM orders GROUP BY customer_id HAVING COUNT(*) > 1;
Medium 2: Combine WHERE and HAVING (25 minutes)
Write a query grouping orders placed in 2026 by customer_id, keeping only customers with more than 3 such orders.
Done when: a correct query using WHERE for the date filter and HAVING for the count filter is given.
Hint
SELECT customer_id, COUNT(*) FROM orders WHERE placed_on >= '2026-01-01' GROUP BY customer_id HAVING COUNT(*) > 3;
Medium 3: Total revenue per book (25 minutes)
Write a query showing each book's total revenue, computed as price times total quantity ordered across all order_items referencing it.
Done when: a correct query joining books to order_items, grouping by book, and summing price times quantity is given.
Hint
SELECT b.book_id, SUM(b.price * oi.quantity) FROM books b JOIN order_items oi ON b.book_id = oi.book_id GROUP BY b.book_id;
Hard
Hard 1: Why this query is invalid (30 minutes)
Explain why SELECT customer_id, full_name, COUNT(*) FROM orders GROUP BY customer_id; is invalid in a strict SQL database, given that full_name lives in customers, not orders.
Done when: the explanation correctly notes full_name is neither aggregated nor in GROUP BY, and does not even exist in the table being grouped without a join.
Hint
full_name belongs to a different, unjoined table, and even if joined, it would need to be in GROUP BY or wrapped in an aggregate to appear safely alongside COUNT(*).
Hard 2: Nested aggregation (35 minutes)
Explain how to find the average number of orders per customer, given that this requires first counting orders per customer, then averaging those counts.
Done when: the explanation correctly describes a subquery or CTE computing per-customer counts first, with an outer AVG over that result.
Hint
Compute per-customer counts in a subquery or CTE first, for example SELECT customer_id, COUNT(*) AS c FROM orders GROUP BY customer_id, then take AVG(c) of that result.
Hard 3: HAVING without GROUP BY (40 minutes)
Explain what SELECT COUNT(*) FROM orders HAVING COUNT(*) > 100; means, given there is no GROUP BY at all, and why this is still valid SQL.
Done when: the explanation correctly states the whole table is treated as one implicit group, so HAVING filters whether that single group's count qualifies.
Hint
With no GROUP BY, the whole table is one group, so HAVING simply decides whether that one group's aggregate passes the condition, equivalent in effect to filtering a single-row result.