Bookstore Project - Part 3: Reports & Cleanup

Let's think this through for a moment

Parts 1 and 2 covered building the tables and writing search/join queries — this Part 3 is the final stage of the project, where we'll produce summary reports the way a business owner would want to see them. We'll use GROUP BY along with SUM() and COUNT() to calculate how many books each author has written and how much each customer has spent. We'll use HAVING to filter down to only the authors who've written more than 2 books. Finally, we'll handle data maintenance tasks like updating prices, fixing stock, and deleting old orders, then add an index on a frequently searched column to close out the project.

Let's build it for real

First, work out how many books each author has written with COUNT() + GROUP BY AuthorID, then filter with HAVING COUNT(*) >= 2. Next, find how much each customer has spent in total (SUM of Quantity * Price) with GROUP BY CustomerID. Then, fix the Stock of 'Fantastic Beasts' — currently 0 — to 15 using UPDATE. If there's order data older than 2025, clean it up with DELETE FROM Orders WHERE OrderDate < '2026-01-01' (this is for the mock data). Finally, add a CREATE INDEX on the Books.Title column to boost search speed.

• Produced a list of authors with more than 2 books using GROUP BY + HAVING
• Produced a total-spending report per customer using SUM()
• Fixed stock with UPDATE and added an index on Books.Title

Code example

-- Authors who wrote 2 or more books
SELECT Authors.AuthorName, COUNT(Books.BookID) AS BookCount
FROM Authors
INNER JOIN Books ON Authors.AuthorID = Books.AuthorID
GROUP BY Authors.AuthorName
HAVING COUNT(Books.BookID) >= 2;

-- Total spending per customer
SELECT Customers.CustomerName, SUM(Orders.Quantity * Books.Price) AS TotalSpent
FROM Orders
INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID
INNER JOIN Books ON Orders.BookID = Books.BookID
GROUP BY Customers.CustomerName
ORDER BY TotalSpent DESC;

-- Restock a book
UPDATE Books SET Stock = 15 WHERE Title = 'Fantastic Beasts';

-- Clean up old order data
DELETE FROM Orders WHERE OrderDate < '2026-01-01';

-- Speed up title search
CREATE INDEX idx_book_title ON Books(Title);

The author report will show Murakami as having written more than 2 books, the customer spending report will list how much each customer has spent in order, and the stock update plus index will apply without errors.