Interview question bank

SQL Interview Questions and Answers

54 SQL interview questions, each with a short answer you can say in the interview, a detailed explanation, a real-world example and an interview tip; 27 of them are marked as frequently asked. Freshers can start with the first section; experienced testers can jump to the harder questions.

SQL interview questions for freshers

Beginner3 Years Frequently asked

1. How do GROUP BY and HAVING differ, and when do you need HAVING instead of WHERE?

Answer: WHERE filters individual rows before grouping happens, while HAVING filters aggregated groups after GROUP BY has computed them, so you use HAVING when your condition depends on an aggregate like COUNT or SUM.

Detailed explanation, example and interview tip

Because aggregate functions don't exist until rows are grouped, you can't reference COUNT(*) or SUM(amount) in a WHERE clause — the database evaluates WHERE before grouping. HAVING runs after grouping, letting you filter on the aggregated result, such as customers with more than 5 orders. In test data validation, this combination is invaluable for finding duplicate records or anomalies, such as usernames that appear more than once when they should be unique.

sql
1-- Find duplicate emails that should be unique in the users table
2SELECT email, COUNT(*) AS occurrences
3FROM users
4GROUP BY email
5HAVING COUNT(*) > 1;

Real-world example

Running this HAVING query against a staging database after a data migration exposed 12 duplicate customer emails caused by a faulty dedup script.

Interview tip

Explain the execution order (WHERE before grouping, HAVING after) clearly — that logical order is exactly what trips people up under interview pressure.

Beginner3 Years Frequently asked

2. What is SELECT fundamentals, and why does it matter in SQL?

Answer: SELECT fundamentals is a core SQL concept used to make testing decisions more accurate, repeatable, and aligned with product risk.

Detailed explanation, example and interview tip

A strong explanation of SELECT fundamentals should cover its purpose, the problem it solves, when it is appropriate, and where it can mislead a team. In SQL, use it deliberately rather than as a checklist item: connect it to observable behavior, choose clear pass/fail evidence, and record assumptions so another engineer can reproduce the result.

Real-world example

During release planning, the team used SELECT fundamentals to focus validation on the most important behavior and to communicate residual risk before deployment.

Interview tip

Answer in four parts: definition, suitable use case, limitation, and a concrete project example.

Beginner5 Years Frequently asked

3. What is HAVING, and why does it matter in SQL?

Answer: HAVING is a core SQL concept used to make testing decisions more accurate, repeatable, and aligned with product risk.

Detailed explanation, example and interview tip

A strong explanation of HAVING should cover its purpose, the problem it solves, when it is appropriate, and where it can mislead a team. In SQL, use it deliberately rather than as a checklist item: connect it to observable behavior, choose clear pass/fail evidence, and record assumptions so another engineer can reproduce the result.

Real-world example

During release planning, the team used HAVING to focus validation on the most important behavior and to communicate residual risk before deployment.

Interview tip

Answer in four parts: definition, suitable use case, limitation, and a concrete project example.

Beginner7 Years Frequently asked

4. What is ROW_NUMBER, and why does it matter in SQL?

Answer: ROW_NUMBER is a core SQL concept used to make testing decisions more accurate, repeatable, and aligned with product risk.

Detailed explanation, example and interview tip

A strong explanation of ROW_NUMBER should cover its purpose, the problem it solves, when it is appropriate, and where it can mislead a team. In SQL, use it deliberately rather than as a checklist item: connect it to observable behavior, choose clear pass/fail evidence, and record assumptions so another engineer can reproduce the result.

Real-world example

During release planning, the team used ROW_NUMBER to focus validation on the most important behavior and to communicate residual risk before deployment.

Interview tip

Answer in four parts: definition, suitable use case, limitation, and a concrete project example.

Beginner10+ Years Frequently asked

5. What is deadlocks, and why does it matter in SQL?

Answer: deadlocks is a core SQL concept used to make testing decisions more accurate, repeatable, and aligned with product risk.

Detailed explanation, example and interview tip

A strong explanation of deadlocks should cover its purpose, the problem it solves, when it is appropriate, and where it can mislead a team. In SQL, use it deliberately rather than as a checklist item: connect it to observable behavior, choose clear pass/fail evidence, and record assumptions so another engineer can reproduce the result.

Real-world example

During release planning, the team used deadlocks to focus validation on the most important behavior and to communicate residual risk before deployment.

Interview tip

Answer in four parts: definition, suitable use case, limitation, and a concrete project example.

Beginner3 Years

6. How would you apply INNER JOIN in a production SQL project?

Answer: Apply INNER JOIN through an explicit, repeatable design: define the expected behavior, automate the highest-risk path, capture useful diagnostics, and verify the result against a stable oracle.

Detailed explanation, example and interview tip

In a production SQL project, start by identifying the risk that INNER JOIN controls and the evidence needed to prove the behavior. Keep setup, action, verification, and cleanup separate; parameterize environment-specific values; and make failures explain the business impact. Run the smallest relevant check on each change, broader coverage in the pipeline, and review flaky or slow results rather than hiding them with retries.

Real-world example

A team introduced a focused INNER JOIN check at the layer closest to the risk, attached diagnostics to failures, and used the result as a release signal instead of relying on manual confirmation.

Interview tip

Describe one trade-off, one failure mode, and one measurable outcome when discussing INNER JOIN.

Beginner3 Years

7. What is outer joins, and why does it matter in SQL?

Answer: outer joins is a core SQL concept used to make testing decisions more accurate, repeatable, and aligned with product risk.

Detailed explanation, example and interview tip

A strong explanation of outer joins should cover its purpose, the problem it solves, when it is appropriate, and where it can mislead a team. In SQL, use it deliberately rather than as a checklist item: connect it to observable behavior, choose clear pass/fail evidence, and record assumptions so another engineer can reproduce the result.

Real-world example

During release planning, the team used outer joins to focus validation on the most important behavior and to communicate residual risk before deployment.

