SQL Interview Questions for QA Engineers with Answers (2026)

TL;DR

QA SQL interviews test query correctness, data-validation judgment, and database safety. Master joins, aggregation, subqueries, windows, test-data setup, transactions, ETL reconciliation, and the edge cases that make plausible queries wrong.

Key Takeaways

• State the expected row grain before writing a join, aggregate, or window function.
• Handle NULL, duplicates, ties, empty inputs, and time boundaries explicitly.
• Use anti-joins and bidirectional comparisons to expose missing and unexpected data.
• Recalculate business totals from the lowest trustworthy grain instead of trusting stored summaries.
• Own test data with stable run identifiers and choose teardown that matches connection boundaries.
• Test transactions and concurrency with independent sessions and observable final-state invariants.
• Use production data access narrowly, read-only, and with evidence-preserving safety controls.

SQL interview questions for QA engineers test whether you can prove what a system stored, not merely repeat SQL syntax. A strong candidate can retrieve the right rows, explain the result grain, expose missing or duplicated data, and use the database safely. This guide gives you 66 fully answered questions covering the SQL patterns that appear in QA, SDET, data-testing, and backend-testing interviews.

Treat every answer as a reasoning pattern. Clarify the business rule, name the expected result shape, write the smallest query that proves or disproves it, and explain the edge cases. The mix includes SQL query interview questions with answers you can rehearse and SQL coding questions for QA you can execute. Interviewers care about correct output, but they also listen for NULL handling, cardinality, deterministic ordering, transaction boundaries, diagnostic evidence, and whether your query could damage shared data.

The runnable examples use PostgreSQL-compatible SQL, self-contained CTE fixtures, or temporary tables. Features such as ILIKE, FILTER, RETURNING, and IS DISTINCT FROM are identified through their behavior, so you can translate them when an interview uses MySQL, SQL Server, Oracle, or another engine. If you need a foundation first, work through the SQL for QA tutorial for beginners, then use the SQL coding interview questions for testers for timed drills.

TL;DR

The fastest preparation loop is to solve a query, predict its rows before running it, insert one adversarial case, and explain what the result proves. Across all 66 questions, keep four checks visible: grain, NULL behavior, duplicate behavior, and safety. Those four details separate a production-minded QA answer from a memorized query.

1. SQL Interview Questions for QA: Foundations and NULL Semantics

Common SQL queries for QA can look simple while still returning the wrong grain, hiding a null, or choosing rows nondeterministically. Start by making those fundamentals explicit.

Q: Why do QA engineers need SQL when the UI or API already shows the result?

A presentation layer proves only what that layer received and rendered. SQL lets QA verify persistence, cross-table side effects, asynchronous processing, audit history, and invariants that a screen may hide. After a create-order API returns 201, for example, the order could exist while its payment row is missing or inventory was decremented twice. Direct data checks also help isolate whether a defect belongs to the client, service, database logic, or downstream pipeline. I would still avoid coupling every automated test to internal tables, because schema-aware checks have maintenance cost and can bypass the product contract. Use SQL where the database state is part of the risk or where it provides decisive diagnostic evidence.

Q: How would you retrieve a user's 20 most recent failed orders with deterministic ordering?

Filter by the stable user key and failed status, sort newest first, then add a unique tie-breaker before applying the limit. Ordering only by a timestamp is nondeterministic when two writes share the stored precision, so repeated executions may return a different twentieth row. A half-open date boundary can further constrain the query if the question specifies a reporting window. Select only fields required for the assertion or investigation rather than using SELECT *. In a multi-tenant system, include the tenant predicate even when the order ID looks globally unique. The resulting query is reviewable, indexable, and unambiguous about which rows qualify.

WITH orders(order_id, tenant_id, user_id, status, created_at) AS (
  VALUES
    (101, 7, 42, 'failed', TIMESTAMPTZ '2026-07-23 09:00:00+00'),
    (102, 7, 42, 'failed', TIMESTAMPTZ '2026-07-23 09:00:00+00'),
    (103, 7, 42, 'paid',   TIMESTAMPTZ '2026-07-23 09:10:00+00'),
    (104, 8, 42, 'failed', TIMESTAMPTZ '2026-07-23 09:20:00+00')
)
SELECT order_id, status, created_at
FROM orders
WHERE tenant_id = 7
  AND user_id = 42
  AND status = 'failed'
ORDER BY created_at DESC, order_id DESC
LIMIT 20;

Q: What is the logical execution order of a SQL query?

SQL is logically evaluated as FROM and JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and finally LIMIT or OFFSET. That sequence explains why a select-list alias is usually available in ORDER BY but not in WHERE at the same query level. Row filters act before aggregates exist, while HAVING evaluates groups after aggregation. PostgreSQL's optimizer may physically reorder operations when doing so preserves meaning, so an execution plan need not visually match the logical sequence. In an interview, use the logical order to reason about correctness and the physical plan to discuss performance. Stating both avoids the common mistake of treating optimizer behavior as a change in SQL semantics.

WITH runs(status, duration_ms) AS (
  VALUES
    ('passed', 120),
    ('passed', 180),
    ('failed', 350),
    ('failed', 400),
    ('failed', 80)
)
SELECT
  status,
  COUNT(*) AS run_count,
  ROUND(AVG(duration_ms), 1) AS avg_duration_ms
FROM runs
WHERE duration_ms >= 100
GROUP BY status
HAVING COUNT(*) >= 2
ORDER BY run_count DESC, status
LIMIT 5;

Q: What does NULL mean, and why does column = NULL fail?

NULL represents an unknown or absent value, not an ordinary value that equals itself. Expressions such as status = NULL and status <> NULL therefore evaluate to unknown, and a WHERE clause keeps only true results. Use IS NULL or IS NOT NULL to test absence. PostgreSQL's IS DISTINCT FROM supplies null-safe inequality, while IS NOT DISTINCT FROM supplies null-safe equality, which is valuable when expected and actual values may both be absent. Do not replace every null with a default using COALESCE before you understand the rule, because that can turn missing data into a false match. A QA answer should explicitly distinguish missing, empty, zero, and the literal text 'null'.

WITH comparisons(id, expected_value, actual_value) AS (
  VALUES
    (1, 'active'::text, 'active'::text),
    (2, NULL::text, NULL::text),
    (3, 'active'::text, NULL::text),
    (4, 'active'::text, 'blocked'::text)
)
SELECT id, expected_value, actual_value
FROM comparisons
WHERE expected_value IS DISTINCT FROM actual_value
ORDER BY id;

Q: How do SQL COUNT expressions differ?

COUNT(*) counts result rows regardless of null values. COUNT(finished_at) counts only rows where that expression is non-null, so it can measure completed executions if that column is a reliable completion marker. COUNT(DISTINCT tester_id) counts unique non-null tester IDs and ignores repeated assignments plus unknown testers. These answers diverge as soon as the fixture contains nulls or duplicates, which is why merely saying that COUNT counts records is incomplete. PostgreSQL's aggregate FILTER clause can calculate named conditional counts without discarding the other input rows. Before choosing one form, translate the metric into plain language and decide whether null represents an excluded state or a defect.

WITH runs(run_id, tester_id, finished_at, status) AS (
  VALUES
    (1, 10, TIMESTAMPTZ '2026-07-20 10:00:00+00', 'passed'),
    (2, 10, NULL::timestamptz, 'running'),
    (3, 11, TIMESTAMPTZ '2026-07-20 10:05:00+00', 'failed'),
    (4, NULL::integer, TIMESTAMPTZ '2026-07-20 10:10:00+00', 'passed')
)
SELECT
  COUNT(*) AS all_rows,
  COUNT(finished_at) AS finished_rows,
  COUNT(DISTINCT tester_id) AS distinct_known_testers,
  COUNT(*) FILTER (WHERE status = 'failed') AS failed_rows
FROM runs;

Q: When is DISTINCT appropriate, and when can it hide a faulty join?

DISTINCT is appropriate when the requested result is genuinely a set of unique selected combinations, such as the list of countries represented by customers. It becomes suspicious when added after a join merely to make duplicate-looking output disappear. A one-to-many join may repeat a customer because each order is a legitimate separate match, or a missing join predicate may create a Cartesian product. DISTINCT can conceal either error without fixing the wrong grain or inflated aggregates. Diagnose the join by counting rows at each stage and checking the uniqueness of the keys used on both sides. If one row per customer is required, aggregate or select the intended child row explicitly rather than erasing evidence after the fact.

WITH events(test_id, status) AS (
  VALUES
    (101, 'passed'),
    (101, 'passed'),
    (102, 'failed'),
    (103, 'failed')
)
SELECT DISTINCT test_id, status
FROM events
ORDER BY test_id, status;

2. Joins and Relationship Validation

