Hands-on SQL exercises and test case examples for validating databases and SQL queries

 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


Followers