#021Level 4 of 6 · Scenario 4: Provenance
Reading the chain
Scenario 4 · Provenance
The provenance file for a catalogue entry has to read as sentences — who sold it, who bought it, in order. The transfers table stores both parties as ids pointing at the same owners table, and the first step of every chain has no seller at all, because that is the object entering the record.
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.
- LEFT JOIN
- Keeps every row from the first table.
- AS
- Give a table an alias. Required when you join the same table twice.
Objective
Return the object title, the step number, the seller's name and the buyer's name for every transfer, ordered by title then step. Keep the first step of each chain, where there is no seller.
Your query
⌘ ⏎
Results
No result yet.