SQL joins interview questions are where otherwise plausible answers often become silently wrong. Decide the expected row grain before choosing a join, and inspect key uniqueness before trusting a total. The deeper SQL joins for testers guide provides extra fixtures for missing relationships and many-to-many defects.

Q: How do INNER JOIN, LEFT JOIN, and FULL OUTER JOIN differ?

An INNER JOIN keeps only key matches found on both sides. A LEFT JOIN preserves every left row and fills right-side columns with nulls when no qualifying child exists. A FULL OUTER JOIN preserves keys from either input, which makes it useful for bidirectional source-to-target reconciliation. None of these joins guarantees one result per left row, because one-to-many matches legitimately duplicate the left values in the output. The choice should follow the question: use inner for existing relationships, left for optional or missing relationships, and full outer for two-sided differences. When a database lacks full outer join, combine two anti-joins or use set operations to report both directions.

WITH customers(customer_id, name) AS (
  VALUES (1, 'Asha'), (2, 'Mateo'), (3, 'Nora')
),
orders(order_id, customer_id, amount_cents) AS (
  VALUES (10, 1, 2500), (11, 1, 4000), (12, 2, 1800)
)
SELECT c.customer_id, c.name, o.order_id, o.amount_cents
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
ORDER BY c.customer_id, o.order_id;

Q: How would you find customers who have never placed an order?

Start with customers because every customer must remain eligible for the answer. Left join orders on the customer key, then keep rows where a non-nullable order key is null. That null is introduced by the missing match, so it distinguishes no order from an order whose optional attribute happens to be null. A correlated NOT EXISTS query expresses the same anti-join and is often clearer. Do not use an inner join, because it removes precisely the customers the question asks for. If the requirement means no completed orders rather than no orders at all, put the qualifying order status inside ON or inside the NOT EXISTS subquery.

WITH customers(customer_id, name) AS (
  VALUES (1, 'Asha'), (2, 'Mateo'), (3, 'Nora')
),
orders(order_id, customer_id, status) AS (
  VALUES (10, 1, 'paid'), (11, 1, 'cancelled'), (12, 2, 'paid')
)
SELECT c.customer_id, c.name
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL
ORDER BY c.customer_id;

Q: How would you detect orphaned order-item rows?

An orphan is a child whose required parent key has no matching parent row. Query order items with NOT EXISTS against orders, or left join them and filter for the missing parent key. A properly enforced foreign key prevents ordinary writes from creating this state, but orphans can still occur in staging tables, legacy imports, disabled-constraint windows, or failed migrations. Exclude null foreign keys only if the column is optional and null is a valid business state. Return the child identifier and referenced key so the defect can be traced to a load or transaction. A zero-row result is the assertion; sampling several valid relationships does not prove referential integrity.

WITH orders(order_id) AS (
  VALUES (10), (20)
),
order_items(item_id, order_id) AS (
  VALUES (100, 10), (101, 99), (102, NULL::integer)
)
SELECT i.item_id, i.order_id
FROM order_items AS i
WHERE i.order_id IS NOT NULL
  AND NOT EXISTS (
    SELECT 1
    FROM orders AS o
    WHERE o.order_id = i.order_id
  )
ORDER BY i.item_id;

Q: Why does moving a right-table filter from ON to WHERE change a LEFT JOIN?

A right-side predicate inside ON controls which child rows may match while still preserving every left row. Put the same predicate in WHERE and null-extended left rows fail it, making that portion of the query behave like an inner join. Suppose QA must list every account and its successful login, if any. ON l.succeeded = true keeps accounts without a successful login, while WHERE l.succeeded = true removes them from the report. The two forms are equivalent only when the requirement intentionally excludes unmatched parents. Explain the intended result set before moving conditions during refactoring, because a visually small edit can erase the negative cases a test was designed to find.

WITH accounts(account_id) AS (
  VALUES (1), (2), (3)
),
logins(login_id, account_id, succeeded) AS (
  VALUES (101, 1, false), (102, 1, true), (103, 3, false)
)
SELECT a.account_id, l.login_id AS successful_login_id
FROM accounts AS a
LEFT JOIN logins AS l
  ON l.account_id = a.account_id
 AND l.succeeded = true
ORDER BY a.account_id, l.login_id;

Q: Why must a tenant-safe join include both tenant_id and the business key?

In a multi-tenant schema, a business key such as order_number may be unique only within one tenant. Joining on that number alone can attach tenant 7's payment to tenant 8's order when both use ORD-100. Include tenant_id in the join and in every filtering predicate, or join through a globally unique surrogate key whose ownership has already been validated. QA should deliberately seed the same business key in two tenants because globally unique test data will never expose the missing predicate. Review composite foreign keys and unique constraints to see what the data model actually promises. A correct tenant-aware query protects both data accuracy and authorization boundaries.

WITH orders(tenant_id, order_number, total_cents) AS (
  VALUES (7, 'ORD-100', 2500), (8, 'ORD-100', 9000)
),
payments(tenant_id, order_number, paid_cents) AS (
  VALUES (7, 'ORD-100', 2500), (8, 'ORD-100', 9000)
)
SELECT
  o.tenant_id,
  o.order_number,
  o.total_cents,
  p.paid_cents
FROM orders AS o
JOIN payments AS p
  ON p.tenant_id = o.tenant_id
 AND p.order_number = o.order_number
ORDER BY o.tenant_id;

Q: Why do joins sometimes multiply totals, and how do you diagnose the problem?

Joining two independent one-to-many child tables through the same parent creates every combination of those children. A customer with two orders and three support tickets produces six rows, so summing order amounts after that join triples the revenue. First state the required grain, then aggregate each child source to that grain before combining them. Compare COUNT(*) with COUNT(DISTINCT order_id) and COUNT(DISTINCT ticket_id) to reveal multiplication during diagnosis. Do not patch the total with SUM(DISTINCT amount), because two legitimate orders may share the same amount. The structural fix is to control cardinality before the final join.

WITH orders(order_id, customer_id, amount_cents) AS (
  VALUES (10, 1, 2500), (11, 1, 2500), (12, 2, 1800)
),
tickets(ticket_id, customer_id) AS (
  VALUES (20, 1), (21, 1), (22, 1)
),
order_totals AS (
  SELECT customer_id, COUNT(*) AS order_count, SUM(amount_cents) AS order_cents
  FROM orders
  GROUP BY customer_id
),
ticket_totals AS (
  SELECT customer_id, COUNT(*) AS ticket_count
  FROM tickets
  GROUP BY customer_id
)
SELECT
  o.customer_id,
  o.order_count,
  o.order_cents,
  COALESCE(t.ticket_count, 0) AS ticket_count
FROM order_totals AS o
LEFT JOIN ticket_totals AS t USING (customer_id)
ORDER BY o.customer_id;

3. Aggregation, GROUP BY, and Reconciliation

Aggregation answers must name their grain. If the query groups by day and status, one output row represents one day-status combination, not one order or one customer. Use the SQL GROUP BY and HAVING guide for testers when you want more reconciliation drills.

Q: How would you count orders by status and day?

Filter to a half-open time window, derive the reporting day in the agreed business time zone, then group by that day and status. Grouping a timestamptz directly by its UTC date can place late-evening orders on the wrong business day. Include both dimensions in GROUP BY and add a deterministic ORDER BY for stable reports. Decide whether absent status-day combinations should be missing or displayed as zero, because zero rows require a calendar or dimension table. The query below uses UTC days for an explicit, reproducible contract.

WITH orders(order_id, status, created_at) AS (
  VALUES
    (1, 'paid', TIMESTAMPTZ '2026-07-22 23:50:00+00'),
    (2, 'paid', TIMESTAMPTZ '2026-07-23 08:00:00+00'),
    (3, 'failed', TIMESTAMPTZ '2026-07-23 09:00:00+00')
)
SELECT
  (created_at AT TIME ZONE 'UTC')::date AS order_day,
  status,
  COUNT(*) AS order_count
FROM orders
WHERE created_at >= TIMESTAMPTZ '2026-07-22 00:00:00+00'
  AND created_at < TIMESTAMPTZ '2026-07-24 00:00:00+00'
GROUP BY (created_at AT TIME ZONE 'UTC')::date, status
ORDER BY order_day, status;

Q: What is the difference between WHERE and HAVING?

WHERE removes individual rows before grouping, whereas HAVING removes groups after aggregates have been calculated. Put ordinary conditions such as state = 'failed' in WHERE so irrelevant rows never enter the aggregate. A condition such as COUNT(*) > 3 belongs in HAVING because that value does not exist until grouping finishes. Both clauses can appear together and serve different responsibilities. If a candidate moves the status condition into HAVING without grouping by status, the query may be invalid or express a different rule.

