How to Write SQL Test Cases: Examples for QA Teams

A test case said, “Verify the invoice total in the database.” Three testers ran three different queries. One summed invoice lines, one read the invoice header, and one included a voided payment. All marked the case passed. The instruction named a topic but did not define data, grain, business rule, or an observable expected result.

A strong SQL test case is a reproducible argument: given this controlled state and action, this query should return this exact evidence. The examples here use PostgreSQL. Where the case depends on PostgreSQL syntax, that dependency is stated rather than hidden.

Start with an invariant, not a query

Write the rule in business language before choosing tables. “For a finalized invoice, header total equals the sum of non-void lines plus tax, and one ledger entry references that invoice” is a testable invariant. “Run a join on invoices” is not.

Identify five things:

1. The event that creates or changes state.
2. The entity and grain being checked.
3. Fields or related records that may change.
4. Records that must not change.
5. The exact pass condition.

This prevents a common mistake: validating a copied display field while missing the source-of-truth calculation. Confirm ownership with a developer or data model before freezing the query.

Use a complete SQL test case template

A case must be runnable by someone who did not write it. Keep navigation steps short and spend detail on data identity, timing, and expected rows.

Do not paste passwords, connection strings, or personal data into the case. Link to the approved access method and use synthetic values.

Make setup data diagnostic

Choose values that reveal the rule. Three identical lines cannot reveal a join or allocation error as clearly as distinct amounts. Include one value near a boundary and a recognizable reference. Calculate the expectation independently:

Line net values: 1000 + 2500 + 650 = 4150
Tax at 20%:      200 +  500 + 130 = 830
Expected total: 4150 + 830 = 4980 minor units

The text block shows the oracle. The database query should confirm 4980, but should not be the only place that number is derived. If rounding is applied at invoice rather than line grain, use data where the two methods differ and state which method the requirement selects.

Create data through supported interfaces unless the purpose is to test a database object directly. Record generated IDs from the response. A test that searches by email and creation minute can collide with parallel runs.

Write a validation query that returns failures

Queries that return only successful rows force a reviewer to remember expectations. Prefer a query that returns zero rows when the invariant holds, with diagnostic columns when it fails.

WITH line_totals AS (
  SELECT invoice_id,
         count(*) FILTER (WHERE status <> 'VOID') AS active_lines,
         sum(net_minor + tax_minor)
           FILTER (WHERE status <> 'VOID') AS calculated_total
  FROM invoice_lines
  GROUP BY invoice_id
), ledger_counts AS (
  SELECT source_id AS invoice_id, count(*) AS ledger_rows
  FROM ledger_entries
  WHERE source_type = 'INVOICE'
  GROUP BY source_id
)
SELECT i.invoice_id, i.total_minor, l.calculated_total,
       l.active_lines, COALESCE(g.ledger_rows, 0) AS ledger_rows
FROM invoices i
JOIN line_totals l USING (invoice_id)
LEFT JOIN ledger_counts g USING (invoice_id)
WHERE i.invoice_id = 730014
  AND (i.status <> 'FINALIZED'
    OR i.total_minor <> l.calculated_total
    OR l.active_lines <> 3
    OR COALESCE(g.ledger_rows, 0) <> 1);

This PostgreSQL sql query should return zero rows. Replace 730014 with the captured ID through a safe parameter mechanism. If it returns a row, the selected values show which part of the invariant failed. The filtered aggregate is PostgreSQL syntax; use CASE expressions for engines that do not support FILTER.

State cardinality and values precisely

“Data is correct” is not an expected result. Specify row count, column values, ordering only when meaningful, null behavior, precision, and allowed timing. Examples include:

• Exactly one invoice row has reference SQLTC-0710-014.
• Exactly three non-void line rows belong to the captured invoice ID.
• total_minor is 4980, not a formatted currency string.
• posted_at is non-null and no earlier than the finalize request timestamp.
• No ledger row exists before finalization; exactly one exists after completion.

For asynchronous work, avoid a fixed sleep. Define a terminal field, polling interval, maximum duration, and failure output. A 20-second allowance does not mean any timestamp inside 20 seconds is functionally correct. It means the test waits for a deterministic result before declaring a timeout.

Use tolerances only for a documented reason. Money and counts are normally exact. Performance durations and floating-point scientific values may require a range. State both the range and why it is legitimate.

Add negative and non-change assertions

A negative test should prove the absence of side effects, not merely an error message. When finalization is rejected for an invalid tax code, verify the invoice remains draft, no ledger entry exists, line values are unchanged, and no posting event is queued.

SELECT
  i.status,
  i.total_minor,
  count(le.entry_id) AS ledger_rows
FROM invoices i
LEFT JOIN ledger_entries le
  ON le.source_type = 'INVOICE' AND le.source_id = i.invoice_id
WHERE i.invoice_id = 730015
GROUP BY i.invoice_id, i.status, i.total_minor;

The PostgreSQL sql query should return one row with DRAFT, the pre-action total, and 0. Save the pre-action value in the test case rather than deciding after execution that the current value “looks unchanged.”

Also cover duplicate requests, missing parents, maximum values, null optional fields, forbidden users, and recovery after dependency failure. Each should name which tables remain untouched.

Design safe setup and cleanup

Read-only validation is the default. When direct writes are necessary in an isolated test database, use the least privilege account and mark every record with a run identifier. Test cleanup separately so it cannot remove another run’s data.

Transactions are helpful only when all work shares the same connection. An API server cannot usually see uncommitted setup from the tester’s session, and rolling back the tester session will not undo writes committed by the service. In integration suites, a fixture endpoint, per-test schema, ephemeral database, or owned cleanup job is safer.

Never hide cleanup inside an expected-result query. If cleanup fails, report it independently. Residual data can make the next case pass or fail for the wrong reason.

Capture evidence and diagnose failures

Retain the executed SQL, bound parameters, database and schema version, test reference, request ID, timestamps, actual output, and query duration. Export a small result set as text or CSV with sensitive columns omitted. Screenshots alone make values hard to compare and queries hard to rerun.

When a case fails, classify the boundary before editing the expected result:

A failed test can reveal a defective query rather than a defective product. Peer review SQL with the same care as automation code.

Review cases for automation readiness

Before automating, check that setup owns its data, identifiers are captured, queries are parameterized, expectations are exact, asynchronous completion is observable, cleanup is scoped, and failure output is diagnostic. Remove manual phrases such as “verify visually in the table.”

Keep SQL assertions close to a user-visible or API assertion. Database state proves persistence, but it does not prove the customer saw the right response. A release-quality case connects request, durable state, downstream effect, and presentation where the risk demands it. That connection is what turns a query into evidence.