Interview tip

Answer in four parts: definition, suitable use case, limitation, and a concrete project example.

Beginner3 Years

8. How would you apply GROUP BY in a production SQL project?

Answer: Apply GROUP BY through an explicit, repeatable design: define the expected behavior, automate the highest-risk path, capture useful diagnostics, and verify the result against a stable oracle.

Detailed explanation, example and interview tip

In a production SQL project, start by identifying the risk that GROUP BY controls and the evidence needed to prove the behavior. Keep setup, action, verification, and cleanup separate; parameterize environment-specific values; and make failures explain the business impact. Run the smallest relevant check on each change, broader coverage in the pipeline, and review flaky or slow results rather than hiding them with retries.

Real-world example

A team introduced a focused GROUP BY check at the layer closest to the risk, attached diagnostics to failures, and used the result as a release signal instead of relying on manual confirmation.

Interview tip

Describe one trade-off, one failure mode, and one measurable outcome when discussing GROUP BY.

Beginner5 Years

9. How would you apply subqueries in a production SQL project?

Answer: Apply subqueries through an explicit, repeatable design: define the expected behavior, automate the highest-risk path, capture useful diagnostics, and verify the result against a stable oracle.

Detailed explanation, example and interview tip

In a production SQL project, start by identifying the risk that subqueries controls and the evidence needed to prove the behavior. Keep setup, action, verification, and cleanup separate; parameterize environment-specific values; and make failures explain the business impact. Run the smallest relevant check on each change, broader coverage in the pipeline, and review flaky or slow results rather than hiding them with retries.

Real-world example

A team introduced a focused subqueries check at the layer closest to the risk, attached diagnostics to failures, and used the result as a release signal instead of relying on manual confirmation.

Interview tip

Describe one trade-off, one failure mode, and one measurable outcome when discussing subqueries.

Beginner5 Years

10. What is correlated subqueries, and why does it matter in SQL?

Answer: correlated subqueries is a core SQL concept used to make testing decisions more accurate, repeatable, and aligned with product risk.

Detailed explanation, example and interview tip

A strong explanation of correlated subqueries should cover its purpose, the problem it solves, when it is appropriate, and where it can mislead a team. In SQL, use it deliberately rather than as a checklist item: connect it to observable behavior, choose clear pass/fail evidence, and record assumptions so another engineer can reproduce the result.

Real-world example

During release planning, the team used correlated subqueries to focus validation on the most important behavior and to communicate residual risk before deployment.

Interview tip

Answer in four parts: definition, suitable use case, limitation, and a concrete project example.

Beginner5 Years

11. How would you apply window functions in a production SQL project?

Answer: Apply window functions through an explicit, repeatable design: define the expected behavior, automate the highest-risk path, capture useful diagnostics, and verify the result against a stable oracle.

Detailed explanation, example and interview tip

In a production SQL project, start by identifying the risk that window functions controls and the evidence needed to prove the behavior. Keep setup, action, verification, and cleanup separate; parameterize environment-specific values; and make failures explain the business impact. Run the smallest relevant check on each change, broader coverage in the pipeline, and review flaky or slow results rather than hiding them with retries.

Real-world example

A team introduced a focused window functions check at the layer closest to the risk, attached diagnostics to failures, and used the result as a release signal instead of relying on manual confirmation.

Interview tip

Describe one trade-off, one failure mode, and one measurable outcome when discussing window functions.

Beginner7 Years

12. How would you apply query execution plans in a production SQL project?

Answer: Apply query execution plans through an explicit, repeatable design: define the expected behavior, automate the highest-risk path, capture useful diagnostics, and verify the result against a stable oracle.

Detailed explanation, example and interview tip

In a production SQL project, start by identifying the risk that query execution plans controls and the evidence needed to prove the behavior. Keep setup, action, verification, and cleanup separate; parameterize environment-specific values; and make failures explain the business impact. Run the smallest relevant check on each change, broader coverage in the pipeline, and review flaky or slow results rather than hiding them with retries.

Real-world example

A team introduced a focused query execution plans check at the layer closest to the risk, attached diagnostics to failures, and used the result as a release signal instead of relying on manual confirmation.

Interview tip

Describe one trade-off, one failure mode, and one measurable outcome when discussing query execution plans.

Beginner7 Years

13. What is transactions, and why does it matter in SQL?

Answer: transactions is a core SQL concept used to make testing decisions more accurate, repeatable, and aligned with product risk.

Detailed explanation, example and interview tip

A strong explanation of transactions should cover its purpose, the problem it solves, when it is appropriate, and where it can mislead a team. In SQL, use it deliberately rather than as a checklist item: connect it to observable behavior, choose clear pass/fail evidence, and record assumptions so another engineer can reproduce the result.

Real-world example

During release planning, the team used transactions to focus validation on the most important behavior and to communicate residual risk before deployment.

Interview tip

Answer in four parts: definition, suitable use case, limitation, and a concrete project example.

Beginner7 Years

14. How would you apply isolation levels in a production SQL project?

Answer: Apply isolation levels through an explicit, repeatable design: define the expected behavior, automate the highest-risk path, capture useful diagnostics, and verify the result against a stable oracle.

Detailed explanation, example and interview tip

In a production SQL project, start by identifying the risk that isolation levels controls and the evidence needed to prove the behavior. Keep setup, action, verification, and cleanup separate; parameterize environment-specific values; and make failures explain the business impact. Run the smallest relevant check on each change, broader coverage in the pipeline, and review flaky or slow results rather than hiding them with retries.

Real-world example

A team introduced a focused isolation levels check at the layer closest to the risk, attached diagnostics to failures, and used the result as a release signal instead of relying on manual confirmation.

Interview tip

Describe one trade-off, one failure mode, and one measurable outcome when discussing isolation levels.

Beginner10+ Years

15. How would you apply constraints in a production SQL project?

Answer: Apply constraints through an explicit, repeatable design: define the expected behavior, automate the highest-risk path, capture useful diagnostics, and verify the result against a stable oracle.