WITH payments(customer_id, state) AS (
  VALUES
    (1, 'failed'), (1, 'failed'), (1, 'failed'), (1, 'failed'),
    (1, 'paid'), (2, 'failed')
)
SELECT customer_id, COUNT(*) AS failed_attempts
FROM payments
WHERE state = 'failed'
GROUP BY customer_id
HAVING COUNT(*) > 3;

Q: How can conditional aggregation return passed, failed, and skipped counts in one query?

Conditional aggregation calculates several measures from the same grouped input. PostgreSQL's FILTER syntax keeps each condition beside its aggregate, while portable SQL can use SUM(CASE WHEN ... THEN 1 ELSE 0 END). This is preferable to three unrelated queries because every count uses the same snapshot and base predicate. Retain the total count as a control so the status buckets can be checked for completeness. If an unexpected status exists, the sum of named buckets will be lower than the total and expose the gap.

WITH test_runs(suite, status) AS (
  VALUES
    ('checkout', 'passed'), ('checkout', 'failed'),
    ('checkout', 'skipped'), ('search', 'passed')
)
SELECT
  suite,
  COUNT(*) AS total_count,
  COUNT(*) FILTER (WHERE status = 'passed') AS passed_count,
  COUNT(*) FILTER (WHERE status = 'failed') AS failed_count,
  COUNT(*) FILTER (WHERE status = 'skipped') AS skipped_count
FROM test_runs
GROUP BY suite
ORDER BY suite;

Q: How would you reconcile an order header total against its line items?

Aggregate line values at the order grain before comparing them with the header. Use exact integer minor units or numeric for money, and reproduce the documented discount, tax, and rounding order. A left join can expose a header with no lines, while a full outer join also reveals line groups whose header is missing. COALESCE a missing line sum to zero only if an empty order is a valid zero-total state. Return declared total, calculated total, and the difference so the failure explains itself.

WITH headers(order_id, declared_cents) AS (
  VALUES (1, 3500), (2, 2000), (3, 0)
),
lines(order_id, quantity, unit_cents) AS (
  VALUES (1, 1, 1500), (1, 2, 1000), (2, 1, 1900)
),
calculated AS (
  SELECT order_id, SUM(quantity * unit_cents) AS calculated_cents
  FROM lines
  GROUP BY order_id
)
SELECT
  h.order_id,
  h.declared_cents,
  COALESCE(c.calculated_cents, 0) AS calculated_cents,
  h.declared_cents - COALESCE(c.calculated_cents, 0) AS difference_cents
FROM headers AS h
LEFT JOIN calculated AS c USING (order_id)
WHERE h.declared_cents IS DISTINCT FROM COALESCE(c.calculated_cents, 0)
ORDER BY h.order_id;

Q: Why can averaging suite-level pass percentages produce the wrong overall rate?

An unweighted average gives a suite with two tests the same influence as a suite with two thousand tests. The overall rate must divide total passes by total executions, or weight each suite rate by its execution count. Preserve fractional arithmetic by casting an operand to numeric, and protect an empty denominator with NULLIF. A null result for zero executions usually communicates not applicable more honestly than zero percent. State whether retries, skipped tests, and quarantined tests belong in the denominator before writing the formula.

WITH suite_results(suite, passed_count, executed_count) AS (
  VALUES ('smoke', 9, 10), ('regression', 50, 100)
)
SELECT
  ROUND(AVG(100.0 * passed_count / executed_count), 2) AS wrong_unweighted_rate,
  ROUND(100.0 * SUM(passed_count) / NULLIF(SUM(executed_count), 0), 2)
    AS overall_pass_rate
FROM suite_results;

Q: What is aggregation grain, and how do you prove a query returns one row per intended entity?

Grain is the real-world unit represented by one result row. A report intended to return one row per order must group or partition by the complete order key, including tenant context when relevant. Extra dimensions in GROUP BY create multiple rows per order, while omitted key parts can merge unrelated orders. Test the result by grouping it again on the intended key and looking for COUNT(*) > 1. Also compare the number of result keys with the eligible base population so missing entities are not overlooked.

WITH report_rows(tenant_id, order_id, metric_name, metric_value) AS (
  VALUES
    (7, 100, 'total', 2500),
    (7, 100, 'tax', 200),
    (7, 101, 'total', 1800)
)
SELECT tenant_id, order_id, COUNT(*) AS rows_at_order_grain
FROM report_rows
GROUP BY tenant_id, order_id
HAVING COUNT(*) > 1;

4. Subqueries, CTEs, and Set Operations

Subqueries answer relationship and comparison questions without forcing extra columns into the result. CTEs name stages, while set operators compare compatible row shapes. The focused SQL subqueries for testers tutorial explores their NULL, duplicate, and tie behavior in more depth.

Q: When should you use EXISTS rather than IN?

Use EXISTS when the requirement asks whether at least one related row is present. It behaves like a semijoin, so a parent is not duplicated merely because several children match. IN is clear for membership in a small literal list or a single-column subquery whose null behavior is understood. Optimizers may produce similar plans, so the main choice is semantic clarity rather than a universal performance rule. Select 1 inside EXISTS because child values are irrelevant; only the existence of a qualifying row matters.

WITH customers(customer_id) AS (VALUES (1), (2), (3)),
orders(order_id, customer_id, status) AS (
  VALUES (10, 1, 'paid'), (11, 1, 'paid'), (12, 2, 'cancelled')
)
SELECT c.customer_id
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
    AND o.status = 'paid'
)
ORDER BY c.customer_id;

Q: Why can NOT IN return no rows when its subquery contains NULL?

For each candidate, NOT IN must prove that the value differs from every subquery result. One null makes one of those comparisons unknown, so the combined predicate is not true and WHERE removes the row. This can turn an anti-filter into an empty result even when many values are clearly unblocked. A correlated NOT EXISTS compares only actual equal keys and avoids that trap. NOT IN is safe when the subquery expression is guaranteed non-null, but NOT EXISTS often communicates QA intent more defensively.

WITH users(user_id) AS (VALUES (1), (2), (3)),
blocked_users(user_id) AS (VALUES (2), (NULL::integer))
SELECT u.user_id
FROM users AS u
WHERE NOT EXISTS (
  SELECT 1
  FROM blocked_users AS b
  WHERE b.user_id = u.user_id
)
ORDER BY u.user_id;

Q: How would a correlated subquery find orders above each customer's average?

A correlated subquery references the current outer customer and calculates a different baseline for each one. The inner average is therefore scoped to peer orders rather than the entire table. It is expressive for an interview answer, although a pre-aggregated join or window function may be easier to optimize and inspect on large data. Define what happens when an amount is null because AVG ignores null inputs. Show the customer key in the result so the reviewer can verify that each comparison used the correct group.

WITH orders(order_id, customer_id, amount_cents) AS (
  VALUES
    (1, 10, 1000), (2, 10, 3000), (3, 10, 5000),
    (4, 20, 2000), (5, 20, 2400)
)
SELECT o.order_id, o.customer_id, o.amount_cents
FROM orders AS o
WHERE o.amount_cents > (
  SELECT AVG(peer.amount_cents)
  FROM orders AS peer
  WHERE peer.customer_id = o.customer_id
)
ORDER BY o.order_id;

Q: How can a CTE make a multi-stage validation query easier to verify?

A CTE gives each transformation or validation stage a name and an explicit result grain. Separate eligible rows, calculated values, and mismatches so each stage can be run independently while debugging. This reduces the risk of mixing filters or aggregations into one dense expression whose logic cannot be reviewed. PostgreSQL may inline a nonrecursive side-effect-free CTE, so readability does not automatically mean materialization. Use MATERIALIZED or NOT MATERIALIZED only after a representative plan shows a reason, not as interview folklore.

WITH orders(order_id, status, declared_cents) AS (
  VALUES (1, 'paid', 3500), (2, 'cancelled', 2000)
),
lines(order_id, quantity, unit_cents) AS (
  VALUES (1, 1, 1500), (1, 2, 1000), (2, 1, 1900)
),
eligible AS (
  SELECT * FROM orders WHERE status = 'paid'
),
line_totals AS (
  SELECT order_id, SUM(quantity * unit_cents) AS calculated_cents
  FROM lines GROUP BY order_id
)
SELECT e.order_id, e.declared_cents, l.calculated_cents
FROM eligible AS e
JOIN line_totals AS l USING (order_id)
WHERE e.declared_cents IS DISTINCT FROM l.calculated_cents;

Q: What is the difference between UNION and UNION ALL, and how can UNION hide defects?

UNION ALL concatenates compatible results and preserves every duplicate row. UNION additionally removes duplicates, which requires work and changes the data's multiplicity. Use UNION only when set semantics are part of the requirement, not as a convenient cleanup step. In event or ETL validation, duplicate records may be the defect, so deduplication can make a bad target appear correct. Add a source label before combining datasets when QA needs to know which input produced each row.

