Database Testing / Scenario
The Orphanage
Invoices are showing items from orders that no longer exist. No foreign keys, a NOT IN query that swears everything is fine, and a schema with a past.
- Difficulty
- Medium
- Format
- Scenario
- Points
- 150
- Estimate
- 13 min
// MISSION BRIEF
Your Mission
Support keeps escalating invoices that reference ghost orders. A previous engineer ran a check and declared "zero orphans". You have the schema, their query, and yours. Find the orphans, explain the lying query, and sequence the cleanup.
Scoring: weighted decisions; the query verdicts carry the most weight.
// FIRST CONTACT
Battle teaser
First artifact
Schema (excerpt)
The query that correctly counts orphaned order_items:
- ASELECT COUNT(*) FROM orders WHERE id NOT IN (SELECT order_id FROM order_items)
- BSELECT COUNT(*) FROM order_items oi LEFT JOIN orders o ON o.id = oi.order_id WHERE oi.order_id IS NOT NULL AND o.id IS NULL (or the equivalent NOT EXISTS)
- CSELECT COUNT(*) FROM order_items WHERE order_id IS NULL
- DThe previous engineer's query; zero means zero
Answers, scoring, hints, and the full battle stay sealed.
// SKILL TAGS
sqldatabase-testingdata-integritynull-semantics