INTERACTIVE LECTURES
Build and change a database
Guided class activity · apply the ideas from the full SQL lessons.
Open the detailed lessons for this activity
Detailed lessons in this module
Open the full explanations, examples, and SQL exercises directly.
Preparing for Lab 10 · After practice, try the team challenge.
Opening your saved work…
Sign in to save across devices → · Download your guest work before switching accounts.
Find the rows a parent table cannot support
Step 7 of 9 · Database: Trail Gear: build rehearsal
READ & UNDERSTAND
Use LEFT JOIN from staging to the parent to expose missing matches, and test the parent key for NULL. The direction matters: an INNER JOIN returns only rows that found a parent, making the very problem you need to document disappear.
The fixed Trail Gear source has a type_id of 99 that has no parent. Its audit records that row as Excluded. In a real client load, validate the identifier text before casting, and decide whether the brief requires exclusion, correction, or a nullable relationship. Those are business decisions, not interchangeable repairs.
A parent deletion is governed by its declared action and connection settings. This kit uses the default action and refuses to delete a referenced gear type with enforcement on. CASCADE, SET NULL and other actions behave differently; inspect the schema rather than assuming every foreign key has the same deletion rule.
Worked example
SELECT s.serial, s.type_id, g.name FROM stg_items s LEFT JOIN gear_types g ON g.id = CAST(s.type_id AS INTEGER) ORDER BY s.rowid;
The full left join preserves the unmatched source row with a missing parent name.
The filtered output is the evidence list to compare with load_audit, not a new application row to insert.
Your question
Return s.serial and s.type_id for staged rows whose gear type has no parent, ordered by s.rowid. The type IDs in this fixed fixture are valid integer text.
Explain your evidence
Why would creating a fake parent merely to silence the foreign-key error be a poor default repair?
Try a query to unlock the worked-answer discussion.
Query results
Predict the result, then run your query.Predict the result, then run your query.
Read-only activity · One statement per run · First 200 rows displayed · Checks compare the complete bounded result, not just this preview.
Explore this query freely in the playground ↗Check the save status above. Account-saved work is available in My Progress. Explanations are for discussion, not automatically graded. No prerequisite locks.