WITH api_events(event_id) AS (VALUES (1), (2), (2)),
db_events(event_id) AS (VALUES (2), (3))
SELECT 'api' AS source_name, event_id FROM api_events
UNION ALL
SELECT 'database', event_id FROM db_events
ORDER BY source_name, event_id;

Q: How would you use EXCEPT for source-to-target comparison?

Run the difference in both directions because source minus target finds missing target rows but cannot find unexpected target rows. EXCEPT uses distinct set semantics, while EXCEPT ALL preserves excess copies and is preferable when multiplicity matters. Both sides must project compatible columns in the same order and with comparable types. Label the direction before combining results so the mismatch is actionable. On an engine without EXCEPT, use two NOT EXISTS anti-joins with null-safe comparisons, then test duplicate counts separately.

WITH source_rows(order_id, status) AS (
  VALUES (1, 'paid'), (1, 'paid'), (2, 'new')
),
target_rows(order_id, status) AS (
  VALUES (1, 'paid'), (2, 'new'), (3, 'new')
)
SELECT 'source_only' AS mismatch_side, d.*
FROM (
  SELECT order_id, status FROM source_rows
  EXCEPT ALL
  SELECT order_id, status FROM target_rows
) AS d
UNION ALL
SELECT 'target_only', d.*
FROM (
  SELECT order_id, status FROM target_rows
  EXCEPT ALL
  SELECT order_id, status FROM source_rows
) AS d
ORDER BY mismatch_side, order_id;

5. Window Functions and Time-Ordered Data

Window functions retain row detail while adding a calculation over related rows. They are especially useful for ranking, deduplication, latest-record selection, and sequence checks. Practice the frame and tie edge cases in SQL window functions for testers.

Q: How do ROW_NUMBER, RANK, and DENSE_RANK handle ties differently?

ROW_NUMBER assigns a unique sequence to every row, even when sort values tie. RANK gives tied rows the same rank and leaves gaps afterward, while DENSE_RANK uses the same rank for ties without gaps. Add a stable secondary sort key to ROW_NUMBER when the requirement needs exactly one reproducible winner. Use DENSE_RANK for distinct score bands or the nth distinct value, and RANK when competition-style positions should skip. An answer is incomplete until it states how ties should affect the requested result.

WITH candidates(candidate_id, score) AS (
  VALUES (101, 95), (102, 90), (103, 90), (104, 80)
)
SELECT
  candidate_id,
  score,
  ROW_NUMBER() OVER (ORDER BY score DESC, candidate_id) AS row_position,
  RANK() OVER (ORDER BY score DESC) AS rank_position,
  DENSE_RANK() OVER (ORDER BY score DESC) AS dense_position
FROM candidates
ORDER BY score DESC, candidate_id;

Q: How would you return the second-highest distinct salary while handling ties?

Rank salaries in descending order with DENSE_RANK and select rank two in an outer query. Everyone tied on the same salary receives one rank, so the answer represents the second distinct amount rather than the second employee row. If the table has only one distinct salary, the result is empty, which should be part of the stated contract. LIMIT 1 OFFSET 1 works only after selecting distinct values and still uses dialect-specific pagination syntax. A correlated maximum-below-maximum query is another valid answer, but the ranking version generalizes cleanly to any nth value.

WITH employees(employee_id, salary) AS (
  VALUES (1, 90000), (2, 90000), (3, 80000), (4, 70000)
),
ranked AS (
  SELECT
    employee_id,
    salary,
    DENSE_RANK() OVER (ORDER BY salary DESC) AS salary_rank
  FROM employees
)
SELECT employee_id, salary
FROM ranked
WHERE salary_rank = 2
ORDER BY employee_id;

Q: How would you select the latest status row for every order?

Partition status history by order, sort newest first, and keep ROW_NUMBER() = 1 in an outer query. Include a unique event key after the timestamp because two events may share the same stored time. This returns the entire winning row, unlike grouping by MAX(changed_at), which does not identify the corresponding status. Decide whether future-dated events are eligible and whether correction records supersede earlier history. PostgreSQL's DISTINCT ON is shorter, but the window approach transfers to more engines.

WITH history(event_id, order_id, status, changed_at) AS (
  VALUES
    (1, 10, 'pending', TIMESTAMPTZ '2026-07-20 09:00:00+00'),
    (2, 10, 'paid', TIMESTAMPTZ '2026-07-20 10:00:00+00'),
    (3, 20, 'pending', TIMESTAMPTZ '2026-07-20 11:00:00+00'),
    (4, 20, 'failed', TIMESTAMPTZ '2026-07-20 11:00:00+00')
),
ranked AS (
  SELECT h.*,
    ROW_NUMBER() OVER (
      PARTITION BY order_id
      ORDER BY changed_at DESC, event_id DESC
    ) AS recency_number
  FROM history AS h
)
SELECT event_id, order_id, status, changed_at
FROM ranked
WHERE recency_number = 1
ORDER BY order_id;

Q: How can ROW_NUMBER identify physical rows beyond the first duplicate event?

Partition by the columns that define business equality and order by a documented retention rule. Row number one is the proposed survivor, while later numbers identify excess copies without collapsing their row IDs. The ordering might favor the earliest accepted event, the newest corrected record, or a trusted source, so do not invent it from convenience. Preview the ranked set and preserve the candidate IDs before deleting anything. Add a unique constraint or idempotency rule after cleanup, otherwise the same defect can recur.

WITH events(row_id, event_key, received_at) AS (
  VALUES
    (1, 'evt-9', TIMESTAMPTZ '2026-07-23 09:00:00+00'),
    (2, 'evt-9', TIMESTAMPTZ '2026-07-23 09:01:00+00'),
    (3, 'evt-10', TIMESTAMPTZ '2026-07-23 09:02:00+00')
),
ranked AS (
  SELECT e.*,
    ROW_NUMBER() OVER (
      PARTITION BY event_key
      ORDER BY received_at, row_id
    ) AS copy_number
  FROM events AS e
)
SELECT row_id, event_key, received_at, copy_number
FROM ranked
WHERE copy_number > 1;

Q: How would LAG expose an invalid order-status transition?

LAG reads the preceding status within each order's time sequence. Compare that previous value with the current value, then join or encode an allowlist of valid transitions such as pending to paid or paid to refunded. A deterministic sequence needs both event time and a unique event ID. Repeated snapshots may be ignored or treated as invalid depending on whether the table records events or periodic state. The first row has no predecessor, so validate its allowed initial state separately.

WITH history(event_id, order_id, status, changed_at) AS (
  VALUES
    (1, 50, 'pending', TIMESTAMPTZ '2026-07-20 09:00:00+00'),
    (2, 50, 'paid', TIMESTAMPTZ '2026-07-20 09:10:00+00'),
    (3, 50, 'pending', TIMESTAMPTZ '2026-07-20 09:20:00+00')
),
valid_transitions(previous_status, current_status) AS (
  VALUES
    ('pending', 'paid'),
    ('pending', 'cancelled'),
    ('paid', 'refunded')
),
sequenced AS (
  SELECT h.*,
    LAG(status) OVER (
      PARTITION BY order_id ORDER BY changed_at, event_id
    ) AS previous_status
  FROM history AS h
)
SELECT s.event_id, s.order_id, s.previous_status, s.status
FROM sequenced AS s
WHERE s.previous_status IS NOT NULL
  AND (
    s.status IS NULL
    OR NOT EXISTS (
      SELECT 1
      FROM valid_transitions AS v
      WHERE v.previous_status = s.previous_status
        AND v.current_status = s.status
    )
  );

Q: Why should a running-total query specify an explicit ROWS frame?

A running total needs a partition, a deterministic order, and a frame that says which ordered rows contribute. PostgreSQL's default RANGE frame treats rows sharing the ordering value as peers, so several same-time events can receive the same cumulative jump. ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW advances one ordered row at a time. Add a unique tie-breaker because a row-based frame is still nondeterministic if the order is incomplete. A bounded row frame can similarly calculate a moving sum over a fixed number of prior events.

