Good SQL interview answers explain the requested result before presenting a memorised pattern. This guide covers questions about joins, aggregates, NULL, keys, ranking and query performance, then works through the second-highest-distinct-salary problem. You can practise saying the reasoning aloud and verify the query against a complete small dataset.
An interviewer may use a particular dialect, so ask which engine and feature version the task expects. A correct PostgreSQL LIMIT answer may need a T-SQL TOP form in SQL Server. Clarifying that assumption is useful precision, not an excuse to avoid writing the query.
What is the difference between WHERE and HAVING?
WHERE filters input rows before grouping, while HAVING filters grouped results. For departments with at least two employees, GROUP BY department followed by HAVING COUNT(*)>=2 tests a group-level measure. For departments with at least two paid employees, a row-level paid filter changes which observations enter that count. Explain both the clause order and the changed business question.
Avoid saying HAVING is simply a slower WHERE. The two operate at different logical stages. A selected aggregate alias is not portable in every HAVING context, so repeating the aggregate or using an outer query is a reliable response. Mention that final ORDER BY remains necessary because grouping does not promise presentation order.
How do joins affect row counts?
INNER JOIN returns matching row combinations. LEFT JOIN also preserves unmatched left rows through NULL placeholders. In a one-to-many relationship, one parent can appear several times because several child records match it. Those repeated parent labels do not automatically mean duplicate business events. State whether the task wants parents, child details or parent summaries.
For an anti-join, NOT EXISTS expresses absence of qualifying matches. A LEFT JOIN followed by a missing right-side primary-key test is another form. Testing a nullable business field is unsafe because a real matched record may also have that field missing. An accidental many-to-many combination can inflate sums, so explain the expected grain before adding DISTINCT.
Why do NULL comparisons surprise people?
NULL marks missing or unknown data. An ordinary comparison such as salary=NULL is unknown rather than true, so WHERE does not retain it. IS NULL is the dedicated predicate. COUNT(*) counts rows, while COUNT(salary) and AVG(salary) use only non-NULL salary observations. Zero is still a known salary and should not be confused with absence.
NOT IN can become unknown when its returned set contains NULL, making NOT EXISTS a clearer answer for many relationship-absence questions. DISTINCT and set operations have their own NULL duplicate-comparison rules. A strong answer distinguishes these contexts rather than claiming either that NULL equals NULL everywhere or that two NULL values can never be deduplicated.
How do primary keys and indexes differ?
A primary key expresses chosen row identity and requires uniqueness and presence. A composite key identifies a row through several columns together. An index is an access structure that can support lookup or ordering; it is not by itself the same business declaration. A surrogate id may still need a separate UNIQUE constraint for a business reference.
Do not claim every primary key is stored as the same clustered structure in every engine. Explain the conceptual rule first, then any local storage implementation. More indexes can improve some reads while adding write and maintenance costs. An execution plan and realistic measurements are better evidence than a universal rule that indexing every filtered field always helps.
What do ranking functions do with ties?
ROW_NUMBER gives distinct row positions within its ordered partition, so a unique tie-breaker makes its selection deterministic. RANK gives tied peers the same rank and leaves gaps afterwards. DENSE_RANK gives tied peers the same rank without gaps. These choices matter for second-place questions: second row, second rank and second distinct value can mean different results.
For the second-highest distinct salary, seventy followed by two sixties should identify sixty and can return both employees at that value. OFFSET one after a descending detail sort can merely pick one tied row and may fail when the highest salary also repeats. State whether ties should all appear before choosing a scalar maximum, dense rank or row-position approach.
How would you investigate a slow query?
Begin by verifying the intended result and the source row counts. Inspect the execution plan for scans, estimates, sorts and join strategies, then check indexes and statistics appropriate to the predicates. Avoid changing the query so aggressively that it loses unmatched entities or alters NULL handling merely to run faster. Compare before-and-after values as well as timings.
For production performance, test representative data rather than only five rows. EXPLAIN ANALYZE in PostgreSQL executes the statement, so consider side effects when investigating writes. Transactions and isolation can also affect concurrency and perceived delay. A clear answer identifies what evidence to collect instead of announcing one favourite index or rewrite as the solution to every slow report.
Run the complete practice setup
The salary table has a highest value, a repeated second-highest value, a lower value and an unknown value. Those cases distinguish the second distinct salary from the second employee row in an ordered list.
CREATE TABLE interview_employees (id INTEGER PRIMARY KEY,name VARCHAR(30) NOT NULL,salary DECIMAL(10,2));
INSERT INTO interview_employees (id,name,salary) VALUES
(1,'Ana',70),(2,'Ben',60),(3,'Cara',60),(4,'Dev',50),(5,'Ema',NULL);Practical exercise and worked solution
Calculate the number of employees, the number with known salary and the number of distinct known salary values. Explain how the unknown record affects each count.
SELECT COUNT(*) AS employees,COUNT(salary) AS known_salaries,
COUNT(DISTINCT salary) AS distinct_salaries FROM interview_employees;employees | known_salaries | distinct_salaries
5 | 4 | 3Five employee rows exist. Ema’s NULL salary is omitted from the expression counts, leaving four known salaries. Ben and Cara share sixty, so the distinct known set is fifty, sixty and seventy. This example is a useful spoken explanation of COUNT forms because it includes both a missing value and a repeated real measure; neither should be treated as a duplicate employee record.
Official documentation
Use the PostgreSQL window tutorial, SQLite window reference and PostgreSQL EXPLAIN guide to verify the underlying concepts rather than memorising unsupported performance claims.
SQL Interview Questions with Clear Answers and Query Examples Examples
Each example shows the code, the output it produces and what each part does.
Return every employee at the second-highest distinct salary
The innermost MAX finds seventy. The next MAX considers salaries below seventy and chooses sixty. The outer comparison returns every employee at sixty, preserving both ties. NULL salaries do not qualify as known comparison values. If fewer than two distinct known salaries exist, the second maximum is NULL and no employee is returned.
SELECT id,name,salary FROM interview_employees
WHERE salary=(
SELECT MAX(salary) FROM interview_employees
WHERE salary<(SELECT MAX(salary) FROM interview_employees)
) ORDER BY id;id | name | salary
2 | Ben | 60
3 | Cara | 60Common SQL Interview Questions with Clear Answers and Query Examples Mistakes
Confusing the second row with the second distinct value
A descending salary query with an offset of one selects a row position. If the highest salary is repeated, that position can still hold the highest value. Even in this fixture it would normally select one of the two sixty-valued employees rather than both. Ask whether the task requests a scalar salary, one employee, or every employee tied at the second distinct salary.
SQL Interview Questions with Clear Answers and Query Examples FAQ
Should I memorise one answer per question?
No. Interviewers often change a detail to see whether you understand the answer. Understand the result contract and edge cases, such as ties, NULLs and empty groups, then practise more than one valid formulation, for example with a join and with a subquery.
Is one join form always fastest?
No. NOT EXISTS, LEFT JOIN ... IS NULL and NOT IN can run differently on different engines and data. Match the intended semantics first, then use plan evidence from EXPLAIN if performance matters.
What if the problem leaves ties unspecified?
Ask. Clarify the expected result shape, for example whether "the top customer" should return every tied customer, or state the tie policy your answer implements, RANK for all ties or ROW_NUMBER with a tie-breaker for exactly one.
How should I talk through a SQL question in an interview?
Restate what one output row means, name the tables and keys you'll use, mention edge cases such as NULLs and ties, then write the query in steps, often with a CTE per step. Checking the result against a tiny example shows the reasoning interviewers want to see.
Which SQL topics come up most in interviews?
Joins and the difference between inner and outer joins, GROUP BY with HAVING, subqueries and CTEs, window functions such as ROW_NUMBER and running totals, NULL handling, and indexes. Data roles add date handling and conditional aggregation; developer roles add transactions and constraints.
SQL Interview Questions with Clear Answers and Query Examples Exercises
Answer each one and check it instantly. If you get stuck, open the hint before the answer.
How many employees share the second-highest distinct salary here?
SQL Interview Questions with Clear Answers and Query Examples Quiz
2 questions to check what you learned.
Which function gives peers the same rank without rank-number gaps?
- ROW_NUMBER
- COUNT(*)
- DENSE_RANK
Which predicate tests an unknown salary?
- salary IS NULL
- salary = NULL
- salary = 'NULL'