The Library Ledger — SQL Milestone Project

You have finished Part II. Now you will operate a small lending library database across five related tables: members, books, physical copies, loans, and fines.

Your job is to build a reproducible ledger notebook. Some answers are read-only reports; the final checkpoint changes data. Every multi-row answer needs a stable ORDER BY, and every UPDATE or DELETE must be rehearsed with a nearby SELECT first.

What you'll build

You will produce a query notebook with four sections:

Ledger checks: count the five tables and map the copy inventory.
Circulation reports: overdue loans, member history, availability, and unpaid fines.
Query composition: never-borrowed copies, overlap/difference questions, and a CTE risk list.
Safe writes: check out a copy, return an overdue loan, assess a fine, receive payment, and delete a mistaken fine.

Use DATE '2026-07-15' as the report date throughout. A loan is currently overdue when returned_on IS NULL and due_on < DATE '2026-07-15'.

Workspace

Run the starter queries first. Then replace the TODO comments with your own answers.

sql — playgroundlive

-- The Library Ledger workspace
-- Tables: members, books, copies, loans, fines
-- Report date: 2026-07-15

SELECT 'members' AS table_name, count(*) AS rows FROM members
UNION ALL SELECT 'books', count(*) FROM books
UNION ALL SELECT 'copies', count(*) FROM copies
UNION ALL SELECT 'loans', count(*) FROM loans
UNION ALL SELECT 'fines', count(*) FROM fines
ORDER BY table_name;

SELECT l.loan_id,
     m.full_name,
     b.title,
     l.due_on,
     DATE '2026-07-15' - l.due_on AS days_overdue
FROM loans AS l
JOIN members AS m
ON m.member_id = l.member_id
JOIN copies AS c
ON c.copy_id = l.copy_id
JOIN books AS b
ON b.book_id = c.book_id
WHERE l.returned_on IS NULL
AND l.due_on < DATE '2026-07-15'
ORDER BY days_overdue DESC, l.loan_id;

-- TODO Checkpoint 1: ledger checks
-- TODO Checkpoint 2: circulation reports
-- TODO Checkpoint 3: query composition

-- TODO Checkpoint 4: safe writes
-- Q13 checkout scaffold: rehearse the member and copy, then insert the loan.
-- SELECT c.copy_id, c.status, m.member_id, m.full_name
-- FROM copies AS c CROSS JOIN members AS m
-- WHERE c.copy_id = 1008 AND m.member_id = 7;
-- INSERT INTO loans (loan_id, copy_id, member_id, checked_out_on, due_on, returned_on)
-- VALUES (5015, 1008, 7, DATE '2026-07-15', DATE '2026-07-29', NULL)
-- RETURNING loan_id, copy_id, member_id, due_on;
-- SELECT copy_id, status FROM copies WHERE copy_id = 1008;
-- UPDATE copies
-- SET status = 'on_loan'
-- WHERE copy_id = 1008
-- RETURNING copy_id, status;

-- Q14 return scaffold: rehearse each target before each update.
-- SELECT l.loan_id, l.copy_id, l.due_on, l.returned_on, c.status
-- FROM loans AS l JOIN copies AS c ON c.copy_id = l.copy_id
-- WHERE l.loan_id = 5006;
-- UPDATE loans
-- SET returned_on = DATE '2026-07-15'
-- WHERE loan_id = 5006
-- RETURNING loan_id, returned_on;
-- SELECT copy_id, status FROM copies WHERE copy_id = 1007;
-- UPDATE copies
-- SET status = 'available'
-- WHERE copy_id = 1007
-- RETURNING copy_id, status;
-- INSERT INTO fines (fine_id, loan_id, assessed_on, amount, paid_on)
-- VALUES (9006, 5006, DATE '2026-07-15', 5.00, NULL)
-- RETURNING fine_id, loan_id, amount, paid_on;

-- Q15 payment and cleanup scaffold: rehearse before the update and delete.
-- SELECT fine_id, amount, paid_on FROM fines WHERE fine_id = 9002;
-- UPDATE fines
-- SET paid_on = DATE '2026-07-15'
-- WHERE fine_id = 9002
-- RETURNING fine_id, amount, paid_on;
-- SELECT f.fine_id, f.amount, l.due_on, l.returned_on
-- FROM fines AS f JOIN loans AS l ON l.loan_id = f.loan_id
-- WHERE f.fine_id = 9005;
-- DELETE FROM fines
-- WHERE fine_id = 9005
-- RETURNING fine_id, amount;

⌘/Ctrl + Enter to run

Checkpoint 1: trust the ledger

Answer these before building reports. They prove you understand the grain of each table.

How many rows are in each table?
How many copies are available versus on_loan?
Which books have more than one copy?
Which members have never had a loan?