WITH ledger(event_id, account_id, event_time, delta_cents) AS (
  VALUES
    (1, 10, TIMESTAMPTZ '2026-07-20 09:00:00+00', 5000),
    (2, 10, TIMESTAMPTZ '2026-07-20 09:00:00+00', -1200),
    (3, 10, TIMESTAMPTZ '2026-07-20 10:00:00+00', 700)
)
SELECT
  event_id,
  delta_cents,
  SUM(delta_cents) OVER (
    PARTITION BY account_id
    ORDER BY event_time, event_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_balance_cents
FROM ledger
ORDER BY event_time, event_id;

6. Data Integrity, Business Rules, and Constraints

A useful validation query returns only violations and includes enough evidence to investigate them. These database validation queries mirror the SQL data validation interview questions used to test whether you can turn product rules into diagnostic evidence. Build checks from explicit invariants, not from a sample that happens to look plausible. The validating data integrity with SQL guide and database constraint testing guide extend these patterns.

Q: How would you find required text fields that are NULL, empty, or whitespace-only?

Check each state deliberately because null and an empty string are different values. NULLIF(TRIM(name), '') IS NULL is a concise PostgreSQL-compatible test that catches null, empty, and ordinary surrounding whitespace after trimming. Unicode whitespace rules may differ from ASCII trimming, so align normalization with the application contract. Return the record key and raw value rather than only a count. A database NOT NULL constraint blocks null but does not block an empty string, which is why QA still needs this rule.

WITH customers(customer_id, display_name) AS (
  VALUES (1, 'Asha'), (2, NULL::text), (3, ''), (4, '   ')
)
SELECT customer_id, display_name
FROM customers
WHERE NULLIF(TRIM(display_name), '') IS NULL
ORDER BY customer_id;

Q: How would you detect values outside an allowed domain or numeric range?

Translate the requirement into separate predicates for enum membership, lower and upper bounds, and nullability. NOT IN does not flag null, so add an explicit null condition when the field is required. Keep unexpected values in the output rather than mapping them to an other bucket that hides drift. Use exact boundary operators, because between 0 and 100 may mean inclusive or exclusive endpoints. A matching CHECK constraint can prevent future invalid rows when the rule depends only on the current record.

WITH discounts(discount_id, status, percent_value) AS (
  VALUES
    (1, 'active', 10.0), (2, 'enabled', 5.0),
    (3, 'inactive', 120.0), (4, NULL::text, 0.0)
)
SELECT discount_id, status, percent_value
FROM discounts
WHERE status IS NULL
   OR status NOT IN ('active', 'inactive')
   OR percent_value < 0
   OR percent_value > 100
ORDER BY discount_id;

Q: How would you encode a cross-column rule such as discount not exceeding subtotal?

Write the invariant using the columns at the same grain, then return rows where it is false. Include related conditions such as negative subtotal, negative discount, currency mismatch, or nulls when they change the conclusion. A lateral values list can name multiple violations per row and make test output more diagnostic. If the rule is always valid for one row, a CHECK constraint is stronger than a periodic query. Rules requiring other rows, exchange rates, or historical context belong in transactional logic and tests instead.

WITH lines(line_id, subtotal_cents, discount_cents) AS (
  VALUES (1, 5000, 500), (2, 1000, 1200), (3, -100, 0)
)
SELECT l.line_id, checks.rule_name
FROM lines AS l
CROSS JOIN LATERAL (
  VALUES
    ('negative_subtotal', l.subtotal_cents < 0),
    ('negative_discount', l.discount_cents < 0),
    ('discount_exceeds_subtotal', l.discount_cents > l.subtotal_cents)
) AS checks(rule_name, failed)
WHERE checks.failed IS TRUE
ORDER BY l.line_id, checks.rule_name;

Q: How would you find impossible lifecycle timestamps?

List the allowed temporal relationships before writing comparisons. Creation should normally precede payment, shipment should follow payment for prepaid orders, and cancellation may require later timestamps to remain null. Ordinary comparisons involving null yield unknown, so each conditional requirement needs an explicit absence check. Use instants in a consistent zone rather than formatted local strings. Return every violated rule separately so one row with several temporal defects does not lose evidence.

WITH orders(order_id, status, created_at, paid_at, shipped_at) AS (
  VALUES
    (1, 'shipped', TIMESTAMPTZ '2026-07-23 09:00+00',
                   TIMESTAMPTZ '2026-07-23 09:10+00',
                   TIMESTAMPTZ '2026-07-23 09:30+00'),
    (2, 'shipped', TIMESTAMPTZ '2026-07-23 10:00+00',
                   NULL::timestamptz,
                   TIMESTAMPTZ '2026-07-23 09:50+00')
)
SELECT order_id
FROM orders
WHERE (status = 'shipped' AND shipped_at IS NULL)
   OR paid_at < created_at
   OR shipped_at < created_at
   OR (shipped_at IS NOT NULL AND paid_at IS NOT NULL AND shipped_at < paid_at)
   OR (status = 'shipped' AND paid_at IS NULL);

Q: How would you detect duplicate emails after trimming and case normalization?

Group by the same normalized key the product uses, such as LOWER(TRIM(email)) within a tenant. Keep groups larger than one and aggregate row IDs so the result is actionable. Exclude null only when several unknown emails are allowed; otherwise test null as a separate required-field violation. Confirm case-folding and Unicode behavior with the authentication or identity contract rather than assuming all visually similar strings are equivalent. Once clean, a functional unique index can enforce the normalized rule for new writes.

WITH users(user_id, tenant_id, email) AS (
  VALUES
    (1, 7, 'Asha@example.com'),
    (2, 7, ' asha@example.com '),
    (3, 8, 'Asha@example.com'),
    (4, 7, 'other@example.com')
)
SELECT
  tenant_id,
  LOWER(TRIM(email)) AS normalized_email,
  COUNT(*) AS duplicate_count,
  ARRAY_AGG(user_id ORDER BY user_id) AS user_ids
FROM users
WHERE email IS NOT NULL
GROUP BY tenant_id, LOWER(TRIM(email))
HAVING COUNT(*) > 1;

Q: How would you prove NOT NULL, UNIQUE, CHECK, and foreign-key constraints reject bad writes?

Create one negative test per constraint and assert both the rejection and the unchanged database state. Use known error categories or constraint names rather than matching a vendor's full human-readable message. Run each case in its own transaction or savepoint so one expected failure does not abort the rest of the test session. Add positive boundary cases, such as the exact minimum allowed quantity, to prove the rule is not too strict. Constraint coverage complements business validation because it tests prevention at the database boundary, not merely detection afterward.

CREATE TEMP TABLE qa_accounts (
  account_id bigint PRIMARY KEY,
  email text NOT NULL UNIQUE,
  state text NOT NULL CHECK (state IN ('active', 'blocked'))
);

CREATE TEMP TABLE qa_orders (
  order_id bigint PRIMARY KEY,
  account_id bigint NOT NULL REFERENCES qa_accounts(account_id)
);

INSERT INTO qa_accounts VALUES (1, 'qa@example.test', 'active');

BEGIN;
SAVEPOINT before_invalid_order;
INSERT INTO qa_orders VALUES (10, 999);
-- PostgreSQL rejects the insert; a test client should assert foreign_key_violation.
ROLLBACK TO SAVEPOINT before_invalid_order;

SELECT COUNT(*) AS invalid_order_count
FROM qa_orders
WHERE order_id = 10;
ROLLBACK;

7. Test-Data Setup, Isolation, and Teardown

SQL test data setup fails for reasons unrelated to the feature when fixtures are ambiguous or cleanup crosses test boundaries. Favor explicit ownership, stable business references, and the smallest valid dependency graph. The complete SQL test-data setup and teardown guide covers committed and rollback-based harnesses.

Q: How would you create deterministic parent-and-child fixtures while capturing generated IDs?

Insert the parent first and capture its generated key with RETURNING instead of predicting an identity value. A data-modifying CTE can pass that key directly into the child insert in the same statement. Give each fixture a stable, test-owned external reference so later assertions can find it without assuming a particular sequence number. Fix timestamps and status values when they influence output. Verify the created relationship through the business reference, not merely by checking that both inserts returned success.

CREATE TEMP TABLE qa_customers (
  customer_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  external_ref text NOT NULL UNIQUE
);
CREATE TEMP TABLE qa_orders (
  order_id bigint GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id bigint NOT NULL REFERENCES qa_customers(customer_id),
  external_ref text NOT NULL UNIQUE
);

WITH new_customer AS (
  INSERT INTO qa_customers(external_ref)
  VALUES ('qa-customer-20260723')
  RETURNING customer_id
)
INSERT INTO qa_orders(customer_id, external_ref)
SELECT customer_id, 'qa-order-20260723'
FROM new_customer
RETURNING order_id, customer_id, external_ref;

Q: When does transaction-per-test rollback work, and why can a separate application connection defeat it?

Rollback works well when setup, action, and assertions all use the same database connection and the behavior does not commit internally. The test opens a transaction, creates data, exercises repository code, checks state, then rolls everything back. An application request usually runs through another pooled connection, which cannot see the test's uncommitted fixture under ordinary isolation. Background workers and commit-aware behavior also escape the single-connection boundary. Those tests need committed, namespaced fixtures plus explicit cleanup, or an isolated disposable database.

BEGIN;

CREATE TEMP TABLE qa_users (
  user_id bigint GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
  email text NOT NULL UNIQUE
) ON COMMIT DROP;

INSERT INTO qa_users(email) VALUES ('candidate@example.test');
SELECT user_id, email FROM qa_users WHERE email = 'candidate@example.test';

ROLLBACK;

Q: How do DELETE, TRUNCATE, and DROP differ for test cleanup?

DELETE removes selected rows and can use a precise ownership predicate, making it the usual shared-environment cleanup tool. TRUNCATE empties an entire table quickly but may require stronger locks, interact with identity counters, and cascade differently by engine. DROP removes the table object itself, which suits disposable schemas rather than shared fixtures. Transaction and rollback behavior for truncate and DDL varies across database products, so avoid universal claims. A QA answer should choose based on isolation, foreign keys, trigger behavior, lock impact, and recovery needs, not only speed.

Q: How do foreign keys, cascades, triggers, and cleanup order affect teardown?

Without cascading deletes, remove dependents before parents so foreign keys remain satisfied. With ON DELETE CASCADE, confirm exactly which tables are affected because a convenient parent delete can erase another test's shared children. Delete triggers may create audit rows or invoke side effects, which means teardown can generate new state while removing fixtures. Keep a unique run identifier on every owned row and inspect the dependency graph before writing cleanup. In a disposable per-run schema, dropping that exact schema can be safer than maintaining a long delete list, provided the schema name is resolved and validated.

BEGIN;

DELETE FROM test_order_items
WHERE test_run_id = 'run-4f32';

DELETE FROM test_payments
WHERE test_run_id = 'run-4f32';

DELETE FROM test_orders
WHERE test_run_id = 'run-4f32'
RETURNING order_id;

COMMIT;

Q: When is an upsert useful for repeatable seed data, and how can it hide a defect?

An upsert makes reference fixtures converge on the same state when setup runs more than once. Use a test-owned natural key and update every mutable field to the expected value so residue cannot leak between runs. It is appropriate for stable configuration such as a named feature flag. Do not use ON CONFLICT DO NOTHING in a test whose purpose is to verify that duplicate creation is rejected, because it converts the expected failure into apparent success. Assert whether the statement inserted or updated when that distinction matters.

CREATE TEMP TABLE qa_flags (
  flag_key text PRIMARY KEY,
  enabled boolean NOT NULL,
  updated_at timestamptz NOT NULL
);

INSERT INTO qa_flags(flag_key, enabled, updated_at)
VALUES ('qa-checkout-v2', true, TIMESTAMPTZ '2026-07-23 09:00:00+00')
ON CONFLICT (flag_key) DO UPDATE
SET enabled = EXCLUDED.enabled,
    updated_at = EXCLUDED.updated_at;

SELECT * FROM qa_flags WHERE flag_key = 'qa-checkout-v2';

Q: How would you isolate database data across parallel test workers?

Give each worker a collision-resistant ownership key such as a run UUID, tenant, schema, or database. Include that key in fixture references, unique values, queries, and cleanup predicates. Per-run schemas are strong isolation when every pooled connection can set the correct search_path; tenant namespaces fit applications already designed around tenant boundaries. Never use a broad cleanup predicate built from an unset environment variable. Test isolation by running the same case concurrently and proving that each worker can see and remove only its own rows.

CREATE TEMP TABLE qa_parallel_orders (
  run_id uuid NOT NULL,
  order_id bigint NOT NULL,
  external_ref text NOT NULL,
  PRIMARY KEY (run_id, order_id),
  UNIQUE (run_id, external_ref)
);

INSERT INTO qa_parallel_orders(run_id, order_id, external_ref)
VALUES ('00000000-0000-0000-0000-000000004f32', 1, 'order-1');

SELECT *
FROM qa_parallel_orders
WHERE run_id = '00000000-0000-0000-0000-000000004f32';

8. Transactions and Concurrency

Concurrency questions cannot be demonstrated with one connection and a few sequential statements. Describe session A, session B, the synchronization checkpoint, and the expected state under a named isolation level. These cases complement the practical constraint scenarios in database testing interview questions.

Q: How do the ACID properties translate into QA assertions?

Atomicity means a failed multi-step operation leaves either all intended changes or none, so query every affected table after injected failure. Consistency means committed state still satisfies constraints and business invariants, not that application behavior is automatically correct. Isolation requires concurrent transactions to show behavior permitted by the chosen level without lost updates or forbidden reads. Durability means a confirmed commit remains after reconnect, restart, or failover within the system's documented guarantees. A strong interview answer turns each definition into observable setup, action, and evidence instead of reciting four words.

Q: What are dirty reads, nonrepeatable reads, and phantom reads?

A dirty read observes another transaction's uncommitted change, which PostgreSQL does not permit even when READ UNCOMMITTED is requested. A nonrepeatable read occurs when the same row query returns a changed value after another transaction commits. A phantom occurs when repeating a predicate query returns a changed set of qualifying rows. Which anomalies are possible depends on the database and isolation level, so name both before stating expected behavior. Reproduce them with two sessions synchronized by explicit checkpoints, not with guessed sleeps that may race.

Q: How would you reproduce and verify a lost-update defect?

Initialize one row with a known balance or stock count, then let two sessions read the same version before either writes. Have each calculate a new absolute value and commit, which may allow the later write to overwrite the earlier one. The invariant should show that both business operations were accepted but only one effect remains. Repeat with an atomic relative update, row lock, serializable transaction, or optimistic version check to prove the chosen fix. Capture affected-row counts and final state because successful commits alone do not establish correctness.

-- Both sessions first read stock = 20.
SELECT stock
FROM products
WHERE product_id = 1;

-- Session A writes its calculated value and commits.
UPDATE products
SET stock = 18
WHERE product_id = 1;

-- Session B writes a value calculated from the stale stock of 20.
UPDATE products
SET stock = 17
WHERE product_id = 1;

-- The final value is 17, so Session A's decrement was lost.
SELECT stock
FROM products
WHERE product_id = 1;

Q: How would you prove that an order, payment, and inventory update are atomic?

Seed a product and account, begin the service operation, then inject a controlled failure after at least one write but before commit. Query order, payment, inventory, and audit tables from a fresh connection after the error. The expected outcome is either no operation rows with unchanged stock, or a complete committed set, never a partial combination. Also test the successful path because rollback evidence alone does not prove the transaction commits all intended side effects. External messages may require an outbox or compensation pattern since a database rollback cannot recall an event already published.

BEGIN;

INSERT INTO qa_orders(order_id, status) VALUES (9001, 'pending');
UPDATE qa_inventory SET stock = stock - 1 WHERE product_id = 77;
INSERT INTO qa_payments(payment_id, order_id, amount_cents)
VALUES (5001, 9001, 2500);

-- Inject or observe the failure before COMMIT during the negative test.
ROLLBACK;

Q: How do you test lock timeouts or deadlocks without leaving sessions blocked?

Use two dedicated sessions, short local timeouts, and a documented lock order. For a lock-timeout test, session A holds a row lock while session B attempts the conflicting statement with lock_timeout set to a few seconds. For a deadlock, each session locks a different row and then requests the other's row, after which the database should abort one transaction. Always roll back or commit both sessions in cleanup and record which error category occurred. Run this only in an isolated database because intentionally held locks can disrupt unrelated work.

-- Session A
BEGIN;
SELECT * FROM accounts WHERE account_id = 1 FOR UPDATE;

-- Session B
BEGIN;
SET LOCAL lock_timeout = '2s';
UPDATE accounts SET status = 'blocked' WHERE account_id = 1;
ROLLBACK;

-- Return to Session A
ROLLBACK;

Q: How would you verify optimistic concurrency with a version column?

Read the row and its version, then include that version in the update predicate. A successful writer increments the version and returns the new row. A stale writer using the old version affects zero rows, which the application must translate into retry or conflict behavior. Assert the affected-row count or empty RETURNING result, not merely the lack of a SQL exception. Add a concurrent API test to confirm that two accepted requests do not silently overwrite one another.

CREATE TEMP TABLE qa_products (
  product_id bigint PRIMARY KEY,
  stock integer NOT NULL,
  version integer NOT NULL
);
INSERT INTO qa_products VALUES (1, 20, 7);

UPDATE qa_products
SET stock = 18, version = version + 1
WHERE product_id = 1 AND version = 7
RETURNING product_id, stock, version;

UPDATE qa_products
SET stock = 15, version = version + 1
WHERE product_id = 1 AND version = 7
RETURNING product_id, stock, version;

9. ETL, Warehouses, and Migration Validation

ETL validation starts with a stable snapshot and the source-to-target mapping. Counts are control totals, not proof of record-level correctness. For deeper practice, see writing SQL to validate ETL and ETL data testing interview questions.

Q: Why are equal source and target row counts insufficient for ETL validation?

Equal counts can hide one missing source row offset by one unexpected target row, duplicated records, truncated values, or wrong transformations. Compare stable business keys in both directions, then validate mapped attributes, null rules, aggregates, and duplicate multiplicity. Both datasets must represent the same cutoff or transactionally consistent snapshot. Segment mismatches by batch, date, tenant, or partition so a failure points to the broken slice.

Q: How would you independently validate transformations involving NULL defaults, rounding, and code mappings?

Recompute the expected target value from raw source columns instead of copying the pipeline's implementation query. Express null defaults, mapping tables, type casts, and the exact rounding stage in named CTEs. Compare expected and actual with null-safe operators and return both inputs plus outputs. Include unmapped codes and boundary decimals because happy-path values rarely expose fallback or precision defects.

WITH source_rows(id, source_code, raw_amount) AS (
  VALUES (1, 'A', 10.005::numeric), (2, NULL::text, 4.444::numeric)
),
expected AS (
  SELECT
    id,
    COALESCE(source_code, 'UNKNOWN') AS expected_code,
    ROUND(raw_amount, 2) AS expected_amount
  FROM source_rows
),
target_rows(id, target_code, amount) AS (
  VALUES (1, 'ACTIVE', 10.01::numeric), (2, 'UNKNOWN', 4.44::numeric)
),
code_map(source_code, target_code) AS (
  VALUES ('A', 'ACTIVE'), ('UNKNOWN', 'UNKNOWN')
)
SELECT e.id, m.target_code AS expected_code, t.target_code AS actual_code,
       e.expected_amount, t.amount AS actual_amount
FROM expected AS e
LEFT JOIN code_map AS m ON m.source_code = e.expected_code
LEFT JOIN target_rows AS t USING (id)
WHERE m.source_code IS NULL
   OR t.id IS NULL
   OR m.target_code IS DISTINCT FROM t.target_code
   OR e.expected_amount IS DISTINCT FROM t.amount;

Q: How would a FULL OUTER JOIN expose source-only, target-only, and changed rows?

Join on a unique business key and preserve both sides. Classify a missing source as target-only, a missing target as source-only, and matched keys with null-safe value differences as changed. Validate key uniqueness before the join, because duplicate keys can multiply the comparison and confuse classification. Return the mismatched fields from both sides so the reconciliation is usable for diagnosis.

WITH source_orders(id, status, amount) AS (
  VALUES (1, 'paid', 25.00::numeric), (2, NULL, 40.00), (3, 'new', 12.00)
),
target_orders(id, status, amount) AS (
  VALUES (1, 'paid', 25.00::numeric), (2, 'new', 40.00), (4, 'new', 12.00)
)
SELECT
  COALESCE(s.id, t.id) AS id,
  CASE
    WHEN s.id IS NULL THEN 'target_only'
    WHEN t.id IS NULL THEN 'source_only'
    ELSE 'changed'
  END AS issue,
  s.status AS source_status,
  t.status AS target_status
FROM source_orders AS s
FULL OUTER JOIN target_orders AS t USING (id)
WHERE s.id IS NULL OR t.id IS NULL
   OR s.status IS DISTINCT FROM t.status
   OR s.amount IS DISTINCT FROM t.amount
ORDER BY id;

Q: How would you test an incremental load for watermark boundaries, late data, and reruns?

Seed records immediately before, exactly at, and immediately after the watermark, then assert the contract's inclusive and exclusive boundaries. Add a late-arriving record whose business time is old but ingestion or update time is new. Rerun the same batch and verify idempotency, including unchanged counts and no duplicate facts. Record the processed watermark only after successful completion so a failed batch cannot skip eligible data.

Q: How would you validate SCD Type 2 history for overlaps, gaps, and incorrect current flags?

Partition by the business key, order versions by valid_from, and compare each start with the greatest prior end. A running maximum detects containment overlaps that a simple LAG(valid_to) can miss. Check that each entity has exactly one current row, historical rows have closed intervals, and attribute changes create a new version rather than overwriting history. Define interval semantics, commonly inclusive start and exclusive end, before deciding whether adjacent dates form a gap.

WITH history(version_id, customer_id, valid_from, valid_to, is_current) AS (
  VALUES
    (1, 10, DATE '2026-01-01', DATE '2026-06-01', false),
    (2, 10, DATE '2026-05-15', NULL::date, true),
    (3, 20, DATE '2026-01-01', NULL::date, true),
    (4, 20, DATE '2026-02-01', NULL::date, true)
),
checked AS (
  SELECT h.*,
    MAX(COALESCE(valid_to, 'infinity'::date)) OVER (
      PARTITION BY customer_id
      ORDER BY valid_from, version_id
      ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING
    ) AS prior_max_end,
    COUNT(*) FILTER (WHERE is_current) OVER (
      PARTITION BY customer_id
    ) AS current_count
  FROM history AS h
)
SELECT *
FROM checked
WHERE (prior_max_end IS NOT NULL AND prior_max_end <> valid_from)
   OR current_count <> 1
   OR (is_current AND valid_to IS NOT NULL)
   OR (NOT is_current AND valid_to IS NULL);

Q: What should a pre-migration and post-migration SQL validation pack contain?

Before migration, capture schema definitions, row and distinct-key counts, null profiles, duplicate groups, control totals, constraint status, and representative checksums where useful. After migration, rerun the same controls against the same snapshot and add bidirectional key plus value comparisons. Verify defaults, precision, collation, indexes, sequences, permissions, and application read/write behavior, not only table contents. Preserve mismatch samples and query parameters as release evidence, and rehearse rollback or roll-forward criteria before production.

10. Query Performance and Safe Diagnostics

Performance answers should connect the plan to real data distribution and workload. A query that is fast on ten uniform QA rows says little about a skewed production table with concurrent writes. Separate read-only investigation from changes to indexes, statistics, or session state.

Q: What does an index improve, and what costs does it introduce?

An index can reduce the rows read for selective filters, joins, uniqueness checks, and ordered retrieval. It consumes storage, adds work to inserts and updates, and creates vacuum or maintenance overhead. Low-selectivity predicates may still favor a sequential scan, especially when many rows are needed. Propose an index from a real query pattern, then verify its benefit with representative plans and write-load impact.

Q: Why does column order matter in a composite index?

A composite index is most directly useful from its leftmost prefix. For WHERE customer_id = ? AND created_at >= ? ORDER BY created_at DESC, (customer_id, created_at DESC) supports equality first and a range within that customer. Reversing the columns may help a different workload but makes this lookup less targeted. Included columns can reduce heap visits, yet they also enlarge the index and do not guarantee an index-only scan.

CREATE INDEX orders_customer_created_idx
ON orders (customer_id, created_at DESC)
INCLUDE (status, total_amount);

Q: How do you read an execution plan, and why must EXPLAIN ANALYZE be used carefully?

Start with actual versus estimated rows, loops, expensive nodes, join strategies, sort spills, and buffer reads. Large estimate errors can lead the planner toward a poor scan or join choice and often point to stale or insufficient statistics. EXPLAIN ANALYZE executes the statement, so a mutating query can change data even though the command begins with explain. Use it on safe reads or inside a controlled rollback transaction, with production access governed by explicit policy.

EXPLAIN (ANALYZE, BUFFERS, SETTINGS)
SELECT customer_id, SUM(total_amount)
FROM orders
WHERE created_at >= TIMESTAMPTZ '2026-07-01 00:00:00+00'
  AND created_at < TIMESTAMPTZ '2026-08-01 00:00:00+00'
GROUP BY customer_id;

Q: What makes a predicate non-sargable, and how would you rewrite a function-wrapped filter?

A sargable predicate allows an index condition to operate directly on stored values. Wrapping an indexed timestamp in DATE(created_at) often prevents a normal timestamp index from serving the filter efficiently. Rewrite it as a half-open timestamp range with explicit zone boundaries. Verify identical rows as well as the improved plan, because a fast rewrite that changes time-zone semantics is a defect.

SELECT *
FROM events
WHERE created_at >= TIMESTAMPTZ '2026-07-23 00:00:00+00'
  AND created_at < TIMESTAMPTZ '2026-07-24 00:00:00+00';

Q: How do offset and keyset pagination differ under concurrent inserts?

Offset pagination skips a changing number of current rows, so inserts or deletes before the next page can create duplicates or omissions. Keyset pagination continues after the last stable ordered key, such as (created_at, order_id), and usually scales better for deep pages. The ordering must be unique and the continuation comparison must match its direction. Offset remains useful for small, stable admin datasets or direct page numbers, but QA should mutate data between requests to test the chosen consistency contract.

SELECT order_id, created_at
FROM orders
WHERE (created_at, order_id)
    < (TIMESTAMPTZ '2026-07-23 10:00:00+00', 5000)
ORDER BY created_at DESC, order_id DESC
LIMIT 50;

Q: How would you diagnose production data safely?

Use approved read-only access, the smallest projection, selective predicates, a bounded time range, and a row limit. Set safe statement and lock timeouts where policy allows, inspect the plan without executing risky statements, and prefer a replica when its lag is acceptable for the question. Never paste customer secrets into tickets or export unrelated rows; redact evidence and preserve only what diagnosis requires. Any repair should follow a reviewed change procedure with a preview query, backup or reversal plan, ownership, and post-change verification.

11. Scenario-Based SQL Interview Questions for QA Engineers

These scenarios test whether you can convert a feature claim into database evidence. Lead with the invariant, then walk the tables and failure modes in order. Avoid presenting a single green row as proof of an entire workflow.

Q: A create-order API returns success. Which database rows and side effects would you validate?

Find the order by the returned ID and tenant, then verify customer, status, currency, totals, and timestamps. Recalculate line totals, confirm the payment or authorization record, verify inventory movement, and inspect the initial status-history or outbox event. Look for duplicate rows and partial state, especially after a timeout or retry. Use the API contract as the primary assertion and SQL as side-effect evidence rather than hard-coding every private column into all end-to-end tests.

Q: How would you prove that retrying a payment request does not create duplicate charges or ledger entries?

Send the same operation twice with one idempotency key, including a case where the first response is lost after the server commits. Query payment attempts, provider references, ledger entries, and order balance by that key. The second request may return the original outcome, but it must not create a second financial effect. Add concurrent duplicates because sequential retries may pass while a race still inserts two rows, and verify a unique constraint or atomic claim backs the behavior.

WITH expected(idempotency_key) AS (
  VALUES ('qa-pay-4f32')
),
summary AS (
  SELECT
    idempotency_key,
    COUNT(DISTINCT provider_charge_id) AS provider_charges,
    COUNT(*) FILTER (WHERE ledger_effect_cents <> 0) AS financial_effects,
    SUM(ledger_effect_cents) AS net_effect_cents
  FROM payment_ledger
  GROUP BY idempotency_key
)
SELECT
  e.idempotency_key,
  COALESCE(s.provider_charges, 0) AS provider_charges,
  COALESCE(s.financial_effects, 0) AS financial_effects,
  COALESCE(s.net_effect_cents, 0) AS net_effect_cents
FROM expected AS e
LEFT JOIN summary AS s USING (idempotency_key)
WHERE COALESCE(s.provider_charges, 0) <> 1
   OR COALESCE(s.financial_effects, 0) <> 1;

Q: How would you test soft deletion across queries, reports, and unique-key reuse?

Soft-delete a known user, then verify ordinary reads, searches, counts, exports, and joins exclude it while authorized audit views retain it. Test whether the email may be reused and enforce that rule with an active-row partial unique index when supported. Attempt restoration after reuse because it can create two active owners of one normalized email. Also verify related records are retained or hidden according to policy rather than assuming a physical foreign-key cascade applies.

Q: How would you validate tenant isolation and row-level security without relying only on the UI?

Create identical business keys under two tenants and execute the same query as each tenant-scoped database role or session context. Each role should see only its rows, and cross-tenant inserts, updates, joins, and direct-ID lookups should fail or return nothing. Test missing tenant context and privileged support roles because bypass paths are part of the policy. SQL checks should complement API authorization tests, not use an all-powerful service account that silently bypasses row-level security.

Q: How would you verify that an audit trail captures actor, timestamp, correlation ID, and before/after values?

Perform create, update, and delete actions with known actors and correlation IDs, then query audit entries by the correlation value. Assert one ordered record per auditable action, immutable actor identity, server-generated time, operation type, and accurate before/after values with sensitive fields masked. A failed transaction should not leave a committed business audit claiming success. Also test retries and background work so correlation propagates without duplicating events.

SELECT
  correlation_id,
  entity_type,
  entity_id,
  action,
  actor_id,
  occurred_at,
  before_data,
  after_data
FROM audit_log
WHERE correlation_id = 'qa-correlation-4f32'
ORDER BY occurred_at, audit_id;

Q: How would you test SQL-injection resistance safely, and why are parameters the real control?

Use a dedicated test environment and send payloads containing quotes, comment markers, boolean fragments, and encoded variants through each input boundary. The expected result is normal validation or literal-value handling, with no authentication bypass, extra rows, timing anomaly, or database error leakage. Parameterized queries keep data separate from SQL syntax, while escaping, input filtering, and stored procedures are not reliable substitutes when they concatenate strings internally. Pair dynamic testing with code review or instrumentation that proves values are bound parameters, and never run destructive injection payloads against production.

PREPARE find_user(text) AS
  SELECT user_id, email
  FROM users
  WHERE email = $1;

EXECUTE find_user('qa@example.test'' OR ''1''=''1');
DEALLOCATE find_user;

How Interviewers Grade Your Answers

Interviewers rarely score only whether the query parses. They evaluate whether you understood the business question, produced the correct result shape, anticipated damaging edge cases, and could use the query responsibly in a real system.

A reliable live-answer sequence is: clarify the rule, sketch the tables, state one output row's meaning, write the query, walk through a tiny fixture, add an adversarial row, and discuss safety or performance. If syntax escapes you, explain the exact operation you need and continue reasoning. A correctable typo costs less than confidently solving the wrong problem.

Common Mistakes

• Using SELECT * when the answer needs three columns, which hides the intended contract and reads unnecessary data.
• Omitting a unique tie-breaker from ORDER BY before LIMIT, ROW_NUMBER, or pagination.
• Comparing nullable values with = or <> and silently losing unknown cases.
• Choosing NOT IN without proving its subquery cannot return null.
• Adding DISTINCT to conceal a cardinality error instead of fixing the join.
• Summing after joining two independent child collections and multiplying totals.
• Using SUM(DISTINCT amount) as a repair, which drops legitimate equal-valued rows.
• Filtering a right-side table in WHERE after a left join when unmatched parents should remain.
• Treating equal counts as proof that source and target contain the same keys and values.
• Averaging percentages without weighting them by their denominators.
• Selecting MAX(timestamp) beside an unrelated status and assuming both came from one row.
• Deleting duplicate data before previewing candidates and agreeing on the survivor rule.
• Assuming transaction rollback can clean data written by another pooled connection.
• Running concurrency scenarios through one session or coordinating them with fragile sleeps.
• Saying an index makes every query faster without considering selectivity and write cost.
• Forgetting that EXPLAIN ANALYZE executes the statement it analyzes.
• Testing SQL injection only with strings while ignoring parameter binding in the data-access code.
• Using production customer data in an interview demo or test fixture without authorization and masking.

Keep Practicing

Turn this reference into timed practice. Open the Database Testing QA Battle track for SQL, constraints, ETL, migration, and transaction challenges. Use /interview-prep to answer questions aloud, then run a role-specific simulation from the mock interview dashboard.

Build depth with these verified QAJobFit hubs and tutorials:

• Top SQL interview questions and answers for a shorter high-frequency review.
• SQL interview questions for testers for tester-focused query reasoning.
• SQL coding interview questions for testers for hands-on query drills.
• SQL joins for testers for anti-joins, cardinality, and reconciliation.
• SQL GROUP BY and HAVING for testers for grain and aggregate checks.
• SQL subqueries for testers for membership, absence, and correlation.
• SQL window functions for testers for ranking, history, and deduplication.
• SQL for test-data setup and teardown for repeatable fixture design.
• Testing database constraints for negative writes and transaction behavior.
• Validating data integrity with SQL for production-grade data-quality checks.
• Writing SQL to validate ETL for source-to-target and warehouse scenarios.

Use a seven-day loop: answer ten questions without notes, run three queries against an adversarial fixture, and explain one trade-off aloud each day. On the final day, simulate a 45-minute round that mixes a join, one aggregate, one window function, and one database-testing scenario. Review the recording for assumptions you failed to state, not just syntax errors.

Conclusion

These SQL interview questions for QA cover the complete path from accurate retrieval to safe data validation. The durable skill is not memorizing sixty-six finished queries. It is translating a product rule into a precise result grain, challenging that result with nulls and duplicates, and producing evidence another engineer can act on.

Start with the sections closest to the target job, but practice across joins, aggregation, data setup, and transactions because interviewers combine them in scenarios. When you can predict the output before execution and explain what the query does not prove, you are ready to handle an unfamiliar schema under interview pressure.

Interview Questions and Answers

Frequently Asked Questions

Related Guides

• SQL Interview Questions for QA and Testers (2026)
• Agile and Scrum Interview Questions for QA Engineers (2026)
• gRPC Testing Interview Questions for QA Engineers (2026)
• LLM Red Teaming Interview Questions for QA Engineers (2026)
• MCP Testing Interview Questions for QA Engineers (2026)
• MockServer Interview Questions for QA Engineers (2026)