Detailed explanation, example and interview tip

In a production SQL project, start by identifying the risk that constraints controls and the evidence needed to prove the behavior. Keep setup, action, verification, and cleanup separate; parameterize environment-specific values; and make failures explain the business impact. Run the smallest relevant check on each change, broader coverage in the pipeline, and review flaky or slow results rather than hiding them with retries.

Real-world example

A team introduced a focused constraints check at the layer closest to the risk, attached diagnostics to failures, and used the result as a release signal instead of relying on manual confirmation.

Interview tip

Describe one trade-off, one failure mode, and one measurable outcome when discussing constraints.

Beginner10+ Years

16. What is NULL handling, and why does it matter in SQL?

Answer: NULL handling is a core SQL concept used to make testing decisions more accurate, repeatable, and aligned with product risk.

Detailed explanation, example and interview tip

A strong explanation of NULL handling should cover its purpose, the problem it solves, when it is appropriate, and where it can mislead a team. In SQL, use it deliberately rather than as a checklist item: connect it to observable behavior, choose clear pass/fail evidence, and record assumptions so another engineer can reproduce the result.

Real-world example

During release planning, the team used NULL handling to focus validation on the most important behavior and to communicate residual risk before deployment.

Interview tip

Answer in four parts: definition, suitable use case, limitation, and a concrete project example.

SQL interview questions for experienced testers

Intermediate3 Years Frequently asked

17. Write a query to find the second highest salary.

Answer: Use DENSE_RANK() in a subquery, or the classic MAX with a nested exclusion.

Detailed explanation, example and interview tip

Window functions handle duplicates correctly and scale to Nth highest. Be ready to explain the difference between RANK, DENSE_RANK and ROW_NUMBER.

sql
1SELECT salary
2FROM (
3 SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
4 FROM employees
5) t
6WHERE rnk = 2;

Real-world example

Data validation tests comparing UI leaderboards against the database.

Interview tip

Offer the Nth-highest generalisation before they ask for it.

Intermediate5 Years Frequently asked

18. Explain the difference between INNER JOIN, LEFT JOIN, and how you'd use them to validate test data relationships.

Answer: INNER JOIN returns only rows with matches in both tables, while LEFT JOIN returns all rows from the left table plus matched data from the right, with NULLs where no match exists — the latter is essential for finding orphaned records.

Detailed explanation, example and interview tip

INNER JOIN is used when you only care about records that definitely have a relationship, like orders that have a matching customer. LEFT JOIN combined with a WHERE right_table.id IS NULL is the standard technique for finding orphaned or missing data, such as orders with no corresponding shipment record. RIGHT JOIN and FULL OUTER JOIN are less common in practice but useful for symmetric completeness checks. As a tester, LEFT JOIN anti-join patterns are one of the most powerful tools for catching data integrity bugs that the application layer might mask.

sql
1-- Find orders that have no matching shipment (data integrity check)
2SELECT o.order_id, o.created_at
3FROM orders o
4LEFT JOIN shipments s ON o.order_id = s.order_id
5WHERE s.order_id IS NULL;

Real-world example

This LEFT JOIN anti-join pattern found 200 orders stuck without shipment records after a message queue silently dropped events during a deploy.

Interview tip

Demonstrate the LEFT JOIN + IS NULL anti-join pattern unprompted — it's the single most useful SQL trick for a QA/SDET role.

Intermediate3 Years Frequently asked

19. How would you apply SELECT fundamentals in a production SQL project?

Answer: Apply SELECT fundamentals through an explicit, repeatable design: define the expected behavior, automate the highest-risk path, capture useful diagnostics, and verify the result against a stable oracle.

Detailed explanation, example and interview tip

In a production SQL project, start by identifying the risk that SELECT fundamentals controls and the evidence needed to prove the behavior. Keep setup, action, verification, and cleanup separate; parameterize environment-specific values; and make failures explain the business impact. Run the smallest relevant check on each change, broader coverage in the pipeline, and review flaky or slow results rather than hiding them with retries.

Real-world example

A team introduced a focused SELECT fundamentals check at the layer closest to the risk, attached diagnostics to failures, and used the result as a release signal instead of relying on manual confirmation.

Interview tip

Describe one trade-off, one failure mode, and one measurable outcome when discussing SELECT fundamentals.

Intermediate3 Years Frequently asked

20. How would you apply outer joins in a production SQL project?

Answer: Apply outer joins through an explicit, repeatable design: define the expected behavior, automate the highest-risk path, capture useful diagnostics, and verify the result against a stable oracle.

Detailed explanation, example and interview tip

In a production SQL project, start by identifying the risk that outer joins controls and the evidence needed to prove the behavior. Keep setup, action, verification, and cleanup separate; parameterize environment-specific values; and make failures explain the business impact. Run the smallest relevant check on each change, broader coverage in the pipeline, and review flaky or slow results rather than hiding them with retries.

Real-world example

A team introduced a focused outer joins check at the layer closest to the risk, attached diagnostics to failures, and used the result as a release signal instead of relying on manual confirmation.

Interview tip

Describe one trade-off, one failure mode, and one measurable outcome when discussing outer joins.

Intermediate3 Years Frequently asked

21. What is self joins, and why does it matter in SQL?

Answer: self joins is a core SQL concept used to make testing decisions more accurate, repeatable, and aligned with product risk.

Detailed explanation, example and interview tip

A strong explanation of self joins should cover its purpose, the problem it solves, when it is appropriate, and where it can mislead a team. In SQL, use it deliberately rather than as a checklist item: connect it to observable behavior, choose clear pass/fail evidence, and record assumptions so another engineer can reproduce the result.

Real-world example

During release planning, the team used self joins to focus validation on the most important behavior and to communicate residual risk before deployment.

Interview tip

Answer in four parts: definition, suitable use case, limitation, and a concrete project example.

Intermediate5 Years Frequently asked

