Here are hands-on SQL exercises and test case examples for validating databases and SQL queries:
1. Basic Database Validation
Exercise: Check Data Consistency
✅ Scenario: Verify that every order in the Orders table has a corresponding customer in the Customers table.
🔹 SQL Query:
SELECT o.order_id, o.customer_id
FROM Orders o
LEFT JOIN Customers c ON o.customer_id = c.customer_id
WHERE c.customer_id IS NULL;
🔹 Test Case Example:
| Test Case ID | Test Scenario | Expected Result |
|---|---|---|
| TC_DB_001 | Check if all orders have valid customers | No rows should be returned |
2. Data Completeness & Integrity Checks
Exercise: Identify Missing Data
✅ Scenario: Ensure that every product in the Products table has a valid price.
🔹 SQL Query:
SELECT * FROM Products WHERE price IS NULL OR price <= 0;
🔹 Test Case Example:
| Test Case ID | Test Scenario | Expected Result |
|---|---|---|
| TC_DB_002 | Check if any product has a missing or invalid price | No rows should be returned |
3. Data Transformation & Business Rules Validation
Exercise: Validate Discount Calculation
✅ Scenario: Verify that a 10% discount is correctly applied to all product prices.
🔹 SQL Query:
SELECT product_id, original_price, discounted_price
FROM Products
WHERE discounted_price != original_price * 0.9;
🔹 Test Case Example:
| Test Case ID | Test Scenario | Expected Result |
|---|---|---|
| TC_DB_003 | Validate 10% discount calculation on products | No rows should be returned |
4. Performance & Optimization Checks
Exercise: Optimize Query for High-Volume Data
✅ Scenario: Fetch the top 5 highest-spending customers efficiently.
🔹 Optimized SQL Query:
SELECT customer_id, SUM(total_amount) AS total_spent
FROM Orders
GROUP BY customer_id
ORDER BY total_spent DESC
LIMIT 5;
🔹 Test Case Example:
| Test Case ID | Test Scenario | Expected Result |
|---|---|---|
| TC_DB_004 | Retrieve top 5 customers based on spending | Query returns correct top 5 customers |
5. ETL & Data Migration Validation
Exercise: Compare Record Counts After Migration
✅ Scenario: Validate that the total number of records in the Customers table is the same in both source and target databases after migration.
🔹 SQL Query for Source DB:
SELECT COUNT(*) FROM SourceDB.Customers;
🔹 SQL Query for Target DB:
SELECT COUNT(*) FROM TargetDB.Customers;
🔹 Test Case Example:
| Test Case ID | Test Scenario | Expected Result |
|---|---|---|
| TC_DB_005 | Compare customer record counts after ETL migration | Counts should match in both databases |
6. Security & Access Control Checks
Exercise: Check for Unauthorized Admin Access
✅ Scenario: Identify users who have an admin role but haven't logged in for the last 6 months.
🔹 SQL Query:
SELECT user_id, username, last_login
FROM Users
WHERE role = 'admin' AND last_login < NOW() - INTERVAL 6 MONTH;
🔹 Test Case Example:
| Test Case ID | Test Scenario | Expected Result |
|---|---|---|
| TC_DB_006 | Identify inactive admin users | Only inactive admin users should be listed |
7. Complex Query Validation & Debugging
Exercise: Fetch Customer’s Last Purchase Date
✅ Scenario: Retrieve each customer’s most recent order date.
🔹 SQL Query:
SELECT customer_id, MAX(order_date) AS last_purchase_date
FROM Orders
GROUP BY customer_id;
🔹 Test Case Example:
| Test Case ID | Test Scenario | Expected Result |
|---|---|---|
| TC_DB_007 | Verify last purchase date per customer | Each customer should have the correct latest order date |
