Project: the state of the library
SQL · Unit 8: More joins
- Full plan
- SQL
- Project
- 3 exercises
The library's month-end report. Last unit, the count of books per author gave four authors and seven books, and the library has five authors and eight books. Nora Page, who has none, was missing. So was "Short Stories," which has no author.
The report also uses a table you haven't seen, loans. Each row is one time a book left the library, with its book_id, the member who borrowed it, and the day they returned it. If returned is empty, the book is still out. In the library, b is books, a is authors, and l is loans.
SELECT id, book_id, member, returned
FROM loans;Returns
| id | book_id | member | returned |
|---|---|---|---|
| 1 | 1 | Ann | 2026-01-09 |
| 2 | 3 | Luke | 2026-01-05 |
| 3 | 1 | Dylan | NULL |
| 4 | 4 | Ann | 2026-01-14 |
| 5 | 6 | Hannah | NULL |
| 6 | 3 | Ann | NULL |
| 7 | 7 | Luke | 2026-01-15 |
| 8 | 2 | Brian | 2026-01-04 |
The rest of the explanation is in the lesson, which is part of the full plan.
Exercises in this lesson
You do them in the app, which checks them on the spot and explains why.
1. Find the bug
Which books have never been borrowed? The librarian wants to move them to storage. There are two, "Short Stories" and "Loose Leaves," and the query returns five titles. Three of them are at a member's home right now. Which line has the problem?
2. Write the code
The inventory. Write a query with three columns, in this order: each book's title; its author's name, or
no authorif it has none; and how many times it's been borrowed. All eight books have to be there. Most borrowed first, and ties by title.3. Write the code
The report by author. Write a query with three columns, in this order: each author's name, how many of their books the library has, and how many times those books have been borrowed in all. All five authors have to be there. Most borrowed first, and ties by name.
It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.