Library Reports That Count Wrong: Rewriting Queries and Repairing Data Types
Saraswati Vidyalaya's library runs MySQL 8 with three tables: members(member_id INT PRIMARY KEY, name VARCHAR(60), class VARCHAR(10), email VARCHAR(80)), books(book_id INT PRIMARY KEY, title VARCHAR(120), isbn INT, copies INT), and loans(loan_id INT PRIMARY KEY, member_id INT, book_id INT, issue_date VARCHAR(10), due_date VARCHAR(10), return_date VARCHAR(10) NULL). Dates were typed by hand as dd/mm/yyyy. The librarian's pending-returns report is:
SELECT m.name, COUNT(*) AS pending FROM members m LEFT JOIN loans l ON m.member_id = l.member_id WHERE l.return_date IS NULL GROUP BY m.name;
It returns 200 rows for 180 members, merges two different students both named Aarti Patil into one line, and never lists first-year students who have not yet borrowed. The overdue list WHERE due_date < CURDATE() returns nothing although about 30 books are late. An ISBN-13 import aborted with "Out of range value for column 'isbn'".
1. Rewrite the pending-returns query and explain each change, showing why 180 rows is the correct answer.
Read the worked answer
SELECT m.member_id, m.name, COUNT(l.loan_id) AS pending
FROM members m
LEFT JOIN loans l
ON l.member_id = m.member_id
AND l.return_date IS NULL
GROUP BY m.member_id, m.name;
Change 1 - move the condition into ON. In the WHERE clause, l.return_date IS NULL rejects every row where the LEFT JOIN produced NULLs, which silently turns the outer join into an inner join. That is exactly why non-borrowers vanish. Tested during the join instead, non-borrowers survive with a NULL loan row and report 0.
Change 2 - COUNT(l.loan_id), not COUNT(*). COUNT(*) counts rows; a non-borrower still contributes one NULL-extended row and would be reported as having 1 pending book. COUNT of a column ignores NULLs.
Change 3 - GROUP BY the key. Grouping by name collapses the two Aarti Patils into a single line with their loans summed, which is one reason the row count is inflated to 200 while members number 180. Grouping by member_id keeps them apart. Under MySQL 8's default ONLY_FULL_GROUP_BY, selecting a non-aggregated column not functionally dependent on the grouping key is also an error, so this form is the only portable one.
Result. One row per member, 180 rows, correct counts, non-borrowers included.
2. Choose correct data types for isbn and the three date columns, and explain why the overdue query returns nothing.
Read the worked answer
isbn. Make it CHAR(13) (or VARCHAR(17) if hyphens are kept). An ISBN is an identifier, never a quantity: it is never added or averaged, it may begin with 0 that an integer would drop, and 13 digits exceed INT's signed maximum of 2,147,483,647 - which is precisely the "out of range" error on import. Storing it as text also allows checksum validation later without casting.
Dates. Make issue_date, due_date and return_date DATE. With dd/mm/yyyy strings, due_date < CURDATE() compares text with text: '15/03/2025' against '2026-01-12'. The first character '1' is less than '2', so the comparison is true for every row regardless of the actual date - or, depending on the values, false for all; either way the result is meaningless. Migrate with UPDATE loans SET due_date_new = STR_TO_DATE(due_date,'%d/%m/%Y') into a new DATE column, verify, then drop the old one. DATE also enables DATEDIFF for fine calculation and sorts correctly in an index.
NULL trap. Never write return_date = NULL; comparison with NULL yields UNKNOWN, never TRUE, so the predicate matches no rows. Only IS NULL or IS NOT NULL test for nullness - this also applies inside the corrected join above.
3. The library also wants: no two members sharing an email, no loan pointing at a deleted member or book, and a reliable count of how many copies of a title are currently out. State where each rule belongs and how to enforce it.
Read the worked answer
Unique email. Enforce with a UNIQUE index on members.email plus NOT NULL, deciding first whether the school issues one address per student - siblings sharing a parent's address would legitimately break a naive unique rule, so the policy comes before the constraint. The database must carry it regardless, because application-level pre-checks race under concurrent inserts.
Referential integrity. Add foreign keys: loans.member_id REFERENCES members(member_id) and loans.book_id REFERENCES books(book_id), with ON DELETE RESTRICT - a member or title with loan history must not be silently erased, and RESTRICT makes the librarian remove or archive loans deliberately. ON DELETE CASCADE would destroy the audit trail and is the wrong default here.
Copies currently out. Do not trust a hand-maintained counter column that drifts whenever a return is missed. Derive it: SELECT b.title, COUNT(*) AS out_now FROM loans l JOIN books b USING (book_id) WHERE l.return_date IS NULL GROUP BY b.book_id, b.title; and validate it against copies so an over-borrowed title is visible rather than hidden. If a denormalised counter is kept for speed, update it in the same transaction as the insert or update on loans, and reconcile nightly.
Also worth adding. A CHECK that due_date >= issue_date, and an index on loans(return_date, member_id) so the pending-returns report does not scan the whole table.