#022Level 5 of 6 · Scenario 4: Provenance
One step to the next
Scenario 4 · Provenance
A chain of custody only makes sense read in pairs: whoever received it at one step should be whoever passes it on at the next. Every transfer carries a step number, so consecutive steps are findable — but a row cannot see its neighbour without help.
Schema · 3 tables
owners
- idpkINTEGER
- nameVARCHAR
- countryVARCHAR
objects
- idpkINTEGER
- titleVARCHAR
- wingVARCHAR
- current_owner_idINTEGER
transfers
- idpkINTEGER
- object_idINTEGER
- seqINTEGER
- from_owner_idINTEGER
- to_owner_idINTEGER
- transferred_onDATE
- document_refVARCHAR
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.
- IS NULL
- Test whether a value is missing.
- INNER JOIN
- Keeps only rows that matched.
- self-join
- Join a table to itself under two aliases, to compare a row with another row.
Objective
For every pair of consecutive steps, return the object title, the earlier step number, who that step handed the object to, and who the next step names as the seller. Order by title then step.
Your query
⌘ ⏎
Results
No result yet.