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
Compare customer record counts after ETL Counts should match in both
TC_DB_005
migration 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
Verify last purchase date per Each customer should have the correct latest
TC_DB_007
customer order date
Email ThisBlogThis!Share to XShare to FacebookShare to Pinterest
Older PostHome
