SQL Interview Questions for Testers: Practical Guide

SQL interview exercises for testers are rarely about producing a clever query in one attempt. The revealing moment comes after a result appears: can you explain whether duplicate joins inflated it, whether NULL changed the logic, whether the query observed the same transaction state as the application, and whether running it is safe? A query is useful test evidence only when its assumptions are visible.

Use a simple commerce schema while practicing: customers, orders, order_items, and payments. State any database-specific syntax you rely on, because date functions, case handling, execution plans, and update limits vary by engine.

Translate a test question into keys and expected cardinality

Before writing SQL, identify the grain of each table and the grain of the requested result. One order can have many items and several payment attempts. Joining both child tables directly can produce item count multiplied by payment count.

Suppose the question is: “List paid orders that do not have a captured payment.” The business contradiction matters more than the join syntax.

SELECT
    o.id,
    o.customer_id,
    o.total_amount
FROM orders AS o
WHERE o.status = 'PAID'
  AND NOT EXISTS (
      SELECT 1
      FROM payments AS p
      WHERE p.order_id = o.id
        AND p.status = 'CAPTURED'
  );

NOT EXISTS expresses absence directly. A LEFT JOIN followed by WHERE p.id IS NULL can also work if the join conditions are correct and p.id cannot be NULL. NOT IN is risky when its subquery can return NULL, because three-valued logic may make the predicate unknown for every row.

Ask what “paid” means. Is order status the source of truth, or must captured amounts equal the order total? Can split payments exist? Can capture be asynchronous? A strong tester turns those policy questions into explicit query conditions rather than treating column names as specifications.

A weak answer returns plausible rows. An acceptable answer uses the correct join or subquery. A strong answer states table grain, expected cardinality, NULL behavior, and the business oracle.

Use joins to find both matches and gaps

Know what each join removes or preserves. INNER JOIN returns matching combinations. LEFT JOIN preserves every row on the left and supplies NULL for absent right-side values. Full outer joins are useful for reconciliation where the database supports them, but equivalent unions may be needed elsewhere.

To find customers with no orders:

SELECT
    c.id,
    c.email
FROM customers AS c
WHERE NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.customer_id = c.id
);

To inspect order and successful-payment combinations:

SELECT
    o.id AS order_id,
    p.id AS payment_id,
    p.amount,
    p.created_at
FROM orders AS o
INNER JOIN payments AS p
    ON p.order_id = o.id
   AND p.status = 'CAPTURED'
WHERE o.created_at >= :start_time
  AND o.created_at < :end_time;

Putting the payment-status condition in ON versus WHERE changes results for an outer join. With an INNER JOIN the result is normally equivalent, but with a LEFT JOIN a WHERE p.status = 'CAPTURED' removes the NULL rows and effectively turns the relevant part into an inner match.

Follow-up probes often add duplicate payment attempts. Do not hide them with DISTINCT before understanding why they appear. DISTINCT can remove legitimate separate records and conceal a faulty join. Inspect keys and count relationships first.

Reconcile totals with grouping and precision

Aggregation questions test both SQL order of operations and business arithmetic. WHERE filters rows before grouping; HAVING filters groups after aggregation.

This query compares stored order totals with item extensions:

SELECT
    o.id,
    o.total_amount,
    COALESCE(SUM(oi.quantity * oi.unit_price), 0) AS item_total
FROM orders AS o
LEFT JOIN order_items AS oi
    ON oi.order_id = o.id
GROUP BY
    o.id,
    o.total_amount
HAVING ABS(
    o.total_amount
    - COALESCE(SUM(oi.quantity * oi.unit_price), 0)
) > 0.01;

The threshold is illustrative, not a universal financial rule. Real calculations may include tax, shipping, discounts, per-line rounding, and currency-specific precision. Use decimal-compatible types rather than binary floating-point for money, and reproduce the product's documented calculation order.

COUNT(*) counts rows. COUNT(column) ignores NULL in that column. COUNT(DISTINCT customer_id) counts distinct non-NULL customer IDs. Explain which population your denominator represents before calculating a rate.

When reconciling systems, compare both aggregate and row-level evidence. Equal daily sums can hide one missing order offset by one duplicate. Start with counts and sums by a useful partition, then drill into keys that differ.

Detect duplicates and identify the row to inspect

“Find duplicate emails” usually means normalize according to product policy, group, and filter:

SELECT
    LOWER(TRIM(email)) AS normalized_email,
    COUNT(*) AS customer_count
FROM customers
GROUP BY LOWER(TRIM(email))
HAVING COUNT(*) > 1;

This assumes case and outer whitespace should not distinguish accounts. Do not impose that rule without confirming it. Database collation may already affect comparison, and international identity rules can be more complex.

A window function keeps row detail while ranking within a group:

WITH ranked_customers AS (
    SELECT
        id,
        email,
        created_at,
        ROW_NUMBER() OVER (
            PARTITION BY LOWER(TRIM(email))
            ORDER BY created_at, id
        ) AS duplicate_rank
    FROM customers
)
SELECT
    id,
    email,
    created_at
FROM ranked_customers
WHERE duplicate_rank > 1
ORDER BY email, duplicate_rank;

