SQL for Testers

Essential SQL skills for verification, test data setup and database testing.

Why testers need SQL

SQL lets testers see the ground truth behind the UI. When an API returns an error, the database tells you whether the write never happened, was rolled back or was stored with wrong values.

It also powers test preparation. With SQL you can create the exact data a scenario needs, reset state between runs and clean up records your tests leave behind.

SELECT, FROM and WHERE

Every query starts by choosing columns and a source table, then narrowing rows. The SELECT clause lists output columns and FROM names the table.

The WHERE clause filters rows before they return. Always filter on the primary key or a unique value in tests so your assertion targets exactly one record and never depends on row order.

Filtering with operators and LIKE

Comparison operators handle numbers, dates and text: =, <>, >, <, >=, <=. Combine conditions with AND, OR, IN and BETWEEN.

LIKE matches patterns, where % matches any sequence and _ matches one character. Query WHERE email LIKE '%@example.com' to find all test accounts in a domain. Use IS NULL and IS NOT NULL rather than equality for missing values.

ORDER BY and LIMIT

ORDER BY puts rows in a predictable sequence, which makes assertions reproducible. LIMIT caps how many rows are returned so a verification query does not flood the result set.

Combine them to fetch the newest record: SELECT * FROM orders ORDER BY created_at DESC LIMIT 1. This single pattern verifies many "most recent write" scenarios.

Aggregations and GROUP BY

Aggregate functions summarize many rows into one value. COUNT, SUM, MIN, MAX and AVG are the workhorses for data checks.

GROUP BY splits the result into buckets first, so you can count rows per category: SELECT status, COUNT(*) FROM orders GROUP BY status. This is the fastest way to verify that a bulk operation updated exactly the expected records.

JOINs in practice

JOINs combine rows from related tables. An INNER JOIN returns only matching rows; a LEFT JOIN returns all rows from the left table plus matches from the right, filling missing ones with NULL.

Use a LEFT JOIN when you want "everything, even with no related record," such as listing users and flagging those without an address. Verify join keys match by prefixing columns with their table name to avoid ambiguity.

Inserting and updating test data

INSERT adds rows and UPDATE modifies them. In test code, prefer explicit column lists with named values so the statement stays readable and stable when the table evolves.

Wrap inserts in a transaction or record the created IDs so your cleanup can delete exactly what the test created. Never update production-shaped rows as a side effect of a test that only needed to read them.

Verifying state after API actions

After an API call, query the affected tables to confirm the outcome. For a POST, check the new row exists; for a PUT, check field values changed; for a DELETE, confirm the row is gone or soft-deleted.

Query with the new record's ID rather than broad conditions so concurrent test runs cannot interfere with your assertions.

Transactions and rollback

A transaction groups statements so they all commit together or all roll back. BEGIN, make your changes, then COMMIT to keep them or ROLLBACK to discard them.

In test setup, transaction helpers reset the database to a known state between runs. On read-only databases a stale snapshot can silently invalidate results, so always confirm transactions and isolation match your test's assumptions.

Related tools: Beautify your queries with the SQL Formatter and generate realistic rows with the Random Data Generator. See Test Data Management for keeping that data safe.