Acceptance criteria:

The table counts are: books 7, copies 12, fines 5, loans 14, members 8.
Copy status counts are available 7 and on_loan 5.
Five books have two copies: Clean Code, Designing Data-Intensive Applications, Invisible Cities, The Left Hand of Darkness, and The Pragmatic Programmer.
The members with no loans are Ivy Stone and Jules Reed, in member id order.

Checkpoint 2: circulation reports

Now answer the questions the front desk needs on the report date.

Which open loans are overdue as of 2026-07-15, and how many days overdue is each?
What is Maya Chen's full loan history?
For every book, how many copies are currently available and how many are on loan?
Which members have unpaid fines, and what is each unpaid total?

Acceptance criteria:

There are 3 overdue open loans: loan 5005 for Nia Patel is 14 days overdue, loan 5006 for Omar Brooks is 10 days overdue, and loan 5013 for Sofia Rossi is 1 day overdue.
Maya Chen has 3 loans: Clean Code, Designing Data-Intensive Applications, and Invisible Cities in checkout-date order.
The availability report has 7 rows. Data and Reality and SQL Antipatterns each have 1 available copy and 0 on loan; the other five books each have 1 available and 1 on loan.
Unpaid fines belong to Omar Brooks 2.50, Luis Ortega 2.00, and Theo Kim 1.00.

Checkpoint 3: query composition

Use the Part II tools deliberately here: NOT EXISTS, INTERSECT, EXCEPT, and CTEs all fit naturally.

Which physical copies have never been borrowed?
Which members have both an open loan and an unpaid fine?
Which books were borrowed in June 2026 but not borrowed in July 2026?
Build a CTE risk list with one row per member who has either overdue open loans or unpaid fines. Include full_name, overdue_loans, and unpaid_total.

Acceptance criteria:

The never-borrowed copies are 1008 Data and Reality and 1009 SQL Antipatterns.
The members with both an open loan and an unpaid fine are Luis Ortega and Omar Brooks.
The books borrowed in June but not July are Designing Data-Intensive Applications and The Left Hand of Darkness.
The risk list has 5 members. In descending risk order: Omar Brooks has 1 overdue loan and 2.50 unpaid, Nia Patel has 1 overdue loan and 0 unpaid, Sofia Rossi has 1 overdue loan and 0 unpaid, Luis Ortega has 0 overdue and 2.00 unpaid, and Theo Kim has 0 overdue and 1.00 unpaid.

Checkpoint 4: safe write operations

Run these in order in one playground session. If you press Run again, the dataset resets and you can repeat the sequence from the beginning. Keep the rehearsal ritual visible in your notebook.

Check out copy 1008, Data and Reality, to Ivy Stone on 2026-07-15, due 2026-07-29. Use loan id 5015. Rehearse that copy 1008 is available and member 7 is Ivy before the insert. Then rehearse copy 1008 before updating its status to on_loan.
Return loan 5006 on 2026-07-15, make copy 1007 available again, and assess fine 9006 for 5.00. Rehearse loan 5006 before updating it, and rehearse copy 1007 before updating it.
Receive payment for fine 9002 on 2026-07-15, then delete mistaken fine 9005. Rehearse fine 9002 before updating it. Rehearse fine 9005 by joining to its loan and proving returned_on = due_on before deleting it.

Acceptance criteria:

After question 13, loan 5015 exists for copy 1008 and member 7, due on 2026-07-29; copy 1008 has status on_loan; open loans count is 6 at this point.
After question 14, loan 5006 has returned_on = 2026-07-15, copy 1007 has status available, fine 9006 exists for amount 5.00 with paid_on still NULL, and open loans count is 5.
After question 15, fine 9002 has paid_on = 2026-07-15, fine 9005 is gone, and the final unpaid fine summary is 2 fines totaling 7.50.

Stretch goals

Try these after the required 15 answers pass:

Add a CASE label to the overdue report: severe for at least 10 days overdue, watch otherwise.
Use FILTER to put available and on-loan copy counts in one row per category.
Use a CTE to calculate late returned loans, then summarize average days late by member.
Add a safe cancellation flow for the new checkout: rehearse loan 5015, delete it, rehearse copy 1008, and set the copy back to available.

How to get unstuck

Start from the table grain. members and books are entities; copies are physical items; loans are events; fines are charges attached to loans. If a join duplicates rows, ask which table changed the grain.

Use 5.2 and 5.3 for the joins, 7.2 for NOT EXISTS, 7.3 for the risk-list CTE, and 7.4 for INTERSECT and EXCEPT. For the write checkpoint, use Section 8's full ritual: rehearse the target, run the write, then verify with RETURNING, a command tag, or a follow-up SELECT.