Top 30 SQL Interview Questions and Answers (2026)

TL;DR

The top SQL interview questions test result-set reasoning more than syntax recall. Master filtering, joins, aggregation, subqueries, CTEs, window functions, transactions, constraints, and indexing, then explain how each concept supports reliable product and data testing.

Key Takeaways

• Explain the result set, duplicate behavior, NULL behavior, and performance tradeoffs of every query you write.
• Practice joins and aggregation against small data sets where you can predict every output row by hand.
• Use window functions when rows must retain detail while also receiving ranks, running totals, or group comparisons.
• Treat constraints, transactions, isolation, and indexes as testable database behavior, not just definitions to memorize.
• QA candidates should connect SQL answers to reconciliation, orphan detection, duplicate detection, and state-transition validation.
• Read execution plans and improve access patterns before suggesting an index for every slow query.
• State the SQL dialect when syntax differs, then write the smallest correct query and verify edge cases.

The top SQL interview questions test whether you can turn a business rule into a correct, explainable result set. For QA and SDET roles, that also means using SQL to expose duplicates, missing relationships, incorrect totals, invalid state transitions, and data that disagrees across system boundaries.

This guide gives you a compact practice schema, runnable PostgreSQL-compatible examples, and 30 model answers. The core SQL is standard where practical. When behavior varies by database, say which dialect you are using instead of pretending all engines are identical.

TL;DR

A strong interview response follows four moves: clarify the expected grain, write the query, predict edge cases, and explain how you would verify the result.

1. Build a Small Schema Before Solving SQL Questions

A shared schema makes interview reasoning concrete. The following PostgreSQL script creates customers and orders with primary keys, a foreign key, a uniqueness rule, and enough variation for joins, NULL handling, and aggregation. It runs as written in PostgreSQL. Other relational databases may require small changes to identity syntax or timestamp literals.

