The exhibition floor
Scenario 6 · The Install List
The curator's list runs to ninety-six objects and the walls go up across the first week of November, each on its own day. Three departments can stop an object reaching one. Conservation records a date the object is due back off the bench; loans records a date it is due home; provenance records a hold, which is open until somebody lifts it and nobody has. None of the three knows what the other two are holding, which is why the registrar's office exists. Somewhere on that list is one object that all three of them are holding at once, and it will not be found by reading any of their books.
Schema · 5 tables
- idpkINTEGER
- titleVARCHAR
- wingVARCHAR
- valuationDOUBLE
- idpkINTEGER
- object_idINTEGER
- wallVARCHAR
- wall_dateDATE
- idpkINTEGER
- object_idINTEGER
- treatmentVARCHAR
- due_backDATE
- idpkINTEGER
- object_idINTEGER
- object_noteVARCHAR
- borrowerVARCHAR
- returns_onDATE
- 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.
- EXISTS ( SELECT ... )
- True when the inner query finds at least one row.
- WITH name AS ( ... )
- Name a query and use it below like a table.
- UNION / UNION ALL
- Stack results. UNION removes duplicate rows; ALL keeps them.
- INTERSECT
- Keep only the rows that appear in both results.
Name the one object on the install list that all three departments are holding on the day its own wall goes up: still on the bench, still out on loan, and under a provenance hold. An object is only held by conservation or by loans if the date it comes back falls after that object's wall date.
Your query
The object nobody can hang
Results
No result yet.