Library Database: NULLs, Outer Joins and the WHERE/HAVING Line
A school library database has three tables: MEMBERS(member_id, name, city, join_date), BOOKS(book_id, title, category, price) and LOANS(loan_id, member_id, book_id, issue_date, return_date). return_date is NULL while a book is still out. There are 120 members, but only 78 have ever borrowed a book. A student volunteer writes SELECT member_id, COUNT(return_date) FROM LOANS GROUP BY member_id; to list outstanding books per member, and every borrower reports 0. She then writes SELECT m.member_id, m.name, COUNT(*) FROM MEMBERS m, LOANS l WHERE m.member_id = l.member_id GROUP BY m.member_id; to list all members with their loan counts, and gets 78 rows instead of 120. Her next query, meant to list categories whose average price for books priced above 200 exceeds 300, is written as SELECT category, AVG(price) FROM BOOKS GROUP BY category HAVING price > 200 AND AVG(price) > 300; and is rejected. She also plans to tidy the table with DELETE FROM LOANS; and re-enter the term's data. The librarian wants a working set of queries and a short note on what each one proves.
1. Fix the outstanding-books query and explain the behaviour of NULL that caused the zeros.
Read the worked answer
COUNT(column) counts non-NULL values only; COUNT(*) counts rows. Since return_date is NULL precisely for books that are still out, COUNT(return_date) counts the returned books and reports 0 for a member who has borrowed and returned nothing. The volunteer measured the opposite of what she wanted.
Correct forms:
SELECT member_id, COUNT(*) - COUNT(return_date) AS outstanding FROM LOANS GROUP BY member_id;
or, more explicitly,
SELECT member_id, SUM(CASE WHEN return_date IS NULL THEN 1 ELSE 0 END) AS outstanding FROM LOANS GROUP BY member_id;
The same NULL logic explains why return_date = NULL never matches: comparison with NULL yields UNKNOWN, so WHERE return_date = NULL returns no rows and must be written WHERE return_date IS NULL. It also explains why AVG(return_date)-style arithmetic or price + fine gives NULL for an unfinished loan - NULL is 'unknown', not zero, and it propagates through arithmetic. The fix is to test with IS NULL/IS NOT NULL or convert with COALESCE before doing arithmetic.
2. Why does the second query return 78 rows, and how should it be rewritten so all 120 members appear?
Read the worked answer
The query is an old-style inner join written with a comma and a WHERE condition: FROM MEMBERS m, LOANS l WHERE m.member_id = l.member_id. An inner join keeps only rows where the condition matches, so any member with no loan disappears - exactly the 42 members who have never borrowed. The GROUP BY then reports only the survivors, giving 78.
To keep all members, use an outer join and count the right column:
SELECT m.member_id, m.name, COUNT(l.loan_id) AS loans FROM MEMBERS m LEFT JOIN LOANS l ON m.member_id = l.member_id GROUP BY m.member_id, m.name ORDER BY loans DESC;
LEFT JOIN preserves every row of MEMBERS, and non-borrowers appear with NULLs in the loan columns. The crucial detail is COUNT(l.loan_id) rather than COUNT(*): after the outer join each non-borrower still has one row, so COUNT(*) would report 1 loan for someone who has never borrowed, while COUNT(l.loan_id) ignores the NULL and gives 0. Include m.name in GROUP BY (or rely on functional dependency, which not every DBMS allows) so the name is not picked arbitrarily.
3. Correct the WHERE/HAVING query, and comment on the planned `DELETE FROM LOANS;`.
Read the worked answer
The query is rejected because price > 200 is a row-level condition and HAVING may only test groups or aggregates; the DBMS cannot evaluate a bare price once rows are collapsed into a category. The rule is: filter rows in WHERE before grouping, filter groups in HAVING after grouping.
SELECT category, AVG(price) AS avg_price FROM BOOKS WHERE price > 200 GROUP BY category HAVING AVG(price) > 300;
This computes the average only over the expensive books and then keeps categories whose mean exceeds 300. The order of evaluation - FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY - explains both the rejection and why an alias defined in SELECT cannot always be used in WHERE.
On DELETE FROM LOANS;: it is valid DML and removes every row, but it is irreversible in most school setups and destroys the term's history the moment it runs. Safer choices are to export the old data first, to delete only test rows (DELETE FROM LOANS WHERE return_date IS NULL AND issue_date < '2024-01-01'; after checking the count), or to use a transaction with ROLLBACK until the new data is verified. DDL such as ALTER TABLE LOANS ADD fine DECIMAL(6,2) DEFAULT 0; is the safe way to extend a table; deleting data to 'tidy up' is not.