Practice and reinforce the concepts from Lesson 4
The library director wants a dashboard showing real-time lending statistics. Build SQL queries to power each dashboard widget!
Open this CodeSandbox: Interactive SQL Library Database
Build these dashboard features:
Show: Title, Borrower Name, Due Date, Days Until Due Sort by: Due date (soonest first)
Show: User name, Books borrowed this month, Currently has Filter: Only users who borrowed in last 30 days
Show: Books that have NEVER been borrowed Include: Title, Author, Days in library
Create a "Users who borrowed X also borrowed Y" feature
-- Fix this query to show borrower names instead of IDs:
SELECT book_id, user_id, due_date
FROM loans
WHERE returned_at IS NULL;
-- Hint: You need to JOIN two more tables!
Expected Output:
title | borrower_name | due_date | days_until_due
--------------------|------------------|-------------|---------------
"The Great Gatsby" | John Smith | 2024-02-01 | 3
"To Kill a..." | Jane Doe | 2024-02-05 | 7
-- Start with this skeleton:
SELECT
-- Add columns here
FROM loans l
-- Add your JOINs here
WHERE returned_at IS NULL
-- Add your ORDER BY here
Hints:
books table to get book titlesusers table to get borrower namesDATEDIFF() or similar to calculate days until due-- Build a query to find active borrowers:
SELECT
-- Show user name and count
FROM loans l
-- Add your JOINs
WHERE
-- Filter for last 30 days
GROUP BY
-- Group by user
ORDER BY
-- Sort by most active
LIMIT 10;
Test Your Query: ✓ Should show maximum 10 users ✓ Count should ``be >= 1`` for all users ✓ Should include both returned and current loans
-- Find books with no loan history:
-- Hint: What type of JOIN finds records with no match?
Your dashboard loads slowly with 100k+ records. Add indexes to make it instant!
-- Measure query time before indexes:
EXPLAIN ANALYZE [your query here];
-- Add strategic indexes:
CREATE INDEX idx_loans_returned ON loans(returned_at);
-- What other indexes would help?
-- Measure again and compare!
Run these to verify your queries:
-- Test 1: Verify overdue books appear first
-- Insert a book due yesterday
INSERT INTO loans (book_id, user_id, due_date, returned_at)
VALUES (1, 1, DATE_SUB(NOW(), INTERVAL 1 DAY), NULL);
-- Test 2: Verify never-borrowed books count
SELECT COUNT(*) FROM books b
LEFT JOIN loans l ON b.id = l.book_id
WHERE l.id IS NULL;
-- Should match your query's row count
-- Test 3: Check for duplicate borrowers
-- Your top borrowers shouldn't have duplicates
SELECT
b.title,
u.name as borrower_name,
l.due_date,
DATEDIFF(l.due_date, CURDATE()) as days_until_due
FROM loans l
INNER JOIN books b ON l.book_id = b.id
INNER JOIN users u ON l.user_id = u.id
WHERE l.returned_at IS NULL
ORDER BY l.due_date ASC;
Think about:
Post your dashboard queries in the forum:
Build a "Smart Recommendations" query:
Share your recommendation algorithm with the class!