Project ยท 25 min
Project: bookshop report
The goal
A small bookshop wants a report on its sales. You will answer six questions with SQL. This project uses JOIN, GROUP BY, ORDER BY, LIMIT, subqueries, and INSERT.
The database has two tables.
books:
| id | title | author | genre | price |
|---|---|---|---|---|
| 1 | The Salt Road | Ines Marchetti | Adventure | 14.50 |
| 2 | Paper Lanterns | Haruka Mori | Fantasy | 12.00 |
The table has six books. The rows above are the first two.
sales:
| id | book_id | quantity | sold_on |
|---|---|---|---|
| 1 | 1 | 3 | 2026-02-10 |
| 2 | 2 | 1 | 2026-02-14 |
The table has ten sales. The column book_id is the id of a book. The column quantity is the number of copies in the sale. The column sold_on is text in the form YYYY-MM-DD.
How to work
- Build each query in small steps. First select the rows, then add the join, then the grouping, then the sorting.
- Press Run often. Look at the result table, and think about whether it makes sense.
- Result numbers follow the order of the
SELECTstatements in your file. Task 1 checks the first result, task 2 the second, and so on. Task 6 checks the data in the database.
After the tasks
Write more questions of your own. For example: which author sold the most copies? Which day had the most sales?
Tasks
6 to do
Task 1, not checked yet. Find the total number of copies sold. Use
SUMon thequantitycolumn ofsales. Name the columncopies_sold.Task 2, not checked yet. Find the revenue of each book. The revenue is the sum of
quantity * price. Select the booktitleand the revenue, rounded to 2 decimals, asrevenue. Put the book with the highest revenue first.Task 3, not checked yet. Find the three books with the most copies sold. Select the
titleand the number of copies ascopies. Put the book with the most copies first.Task 4, not checked yet. Find the
titleof the books that were never sold. Use a subquery withNOT IN.Task 5, not checked yet. Find the revenue of each
genrefor the sales made in March 2026, rounded to 2 decimals, asrevenue. Put the genre with the highest revenue first. Dates are text in the form2026-03-18.Task 6, not checked yet. Create a table named
top_bookswith one column,title(typeTEXT). Fill it with the titles of the two books that have the most copies sold. UseINSERT INTO ... SELECT ....
Task 1 of 6
Find the total number of copies sold. Use SUM on the quantity column of sales. Name the column copies_sold.