Practice Exercises

Time to get your hands dirty. These exercises simulate real migration scenarios you will face in projects. Try writing the SQL yourself before looking at the hints. The goal is to build the muscle memory so that when a real migration lands on your desk, you know exactly what queries to write.

Exercise 1: Banking Portal Migration

The Banking Portal is migrating from a legacy schema to a normalized schema. The old schema has one table: users(id, name, email, phone, account_number, balance, address). The new schema splits this into three tables: users(id, first_name, last_name, email, phone), accounts(id, user_id, account_number, balance, type), addresses(id, user_id, street, city, state, pin).

1. Write pre-migration profiling queries: record counts, distinct emails, NULL phone counts, min/max balance, duplicate emails
2. Write a data mapping document: which old column maps to which new column in which new table
3. Write post-migration validation queries: record counts, missing records, name split verification, balance match
4. Write referential integrity checks: every account links to a valid user, every address links to a valid user

Exercise 2: Date Format Migration

The transactions table stores dates as VARCHAR in format "DD-MM-YYYY" (Indian format). The new system needs proper DATE columns. Write SQL to:

1. Find all records with invalid date strings (not matching DD-MM-YYYY pattern)
2. Convert the VARCHAR dates to DATE type and verify they converted correctly
3. Check for dates that might be ambiguous (like 01-02-2024 — is it Jan 2 or Feb 1?)
4. Verify no dates became NULL after conversion when the source was NOT NULL

-- Hint: Find invalid date strings
SELECT txn_id, txn_date_varchar
FROM transactions
WHERE txn_date_varchar NOT REGEXP
  '^[0-9]{2}-[0-9]{2}-[0-9]{4}$';

-- Hint: Convert and compare
SELECT txn_id,
  txn_date_varchar,
  STR_TO_DATE(txn_date_varchar, '%d-%m-%Y') AS converted_date
FROM transactions
WHERE STR_TO_DATE(txn_date_varchar, '%d-%m-%Y') IS NULL
  AND txn_date_varchar IS NOT NULL;

Exercise 3: Platform Migration — Oracle to MySQL

Your company is migrating from Oracle to MySQL. Write queries to verify:

1. Oracle SYSDATE columns converted correctly to MySQL CURRENT_TIMESTAMP
2. Oracle NUMBER(10,2) precision preserved in MySQL DECIMAL(10,2)
3. Oracle sequences replaced with MySQL AUTO_INCREMENT (verify no ID gaps or duplicates)
4. Oracle NVL() calls in stored procedures replaced with MySQL COALESCE()

Exercise 4: Write a Complete Migration Test Plan

The insurance portal is migrating its claims data to a new cloud database. Write a test plan that covers:

1. Pre-migration: list all tables, record counts, data profiling for the claims table
2. Data mapping: old claims(id, policy_no, customer_name, amount, status, filed_date) to new claims(id, policy_id, amount, status, filed_at) with a separate policies table
3. Post-migration: record counts, missing records, field comparisons, referential integrity
4. Performance: benchmark three critical queries before and after
5. Rollback plan: how to verify rollback worked if the migration fails

Exercise 5: Debug This Migration Script

This migration script has four bugs. Find them all.

-- Migration: users table to new schema
INSERT INTO new_db.users (id, first_name, last_name, email, phone)
SELECT
  id,
  SUBSTRING_INDEX(name, ' ', 1) AS first_name,
  SUBSTRING_INDEX(name, ' ', -1) AS last_name,
  email,
  phone
FROM old_db.users;

-- Bug 1: Single-word names — first_name and last_name will be the same
-- Bug 2: Names with 3+ words — "Aamir Khan Shah" → first="Aamir", last="Shah", middle lost
-- Bug 3: No WHERE clause to exclude soft-deleted users (is_deleted = 1)
-- Bug 4: No ON DUPLICATE KEY handling — re-running this creates duplicates

Exercise 6: ETL Validation

An ETL job extracted customer data from three source systems (CRM, Billing, Support), transformed and merged them into a single target table. Write queries to check:

1. Total records from all three sources equals the target count (minus expected deduplication)
2. No duplicate customer_ids in the target after merging
3. Email normalization worked (all lowercase, no leading/trailing spaces)
4. Records from each source system can be traced back (source_system column populated)
5. The deduplication rule was applied correctly (latest record kept, older ones discarded)