SQL Interview Questions for QA and Testers (2026)

TL;DR

SQL interviews for QA test whether you can turn a business rule into a reliable data oracle. Expect filtering and null questions, joins and cardinality, GROUP BY and HAVING, EXISTS and subqueries, window functions, constraints and transactions, plus hands-on queries for duplicates, missing records, latest rows, and reconciliation. State assumptions, write clear SQL, predict edge cases, and explain what the result proves and does not prove.

Key Takeaways

• Answer SQL questions by stating the business grain, eligible population, expected result, and edge cases before syntax.
• Prepare filtering, null semantics, joins, aggregation, subqueries, window functions, transactions, constraints, and query plans.
• Use small deterministic fixtures and predict exact identifiers, not only row counts.
• Explain how inner joins can hide missing data and how one-to-many joins can inflate aggregates.
• Treat direct database access as bounded evidence with read-only permissions and safe handling of sensitive data.
• For every coding answer, discuss duplicates, nulls, ties, tenant scope, time boundaries, and database dialect.
• A strong senior answer connects SQL syntax to data integrity, failure diagnosis, and trustworthy release decisions.

These sql interview questions for testers cover what QA engineers and SDETs are actually expected to do with relational data in 2026: validate backend state, create and clean test fixtures safely, investigate defects, reconcile APIs with persistence, and explain why a query is trustworthy. The goal is not to memorize fifty isolated keywords. It is to convert a requirement into an accurate, reviewable data check.

Interviewers usually reward candidates who clarify grain and scope before typing. A concise answer such as, "I will treat one row as one invoice, preserve every eligible subscription, and return only mismatches" demonstrates more judgment than a clever query with an undefined population. Use this guide to practice both syntax and reasoning.

TL;DR

1. sql interview questions for testers: Use a Four-Part Answer Model

For a query prompt, begin with four points: business rule, result grain, eligible population, and expected exceptions. Suppose the prompt is, "Find customers with duplicate active email addresses." Clarify whether uniqueness is global or tenant-scoped, how email is normalized, which lifecycle states participate, and whether null is allowed. Then say one output row represents one duplicate business key.

Next, write the simplest correct query. Explain why each clause exists, then test it mentally with one unique row, two duplicates, the same email in another tenant, a null email, and an inactive account. This progression demonstrates test design rather than syntax recall.

When asked a conceptual question, use definition, contrast, QA application, and risk. For example: a left join preserves all left rows, unlike an inner join; I use it to find missing related data; a right-table filter in WHERE can accidentally remove those missing rows. The structure keeps answers compact and credible.

State database assumptions. Standard SQL concepts transfer, but date functions, case-insensitive matching, null-safe equality, QUALIFY, full outer joins, execution plans, and transaction behavior vary by engine. If the interviewer names PostgreSQL, write PostgreSQL. If no dialect is given, use broadly portable syntax and identify any exception.

Finally, distinguish evidence from proof. A zero-row exception query means no problem was observed in that snapshot with those permissions and rules. It does not prove replication freshness, query correctness, or future constraint enforcement. Senior candidates say what the check covers and what still needs another layer.

2. Master SELECT, Filtering, NULL, and Boundaries

SELECT chooses expressions, FROM establishes row sources, WHERE filters rows, and ORDER BY controls display order. SQL does not guarantee row order without ORDER BY. DISTINCT removes duplicate projected rows, but it should not be used to hide an unknown join problem.

SQL uses three-valued logic: true, false, and unknown. Comparisons with null are usually unknown, so use IS NULL and IS NOT NULL, not = NULL. COUNT(*) counts rows, while COUNT(nullable_column) counts non-null values. COALESCE substitutes the first non-null expression, but converting missing values to zero is correct only when the requirement treats them as equivalent.

For ranges, prefer half-open time windows: created_at >= start_time AND created_at < end_time. This avoids guessing the final fractional second and composes across adjacent periods without overlap. Confirm timestamp type and time zone. A date displayed in the UI may map to a UTC interval based on the user's zone.

LIKE pattern rules and case sensitivity vary by collation and database. % matches any sequence and _ matches one character. Escape literal wildcard characters according to the driver and dialect. Never build SQL by concatenating a search string. Use parameters for values and an allowlist for identifiers that cannot be parameterized.

Interview test cases should include empty strings, whitespace, null, maximum length, Unicode where supported, case variants, leading zeros, negative and zero numbers, exact boundaries, and values just outside the range. Tie each case to schema and product rules rather than generating invalid data with no purpose.