CREATE TABLE customers (
  customer_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  email TEXT NOT NULL UNIQUE,
  region TEXT,
  created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

CREATE TABLE orders (
  order_id BIGINT GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
  customer_id BIGINT NOT NULL REFERENCES customers(customer_id),
  status TEXT NOT NULL CHECK (status IN ('PENDING', 'PAID', 'CANCELLED')),
  total_amount NUMERIC(12, 2) NOT NULL CHECK (total_amount >= 0),
  created_at TIMESTAMPTZ NOT NULL DEFAULT CURRENT_TIMESTAMP
);

INSERT INTO customers (email, region) VALUES
  ('ada@example.test', 'US'),
  ('lin@example.test', 'CA'),
  ('sam@example.test', NULL);

INSERT INTO orders (customer_id, status, total_amount, created_at) VALUES
  (1, 'PAID', 120.00, '2026-07-01 10:00:00+00'),
  (1, 'CANCELLED', 40.00, '2026-07-02 10:00:00+00'),
  (2, 'PAID', 75.50, '2026-07-03 10:00:00+00');

Before running a query, name its grain. For example, one row per customer differs from one row per order. Many interview mistakes come from silently changing the grain after a join.

2. Master SELECT, Predicates, and SQL NULL Semantics

SQL uses three-valued logic: a comparison can be true, false, or unknown. region = NULL never matches because equality with NULL is unknown. Use IS NULL or IS NOT NULL. This behavior matters when a requirement says that an optional value is missing.

SELECT customer_id, email
FROM customers
WHERE region IS NULL;

Operator precedence also matters. AND binds more tightly than OR, but explicit parentheses make intent reviewable. For a paid order above 100 or any cancelled order, write the rule directly:

SELECT order_id, status, total_amount
FROM orders
WHERE (status = 'PAID' AND total_amount > 100)
   OR status = 'CANCELLED';

Do not use SELECT * in a production validation merely because it is convenient. Selecting named columns documents the contract, avoids moving unnecessary data, and prevents a schema addition from silently changing consumers. In an interview, mention parameterized queries for application input. String concatenation is both a security risk and a source of quoting defects.

For more API-to-database examples, pair these checks with the API test engineer interview guide.

3. Reason About Joins Through Cardinality

A join is not only syntax. It describes how rows relate. An inner join returns matching combinations. A left join retains every row on the left and supplies NULL for missing right-side values. A full outer join retains unmatched rows from both sides when the database supports it. A cross join forms every combination and should be intentional.

To find customers with no orders, keep customers on the left and check for a missing right-side key:

SELECT c.customer_id, c.email
FROM customers AS c
LEFT JOIN orders AS o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

The placement of a filter can change outer-join behavior. A right-table predicate in WHERE may remove NULL-extended rows and make the result behave like an inner join. Put a condition in ON when it defines which right-side rows may match while all left rows must remain.

Always ask whether the relationship is one-to-one, one-to-many, or many-to-many. If one customer has three orders, the customer columns repeat three times. That is correct at order grain, but it can inflate a customer count or a sum from the customer table. Interviewers value candidates who predict that multiplication before adding DISTINCT as a patch.

4. Aggregate at the Correct Grain

COUNT(*) counts rows, while COUNT(column) counts non-NULL values in that column. COUNT(DISTINCT customer_id) counts unique non-NULL customer identifiers. Choose based on the question, not habit.

This query returns one row per customer and preserves customers with zero paid orders:

SELECT
  c.customer_id,
  c.email,
  COUNT(o.order_id) AS paid_order_count,
  COALESCE(SUM(o.total_amount), 0) AS paid_total
FROM customers AS c
LEFT JOIN orders AS o
  ON o.customer_id = c.customer_id
 AND o.status = 'PAID'
GROUP BY c.customer_id, c.email
ORDER BY c.customer_id;

WHERE filters input rows before grouping. HAVING filters groups after aggregation. If you want customers whose paid total exceeds 100, add HAVING COALESCE(SUM(o.total_amount), 0) > 100.

For money, preserve decimal types. Binary floating-point can introduce representation differences that are inappropriate for exact currency rules. QA reconciliation should also define whether totals are gross, net, settled, refunded, or merely authorized. A numerically correct query at the wrong business state is still a bad test.

5. Use Subqueries and CTEs to Make Intent Visible

A subquery can return a scalar, a set, or a derived table. A correlated subquery refers to the current row of the outer query. EXISTS is a natural choice when the requirement is about existence, because it expresses a Boolean question without multiplying rows.

SELECT c.customer_id, c.email
FROM customers AS c
WHERE EXISTS (
  SELECT 1
  FROM orders AS o
  WHERE o.customer_id = c.customer_id
    AND o.status = 'CANCELLED'
);

A common table expression, introduced with WITH, names an intermediate result. It can make multi-stage validation easier to review, though it is not automatically faster than a subquery. Modern optimizers may inline or materialize it depending on the engine, query, and explicit options.

WITH paid_by_customer AS (
  SELECT customer_id, SUM(total_amount) AS paid_total
  FROM orders
  WHERE status = 'PAID'
  GROUP BY customer_id
)
SELECT c.email, p.paid_total
FROM paid_by_customer AS p
JOIN customers AS c ON c.customer_id = p.customer_id
WHERE p.paid_total >= 100;

Explain readability and execution behavior separately. A clean query is easier to validate, but only an execution plan and measurements can establish performance.

6. Apply Window Functions Without Collapsing Rows

Window functions calculate across related rows while retaining each source row. They use OVER, usually with PARTITION BY and ORDER BY. This makes them ideal for latest-event selection, ranking, running totals, and comparisons with a previous value.

SELECT
  order_id,
  customer_id,
  created_at,
  total_amount,
  ROW_NUMBER() OVER (
    PARTITION BY customer_id
    ORDER BY created_at DESC, order_id DESC
  ) AS recency_rank,
  SUM(total_amount) OVER (
    PARTITION BY customer_id
    ORDER BY created_at, order_id
    ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW
  ) AS running_total
FROM orders
WHERE status = 'PAID';

The second sort key makes ordering deterministic when two orders share a timestamp. The explicit ROWS frame states exactly which rows contribute to the running total. Defaults vary by function and can surprise candidates when peer rows have the same ordering value.

Use ROW_NUMBER for exactly one sequence number per row. Use RANK when ties should share rank and leave gaps. Use DENSE_RANK when ties share rank without gaps. These semantics are more important than memorizing function names.

7. Test Constraints, Transactions, and Concurrency

Constraints are executable data rules. Primary keys enforce row identity, unique constraints prevent duplicate candidate keys, foreign keys protect relationships, NOT NULL protects required values, and check constraints restrict allowed values. A good QA strategy tests rejection paths as well as valid inserts.

Transactions group changes into an atomic unit. ACID is a useful model: atomicity means all or none, consistency means declared invariants remain valid, isolation controls interference among concurrent transactions, and durability means committed data survives the promised failure model. These are properties to test, not slogans.

Concurrency questions require a schedule. Name transaction A and transaction B, their isolation levels, the interleaving of reads and writes, and the expected outcome. Dirty reads, non-repeatable reads, phantoms, lost updates, and write skew are different anomalies. Database engines also differ in how they implement isolation, so verify actual documentation and behavior.

For a data migration, compare row counts only as a first check. Validate keys, aggregates, referential integrity, transformations, rejected rows, restart behavior, and reconciliation after cutover. The data migration testing guide expands this into a complete strategy.

8. Diagnose SQL Performance With Evidence

An index is a data structure that can reduce the work needed to locate or order rows. It also consumes storage and adds maintenance work to inserts, updates, and deletes. The best index matches real predicates, joins, ordering, and selectivity. An index on every column is not a performance plan.

Start with the execution plan. In PostgreSQL, EXPLAIN shows the planned operations, while EXPLAIN ANALYZE executes the statement and reports actual timing and row information. Use the latter safely because data-changing statements really execute unless contained and rolled back.

CREATE INDEX idx_orders_customer_status_created
  ON orders (customer_id, status, created_at DESC);

EXPLAIN (ANALYZE, BUFFERS)
SELECT order_id, created_at, total_amount
FROM orders
WHERE customer_id = 1
  AND status = 'PAID'
ORDER BY created_at DESC;

Compare estimated rows with actual rows, inspect scans and joins, and look for sorts, spills, or repeated work. Test at representative volume and distribution. A tiny local table may correctly use a sequential scan because reading it is cheaper than traversing an index. Keep performance claims contextual rather than memorizing a rule such as indexes always make SELECT faster.

9. Top SQL Interview Questions for QA Data Validation

SQL is most valuable in testing when it checks a business invariant independent of the UI. Examples include one active subscription per account, every shipped order has a shipment, a ledger balances to zero, and no state transition skips an approval. Convert each rule into a query that should return zero violating rows.

SELECT customer_id, COUNT(*) AS active_count
FROM subscriptions
WHERE status = 'ACTIVE'
GROUP BY customer_id
HAVING COUNT(*) > 1;

That query assumes a subscriptions(customer_id, status) table and returns evidence rather than a pass count. In automation, fail when any row is returned and attach a bounded sample to the report. Avoid exposing personal data or secrets in logs.

Database validation should respect system boundaries. If an API is eventually consistent, immediate database equality may be the wrong oracle. Poll a documented observable state with a deadline, or validate the event and downstream projection separately. The JSON response schema validation guide helps separate API contract checks from persistence checks.

Clean up test data through public APIs when lifecycle behavior matters. Direct database cleanup is faster but can bypass audit, events, and cascades, leaving an unrealistic state. Make the tradeoff explicit.

10. How to Answer Top SQL Interview Questions

For top SQL interview questions, first restate the requested output grain and clarify ambiguous terms. Top customer might mean revenue, order count, margin, or settled revenue within a time zone. State your interpretation before writing.

Then build the query in layers. Start with the base rows and filters, add joins, verify cardinality, aggregate, and only then add presentation ordering or limits. Use meaningful aliases. Talk through NULLs, ties, duplicates, empty sets, time boundaries, and data types. If asked to optimize, request or infer table sizes, distributions, existing indexes, and the execution plan.

A useful verbal template is: The output is one row per X. I filter Y before joining to avoid Z. This left join preserves A. I aggregate B at that grain. For equal values, this secondary sort makes the result deterministic. That explanation shows control of semantics.

Finally, test your own query. Name a normal case, no-match case, duplicate relationship, NULL case, tie, and boundary timestamp. Candidates who can challenge their solution often outperform candidates who type faster. For broader practice, use the scenario-based Java automation questions to rehearse the same evidence-first thinking.

Interview Questions and Answers

These 30 model answers cover the top SQL interview questions for QA, SDET, and data-focused interviews. Adapt the dialect and depth to the role.

Q1: What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping and aggregation. HAVING filters groups after GROUP BY, so it can use aggregate conditions such as COUNT(*) > 1. Put non-aggregate filters in WHERE when possible to reduce the input set and state the intended semantics clearly.

Q2: What is the difference between INNER JOIN and LEFT JOIN?

An inner join returns matching row combinations only. A left join retains every left-side row and fills right-side columns with NULL when no match exists. A filter on the right table in WHERE can remove those NULL rows, so predicate placement matters.

Q3: How do you find duplicate values?

Group by the candidate key and keep groups with more than one row: SELECT email, COUNT(*) FROM customers GROUP BY email HAVING COUNT(*) > 1. Define whether NULL values count as duplicates and whether case or whitespace should be normalized. Return identifiers for investigation when the interview moves from detection to cleanup.

Q4: How do you find the second-highest salary?

Clarify whether ties share a position. For the second distinct salary, rank distinct values with DENSE_RANK() OVER (ORDER BY salary DESC) and filter for rank 2. If the interviewer wants the second row even when salaries tie, ROW_NUMBER represents a different rule and needs a deterministic tie-breaker.

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

UNION ALL concatenates compatible result sets and preserves duplicates. UNION removes duplicate rows, which requires additional work and can hide meaningful repetition. Use UNION ALL unless the business rule explicitly requires set deduplication.

Q6: What does GROUP BY do?

GROUP BY partitions input rows by equal grouping expressions and produces one output group per distinct combination. Aggregate functions then summarize each group. Every selected expression must either belong to the grouping rule or be aggregated, subject to dialect-specific functional dependency rules.

Q7: COUNT(*) versus COUNT(column), what changes?

COUNT(*) counts input rows. COUNT(column) counts rows where that expression is not NULL. This difference is useful in outer joins, where COUNT(right_table.id) can correctly report zero matches while COUNT(*) still counts the preserved left row.

Q8: Why does column = NULL not work?

NULL represents missing or unknown information, so equality with NULL evaluates to unknown rather than true. Use IS NULL or IS NOT NULL. Remember that NOT IN can also produce surprising unknown results if its set contains NULL, so NOT EXISTS is often safer for anti-joins.

Q9: What is a primary key?

A primary key is the chosen constraint that uniquely identifies each row and disallows NULL. A table has one primary key constraint, which may contain multiple columns. Other unique candidate keys can be enforced with UNIQUE constraints.

Q10: What is a foreign key?

A foreign key requires referencing values to match a candidate key in the referenced table, subject to NULL and action rules. It protects referential integrity across inserts, updates, and deletes. Tests should cover accepted references, rejected references, and configured cascade or restriction behavior.

Q11: What is normalization?

Normalization organizes relations to reduce undesirable redundancy and update anomalies. In interviews, explain the functional dependency and risk instead of only naming normal forms. Denormalization can be deliberate for read patterns, but it introduces synchronization and reconciliation responsibilities.

Q12: What is a CTE?

A common table expression is a named query introduced by WITH and scoped to one statement. It improves readability for staged logic and supports recursive queries. It is not inherently faster, and materialization behavior depends on the database and query.

Q13: What is a correlated subquery?

A correlated subquery references a value from the outer query and is logically evaluated for each outer row. Optimizers may transform it, but its result depends on that outer row. EXISTS with a correlation is a clear way to express relationship existence.

Q14: EXISTS versus IN, which should you use?

Use the form that best expresses the rule and verify the plan. EXISTS naturally answers whether at least one related row exists and handles correlated checks well. NOT EXISTS also avoids the NULL trap that can make NOT IN return no rows when its subquery contains NULL.

Q15: What is a window function?

A window function calculates over a related set of rows without collapsing them into one group row. The OVER clause defines partitions, ordering, and sometimes a frame. Typical uses are ranks, running totals, lag comparisons, and selecting the latest record per entity.

Q16: ROW_NUMBER, RANK, and DENSE_RANK, how do they differ?

ROW_NUMBER assigns a unique sequence even for ties. RANK gives peers the same rank and leaves gaps after ties. DENSE_RANK gives peers the same rank without gaps, so choose according to the product rule.

Q17: How do you select the latest row per customer?

Assign ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY created_at DESC, id DESC) and keep row 1. The stable identifier breaks timestamp ties and makes the result deterministic. A database-specific alternative may exist, but the window approach is portable and explicit.

