📊 Database Schema — Use this throughout the module
users
id, name, email,
status, created_at,
plan, country
status, created_at,
plan, country
orders
id, user_id, amount,
status, created_at,
promo_code
status, created_at,
promo_code
payments
id, order_id, amount,
method, status,
processed_at
method, status,
processed_at
sessions
id, user_id, token,
created_at,
expires_at, ip
created_at,
expires_at, ip
1
Concept 01
SELECT & WHERE — Basic Validation
Your first QA superpower: querying data to verify what the system actually stored.
Why QA needs SELECT
When a user registers, you see the success message — but did the data actually save correctly? SELECT lets you verify what's in the database, not just what the UI shows. The UI can lie. The database tells the truth.
SQL — Basic Validation
-- Get all active users
SELECT id, name, email, status, created_at
FROM users
WHERE status = 'active';
-- Get users from a specific country
SELECT * FROM users
WHERE country = 'Colombia'
AND status = 'active'
ORDER BY created_at DESC;
-- Count users by status
SELECT status, COUNT(*) AS total
FROM users
GROUP BY status;
Sample Data — users table
| id | name | status | plan | country | created_at | |
|---|---|---|---|---|---|---|
| 1 | Ana García | user@example.com | active | pro | Colombia | 2024-01-15 |
| 2 | John Smith | user@example.com | active | free | USA | 2024-01-16 |
| 3 | María López | user@example.com | pending | free | Mexico | 2024-01-17 |
| 4 | Carlos Ruiz | user@example.com | suspended | pro | Colombia | 2024-01-10 |
| 5 | Laura Chen | user@example.com | active | pro | Colombia | 2024-01-18 |
✏️ Exercise 1 — Active Users Validation
BEGINNER
Scenario: After a deployment, the QA lead asks you to verify that all active users in Colombia are correctly stored. Write a query to retrieve the name, email and plan of all active users from Colombia, ordered by most recently created.
💡 Hint: Use WHERE with two conditions (AND). Use ORDER BY created_at DESC.
Try:
SELECT name, email, plan FROM users WHERE status = 'active' AND country = 'Colombia' ORDER BY created_at DESC
💡 QA Use Case
After every deployment that touches user registration or status changes — run a SELECT to verify the data layer matches what the UI shows. The UI might say "user activated" but the DB might still show "pending". That's a bug the UI test would miss.
🔍 Quick Check
SELECT & WHERE fundamentals
2
Concept 02
Post-API Validation
API returns 200 OK — but did the right data actually persist?
The Post-API Pattern
When you test an API endpoint that creates or updates data, a 200 OK response is not enough. You must verify the database reflects exactly what the API claimed to do. This is called post-execution database validation — and it catches bugs that API tests alone never find.
SQL — Post-API Validation Pattern
-- Step 1: API POST /users/register returns 200 OK
-- Step 2: Verify the user was actually created in DB
SELECT id, name, email, status, plan, created_at
FROM users
WHERE email = 'user@example.com';
-- Verify correct default values were set
SELECT
email,
status, -- Should be 'pending' (not yet verified)
plan, -- Should be 'free' (default)
created_at -- Should be today's date
FROM users
WHERE email = 'user@example.com';
✏️ Exercise 2 — Post-API User Lookup
BEGINNER
Scenario: Your team's API
POST /api/users/register returned 200 OK for email user@example.com. Write a query to verify: 1) the user exists, 2) their status, 3) their plan, and 4) when they were created.
💡 Hint: SELECT the fields you need to verify. Filter by email using WHERE.
Try:
SELECT email, status, plan, created_at FROM users WHERE email = 'user@example.com'
💡 Real bug this catches
API returns 200 OK. User record created. But the status was set to 'active' instead of 'pending' — the email verification step is bypassed. The UI test passes. The API test passes. Only the database validation catches it. That's the test that matters.
🔍 Quick Check
Post-API database validation
3
Concept 03 — Real Bug
Bug: Duplicates with GROUP BY + HAVING
Finding the bug that the UI never shows — duplicate records hiding in the database.
The Duplicate Bug
A user registers twice with the same email. The UI shows one account. But the database has two records. This causes billing issues, duplicate emails, inconsistent data, and security vulnerabilities.
GROUP BY + HAVING COUNT > 1 finds them instantly.SQL — Finding Duplicates
-- Find duplicate emails in users table
SELECT
email,
COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1
ORDER BY occurrences DESC;
-- See ALL records for a specific duplicate
SELECT id, name, email, status, created_at
FROM users
WHERE email = 'user@example.com'
ORDER BY created_at;
-- Count total duplicated records
SELECT COUNT(*) AS total_duplicates
FROM (
SELECT email
FROM users
GROUP BY email
HAVING COUNT(*) > 1
) AS dupes;
⚠️ Bug Data — Duplicates hiding in the database
| id | name | status | created_at | |
|---|---|---|---|---|
| 1 | Ana García | user@example.com | active | 2024-01-15 |
| 2 | John Smith | user@example.com | active | 2024-01-16 |
| 3 | John Smith | user@example.com | pending | 2024-01-16 |
| 4 | María López | user@example.com | pending | 2024-01-17 |
| 5 | María L. | user@example.com | active | 2024-01-18 |
| 6 | Laura Chen | user@example.com | active | 2024-01-18 |
✏️ Exercise 3 — Find the Duplicate Users
INTERMEDIATE
Bug Report received: "Some users are receiving duplicate welcome emails." You suspect duplicate records in the database. Write a query to find all emails that appear more than once in the users table, showing the email and how many times it appears.
💡 Hint: Use GROUP BY email with HAVING COUNT(*) > 1
Try:
SELECT email, COUNT(*) AS occurrences FROM users GROUP BY email HAVING COUNT(*) > 1 ORDER BY occurrences DESC
💡 Why HAVING and not WHERE
WHERE filters rows before grouping. HAVING filters groups after grouping. You can't use
WHERE COUNT(*) > 1 because COUNT is calculated after the GROUP BY. HAVING is specifically designed for filtering aggregated results. This distinction appears in ISTQB and in senior QA interviews.🔍 Quick Check
GROUP BY + HAVING
4
Concept 04
NULL Validation
NULL is not zero. NULL is not empty string. NULL is the unknown — and it breaks things.
NULL in databases
NULL means no value — completely absent. It is not zero, not empty string, not false. NULL can break calculations, cause unexpected query results, and create display bugs. QA must validate that required fields are never NULL after operations that should populate them.
SQL — NULL Validation Queries
-- Find users with NULL email (should NEVER happen)
SELECT id, name, email, created_at
FROM users
WHERE email IS NULL;
-- Find orders with no promo code (NULL is OK here)
SELECT id, amount, promo_code
FROM orders
WHERE promo_code IS NOT NULL;
-- Count NULLs in critical fields
SELECT
SUM(CASE WHEN email IS NULL THEN 1 ELSE 0 END) AS null_emails,
SUM(CASE WHEN name IS NULL THEN 1 ELSE 0 END) AS null_names,
COUNT(*) AS total_users
FROM users;
-- ⚠️ WRONG: This won't find NULLs!
WHERE email = NULL -- Always FALSE — use IS NULL
✏️ Exercise 4 — NULL Detection
INTERMEDIATE
Scenario: After a data migration, some user records might have missing required fields. Write a query to find all users where either their name OR their email is NULL. Show their id, name, email, and created_at.
💡 Hint: Use IS NULL with OR to check multiple fields.
Try:
SELECT id, name, email, created_at FROM users WHERE name IS NULL OR email IS NULL
💡 The classic NULL trap
WHERE email = NULL always returns 0 rows — even if NULLs exist. This is one of the most common SQL mistakes. NULL is never equal to anything, including itself. Always use IS NULL or IS NOT NULL.🔍 Quick Check
NULL in SQL
5
Concept 05
JOINs for Cross-Table Validation
Data integrity across tables — the advanced QA weapon.
Why JOINs matter for QA
Real bugs often involve relationships between tables. A payment exists without an order. An order references a deleted user. A session has no owner. JOINs let you validate these relationships — and find the orphaned records that cause production incidents.
SQL — JOIN Types for QA
-- INNER JOIN: Only users who have orders
SELECT u.name, u.email, o.id AS order_id, o.amount, o.status
FROM users u
INNER JOIN orders o ON u.id = o.user_id
WHERE o.status = 'completed';
-- LEFT JOIN: ALL users, with orders if they exist
SELECT u.name, u.email, o.id AS order_id
FROM users u
LEFT JOIN orders o ON u.id = o.user_id;
-- Find orphan orders (no matching user — data integrity bug!)
SELECT o.id, o.user_id, o.amount, o.created_at
FROM orders o
LEFT JOIN users u ON o.user_id = u.id
WHERE u.id IS NULL; -- Orders with no owner!
✏️ Exercise 5 — Cross-Table Validation
INTERMEDIATE
Scenario: The payments team reports that some payments don't match any order in the system — possible data integrity bug. Write a query to find all payments that have no matching order (orphan payments). Show payment id, order_id, amount, and status.
💡 Hint: LEFT JOIN payments to orders. WHERE orders.id IS NULL finds the orphans.
Try:
SELECT p.id, p.order_id, p.amount, p.status FROM payments p LEFT JOIN orders o ON p.order_id = o.id WHERE o.id IS NULL
💡 INNER vs LEFT JOIN — know the difference
INNER JOIN returns only rows that match in BOTH tables. LEFT JOIN returns ALL rows from the left table, with NULLs where there's no match on the right. For finding orphaned records, always use LEFT JOIN + WHERE right.id IS NULL. This is the most powerful data integrity pattern in QA SQL.
🔍 Quick Check
SQL JOINs for QA
6
Concept 06
Timestamps & Date Validation
Time is data. Verify it like everything else.
Why timestamps matter
Timestamps tell you when something happened. Wrong timestamps break audit logs, expire sessions incorrectly, cause billing errors, and fail regulatory compliance. QA must validate that timestamps are correct, consistent, and in the right timezone.
SQL — Timestamp Validation
-- Find records created today
SELECT id, name, email, created_at
FROM users
WHERE DATE(created_at) = CURRENT_DATE;
-- Find sessions that should be expired but aren't
SELECT id, user_id, token, expires_at
FROM sessions
WHERE expires_at < NOW()
AND status = 'active'; -- Bug: expired but still active!
-- Orders in last 7 days
SELECT id, user_id, amount, created_at
FROM orders
WHERE created_at >= NOW() - INTERVAL '7 days'
ORDER BY created_at DESC;
-- Find orders where payment happened BEFORE the order
-- (Chronological integrity bug!)
SELECT o.id, o.created_at AS order_date,
p.processed_at AS payment_date
FROM orders o
JOIN payments p ON o.id = p.order_id
WHERE p.processed_at < o.created_at; -- Paid before ordering?!
✏️ Exercise 6 — Session Expiry Bug
INTERMEDIATE
Bug reported: "Some users remain logged in after their session should have expired." Write a query to find all sessions where expires_at is in the past (before NOW), showing the session id, user_id, and expires_at. These are the phantom sessions that shouldn't exist.
💡 Hint: Compare expires_at with NOW() using < operator.
Try:
SELECT id, user_id, expires_at FROM sessions WHERE expires_at < NOW() ORDER BY expires_at DESC
💡 Timestamp bugs in production
The most dangerous timestamp bug: timezone mismatch. The app stores UTC, the query filters by local time — and suddenly users see orders from "tomorrow." Always verify which timezone your database uses and whether the application converts correctly. Ask developers: "What timezone is the DB set to?"
🔍 Quick Check
Timestamp validation
7
Final Challenge
🔍 Bug Hunt — Caso Completo
A production incident just landed in your inbox. Use everything you've learned.
The Incident
Production alert: "Some Pro plan users are being charged twice for the same month." You need to investigate using SQL. Find the affected users, the duplicate payments, and the timeline. This is a real QA investigation.
✏️ Final Exercise — Production Bug Investigation
ADVANCED
Mission: Find all users on the 'pro' plan who have more than one completed payment in the same month. Show: user name, email, month, and payment count. This will identify who was double-charged.
Tables needed: users (name, email, plan) → orders (user_id) → payments (order_id, status, processed_at)
Tables needed: users (name, email, plan) → orders (user_id) → payments (order_id, status, processed_at)
💡 Hint: JOIN users → orders → payments. Use GROUP BY with DATE_TRUNC or YEAR/MONTH. HAVING COUNT > 1.
Full solution:
SELECT u.name, u.email,
DATE_FORMAT(p.processed_at, '%Y-%m') AS month,
COUNT(*) AS payment_count
FROM users u
JOIN orders o ON u.id = o.user_id
JOIN payments p ON o.id = p.order_id
WHERE u.plan = 'pro'
AND p.status = 'completed'
GROUP BY u.id, u.name, u.email, month
HAVING COUNT(*) > 1
ORDER BY payment_count DESC
💡 This is real QA work
This exact type of investigation happens in production every week at every tech company. The QA engineer who can open a DB client, write this query, and present the affected users to the dev team in 5 minutes — that's the QA engineer who gets promoted and hired for remote roles. SQL is not optional for senior QA.
🔍 Final Quiz
Put it all together