The Library Query That Returned Nothing
A higher secondary school library stores data in three tables: Books(book_id, title, author, price), Members(member_id, name, class), and Loans(book_id, member_id, issue_date, return_date). The librarian wants two reports. First, list the titles of books that were borrowed at least twice during 2024. A volunteer writes: SELECT title FROM Books JOIN Loans ON Books.book_id = Loans.book_id WHERE COUNT(*) >= 2; MySQL rejects this query. Second, list members who have never borrowed a book. Another volunteer writes: SELECT name FROM Members WHERE member_id NOT IN (SELECT member_id FROM Loans); This returns zero rows, although the library has 300 members and only 280 distinct member IDs in Loans. Investigation shows that one Loans row has a NULL member_id because of a data-entry error. The librarian also wants to prevent any future loan row from having a missing member.
1. Correct the first query using GROUP BY and HAVING, and explain why WHERE cannot contain COUNT(*) in this form.
Read the worked answer
SELECT b.title FROM Books b JOIN Loans l ON b.book_id = l.book_id WHERE YEAR(l.issue_date) = 2024 GROUP BY b.book_id, b.title HAVING COUNT(*) >= 2;
WHERE filters individual rows before grouping. COUNT(*) is an aggregate over a group, so it belongs in HAVING, which filters groups after GROUP BY. Including book_id in GROUP BY avoids combining two different books with the same title.
2. Explain why the `NOT IN` query returns zero rows because of the NULL, and write two corrected versions that would list the 20 members with no loans.
Read the worked answer
NOT IN compares each member_id with every value returned by the subquery. If the subquery contains a NULL, the comparison for every row becomes UNKNOWN, and no row satisfies NOT IN. That is why zero rows come back even though 20 members have no loans.
Fix 1: SELECT name FROM Members WHERE member_id NOT IN (SELECT member_id FROM Loans WHERE member_id IS NOT NULL);
Fix 2: SELECT m.name FROM Members m LEFT JOIN Loans l ON m.member_id = l.member_id WHERE l.member_id IS NULL;
A NOT EXISTS version also works and is often safer with NULLs.
3. Recommend a constraint to prevent missing `member_id` in `Loans` and explain what the DBMS will do when a loan is inserted without a member.
Read the worked answer
Declare member_id INT NOT NULL in Loans and add a foreign key: FOREIGN KEY (member_id) REFERENCES Members(member_id).
NOT NULL stops a row with no member_id; the foreign key stops a member_id that does not exist in Members. An attempted insert is rejected with an error, so the bad row never enters the table.