Database Query Design
A library database has two tables: Books (BookID, Title, Author, YearPublished, Genre) and Loans (LoanID, BookID, MemberID, LoanDate, ReturnDate). A librarian wants to find all books published after 2010 that have never been loaned. They also want to list members who have borrowed more than 5 books in 2023. The database has thousands of records.
1. Write an SQL query to find all books published after 2010 that have never been loaned. Assume the Books table has BookID as primary key and Loans has BookID as foreign key.
Read the worked answer
Use a LEFT JOIN or NOT EXISTS. Query: SELECT b.* FROM Books b LEFT JOIN Loans l ON b.BookID = l.BookID WHERE b.YearPublished > 2010 AND l.BookID IS NULL; Alternatively: SELECT * FROM Books WHERE YearPublished > 2010 AND BookID NOT IN (SELECT BookID FROM Loans);
2. Write an SQL query to list members (MemberID) who borrowed more than 5 books in 2023. Consider that a member may have multiple loans; count distinct BookID or LoanID? Explain your choice.
Read the worked answer
Query: SELECT MemberID FROM Loans WHERE YEAR(LoanDate) = 2023 GROUP BY MemberID HAVING COUNT(DISTINCT BookID) > 5; We count distinct BookID because a member might borrow the same book multiple times? Actually, if they borrow the same book twice, it's two loans but one book. The question says 'borrowed more than 5 books' meaning distinct books. So COUNT(DISTINCT BookID) is appropriate. If counting loans, use COUNT(*).
3. What issue might arise if the Loans table is very large and we run the first query without an index? Suggest an optimization.
Read the worked answer
Without an index on Loans.BookID, the LEFT JOIN or subquery would require a full table scan of Loans for each book, leading to slow performance. Create an index on Loans.BookID: CREATE INDEX idx_loans_bookid ON Loans(BookID); This speeds up the join and subquery.