Practice Set 2: Joins & Aggregates

Let's think this through for a moment

This Practice Set 2 steps up a level from Practice Set 1 with tasks that combine JOIN, GROUP BY, HAVING, and aggregate functions. Looking at a single table isn't enough anymore — you'll deal with practical scenarios where you join 2-3 tables together to produce a summary. We'll keep using the Books, Authors, Orders, and Customers tables from the Bookstore project, so it helps to remind yourself of the structure first. Try writing each task's query yourself before referring to the sample answer — this level is about as close as it gets to the report-writing patterns you'll run into in a real project.

Exercises

Task 1: INNER JOIN Books with Authors and get the book count per AuthorName using COUNT() + GROUP BY (how many books has each author written). Task 2: INNER JOIN Orders with Books to find total copies sold per book title (SUM(Quantity)) with GROUP BY Title, then get the top 3 best-selling books with ORDER BY DESC + LIMIT 3. Task 3: LEFT JOIN Customers with Orders and find customers who have never placed an order using HAVING COUNT(Orders.OrderID) = 0.

Produced each author's book count with INNER JOIN + GROUP BY
Produced a top-selling book report combining SUM() + GROUP BY + ORDER BY DESC + LIMIT
Found customers who haven't placed an order yet using LEFT JOIN + HAVING COUNT() = 0

Code example

sql

-- Task 1: Book count per author
SELECT Authors.AuthorName, COUNT(Books.BookID) AS TotalBooks
FROM Books
INNER JOIN Authors ON Books.AuthorID = Authors.AuthorID
GROUP BY Authors.AuthorName;

-- Task 2: Top 3 best-selling books
SELECT Books.Title, SUM(Orders.Quantity) AS TotalSold
FROM Orders
INNER JOIN Books ON Orders.BookID = Books.BookID
GROUP BY Books.Title
ORDER BY TotalSold DESC
LIMIT 3;

-- Task 3: Customers who never ordered
SELECT Customers.CustomerName, COUNT(Orders.OrderID) AS OrderCount
FROM Customers
LEFT JOIN Orders ON Customers.CustomerID = Orders.CustomerID
GROUP BY Customers.CustomerName
HAVING COUNT(Orders.OrderID) = 0;

You should see

All 3 tasks will each produce a table: author book counts, a top-3 best-seller list, and a list of customers who haven't placed an order yet.

5-minute try-it

Rewrite Task 2's query to rank the top 3 by total revenue (SUM(Quantity * Price)) instead — you'll need to add another JOIN to the Books table — give yourself 5 minutes to try it.

A quick word of caution

When using LEFT JOIN to find 'what's missing,' both the GROUP BY + HAVING COUNT() = 0 approach and the WHERE ... IS NULL approach can work, but their logic can differ — compare the results before using either one for real in your project.