3. Explain Keys, Constraints, and Data Integrity

A primary key uniquely identifies a row and is non-null. A unique constraint enforces uniqueness according to the database's null semantics, which can vary in important details. A foreign key requires a referenced parent value or null when the child column permits it. NOT NULL, CHECK, default values, and data types add more integrity rules.

Candidate keys are minimal attribute sets that could uniquely identify a row. The chosen primary key may be a surrogate ID, while a business key such as (tenant_id, normalized_email) still needs an appropriate unique rule. QA should test both generated identity and business uniqueness.

Constraints are defense in depth, not a replacement for application validation. The API should return a safe, useful error, while the database prevents races or alternate paths from violating integrity. Test sequential duplicates and concurrent attempts. Two requests can both pass an application pre-check before one reaches a unique constraint. The application must translate that outcome consistently.

Foreign keys can define delete and update actions such as restrict, cascade, or set null. Do not assume cascade is desirable. Test parent deletion through approved interfaces, check child state and audit evidence, and verify that soft-delete models remain consistent. A soft-deleted parent can still satisfy a physical foreign key while violating a business relationship.

A check constraint can enforce row-local rules, such as amount >= 0, but cross-row or cross-table invariants require other mechanisms. Interview answers should separate what the schema guarantees from what a query merely observes. Use testing database constraints for a deeper integrity checklist.

4. Prepare Joins and Cardinality Questions

An inner join returns matching row combinations. A left join preserves every left row and null-extends the right side when no match exists. A full outer join preserves unmatched rows on both sides and is useful for source-to-target reconciliation where supported. A cross join creates every combination. A self join assigns two roles to one table.

Cardinality is the critical interview concept. Joining one order to three items legitimately returns three rows. Joining the same order to three items and two payments returns six combinations. Summing payment amount now triples it, and summing item amount doubles it. The fix is normally to aggregate each many-side to order grain before joining, not to sprinkle DISTINCT.

To find missing data, put the control population on the left. For every active subscription requiring an invoice, start from eligible subscriptions, left join acceptable invoices, and check a non-nullable invoice key for null. Put invoice eligibility in ON so subscriptions with no invoice survive. A condition such as WHERE invoice.status = 'issued' would remove null-extended rows and defeat the completeness check.

Join on the complete identity. Multi-tenant business keys often require tenant plus local ID. Migration joins might also require version, effective date, locale, or source-system ID. Null keys do not match with ordinary equality. Decide whether missing identity is an exception rather than forcing null-safe matching.

For hands-on examples, study SQL joins for testers and practice predicting output row counts before running each query. Interviewers often care more about that prediction than the join keyword itself.

5. Prepare GROUP BY, HAVING, and Aggregate Questions

GROUP BY changes detail into one row per grouping key. Aggregate functions calculate facts inside each group. WHERE filters eligible detail rows before grouping, while HAVING filters groups after aggregate values exist. A query for active emails appearing more than once uses active status in WHERE and HAVING COUNT(*) > 1 for the duplicate rule.

State the result grain first. Grouping by customer produces one row per customer. Adding currency produces one row per customer and currency. The second is required if monetary totals cannot be combined across currencies. Adding unnecessary columns can split a defect into groups that no longer cross the threshold.

Know aggregate null behavior. COUNT(*) counts rows. COUNT(column) and common numeric aggregates ignore null values. An aggregate such as SUM over no eligible values can yield null. Use COALESCE only after defining whether no rows and numeric zero mean the same thing.

Conditional aggregation uses patterns such as SUM(CASE WHEN status = 'failed' THEN 1 ELSE 0 END) to calculate several facts from one population. It is useful for state distributions and release summaries. Define the denominator for rates. Filtering failures before counting all executions makes every retained row look failed.

Grand totals can hide offsetting defects. Reconcile at the lowest stable key, such as invoice and currency, then summarize exceptions. SQL group by and having for testers includes duplicate, null, conditional aggregation, and reconciliation drills.

6. Prepare Subqueries, EXISTS, and CTE Questions

A scalar subquery must return at most one value. IN compares with a one-column set. EXISTS checks whether an eligible related row exists. A correlated subquery references the current outer row. A derived table is a subquery in FROM, and a common table expression names a statement-level result with WITH.

For presence or absence, EXISTS and NOT EXISTS clearly express relationship rules without duplicating outer rows. The correlation must include complete scope. Forgetting inner.customer_id = outer.customer_id makes the question global: if one match exists anywhere, every outer row can pass.

