Detect orphaned records
Hard120 pts~45 min
- Orphaned records
- Anti-join
- Referential integrity
Practice app · Acme Commerce DB
A real SQL database (SQLite engine) seeded with an e-commerce and HR dataset, with schema browser and query runner.
Your starter code already declares TEST_URL — never hardcode a host.
Objective
Reproduce a legacy import that ran with foreign keys disabled, then write the data-quality query that finds the orphaned orders.
Your task
- 1Simulate the bad import: PRAGMA foreign_keys = OFF; DELETE FROM customers WHERE id = 4; PRAGMA foreign_keys = ON;
- 2Find every order whose customer no longer exists.
- 3Return orders.id and orders.customer_id for each orphan.
Acceptance criteria
- The last result set lists exactly the orders of the deleted customer
- The query uses an anti-join (LEFT JOIN … IS NULL, NOT EXISTS, NOT IN) or foreign_key_check
Constraints
- Every run starts from a freshly seeded database, so your script must include its own setup statements.
- Only the last result set of your script is compared, so finish with the verification query.
SQL (SQLite engine) · Database Testing · CRUD & data integrity