Q18: What is a transaction?

A transaction is a unit of work committed or rolled back as a whole. It creates a boundary for atomicity and interacts with the database isolation model. Tests should verify both the successful commit and failure rollback, including side effects outside the database.

Q19: Explain transaction isolation levels.

Isolation levels define which concurrent effects a transaction may observe and prevent. Standard names include Read Uncommitted, Read Committed, Repeatable Read, and Serializable, but actual engine guarantees differ. Describe a concrete two-transaction schedule and confirm the selected database behavior.

Q20: What is a deadlock?

A deadlock occurs when transactions wait in a cycle for resources held by one another. Databases detect the cycle and abort a participant so work can continue. Applications should keep transactions focused, access resources consistently where possible, and retry only when the operation is safe to repeat.

Q21: What is an index?

An index is an auxiliary structure that supports faster access for suitable queries. It costs storage and write maintenance, and it may not help low-selectivity predicates or small tables. Propose an index from a measured query pattern and confirm it with an execution plan.

Q22: What is a composite index?

A composite index contains multiple key columns in a defined order. Its usefulness depends on predicates, ordering, and the database's index access rules, not simply on whether a queried column appears somewhere in it. Place columns based on actual access patterns and validate with representative data.

Q23: Why might a database ignore an index?

The optimizer may estimate that a sequential scan costs less, especially for a small table or a predicate returning many rows. Stale statistics, type conversions, expressions, collation, and mismatched column order can also matter. Inspect the plan and estimates instead of forcing an index immediately.