NOT IN is risky when the inner result can include null. Comparisons may become unknown, causing no row to qualify. NOT EXISTS is usually the safer anti-relationship pattern. If using NOT IN, prove the inner expression cannot be null through schema and query rules.

A scalar subquery returning multiple rows should fail. Do not repair it automatically with an unordered LIMIT 1. Investigate duplicate active configuration, incomplete keys, or a wrong assumption. If one latest value is required, specify authoritative ordering and a deterministic tie-breaker.

A CTE improves readability when it names populations and stages. It is not inherently cached or faster. Optimizer behavior differs by database and version. Choose clear semantics, then inspect the plan. SQL subqueries for testers provides runnable empty-set, tie, and correlation cases.

7. Prepare Window Function Questions

Window functions calculate across peer rows while preserving detail. PARTITION BY defines independent populations, window ORDER BY defines sequence, and a frame limits rows used by ordered aggregate windows. ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and aggregate functions with OVER are common interview topics.

To retrieve one latest result per test case, use ROW_NUMBER() partitioned by case identity and ordered by authoritative event time or sequence descending, plus a valid tie-breaker. Filter row number 1 in an outer query because WHERE logically runs before window calculations. Some databases offer QUALIFY; do not assume it exists in PostgreSQL.

ROW_NUMBER always assigns unique positions. RANK gives ties the same rank and leaves gaps, while DENSE_RANK leaves no gaps. Select according to the requirement rather than preference. If duplicate top rows should be reported as a data defect, forcing one row with ROW_NUMBER can hide it.

LAG and LEAD compare adjacent observations. Clarify whether previous means previous stored row, previous build, previous successful result, or previous calendar period. Missing builds and filters can change adjacency.

For cumulative or rolling aggregates, write an explicit ROWS frame when physical-row behavior is intended. A three-row window is not automatically three days. Review SQL window functions for testers for partition, tie, and frame exercises.

8. Understand Transactions, Isolation, and Safe Test Data

A transaction groups operations into an atomic unit according to database guarantees. COMMIT makes changes durable and ROLLBACK abandons uncommitted changes. ACID summarizes atomicity, consistency, isolation, and durability, but actual behavior depends on the database, isolation level, statement, and failure mode.

Isolation anomalies are valuable senior interview topics. A dirty read observes uncommitted data where permitted. A nonrepeatable read sees a row change between reads. A phantom changes the matching row set. Lost updates occur when concurrent writers overwrite one another. Serializable isolation aims to make committed outcomes equivalent to a serial order, but applications must handle serialization failures and retries correctly. Exact supported levels and behavior are database-specific.

Test concurrency with controlled parallel sessions, barriers, and independent connections. A single connection executing calls sequentially does not create a real race. Verify final state, returned errors, audit events, and side effects. For idempotent API operations, repeat and race the same idempotency key and payload, then test the same key with a different payload.

Test data setup should use owned identifiers and narrow cleanup. A rollback fixture is fast when all tested work shares the transaction and no external process must observe committed rows. It does not work for asynchronous workers on other connections. For those cases, create namespaced committed data and delete only that namespace through an approved cleanup path or expiry process.

Never run broad DELETE or UPDATE statements on shared data as part of interview-style experimentation. Use a disposable database, transaction, or read-only copy. Parameterize values and keep secrets and personal data out of logs.

9. Solve a Runnable QA SQL Exercise

Use this PostgreSQL fixture for timed practice. It models users, subscriptions, and invoices. One active subscription has no invoice, one invoice total is wrong, a user has two active subscriptions, and another user has none.

DROP TABLE IF EXISTS invoices;
DROP TABLE IF EXISTS subscriptions;
DROP TABLE IF EXISTS users;

CREATE TABLE users (
  user_id INTEGER PRIMARY KEY,
  tenant_id INTEGER NOT NULL,
  email VARCHAR(200) NOT NULL,
  status VARCHAR(20) NOT NULL
);

CREATE TABLE subscriptions (
  subscription_id INTEGER PRIMARY KEY,
  user_id INTEGER NOT NULL REFERENCES users(user_id),
  plan_code VARCHAR(30) NOT NULL,
  status VARCHAR(20) NOT NULL,
  monthly_price DECIMAL(10, 2) NOT NULL,
  started_at DATE NOT NULL
);

CREATE TABLE invoices (
  invoice_id INTEGER PRIMARY KEY,
  subscription_id INTEGER NOT NULL REFERENCES subscriptions(subscription_id),
  billing_month DATE NOT NULL,
  total_amount DECIMAL(10, 2) NOT NULL,
  status VARCHAR(20) NOT NULL,
  UNIQUE (subscription_id, billing_month)
);

