Learn / Cheat sheet
SQL Cheat Sheet for Testers
The SQL testers use to verify data behind a UI or API: find the record, check it changed, and hunt for duplicates, orphans and NULLs that shouldn’t be there. Syntax is standard SQL that runs on PostgreSQL, MySQL 8 and SQLite. Where a database differs, the note says so.
Checked against Standard SQL; checked against PostgreSQL 16, MySQL 8 and SQLite 3
Find rows
| Task | Code |
|---|---|
| Columns from a table | SELECT id, email FROM users; |
| Filter | SELECT * FROM orders WHERE status = 'paid' AND total > 100; |
| One of several values | WHERE status IN ('paid', 'shipped') |
| Range (inclusive both ends) | WHERE created_at BETWEEN '2026-01-01' AND '2026-01-31' |
| Pattern | WHERE email LIKE '%@example.com' |
| Missing value | WHERE phone IS NULL |
| Sort, newest first | ORDER BY created_at DESC |
| First 10 rows | LIMIT 10 |
| Distinct values | SELECT DISTINCT status FROM orders; |
SQL Server uses SELECT TOP 10 instead of LIMIT. BETWEEN on timestamps includes only midnight of the end date; use < next day for whole days.
Aggregate
| Task | Code |
|---|---|
| Count rows | SELECT COUNT(*) FROM orders; |
| Count non-NULL values | SELECT COUNT(shipped_at) FROM orders; |
| Count distinct | SELECT COUNT(DISTINCT user_id) FROM orders; |
| Totals per group | SELECT status, COUNT(*), SUM(total) FROM orders GROUP BY status; |
| Filter groups | SELECT user_id, COUNT(*) FROM orders GROUP BY user_id HAVING COUNT(*) > 5; |
| Min, max, average | SELECT MIN(total), MAX(total), AVG(total) FROM orders; |
WHERE filters rows before grouping; HAVING filters the groups after.
Join
| Task | Code |
|---|---|
| Matching rows only | SELECT o.id, u.email FROM orders o JOIN users u ON u.id = o.user_id; |
| Keep all left rows | SELECT u.email, o.id FROM users u LEFT JOIN orders o ON o.user_id = u.id; |
| Rows with no match (anti-join) | SELECT u.* FROM users u LEFT JOIN orders o ON o.user_id = u.id WHERE o.id IS NULL; |
| Same, with NOT EXISTS | SELECT * FROM users u WHERE NOT EXISTS (SELECT 1 FROM orders o WHERE o.user_id = u.id); |
A join on a one-to-many key repeats the "one" side. If SUM() looks too big after a join, the join fanned out.
Data checks testers run
| Task | Code |
|---|---|
| Duplicates | SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1; |
| Orphans (child without parent) | SELECT o.* FROM orders o LEFT JOIN users u ON u.id = o.user_id WHERE u.id IS NULL; |
| Required field empty | SELECT * FROM users WHERE email IS NULL OR TRIM(email) = ''; |
| Out-of-range values | SELECT * FROM order_items WHERE quantity <= 0 OR unit_price < 0; |
| Header total ≠ line items | SELECT o.id FROM orders o JOIN order_items i ON i.order_id = o.id GROUP BY o.id, o.total HAVING o.total <> SUM(i.quantity * i.unit_price); |
| Row exists after a UI action | SELECT * FROM orders WHERE user_id = 42 ORDER BY created_at DESC LIMIT 1; |
NULL traps
| Trap | What happens |
|---|---|
| WHERE col = NULL | Never true. Use IS NULL / IS NOT NULL. |
| WHERE status <> 'paid' | Skips rows where status is NULL. Add OR status IS NULL if you want them. |
| NOT IN (subquery that returns a NULL) | Returns no rows at all. Use NOT EXISTS instead. |
| COUNT(col) vs COUNT(*) | COUNT(col) skips NULLs, COUNT(*) counts every row. |
| Arithmetic with NULL | price + NULL is NULL. Wrap with COALESCE(col, 0) if 0 is the right default. |
Window functions
| Task | Code |
|---|---|
| Number rows per group | ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) |
| Latest row per group | SELECT * FROM (SELECT o.*, ROW_NUMBER() OVER (PARTITION BY user_id ORDER BY created_at DESC) AS rn FROM orders o) t WHERE rn = 1; |
| Rank with ties | RANK() OVER (ORDER BY total DESC) / DENSE_RANK() … |
| Previous row’s value | LAG(status) OVER (PARTITION BY test_name ORDER BY run_at) |
| Running total | SUM(total) OVER (ORDER BY created_at) |
Supported in PostgreSQL, MySQL 8+, SQLite 3.25+ and SQL Server. A window function can’t be used in WHERE; filter in an outer query.
Change data safely
| Task | Code |
|---|---|
| Insert | INSERT INTO users (email, name) VALUES ('qa+1@example.com', 'QA One'); |
| Update (always with WHERE) | UPDATE orders SET status = 'cancelled' WHERE id = 1043; |
| Delete (always with WHERE) | DELETE FROM sessions WHERE user_id = 42; |
| Try it, then undo | BEGIN; UPDATE orders SET status = 'cancelled' WHERE id = 1043; SELECT status FROM orders WHERE id = 1043; ROLLBACK; |
Run the same WHERE as a SELECT first and check the row count before you UPDATE or DELETE.