22. How would you apply HAVING in a production SQL project?

Answer: Apply HAVING through an explicit, repeatable design: define the expected behavior, automate the highest-risk path, capture useful diagnostics, and verify the result against a stable oracle.

Detailed explanation, example and interview tip

In a production SQL project, start by identifying the risk that HAVING controls and the evidence needed to prove the behavior. Keep setup, action, verification, and cleanup separate; parameterize environment-specific values; and make failures explain the business impact. Run the smallest relevant check on each change, broader coverage in the pipeline, and review flaky or slow results rather than hiding them with retries.

Real-world example

A team introduced a focused HAVING check at the layer closest to the risk, attached diagnostics to failures, and used the result as a release signal instead of relying on manual confirmation.

Interview tip

Describe one trade-off, one failure mode, and one measurable outcome when discussing HAVING.

Intermediate5 Years Frequently asked

23. How would you apply correlated subqueries in a production SQL project?

Answer: Apply correlated subqueries through an explicit, repeatable design: define the expected behavior, automate the highest-risk path, capture useful diagnostics, and verify the result against a stable oracle.

Detailed explanation, example and interview tip

In a production SQL project, start by identifying the risk that correlated subqueries controls and the evidence needed to prove the behavior. Keep setup, action, verification, and cleanup separate; parameterize environment-specific values; and make failures explain the business impact. Run the smallest relevant check on each change, broader coverage in the pipeline, and review flaky or slow results rather than hiding them with retries.

Real-world example

A team introduced a focused correlated subqueries check at the layer closest to the risk, attached diagnostics to failures, and used the result as a release signal instead of relying on manual confirmation.

Interview tip

Describe one trade-off, one failure mode, and one measurable outcome when discussing correlated subqueries.

Intermediate5 Years Frequently asked

24. What is common table expressions, and why does it matter in SQL?

Answer: common table expressions is a core SQL concept used to make testing decisions more accurate, repeatable, and aligned with product risk.

Detailed explanation, example and interview tip

A strong explanation of common table expressions should cover its purpose, the problem it solves, when it is appropriate, and where it can mislead a team. In SQL, use it deliberately rather than as a checklist item: connect it to observable behavior, choose clear pass/fail evidence, and record assumptions so another engineer can reproduce the result.

Real-world example

During release planning, the team used common table expressions to focus validation on the most important behavior and to communicate residual risk before deployment.

Interview tip

Answer in four parts: definition, suitable use case, limitation, and a concrete project example.

Intermediate7 Years Frequently asked

25. How would you apply ROW_NUMBER in a production SQL project?

Answer: Apply ROW_NUMBER through an explicit, repeatable design: define the expected behavior, automate the highest-risk path, capture useful diagnostics, and verify the result against a stable oracle.

Detailed explanation, example and interview tip

In a production SQL project, start by identifying the risk that ROW_NUMBER controls and the evidence needed to prove the behavior. Keep setup, action, verification, and cleanup separate; parameterize environment-specific values; and make failures explain the business impact. Run the smallest relevant check on each change, broader coverage in the pipeline, and review flaky or slow results rather than hiding them with retries.

Real-world example

A team introduced a focused ROW_NUMBER check at the layer closest to the risk, attached diagnostics to failures, and used the result as a release signal instead of relying on manual confirmation.

Interview tip

Describe one trade-off, one failure mode, and one measurable outcome when discussing ROW_NUMBER.

Intermediate7 Years Frequently asked

26. How would you apply transactions in a production SQL project?

Answer: Apply transactions through an explicit, repeatable design: define the expected behavior, automate the highest-risk path, capture useful diagnostics, and verify the result against a stable oracle.

Detailed explanation, example and interview tip

In a production SQL project, start by identifying the risk that transactions controls and the evidence needed to prove the behavior. Keep setup, action, verification, and cleanup separate; parameterize environment-specific values; and make failures explain the business impact. Run the smallest relevant check on each change, broader coverage in the pipeline, and review flaky or slow results rather than hiding them with retries.

Real-world example

A team introduced a focused transactions check at the layer closest to the risk, attached diagnostics to failures, and used the result as a release signal instead of relying on manual confirmation.

Interview tip

Describe one trade-off, one failure mode, and one measurable outcome when discussing transactions.

Intermediate7 Years Frequently asked

27. What is ACID properties, and why does it matter in SQL?

Answer: ACID properties is a core SQL concept used to make testing decisions more accurate, repeatable, and aligned with product risk.

Detailed explanation, example and interview tip

A strong explanation of ACID properties should cover its purpose, the problem it solves, when it is appropriate, and where it can mislead a team. In SQL, use it deliberately rather than as a checklist item: connect it to observable behavior, choose clear pass/fail evidence, and record assumptions so another engineer can reproduce the result.

Real-world example

During release planning, the team used ACID properties to focus validation on the most important behavior and to communicate residual risk before deployment.

Interview tip

Answer in four parts: definition, suitable use case, limitation, and a concrete project example.

Intermediate10+ Years Frequently asked

28. How would you apply deadlocks in a production SQL project?

Answer: Apply deadlocks through an explicit, repeatable design: define the expected behavior, automate the highest-risk path, capture useful diagnostics, and verify the result against a stable oracle.

Detailed explanation, example and interview tip

In a production SQL project, start by identifying the risk that deadlocks controls and the evidence needed to prove the behavior. Keep setup, action, verification, and cleanup separate; parameterize environment-specific values; and make failures explain the business impact. Run the smallest relevant check on each change, broader coverage in the pipeline, and review flaky or slow results rather than hiding them with retries.

Real-world example

A team introduced a focused deadlocks check at the layer closest to the risk, attached diagnostics to failures, and used the result as a release signal instead of relying on manual confirmation.

Interview tip

Describe one trade-off, one failure mode, and one measurable outcome when discussing deadlocks.

Intermediate10+ Years Frequently asked

29. How would you apply NULL handling in a production SQL project?

