#033Level 3 of 6 · Scenario 6: The Install List
Back in time
Scenario 6 · The Install List
The three walls do not go up on the same day. A is hung on the 2nd, B on the 3rd, C on the 5th, and conservation's book gives a date each object is due back off the bench. Those two dates are the whole question, and they are different questions for different objects: a treatment finishing on the 4th is a disaster for wall A and no trouble at all for wall C.
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.
- EXISTS ( SELECT ... )
- True when the inner query finds at least one row. What it selects is irrelevant, so SELECT 1 is the convention.
- correlated subquery
- An inner query that mentions a column from the outer one, so it is asked again for every outer row.
Objective
Return the accession number, title, wall and wall date of every pick that is still on the conservation bench when its own wall goes up. Lowest accession number first.
Your query
⌘ ⏎
Results
No result yet.