Q24: DELETE, TRUNCATE, and DROP, what is the difference?

DELETE removes qualifying rows and can use a WHERE clause. TRUNCATE removes all rows through a database-specific operation with different locking, logging, identity, and transactional behavior. DROP removes the database object itself, so always state the engine before making rollback or trigger claims.

Q25: What is a view?

A view stores a query definition and exposes its result like a relation. It can simplify access and provide a stable interface, but it does not generally store result rows. A materialized view does store results and needs an engine-specific refresh strategy.

Q26: How do you prevent SQL injection?

Use parameterized statements through the database driver and allow-list dynamic identifiers that cannot be bound as values. Do not concatenate untrusted text into SQL. Least-privilege database accounts and secure error handling reduce impact but do not replace parameterization.

Q27: How would you validate an API response against the database?

First define which system is authoritative and whether consistency is immediate or eventual. Compare stable business fields with correct type, time zone, and rounding transformations, while excluding generated or intentionally masked values. Validate side effects and relationships, then clean up through an appropriate lifecycle path.

Q28: How do you test a data migration?

Validate schema mapping, row eligibility, transformed values, keys, relationships, aggregates, rejected records, and restart behavior. Reconcile source and target using business-aware checks rather than counts alone. Test rollback or forward-fix procedures and monitor post-cutover writes.

