#031Level 1 of 6 · Scenario 6: The Install List
Above the line
Scenario 6 · The Install List
A curator has sent the registrar a list of eight objects for the spring show and the insurers want to see it before anything moves. Their question is not what the show is worth; it is which pieces sit above the collection's own average, because those are the ones that need a separate rider. The valuations live in the register. The list does not carry them, and was never meant to.
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.
- ( SELECT ... )
- A query inside another query. One that returns a single value can stand anywhere a value can.
- AVG
- The mean of a numeric column.
Objective
Return the title and valuation of every object on the install list that is insured for more than the average valuation across the whole register. Highest valuation first.
Your query
⌘ ⏎
Results
No result yet.