Answer: Apply NULL handling through an explicit, repeatable design: define the expected behavior, automate the highest-risk path, capture useful diagnostics, and verify the result against a stable oracle.

Detailed explanation, example and interview tip

In a production SQL project, start by identifying the risk that NULL handling controls and the evidence needed to prove the behavior. Keep setup, action, verification, and cleanup separate; parameterize environment-specific values; and make failures explain the business impact. Run the smallest relevant check on each change, broader coverage in the pipeline, and review flaky or slow results rather than hiding them with retries.

Real-world example

A team introduced a focused NULL handling check at the layer closest to the risk, attached diagnostics to failures, and used the result as a release signal instead of relying on manual confirmation.

Interview tip

Describe one trade-off, one failure mode, and one measurable outcome when discussing NULL handling.

Intermediate10+ Years Frequently asked

30. What is data validation queries, and why does it matter in SQL?

Answer: data validation queries is a core SQL concept used to make testing decisions more accurate, repeatable, and aligned with product risk.

Detailed explanation, example and interview tip

A strong explanation of data validation queries should cover its purpose, the problem it solves, when it is appropriate, and where it can mislead a team. In SQL, use it deliberately rather than as a checklist item: connect it to observable behavior, choose clear pass/fail evidence, and record assumptions so another engineer can reproduce the result.

Real-world example

During release planning, the team used data validation queries to focus validation on the most important behavior and to communicate residual risk before deployment.

Interview tip

Answer in four parts: definition, suitable use case, limitation, and a concrete project example.

Intermediate5 Years

31. When would you use a CTE instead of a subquery, and are there performance differences?

Answer: A CTE (WITH clause) improves readability by naming an intermediate result set you can reference multiple times, and recursive CTEs handle hierarchical data that a plain subquery cannot express at all, though most databases optimize non-recursive CTEs similarly to subqueries.

Detailed explanation, example and interview tip

Subqueries nested several levels deep become hard to read and debug, especially when validating multi-step data transformations. A CTE lets you break the logic into named, sequential steps that read top to bottom like a small script, and if referenced multiple times in the same query, some databases materialize it once rather than recomputing. Recursive CTEs are uniquely suited for traversing hierarchical data, like an org chart or a category tree, which is effectively impossible to express cleanly with a plain subquery. On query planners like Postgres 12+, CTEs are typically inlined like subqueries unless explicitly materialized, so the performance gap has narrowed significantly in modern engines.

sql
1WITH recent_orders AS (
2 SELECT customer_id, COUNT(*) AS order_count
3 FROM orders
4 WHERE created_at > NOW() - INTERVAL '30 days'
5 GROUP BY customer_id
6)
7SELECT c.name, r.order_count
8FROM customers c
9JOIN recent_orders r ON c.customer_id = r.customer_id
10WHERE r.order_count > 10;

Real-world example

Rewriting a triple-nested subquery data-validation script into two chained CTEs made the query reviewable by a teammate in minutes instead of requiring a walkthrough.

Interview tip

Mention that modern optimizers often inline CTEs just like subqueries — it prevents the outdated 'CTEs are always slower' myth from undermining your answer.

Intermediate3 Years

32. What is WHERE filtering, and why does it matter in SQL?

Answer: WHERE filtering is a core SQL concept used to make testing decisions more accurate, repeatable, and aligned with product risk.

Detailed explanation, example and interview tip

A strong explanation of WHERE filtering should cover its purpose, the problem it solves, when it is appropriate, and where it can mislead a team. In SQL, use it deliberately rather than as a checklist item: connect it to observable behavior, choose clear pass/fail evidence, and record assumptions so another engineer can reproduce the result.

Real-world example

During release planning, the team used WHERE filtering to focus validation on the most important behavior and to communicate residual risk before deployment.

Interview tip

Answer in four parts: definition, suitable use case, limitation, and a concrete project example.

Intermediate5 Years

33. What is aggregate functions, and why does it matter in SQL?

Answer: aggregate functions is a core SQL concept used to make testing decisions more accurate, repeatable, and aligned with product risk.

Detailed explanation, example and interview tip

A strong explanation of aggregate functions should cover its purpose, the problem it solves, when it is appropriate, and where it can mislead a team. In SQL, use it deliberately rather than as a checklist item: connect it to observable behavior, choose clear pass/fail evidence, and record assumptions so another engineer can reproduce the result.

Real-world example

During release planning, the team used aggregate functions to focus validation on the most important behavior and to communicate residual risk before deployment.

Interview tip

Answer in four parts: definition, suitable use case, limitation, and a concrete project example.

Intermediate7 Years

34. What is indexes, and why does it matter in SQL?

Answer: indexes is a core SQL concept used to make testing decisions more accurate, repeatable, and aligned with product risk.

Detailed explanation, example and interview tip

A strong explanation of indexes should cover its purpose, the problem it solves, when it is appropriate, and where it can mislead a team. In SQL, use it deliberately rather than as a checklist item: connect it to observable behavior, choose clear pass/fail evidence, and record assumptions so another engineer can reproduce the result.

Real-world example

During release planning, the team used indexes to focus validation on the most important behavior and to communicate residual risk before deployment.

Interview tip

Answer in four parts: definition, suitable use case, limitation, and a concrete project example.

Intermediate10+ Years

35. What is normalization, and why does it matter in SQL?

Answer: normalization is a core SQL concept used to make testing decisions more accurate, repeatable, and aligned with product risk.

Detailed explanation, example and interview tip

A strong explanation of normalization should cover its purpose, the problem it solves, when it is appropriate, and where it can mislead a team. In SQL, use it deliberately rather than as a checklist item: connect it to observable behavior, choose clear pass/fail evidence, and record assumptions so another engineer can reproduce the result.

Real-world example

During release planning, the team used normalization to focus validation on the most important behavior and to communicate residual risk before deployment.

Interview tip

Answer in four parts: definition, suitable use case, limitation, and a concrete project example.

