The Library Query That Returns 13,500 Rows

In SQL, NULL is not a value but a marker for 'unknown'. Any comparison with NULL yields UNKNOWN, and WHERE keeps only rows for which the condition is TRUE. Therefore return_date = NULL is never TRUE, even for rows where return_date is NULL; COUNT() counts zero such rows. The correct predicate is return_date IS NULL. The 13,500 figure is 300 × 45: the FROM clause lists Members, Books and Loans with no join conditions, so every member row is paired with every book row and every outstanding loan row - a Cartesian product. The WHERE clause filters to the 45 outstanding loans but does not connect member_id or book_id, so each of the 300 members appears with each of the 45 loans. Correct count: SELECT COUNT() FROM Loans WHERE return_date IS NULL; and for a joined list, add JOIN ... ON Loans.member_id = Members.member_id AND Loans.book_id = Books.book_id.

SELECT m.member_name, m.class, b.title, DATEDIFF('2025-02-10', l.due_date) AS days_overdue FROM Loans l JOIN Members m ON l.member_id = m.member_id JOIN Books b ON l.book_id = b.book_id WHERE l.return_date IS NULL AND l.due_date < '2025-02-10' ORDER BY days_overdue DESC; The join conditions are essential; without them the result is a Cartesian product. return_date IS NULL excludes returned copies, and due_date < date excludes loans due today, which are not yet overdue. DATEDIFF(expr1, expr2) in MySQL returns expr1 − expr2 in days, so the later date goes first. In standard SQL or SQLite, use date subtraction or CURRENT_DATE, and if 'today' is dynamic replace the literal with CURRENT_DATE. The query assumes one loan row per physical copy and that due_date is never NULL; if it can be NULL, add due_date IS NOT NULL. It also assumes a book can have multiple loan records over time.

Storing member_name and class in Loans repeats data already in Members; class is functionally dependent on member_id, not on loan_id, so the table is not in 3NF. Storing book_title repeats data from Books; title depends on book_id. Update anomaly: when a member changes class, staff must update every loan row, and any missed row leaves conflicting class values. Insertion anomaly: a new member or book cannot be recorded until a loan exists if the loan table is the only place with the details. Deletion anomaly: deleting the last loan can erase the only record of a member or book. Revised: Members(member_id PK, member_name, class), Books(book_id PK, title, category), Loans(loan_id PK, member_id FK, book_id FK, issue_date, due_date, return_date). Foreign keys enforce referential integrity, and a member's class is stored once. If a book can have multiple copies, add copy_id and make Loans reference the copy. The rule is that each fact should be stored in one place.