The ordering chooses the earliest row as the retained record. That is only an investigation convention, not authorization to delete later rows. Merged accounts, verified identities, related orders, and audit rules may require a different survivor.

For “latest status per order,” use ROW_NUMBER() over order_id ordered by event sequence or timestamp plus a deterministic tie-breaker. MAX(timestamp) alone gives the time, not necessarily the other columns from the same row.

Treat NULL, time, and text as test dimensions

NULL means unknown or absent, not an empty string and not zero. Use IS NULL and IS NOT NULL. Comparisons such as status <> 'CANCELLED' do not include NULL status values. If NULL should be included, state it:

SELECT id, status
FROM orders
WHERE status <> 'CANCELLED'
   OR status IS NULL;

Time filters should usually use half-open ranges:

SELECT COUNT(*) AS order_count
FROM orders
WHERE created_at >= :day_start
  AND created_at < :next_day_start;

This avoids guessing the last representable fraction of a second. Clarify whether stored timestamps are UTC, which time zone defines the reporting day, and how daylight-saving transitions affect local ranges. Avoid wrapping an indexed timestamp column in a function if a computed boundary can express the same test.

Text comparisons may depend on collation, Unicode normalization, case, and trailing-space rules. A defect that occurs for “é” or Turkish case conversion cannot be investigated reliably if the query silently applies a different normalization from the application.

Empty result sets also deserve interpretation. They may mean no defects, wrong environment, stale replica, restrictive predicate, missing permissions, or test data cleanup. Validate a known control record before trusting “zero failures.”

Validate migrations and ETL with layered reconciliation

Migration interviews test how you compare large datasets without checking every row manually. Establish mapping rules first: keys, transformed fields, defaults, rejected records, referential integrity, and allowed timing differences.

Use several layers:

1. Source and target counts by business partition
2. Distinct key counts and duplicate checks
3. Sums or hashes for stable fields
4. Anti-joins to find missing keys on each side
5. Field-level comparison for matched keys
6. Samples from boundaries, errors, and high-risk transformations
7. Referential-integrity and constraint checks

A count match is necessary in some migrations but insufficient. Two missing records and two duplicates can cancel. A checksum can help, but concatenation order, NULL representation, collation, and hash implementation must match.

For incremental loads, record the watermark and its inclusivity. If one run selects updated_at > last_watermark while multiple rows share that timestamp, late rows can be lost. A composite watermark such as timestamp plus stable ID, or a source change sequence, can prevent gaps.

A strong project example names a reconciliation defect and its cause. Perhaps source amounts used four decimal places but target rounding occurred before aggregation. Explain the query that isolated affected records, the mapping decision, and the verification after reload.

Query safely and understand observation limits

Interviewers may ask testers to update data. Production and shared test databases should default to read-only access. Prefer supported APIs or seed tools for setup because they preserve business rules. If direct modification is authorized in an isolated environment, use a transaction, a precise key, a pre-check, and a post-check.

BEGIN;

SELECT id, status
FROM orders
WHERE id = :test_order_id;

UPDATE orders
SET status = 'CANCELLED'
WHERE id = :test_order_id
  AND status = 'PENDING';

SELECT id, status
FROM orders
WHERE id = :test_order_id;

ROLLBACK;

Syntax and transactional behavior vary by database. Even a rollback may not undo external effects from triggers or integrations. Know the environment and policy before executing writes.

Parameterized queries protect values from being interpreted as SQL and improve repeatability. Do not build SQL by concatenating test input. Restrict selected columns and rows so sensitive data does not enter logs or reports.

Isolation matters too. A query can see different results depending on transaction level, replica lag, and concurrent writes. If the UI writes to a primary and the test reads a replica, temporary disagreement may be expected within a defined bound. Your answer should name the consistency contract rather than call every delay a database bug.

Read performance clues without pretending to be the DBA

A validation query that scans millions of rows during a shared test can become the defect. Filter on useful keys, select only needed columns, and inspect the execution plan when a query is unexpectedly slow. Look for full scans, join strategy, row estimates, sorts, and index use, while recognizing that plan terminology varies.

Indexes can accelerate reads but add storage and write cost. An index on every filtered column is not a responsible recommendation. Composite index usefulness depends on predicates, order, selectivity, and the database optimizer.

When a query times out, separate database performance from query correctness. Run it on a small known subset, inspect cardinality after each join, and compare the plan with representative statistics. Avoid adding LIMIT to a reconciliation query and then treating the sample as complete evidence.

Test teardown queries deserve the same care. Deleting by a broad date range can remove another worker's data. Namespace test records and clean by exact ownership identifiers.

Solve interview exercises with an audit trail

Talk through the schema before coding. State the keys, expected row count, and treatment of NULL. Write a clear first query, test it with a tiny counterexample, and only then optimize.

Use this calibration:

Practice four connected tasks: find missing relationships, reconcile calculated totals, rank duplicates, and validate an incremental migration. For each, create a few rows that would fool a naive query. One NULL in a NOT IN subquery or two child tables with multiple rows can reveal more understanding than a long set of basic SELECT statements.

Finish every SQL answer by translating rows back into product meaning. Which record is wrong, which rule says so, what independent evidence confirms it, and can the query run safely in the intended environment? That is what turns database access into credible QA investigation.