Project: the bookshop business report
Answer the owner's real questions about sales, best sellers and reviews.
Lesson 33 of 33 · about 30 minutes
The lesson
The bookshop's owner has a meeting with investors and needs answers. You have everything you need to give them.
This lesson's database is the bookshop plus two more tables:
reviews: id, book_id, customer_id, rating (1 to 5), comment. It holds seven reviews.
order_items: which books were in each order.
| Column | Meaning |
|---|---|
| order_id | the order (links to orders.id) |
| book_id | the book (links to books.id) |
| quantity | how many copies |
| price | the price per copy when it was bought |
An order can contain several books, so order_items can have several rows for the same order. The money from one row is quantity * price.
A word on cancelled orders. Order 3 was cancelled, so it shouldn't count as a sale. Join to orders and filter on its status.
Tips for report queries
- Write the
FROMandJOINlines first, run them, and look at the rows. Then addWHERE,GROUP BYand the columns. - Money sums can show long decimals like 119.41000000000001. Use
ROUND(..., 2), as the questions ask. - Give result columns clear names with
AS. The owner will thank you.
These questions are deliberately like the ones real analysts get every day. Take your time, and use Hint whenever you need it.
- Build big queries in small steps: joins first, then filters, then grouping.
- Leave out cancelled orders when counting sales.
- ROUND money to 2 decimal places and name columns clearly.