Advanced SQL interview questions

Advanced7 Years Frequently asked

36. How would you use a window function to validate ordering or detect gaps in sequential test data?

Answer: Window functions like ROW_NUMBER(), RANK(), and LAG()/LEAD() let you compute values across a set of rows related to the current row without collapsing them into groups, which is ideal for detecting sequence gaps or comparing a row to the previous one.

Detailed explanation, example and interview tip

Unlike GROUP BY which reduces rows into aggregates, window functions preserve every row while adding a calculated column, using OVER (PARTITION BY ... ORDER BY ...) to define the window. LAG() lets you compare the current row's value to the previous row's, which is the standard technique for detecting gaps in an auto-incrementing ID sequence or verifying timestamps are strictly increasing. ROW_NUMBER() partitioned by a natural key is also commonly used to identify and keep/remove duplicate rows in a cleanup or validation query.

sql
1-- Detect gaps in an order_id sequence (possible missing/deleted records)
2SELECT order_id,
3 order_id - LAG(order_id) OVER (ORDER BY order_id) AS gap
4FROM orders
5QUALIFY gap > 1;
6-- (or wrap in a subquery with WHERE gap > 1 on databases without QUALIFY)

Real-world example

A LAG()-based gap detection query revealed that a batch job was silently failing to insert every 500th record during a load test.

Interview tip

Mention QUALIFY or the WHERE-on-a-subquery workaround for filtering window function results — it shows you know the syntax constraint, not just the concept.

Advanced7 Years Frequently asked

37. How do indexes affect query performance, and what's a scenario where a missing index caused a test to time out?

Answer: An index creates a sorted lookup structure (typically a B-tree) so the database can find matching rows without scanning the entire table, turning an O(n) full table scan into a much faster O(log n) lookup.

Detailed explanation, example and interview tip

Indexes speed up SELECT queries filtering or joining on indexed columns, but they add overhead to INSERT/UPDATE/DELETE since the index must be maintained, so they're a trade-off rather than a free win. As a tester, EXPLAIN or EXPLAIN ANALYZE is the tool to check whether a slow validation query is doing a full table scan versus an index scan. Composite indexes matter for multi-column WHERE clauses, and column order in the index must match the query's filtering pattern to be used effectively.

sql
1EXPLAIN ANALYZE
2SELECT * FROM orders WHERE customer_id = 12345 AND status = 'pending';
3-- If this shows "Seq Scan" instead of "Index Scan", a composite index
4-- like CREATE INDEX idx_orders_cust_status ON orders(customer_id, status);
5-- would likely fix a slow data-validation query in CI.

Real-world example

A nightly data-validation job started timing out after the orders table grew past 10 million rows; EXPLAIN ANALYZE showed a full sequential scan, and adding a composite index cut runtime from 40 minutes to under 5 seconds.

Interview tip

Bring up EXPLAIN ANALYZE as your diagnostic step before proposing an index — showing method, not just the answer, is what separates senior candidates.

Advanced7 Years Frequently asked

38. How do you verify database state after an API call in an automated test, and what pitfalls do you watch for?

Answer: Query the relevant table(s) directly after the API call using a unique identifier from the response, and guard against race conditions by polling with a timeout rather than asserting immediately, since some writes are asynchronous.

Detailed explanation, example and interview tip

The typical pattern is to call the API, capture the returned ID, then query the database to assert the row exists with expected field values, which catches bugs the API's own response might mask (e.g., a 200 OK that didn't actually persist correctly). A major pitfall is asynchronous processing — if the API triggers a queue/worker to write data, an immediate query can flake because the write hasn't landed yet, so a short polling loop with a sane timeout is safer than a fixed sleep. Another pitfall is test data isolation: querying by a non-unique field like status can match rows from other parallel tests, so always filter by the specific ID or a test-run-scoped identifier.

sql
1-- After POST /orders returns { id: 501 }
2SELECT status, total_amount
3FROM orders
4WHERE order_id = 501;
5-- In test code: poll this query every 200ms up to a 5s timeout
6-- instead of a hard sleep, to handle async order processing.

Real-world example

Switching a fixed 2-second sleep to a polling assertion against the database eliminated an intermittent 3% failure rate in the order-creation regression suite.

Interview tip

Call out polling-with-timeout versus fixed sleeps explicitly — it's the exact distinction that separates flaky suites from reliable ones.

Advanced7 Years Frequently asked

39. What's your approach to writing a query that finds duplicate records that shouldn't exist, and how do you decide which duplicate to keep when cleaning up?

Answer: Group by the fields that should be unique, filter with HAVING COUNT(*) > 1 to find duplicates, then use ROW_NUMBER() partitioned by those fields to identify which rows to keep (usually the earliest or most complete) versus delete.

Detailed explanation, example and interview tip

The first pass is a diagnostic GROUP BY/HAVING query to confirm duplicates exist and get a count. For remediation, ROW_NUMBER() OVER (PARTITION BY <unique fields> ORDER BY created_at) numbers each duplicate group, letting you keep row number 1 (typically the oldest or most authoritative) and target the rest for deletion or investigation. Before deleting anything, it's critical to check whether the duplicates have divergent related data (e.g., different orders linked to each duplicate customer), since blind deletion can silently orphan child records — this should always run as a SELECT first, never a live DELETE, until validated.

sql
1WITH ranked AS (
2 SELECT *, ROW_NUMBER() OVER (
3 PARTITION BY email ORDER BY created_at ASC
4 ) AS rn
5 FROM users
6)
7SELECT * FROM ranked WHERE rn > 1; -- candidates for cleanup, review before deleting

Real-world example

Using ROW_NUMBER() to isolate true duplicate customer accounts revealed several 'duplicates' actually had different linked payment methods, so we flagged them for manual review instead of auto-merging.

Interview tip

Emphasize running the identification query as a SELECT before ever writing a DELETE — it shows the caution a senior SDET brings to data cleanup.

Advanced3 Years Frequently asked

