SQL Interview Questions

Write a query to find the second highest salary.

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

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.

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

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.

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

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.

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

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.

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

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.

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

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.

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?

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.

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

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

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

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.

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

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

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

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.

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

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

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

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.

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

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

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

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.

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

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

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

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.

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

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

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

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.

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

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

How would you apply HAVING in a production SQL project?

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.

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

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

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

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.

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

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

How would you apply subqueries in a production SQL project?

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.

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

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

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

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.

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

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

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

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.

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

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

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

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.

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

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

How would you apply ROW_NUMBER in a production SQL project?

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.

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

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

How would you apply indexes in a production SQL project?

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.

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

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

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

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.

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

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

How would you apply transactions in a production SQL project?

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.

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

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

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

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.

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

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

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

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.

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

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

How would you apply deadlocks in a production SQL project?

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.

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

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

How would you apply normalization in a production SQL project?

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.

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

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

How would you apply constraints in a production SQL project?

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.

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

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

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

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.

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

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

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

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.