Verify zero data loss on migration
Expert190 pts~70 min
- Data migration
- Zero data loss
- EXCEPT
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
Move customer addresses into a new customer_addresses table and prove, with counts and two-way set differences, that no row or value was lost.
Your task
- 1Create customer_addresses (customer_id INTEGER PRIMARY KEY REFERENCES customers(id), city TEXT, country TEXT NOT NULL).
- 2Copy id, city, country from customers; three customers have a NULL city and must survive the copy.
- 3Compute source_rows, target_rows, missing_rows (customers EXCEPT customer_addresses) and unexpected_rows (the reverse).
- 4Return them as one row: SELECT (SELECT COUNT(*) FROM customers) AS source_rows, … AS target_rows, … AS missing_rows, … AS unexpected_rows.
Acceptance criteria
- customer_addresses holds every customer's city and country
- The last result set is (30, 30, 0, 0)
- The verification uses EXCEPT
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 · Migrations & schema