40. What is INNER JOIN, and why does it matter in SQL?

Answer: INNER JOIN is a core SQL concept used to make testing decisions more accurate, repeatable, and aligned with product risk.

Detailed explanation, example and interview tip

A strong explanation of INNER JOIN should cover its purpose, the problem it solves, when it is appropriate, and where it can mislead a team. In SQL, use it deliberately rather than as a checklist item: connect it to observable behavior, choose clear pass/fail evidence, and record assumptions so another engineer can reproduce the result.

Real-world example

During release planning, the team used INNER JOIN to focus validation on the most important behavior and to communicate residual risk before deployment.

Interview tip

Answer in four parts: definition, suitable use case, limitation, and a concrete project example.

Advanced5 Years Frequently asked

41. What is subqueries, and why does it matter in SQL?

Answer: subqueries is a core SQL concept used to make testing decisions more accurate, repeatable, and aligned with product risk.

Detailed explanation, example and interview tip

A strong explanation of subqueries should cover its purpose, the problem it solves, when it is appropriate, and where it can mislead a team. In SQL, use it deliberately rather than as a checklist item: connect it to observable behavior, choose clear pass/fail evidence, and record assumptions so another engineer can reproduce the result.

Real-world example

During release planning, the team used subqueries to focus validation on the most important behavior and to communicate residual risk before deployment.

Interview tip

Answer in four parts: definition, suitable use case, limitation, and a concrete project example.

Advanced7 Years Frequently asked

42. What is query execution plans, and why does it matter in SQL?

Answer: query execution plans is a core SQL concept used to make testing decisions more accurate, repeatable, and aligned with product risk.

Detailed explanation, example and interview tip

A strong explanation of query execution plans should cover its purpose, the problem it solves, when it is appropriate, and where it can mislead a team. In SQL, use it deliberately rather than as a checklist item: connect it to observable behavior, choose clear pass/fail evidence, and record assumptions so another engineer can reproduce the result.

Real-world example

During release planning, the team used query execution plans to focus validation on the most important behavior and to communicate residual risk before deployment.

Interview tip

Answer in four parts: definition, suitable use case, limitation, and a concrete project example.

Advanced10+ Years Frequently asked

43. What is constraints, and why does it matter in SQL?

Answer: constraints is a core SQL concept used to make testing decisions more accurate, repeatable, and aligned with product risk.

Detailed explanation, example and interview tip

A strong explanation of constraints should cover its purpose, the problem it solves, when it is appropriate, and where it can mislead a team. In SQL, use it deliberately rather than as a checklist item: connect it to observable behavior, choose clear pass/fail evidence, and record assumptions so another engineer can reproduce the result.

Real-world example

During release planning, the team used constraints to focus validation on the most important behavior and to communicate residual risk before deployment.

Interview tip

Answer in four parts: definition, suitable use case, limitation, and a concrete project example.

Advanced3 Years

44. How would you apply WHERE filtering in a production SQL project?

Answer: Apply WHERE filtering through an explicit, repeatable design: define the expected behavior, automate the highest-risk path, capture useful diagnostics, and verify the result against a stable oracle.

Detailed explanation, example and interview tip

In a production SQL project, start by identifying the risk that WHERE filtering controls and the evidence needed to prove the behavior. Keep setup, action, verification, and cleanup separate; parameterize environment-specific values; and make failures explain the business impact. Run the smallest relevant check on each change, broader coverage in the pipeline, and review flaky or slow results rather than hiding them with retries.

Real-world example

A team introduced a focused WHERE filtering check at the layer closest to the risk, attached diagnostics to failures, and used the result as a release signal instead of relying on manual confirmation.

Interview tip

Describe one trade-off, one failure mode, and one measurable outcome when discussing WHERE filtering.

Advanced3 Years

45. How would you apply self joins in a production SQL project?

Answer: Apply self joins through an explicit, repeatable design: define the expected behavior, automate the highest-risk path, capture useful diagnostics, and verify the result against a stable oracle.

Detailed explanation, example and interview tip

In a production SQL project, start by identifying the risk that self joins controls and the evidence needed to prove the behavior. Keep setup, action, verification, and cleanup separate; parameterize environment-specific values; and make failures explain the business impact. Run the smallest relevant check on each change, broader coverage in the pipeline, and review flaky or slow results rather than hiding them with retries.

Real-world example

A team introduced a focused self joins check at the layer closest to the risk, attached diagnostics to failures, and used the result as a release signal instead of relying on manual confirmation.

Interview tip

Describe one trade-off, one failure mode, and one measurable outcome when discussing self joins.

Advanced3 Years

46. What is GROUP BY, and why does it matter in SQL?

Answer: GROUP BY is a core SQL concept used to make testing decisions more accurate, repeatable, and aligned with product risk.

Detailed explanation, example and interview tip

A strong explanation of GROUP BY should cover its purpose, the problem it solves, when it is appropriate, and where it can mislead a team. In SQL, use it deliberately rather than as a checklist item: connect it to observable behavior, choose clear pass/fail evidence, and record assumptions so another engineer can reproduce the result.

Real-world example

During release planning, the team used GROUP BY to focus validation on the most important behavior and to communicate residual risk before deployment.

Interview tip

Answer in four parts: definition, suitable use case, limitation, and a concrete project example.

Advanced5 Years

47. How would you apply aggregate functions in a production SQL project?

Answer: Apply aggregate functions through an explicit, repeatable design: define the expected behavior, automate the highest-risk path, capture useful diagnostics, and verify the result against a stable oracle.

Detailed explanation, example and interview tip

In a production SQL project, start by identifying the risk that aggregate functions controls and the evidence needed to prove the behavior. Keep setup, action, verification, and cleanup separate; parameterize environment-specific values; and make failures explain the business impact. Run the smallest relevant check on each change, broader coverage in the pipeline, and review flaky or slow results rather than hiding them with retries.

Real-world example