INSERT INTO users VALUES
  (1, 10, 'ana@example.test', 'active'),
  (2, 10, 'ben@example.test', 'active'),
  (3, 20, 'cy@example.test', 'active'),
  (4, 20, 'dia@example.test', 'invited');

INSERT INTO subscriptions VALUES
  (101, 1, 'pro', 'active', 25.00, '2026-01-01'),
  (102, 2, 'basic', 'active', 10.00, '2026-02-01'),
  (103, 2, 'addon', 'active', 5.00, '2026-03-01'),
  (104, 3, 'pro', 'canceled', 25.00, '2026-01-01');

INSERT INTO invoices VALUES
  (1001, 101, '2026-07-01', 25.00, 'issued'),
  (1002, 102, '2026-07-01', 8.00, 'issued'),
  (1003, 104, '2026-07-01', 25.00, 'void');

Task one: find active subscriptions with no July 2026 invoice. Start with active subscriptions, left join July invoices in ON, then keep a null invoice key. Subscription 103 should return.

SELECT s.subscription_id, s.user_id, s.plan_code
FROM subscriptions AS s
LEFT JOIN invoices AS i
  ON i.subscription_id = s.subscription_id
 AND i.billing_month = DATE '2026-07-01'
WHERE s.status = 'active'
  AND i.invoice_id IS NULL
ORDER BY s.subscription_id;

Task two: find July issued invoices whose total differs from active subscription price. Invoice 1002 should return with expected 10.00 and observed 8.00.

SELECT
  i.invoice_id,
  s.subscription_id,
  s.monthly_price AS expected_amount,
  i.total_amount AS observed_amount
FROM invoices AS i
JOIN subscriptions AS s ON s.subscription_id = i.subscription_id
WHERE i.billing_month = DATE '2026-07-01'
  AND i.status = 'issued'
  AND s.status = 'active'
  AND i.total_amount <> s.monthly_price;

Task three: find users with more than one active subscription. User 2 should return with count 2. Predict exact output before executing each answer, then add null, duplicate, cross-tenant, canceled, and future-month cases.

10. sql interview questions for testers: A Seven-Day Preparation Plan

Day one: practice SELECT, aliases, CASE, nulls, strings, dates, and half-open ranges. Explain three-valued logic without jargon. Day two: draw relationship cardinalities and solve inner, left, anti-join, self-join, and reconciliation prompts. Predict row counts.

Day three: work through COUNT, SUM, AVG, MIN, MAX, GROUP BY, HAVING, conditional aggregation, duplicate keys, and money by currency. Day four: solve scalar, IN, EXISTS, NOT EXISTS, correlated, derived-table, and CTE problems. Test empty and multiple-row shapes.

Day five: practice ROW_NUMBER, rank variants, LAG, LEAD, partitioned averages, and explicit frames. Add ties and missing periods. Day six: review keys, constraints, normalization, transactions, isolation, idempotency, test-data setup, permissions, and cleanup. Use the target database's official documentation for dialect specifics.

Day seven: complete two 45-minute mock rounds. In each, spend a few minutes clarifying assumptions, write queries without autocomplete, hand-calculate a fixture, and explain performance and security. Review mistakes by category rather than memorizing the final query.

During the interview, narrate key choices but do not read every keystroke. If syntax is uncertain, state the relational approach and mark the dialect detail you would verify. Never fabricate a function. A correct standard pattern plus a clear portability note is stronger than confident invented syntax.

Finish every answer with validation: expected IDs, edge cases, and what another layer must test. That closing sentence turns a database answer into a QA answer.

Interview Questions and Answers

Q: What is the difference between WHERE and HAVING?

WHERE filters source rows before grouping. HAVING filters groups after aggregates are calculated. I put eligible lifecycle, tenant, and time conditions in WHERE, then use HAVING for rules such as COUNT(*) > 1.

Q: What is the difference between DELETE, TRUNCATE, and DROP?

DELETE removes qualifying rows and can use a predicate. TRUNCATE removes all rows through database-specific DDL-like behavior, and DROP removes the object itself. Transaction, identity reset, trigger, locking, and permission behavior varies by engine, so I verify the target documentation and never use these casually in shared QA data.

Q: How do you find duplicate records?

I define the full business key and eligible states, group by the key, and keep groups with HAVING COUNT(*) > 1. I include tenant and approved normalization rules. Then I retrieve detail IDs separately for diagnosis.

Q: How do you find records with no related row?

