SQL for QA

checking data in the database

What basic SQL queries should QA know?

SELECT with filtering, sorting and limiting, aggregate functions and grouping.

-- the user's last 10 transactions
SELECT id, amount, currency, status, created_at
FROM transactions
WHERE user_id = 1042
ORDER BY created_at DESC
LIMIT 10;

-- the number and total of transactions by status
SELECT status, COUNT(*) AS cnt, SUM(amount) AS total
FROM transactions
WHERE created_at >= '2026-10-01'
GROUP BY status
HAVING COUNT(*) > 0;

In an interview: I confidently use SELECT with filtering through WHERE, sorting and LIMIT, as well as the aggregate functions COUNT and SUM with GROUP BY. HAVING is for filtering already grouped results, unlike WHERE, which filters rows before grouping.

What kinds of JOIN are there?

INNER JOIN: only rows that have a match in both tables.
LEFT JOIN: all rows of the left table, from the right only if there is a match, otherwise NULL.
RIGHT JOIN: the opposite.
FULL JOIN: all rows of both tables.

A LEFT JOIN with a NULL check is a convenient way to find "orphans": records without related data.

-- transactions without an audit log entry (and each must have one)
SELECT t.id, t.amount
FROM transactions t
LEFT JOIN audit_log a ON a.transaction_id = t.id
WHERE a.id IS NULL;

In an interview: INNER JOIN returns only rows with a match in both tables, LEFT JOIN all rows of the left one, and from the right only if present. I often use LEFT JOIN with a NULL check to find records that lack required related data, for example transactions without an audit log entry.

How do you use SQL to verify data?

After an action in the UI or API: check that a record appeared with the right values.
Find duplicates.
Check integrity: there are no records without related data.
Prepare test data or find a user in the needed state.

On shared environments, only SELECT, unless there is explicit permission to change data.

-- duplicate payments with the same idempotency key
SELECT idempotency_key, COUNT(*)
FROM payments
GROUP BY idempotency_key
HAVING COUNT(*) > 1;

In an interview: After an action in the UI or API I check in the database that the record was created with the right values. I also look for duplicates and records without required relations, and I find users in the needed state for tests. On shared environments I only read, unless I have permission to change data.