SQL Server Scenario-Based Interview Questions: Find the 2nd Highest Salary, and More

Modern interviews increasingly favor “solve this problem” over “define this term.” Here’s how to actually handle the classics — not just the working query, but the reasoning an interviewer is actually listening for.

Find the Second-Highest Salary (Correctly, With Ties)

-- Robust version using DENSE_RANK, correctly handles ties at the top
WITH Ranked AS (
    SELECT *, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM dbo.Salary
)
SELECT * FROM Ranked WHERE rnk = 2;

The common wrong answer (MAX(salary) WHERE salary < MAX(salary)) often works by luck, but most candidates can’t explain why it breaks down when the top salary is tied across multiple people — in that case, the “wrong” version silently returns the third-highest distinct salary, not the second, because two people share first place. DENSE_RANK (Ch.6) makes the tie-handling explicit and correct by definition, not by accident.

-- Prove the difference yourself: with a tie at the top, these give different answers
INSERT INTO dbo.Salary (name, salary) VALUES ('Alex', 100000), ('Sam', 100000), ('Priya', 90000);
SELECT MAX(salary) FROM dbo.Salary WHERE salary < (SELECT MAX(salary) FROM dbo.Salary); -- 90000, correct here by luck
-- Add a 4th row: ('Jordan', 95000) and re-run — now compare against the DENSE_RANK version

Find Duplicate Rows

SELECT name, department, COUNT(*) AS occurrences
FROM dbo.Salary
GROUP BY name, department
HAVING COUNT(*) > 1;

This is Fundamentals Ch.4's GROUP BY + HAVING pattern applied directly — "duplicates" is really just "groups with more than one member," the same shape as every other GROUP BY/HAVING question, just with a different threshold.

Delete Duplicates, Keeping One Copy

WITH Deduped AS (
    SELECT *, ROW_NUMBER() OVER (PARTITION BY name, department ORDER BY employee_id) AS rn
    FROM dbo.Salary
)
DELETE FROM Deduped WHERE rn > 1;

A genuinely common follow-up to the duplicate-finding question above, and a real test of whether you understand ROW_NUMBER's uniqueness-guarantee well enough to use it for a DELETE, not just a SELECT — note this is deleting through the CTE, a pattern worth having ready.

"How Would You Diagnose a Slow Query?" — The Strong Answer Structure

Interviewers evaluate the process, not just the final answer — narrate your reasoning out loud, in this order (which is precisely the Chapter 8 DMV lesson's toolkit, applied as a live workflow), rather than jumping straight to "add an index." A candidate who says "I'd add an index" with no diagnostic step first reads as guessing; one who walks through sys.dm_exec_query_stats → execution plan → sys.dm_os_wait_stats → a specific, testable fix reads as someone who's actually done this under pressure before.