Last updated
Common Testing Scenarios
- Verify JOIN type (INNER vs LEFT) returns the expected rows
- Test GROUP BY and HAVING filter logic with edge cases
- Confirm subquery returns the expected scalar value
- Test UPDATE/DELETE WHERE conditions before running on production
- Verify CASE expression covers all expected conditions
- Test pagination (LIMIT/OFFSET) returns the correct page of results
- Confirm NULL handling in aggregates and comparisons
Examples
Example 1: Setting Up Sample Data
Define tables using CSV or JSON format:
-- Table: users
id, name, email, department, salary
1, Alice Smith, alice@example.com, Engineering, 95000
2, Bob Jones, bob@example.com, Marketing, 72000
3, Carol White, carol@example.com, Engineering, 88000
4, Dave Brown, dave@example.com, Sales, 65000
5, Eve Davis, eve@example.com, Engineering, 102000
-- Table: orders
id, user_id, total, status, created_at
1, 1, 150.00, completed, 2026-01-15
2, 1, 89.50, completed, 2026-02-10
3, 2, 220.00, pending, 2026-03-01
4, 3, 45.00, completed, 2026-01-20
5, 5, 310.00, completed, 2026-02-28
Example 2: Testing a JOIN Query
-- Query to test
SELECT
u.name,
u.department,
COUNT(o.id) AS order_count,
SUM(o.total) AS total_spent
FROM users u
LEFT JOIN orders o ON u.id = o.user_id
GROUP BY u.id, u.name, u.department
ORDER BY total_spent DESC NULLS LAST;
Expected result:
name department order_count total_spent
----------- ---------- ----------- -----------
Alice Smith Engineering 2 239.50
Eve Davis Engineering 1 310.00
Carol White Engineering 1 45.00
Bob Jones Marketing 1 220.00
Dave Brown Sales 0 NULL
Testing LEFT vs INNER JOIN behavior:
-- INNER JOIN: Dave Brown excluded (no orders)
SELECT u.name, o.total
FROM users u
INNER JOIN orders o ON u.id = o.user_id;
-- Returns 5 rows (only users with orders)
-- LEFT JOIN: Dave Brown included with NULL
SELECT u.name, o.total
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- Returns 6 rows (all users, NULL for Dave)
Example 3: Testing Aggregate Queries
-- Average salary by department
SELECT
department,
COUNT(*) AS headcount,
AVG(salary) AS avg_salary,
MIN(salary) AS min_salary,
MAX(salary) AS max_salary
FROM users
GROUP BY department
HAVING COUNT(*) > 1
ORDER BY avg_salary DESC;
Expected result:
department headcount avg_salary min_salary max_salary
---------- --------- ---------- ---------- ----------
Engineering 3 95000 88000 102000
(Marketing and Sales have only 1 employee each, filtered out by HAVING COUNT(*) > 1)