Project: overdue loans

SQL · Unit 14: Text and dates

Full plan
SQL
Project
3 exercises

The librarian is going to call everyone who has a book out. She wants to know who she's calling, about which book, and how many days they've had it. The title is in books, so it takes a JOIN, with l for loans and b for books.

Today, January 15, 2026, three books are out. The loan period is 3 days, and Dylan has had "The Lighthouse" for 5.

The list has one row per loan, not per member. In loans, Ann can be two people, and a loan belongs to only one.

SELECT b.title, l.member,
  JULIANDAY('2026-01-15')
    - JULIANDAY(l.borrowed) AS days
FROM loans l
JOIN books b
  ON l.book_id = b.id
WHERE l.returned IS NULL
ORDER BY days DESC;

Returns

title	member	days
The Lighthouse	Dylan	5
The Train South	Hannah	3
The Blue House	Ann	2

Exercises in this lesson

You do them in the app, which checks them on the spot and explains why.

1. Complete the code
Before calling, the librarian wants to see where each loan stands. A book whose loan period ended before today is overdue. If it ends today, January 15, 2026, it can still be returned. Fill in the blanks in the two comparisons.
2. Write the code
Write the call list, with two columns, in this order. First, the line the librarian reads over the phone, with the member, a colon, a space, and the title, like Dylan: The Lighthouse. Then, the days late: the days the book has been out minus the 3-day loan period. Include only loans that haven't been returned and whose loan period ended before today. One that's due today stays out. Sort from most days late to fewest and, on a tie, by the loan's id. Today can be written '2026-01-15' or DATE('now'). Use the aliases l and b.
3. Multiple choice
In a real library, the librarian would open this list every morning without anyone rewriting it. With the date typed in by hand, today is in two places in the query: in the days-late calculation and in the WHERE. What would have to change?

Do this lesson

It opens in your browser. This lesson is part of the full plan; the first unit of each course is free.

See all lessons →