sql · level 2 · unit 7 · lesson 4
- meet it
- guess first
- your turn
- fix it
- nailed it
Tap a green word if you get lost.
meet it
Read the , then build the query one piece at a time.
Real jobs arrive as a brief: a few sentences from someone who needs answers. The librarian wants, for each member, how many loans they have had and how many books they still have out.
Build it in steps. Members is what the question is about, so it goes first. LEFT JOIN the loans, so nobody is lost, and group by member. Then add one column at a time, running as you go.
A flag inside SUM counts the books still out: loans with no returned date. Last of all, ORDER BY puts the busiest members first.
SELECT m.name, COUNT(l.id) AS loans,
SUM(CASE WHEN l.returned IS NULL THEN 1 ELSE 0 END) AS still_out
FROM members m
LEFT JOIN loans l ON l.member_id = m.id
GROUP BY m.name
ORDER BY loans DESC, m.name;Stuck? Go back to the last version that ran, then add one piece at a time.
guess first
Today is 1 May. Who is late?
Guess before you run it. Wrong guesses are how you learn, and nothing is marked.
SELECT m.name, b.title,
julianday('2026-05-01') - julianday(l.due) AS days_late
FROM loans l
JOIN books b ON l.book_id = b.id
JOIN members m ON l.member_id = m.id
WHERE l.returned IS NULL AND l.due < '2026-05-01'
ORDER BY days_late DESC;your turn
Your turn. Show one member's history.
Type a member's name between the quotes: Leo, Mia, Sam or Ava. You get their loans in date order, with still out for books not back yet.
result
Your answer appears here as a table.
fix it
Leo's book is 16 days late, not 30. Debug it!
result
Your answer appears here as a table.
- 30 days is how long Leo has had the book. On which day did it become late?
- A book is late from its due date, so count from
l.due, notl.borrowed. - Change julianday(l.borrowed) to julianday(l.due), then press run.
nailed it
you turned a brief into a report. :D
- Start from the table the question is about.
- Add one join, clause or column at a time, running as you go.
- Joins, groups,
CASEand dates combine into real reports.
new words in your collection
- brief
This lesson isn’t saved. A free account remembers every lesson you finish.