SQL (SQLite engine)

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.

Test URL
/lab/database-testing-verify-zero-data-loss-on-migration

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

  1. 1Create customer_addresses (customer_id INTEGER PRIMARY KEY REFERENCES customers(id), city TEXT, country TEXT NOT NULL).
  2. 2Copy id, city, country from customers; three customers have a NULL city and must survive the copy.
  3. 3Compute source_rows, target_rows, missing_rows (customers EXCEPT customer_addresses) and unexpected_rows (the reverse).
  4. 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