#032Level 2 of 6 · Scenario 6: The Install List
Still in the building
Scenario 6 · The Install List
Before the registrar can walk the list she needs to know what is actually here. Loans out keeps its own book: one row per object that has left the building, with the borrower and the date it is due home. It also keeps a column for the accession number, which the clerk fills in when there is time. One of the four rows on the book has never had it filled in.
Schema · 5 tables
objects
- idpkINTEGER
- titleVARCHAR
- wingVARCHAR
- valuationDOUBLE
picks
- idpkINTEGER
- object_idINTEGER
- wallVARCHAR
- wall_dateDATE
bench
- idpkINTEGER
- object_idINTEGER
- treatmentVARCHAR
- due_backDATE
loans_out
- idpkINTEGER
- object_idINTEGER
- object_noteVARCHAR
- borrowerVARCHAR
- returns_onDATE
holds
- idpkINTEGER
- object_idINTEGER
- raised_byVARCHAR
- raised_onDATE
Toolkit
- SELECT
- Choose which columns come back.
- FROM
- Specify which table to read.
- WHERE
- Filter rows by a condition.
- ORDER BY
- Sort the results by a column.
- INNER JOIN
- Keep only rows that matched in both tables.
- IS NULL
- Test for a missing value. Never use = NULL.
- COUNT(*)
- How many rows there are.
- GROUP BY
- Split the rows into groups first, then aggregate each one.
- IN ( SELECT ... )
- Test a value against a whole column of values, produced by another query.
- NOT IN
- The same test, negated. Read the explanation on this one.
Objective
Return the accession number and title of every object on the install list that is not currently out on loan, lowest accession number first.
Your query
⌘ ⏎
Results
No result yet.