Q29: How do you optimize a slow query?

Capture the query, parameters, plan, data volume, and timing under representative conditions. Reduce unnecessary work, correct joins and predicates, then consider indexes, statistics, or schema changes. Measure again and check that write cost and other important queries did not regress.

Q30: How do you test your SQL answer during an interview?

Create the smallest data set that distinguishes correct from incorrect logic. Include no matches, one match, multiple matches, NULL, duplicates, ties, and boundary values. Predict the output before executing, then explain any discrepancy rather than editing blindly.

Common Mistakes

• Writing a query before defining one row of expected output.
• Using DISTINCT to hide row multiplication without finding the incorrect join grain.
• Comparing NULL with = or overlooking NULL inside NOT IN.
• Turning a left join into an inner join through a right-side filter in WHERE.
• Counting * after an outer join when the requirement is to count matching child rows.
• Omitting a tie-breaker from LIMIT, ROW_NUMBER, or latest-record logic.
• Claiming a CTE, subquery, or index is always faster without examining a plan.
• Concatenating input into SQL instead of using bound parameters.
• Treating row-count equality as complete migration or reconciliation evidence.
• Quoting ACID definitions without describing a concurrent schedule or failure test.

Conclusion

The top SQL interview questions reward precise reasoning about data grain, relationships, NULL, aggregation, concurrency, and access paths. Learn the definitions, but practice by predicting rows, executing queries, and explaining the business invariant each query protects.

Run the sample schema, change it to create duplicates and missing relationships, and solve the 30 questions without notes. Your goal is not clever SQL. It is the smallest correct query, a clear explanation, and evidence that you know how it can fail.

Interview Questions and Answers

Frequently Asked Questions

Related Guides

• Top 30 Agile Interview Questions and Answers (2026)
• Top 30 API testing Interview Questions and Answers (2026)
• Top 30 Appium Interview Questions and Answers (2026)
• Top 30 Automation Testing Interview Questions and Answers (2026)
• Top 30 Cucumber Interview Questions and Answers (2026)
• Top 30 DevOps for QA Interview Questions and Answers (2026)