The Library Report That Counted the Wrong Thing
The school library uses SQLite. Its tables are Members(member_id, name, class), Books(book_id, title, category) and Loans(loan_id, member_id, book_id, issue_date, due_date, return_date). return_date is NULL while a book is still out. On 15 December 2024 the librarian runs:
SELECT COUNT(*) FROM Loans WHERE return_date <> '2024-11-30';
and gets 40, then reports that 40 members have overdue books. The actual number of distinct members with at least one currently overdue loan is 25. Separately, 6 loans were returned late (return_date after due_date) but are now back. A second query:
SELECT m.name, COUNT(*) FROM Members m LEFT JOIN Loans l ON m.member_id = l.member_id WHERE l.issue_date >= '2024-04-01' GROUP BY m.name;
lists 18 names, but the librarian expected all 60 members, including those who never borrowed.
1. Explain why the first query's number 40 is not the number of overdue members, covering NULL behaviour and COUNT(*). Then rewrite it to return distinct members with a currently overdue loan, using 2024-12-15 as today.
Read the worked answer
return_date <> '2024-11-30' is UNKNOWN when return_date is NULL, so all currently-out books are excluded. Conversely, every book returned on any date other than 30 November - including books returned late and books returned early - satisfies the condition. So 40 is a count of returned loans with a return date not equal to one arbitrary date, not overdue loans. Also, COUNT(*) counts loans, not members; one member with three overdue books adds three.
On 15 December 2024, a currently overdue loan is one still out whose due date has passed:
SELECT COUNT(DISTINCT member_id) FROM Loans WHERE return_date IS NULL AND due_date < '2024-12-15';
The 6 late-but-returned loans do not belong in this list; they belong in a separate 'returned late' report.
2. The LEFT JOIN query returns 18 names. Identify the clause that destroys the outer join and rewrite it so every member appears, with 0 for non-borrowers. Then say whether the library should use COUNT(*) or COUNT(l.loan_id).
Read the worked answer
The WHERE clause is evaluated after the LEFT JOIN. For a member with no loans, every l column is NULL, so l.issue_date >= '2024-04-01' is UNKNOWN and the row is filtered out. The outer join has effectively become an inner join.
Move the loan-date condition into the ON clause so non-borrowers survive:
SELECT m.name, COUNT(l.loan_id) FROM Members m LEFT JOIN Loans l ON m.member_id = l.member_id AND l.issue_date >= '2024-04-01' GROUP BY m.member_id, m.name;
Use COUNT(l.loan_id), not COUNT(): COUNT() counts the preserved row for a non-borrower as 1, while COUNT(l.loan_id) ignores NULL and gives 0. Group by member_id as well as name to avoid merging two students with the same name.
3. The 6 late-but-returned loans: should they appear in the overdue list? Write a query that lists all loans that were ever returned late, and explain the difference between 'currently overdue' and 'returned late' in terms of return_date.
Read the worked answer
They should not appear in the current-overdue list. Currently overdue means the book is still out and the due date has passed: return_date IS NULL AND due_date < '2024-12-15'. Returned late means the book came back after its due date: return_date IS NOT NULL AND return_date > due_date. The two sets are disjoint because the first requires NULL and the second requires a value.
A query for returned-late loans:
SELECT loan_id, member_id, due_date, return_date FROM Loans WHERE return_date IS NOT NULL AND return_date > due_date;
The librarian should not add the 6 to the 25; they are historical compliance data, useful for fines, not for current reminders.