Relate Tables Instead of Repeating Data · capstone
Capstone project: Project: Report the Lending Desk
The lending desk needs one view that answers two questions at once: which items exist, and who has each item right now?
Build that view independently in report.sql. Return exactly these five columns:
| Heading | |
|---|---|
asset_tag | The item’s tag |
item_name | The item’s name |
borrower_code | The current member code, or NULL |
borrower_name | The current member name, or NULL |
checked_out_on | The active checkout date, or NULL |
Start from items, because every item must appear. Left-join only active loans, keeping the returned_on IS NULL test inside that join’s ON members through the loan’s member ID. The second join must also preserve an item when the first join found no active loan.
Use column aliases for the two result names and order the final rows by items.asset_tag. Do not add a filter that removes missing matches. Available items belong in the report with three NULL borrower fields.
Press Run and inspect both kinds of row:
IT-100 | Cordless drill | MB-017 | Riley | 2026-08-18
IT-200 | Folding table | NULL | NULL | NULL
The folding table’s returned history must not appear as a current borrower. The projector screen and repair kit have no history, but they should have the same empty borrower shape. The projector’s active loan should show Sam.
Submit tests IDs and names you have not seen, including duplicate displayed names and a member with several active loans. It requires one correct row per item and the exact headings, but it accepts harmless SQL formatting and any equivalent joins that preserve the same behavior.
The finished report shows why these small relationship rows are so useful. A query follows them when details are needed, keeps unmatched items when the question requires them, and interprets a missing active match without another availability column.
Task
Build the lending-desk report in report.sql.
Return one row per item with asset_tag, item_name, borrower_code, borrower_name, and checked_out_on, ordered by asset tag. Match only an active loan. Available items must remain with NULL borrower values, and returned history must not appear as a current borrower.