A team introduced a focused aggregate functions check at the layer closest to the risk, attached diagnostics to failures, and used the result as a release signal instead of relying on manual confirmation.

Interview tip

Describe one trade-off, one failure mode, and one measurable outcome when discussing aggregate functions.

Advanced5 Years

48. How would you apply common table expressions in a production SQL project?

Answer: Apply common table expressions through an explicit, repeatable design: define the expected behavior, automate the highest-risk path, capture useful diagnostics, and verify the result against a stable oracle.

Detailed explanation, example and interview tip

In a production SQL project, start by identifying the risk that common table expressions controls and the evidence needed to prove the behavior. Keep setup, action, verification, and cleanup separate; parameterize environment-specific values; and make failures explain the business impact. Run the smallest relevant check on each change, broader coverage in the pipeline, and review flaky or slow results rather than hiding them with retries.

Real-world example

A team introduced a focused common table expressions check at the layer closest to the risk, attached diagnostics to failures, and used the result as a release signal instead of relying on manual confirmation.

Interview tip

Describe one trade-off, one failure mode, and one measurable outcome when discussing common table expressions.

Advanced5 Years

49. What is window functions, and why does it matter in SQL?

Answer: window functions is a core SQL concept used to make testing decisions more accurate, repeatable, and aligned with product risk.

Detailed explanation, example and interview tip

A strong explanation of window functions should cover its purpose, the problem it solves, when it is appropriate, and where it can mislead a team. In SQL, use it deliberately rather than as a checklist item: connect it to observable behavior, choose clear pass/fail evidence, and record assumptions so another engineer can reproduce the result.

Real-world example

During release planning, the team used window functions to focus validation on the most important behavior and to communicate residual risk before deployment.

Interview tip

Answer in four parts: definition, suitable use case, limitation, and a concrete project example.

Advanced7 Years

50. How would you apply indexes in a production SQL project?

Answer: Apply indexes through an explicit, repeatable design: define the expected behavior, automate the highest-risk path, capture useful diagnostics, and verify the result against a stable oracle.

Detailed explanation, example and interview tip

In a production SQL project, start by identifying the risk that indexes controls and the evidence needed to prove the behavior. Keep setup, action, verification, and cleanup separate; parameterize environment-specific values; and make failures explain the business impact. Run the smallest relevant check on each change, broader coverage in the pipeline, and review flaky or slow results rather than hiding them with retries.

Real-world example

A team introduced a focused indexes check at the layer closest to the risk, attached diagnostics to failures, and used the result as a release signal instead of relying on manual confirmation.

Interview tip

Describe one trade-off, one failure mode, and one measurable outcome when discussing indexes.

Advanced7 Years

51. How would you apply ACID properties in a production SQL project?

Answer: Apply ACID properties through an explicit, repeatable design: define the expected behavior, automate the highest-risk path, capture useful diagnostics, and verify the result against a stable oracle.

Detailed explanation, example and interview tip

In a production SQL project, start by identifying the risk that ACID properties controls and the evidence needed to prove the behavior. Keep setup, action, verification, and cleanup separate; parameterize environment-specific values; and make failures explain the business impact. Run the smallest relevant check on each change, broader coverage in the pipeline, and review flaky or slow results rather than hiding them with retries.

Real-world example

A team introduced a focused ACID properties check at the layer closest to the risk, attached diagnostics to failures, and used the result as a release signal instead of relying on manual confirmation.

Interview tip

Describe one trade-off, one failure mode, and one measurable outcome when discussing ACID properties.

Advanced7 Years

52. What is isolation levels, and why does it matter in SQL?

Answer: isolation levels is a core SQL concept used to make testing decisions more accurate, repeatable, and aligned with product risk.

Detailed explanation, example and interview tip

A strong explanation of isolation levels should cover its purpose, the problem it solves, when it is appropriate, and where it can mislead a team. In SQL, use it deliberately rather than as a checklist item: connect it to observable behavior, choose clear pass/fail evidence, and record assumptions so another engineer can reproduce the result.

Real-world example

During release planning, the team used isolation levels to focus validation on the most important behavior and to communicate residual risk before deployment.

Interview tip

Answer in four parts: definition, suitable use case, limitation, and a concrete project example.

Advanced10+ Years

53. How would you apply normalization in a production SQL project?

Answer: Apply normalization through an explicit, repeatable design: define the expected behavior, automate the highest-risk path, capture useful diagnostics, and verify the result against a stable oracle.

Detailed explanation, example and interview tip

In a production SQL project, start by identifying the risk that normalization controls and the evidence needed to prove the behavior. Keep setup, action, verification, and cleanup separate; parameterize environment-specific values; and make failures explain the business impact. Run the smallest relevant check on each change, broader coverage in the pipeline, and review flaky or slow results rather than hiding them with retries.

Real-world example

A team introduced a focused normalization check at the layer closest to the risk, attached diagnostics to failures, and used the result as a release signal instead of relying on manual confirmation.

Interview tip

Describe one trade-off, one failure mode, and one measurable outcome when discussing normalization.

Advanced10+ Years

54. How would you apply data validation queries in a production SQL project?

Answer: Apply data validation queries through an explicit, repeatable design: define the expected behavior, automate the highest-risk path, capture useful diagnostics, and verify the result against a stable oracle.

Detailed explanation, example and interview tip

In a production SQL project, start by identifying the risk that data validation queries controls and the evidence needed to prove the behavior. Keep setup, action, verification, and cleanup separate; parameterize environment-specific values; and make failures explain the business impact. Run the smallest relevant check on each change, broader coverage in the pipeline, and review flaky or slow results rather than hiding them with retries.

Real-world example

A team introduced a focused data validation queries check at the layer closest to the risk, attached diagnostics to failures, and used the result as a release signal instead of relying on manual confirmation.

Interview tip

Describe one trade-off, one failure mode, and one measurable outcome when discussing data validation queries.

Interview questions by subject

Want to search across every subject? Open the full question bank.