I use NOT EXISTS with a complete correlated predicate, or a left join and right_primary_key IS NULL. The expected population goes on the outside or left. Match eligibility stays in the subquery or ON clause so missing rows remain visible.

Q: Why can a join inflate a SUM?

A fact row repeats when it joins to several related rows, especially across two independent one-to-many relationships. I inspect cardinality and pre-aggregate each many-side to the target grain before joining. DISTINCT does not generally repair multiplied monetary values.

Q: What is the difference between UNION and UNION ALL?

UNION ALL concatenates compatible result sets and preserves duplicates. UNION applies duplicate elimination across the projected rows, which adds semantics and often work. I use UNION ALL when sources are intentionally distinct or duplicates are evidence, and UNION only when set deduplication is required.

Q: What is a correlated subquery?

It references the current row from an outer query. For example, NOT EXISTS can check whether each active subscription has an invoice for the target month. I test complete correlation, multiple matches, no match, and nullable keys.

Q: ROW_NUMBER or RANK, which should you use?

ROW_NUMBER assigns a unique sequence and is useful for selecting one deterministic record. RANK preserves equal positions and leaves gaps after ties. I choose according to whether the requirement concerns exact records or tied values and make ordering deterministic.

Q: How do you retrieve the second highest value?

I first clarify whether the question means the second row or second distinct value, and whether ties should return several records. DENSE_RANK ordered descending and filtered to rank 2 returns the second distinct value. A subquery over distinct values is another valid approach.

Q: What is an index?

An index is a database structure that can support faster access, ordering, or uniqueness at the cost of storage and write maintenance. Its usefulness depends on query predicates, selectivity, ordering, distribution, and the optimizer. I read the plan rather than claiming every filtered column needs an index.

Q: How do you test a database transaction?

I verify successful commit, rollback on each failure point, constraint errors, concurrent operations, retries, final state, and external side effects. I use separate connections and controlled synchronization for isolation scenarios. I do not infer atomicity from one API response.

Q: How do you validate API data with SQL?

I use the API contract as the business oracle and query an authorized database view for independent evidence when appropriate. I correlate stable identifiers, account for asynchronous consistency, compare exact fields and side effects, and avoid coupling every end-to-end test to implementation tables.

Q: How do you make SQL test automation safe?

I use least-privileged credentials, parameterized values, synthetic namespaced data, and narrow cleanup. Queries are bounded by tenant, run, or time window. Logs omit credentials and unnecessary personal data, and destructive operations require an approved disposable environment.

Q: How do you optimize a slow SQL validation query?

I confirm correctness and grain first, then examine the target database's execution plan with representative data. I reduce unnecessary columns and rows, inspect joins and cardinality estimates, verify statistics and appropriate indexes through review, and measure again. I do not rely on generic syntax folklore.

Common Mistakes

• Writing SQL before clarifying tenant, lifecycle, time window, currency, and result grain.
• Saying null equals empty string or comparing it with = NULL.
• Using an inner join for a completeness check and hiding missing records.
• Adding DISTINCT to suppress unexpected join multiplication.
• Putting a nullable-side filter in WHERE after a left join.
• Grouping by extra columns until a duplicate or threshold failure disappears.
• Using NOT IN without proving the subquery cannot return null.
• Selecting a latest row with no deterministic business tie-breaker.
• Claiming CTEs, EXISTS, or one join order are always faster.
• Testing concurrency through sequential calls on one connection.
• Checking only row count instead of exact expected identifiers and values.
• Running interview practice SQL against shared or sensitive data.

Conclusion

The strongest answers to sql interview questions for testers combine relational accuracy with QA judgment. Define population and grain, write a clear query, predict edge cases, and explain the evidence. Prepare joins, aggregation, subqueries, windows, constraints, transactions, plans, and safe data handling as connected tools rather than isolated trivia.

Run the subscription fixture without copying the answers, then change one assumption at a time: allow multiple invoice attempts, add currencies, introduce a tenant-local subscription number, and create two events at the same timestamp. Explaining how the query must change is the kind of reasoning that distinguishes an interview-ready QA engineer from someone who only memorized SQL patterns.

Interview Questions and Answers

Frequently Asked Questions

Related Guides

• SQL Interview Questions for QA and Testers
• MongoDB QA and SDET Interview Questions (2026)
• SQL Coding Interview Questions for Testers (2026)
• Top 30 DevOps for QA Interview Questions and Answers (2026)
• Top 30 SQL Interview Questions and Answers (2026)
• Accenture QA Engineer Interview Questions and Process (2026)