SELECT, GROUP BY and subqueries are the core of every SQL screening question, and "second-highest salary" and "find duplicates" are so common that interviewers treat them as a filter. They probe whether you know that SQL is declarative and has a logical evaluation order that differs from the order you type it, and whether you understand NULL and ties, which is where most wrong answers fail. For a Backend or AI engineer the same fluency is what you use to debug production data, write reports, and check what an ORM is really asking the database.
You write it in this order: The database LOGICALLY evaluates it in this order:
SELECT dept, COUNT(*) 1. FROM pick the tables (and JOINs)
FROM employees 2. WHERE filter individual ROWS
WHERE salary >= 80 3. GROUP BY form groups from the surviving rows
GROUP BY dept 4. HAVING filter whole GROUPS (aggregates allowed)
HAVING COUNT(*) >= 2 5. SELECT compute output columns, aliases, window functions
ORDER BY dept 6. DISTINCT remove duplicate output rows
LIMIT 10; 7. ORDER BY sort (may use SELECT aliases)
8. LIMIT / OFFSETThis is the logical order, not a promise about physical execution: the optimizer may reorder work, use indexes and push filters down as long as the result is identical. It explains the classic rules. WHERE runs before groups exist, so it cannot contain COUNT(*); HAVING runs after grouping, so it can. Column aliases are created in step 5, so a standard engine such as PostgreSQL rejects an alias in WHERE but allows it in ORDER BY (SQLite and MySQL are more lenient in places, so do not rely on it). Filtering with WHERE before grouping is also cheaper because fewer rows reach the aggregation.
employees WHERE salary >= 80 keeps ids 1..6 (Zed, 70, is dropped)
+----+-------+-------+--------+-------+ GROUP BY dept -> Eng{1,2,3} Sales{4,5,6}
| id | name | dept | salary | bonus | HAVING COUNT(*)>=2 -> both groups survive
+----+-------+-------+--------+-------+
| 1 | Asha | Eng | 120 | 10 | dept n avg_sal
| 2 | Ravi | Eng | 100 | NULL | Eng 3 106.67
| 3 | Meera | Eng | 100 | 5 | Sales 3 83.33
| 4 | Kabir | Sales | 90 | NULL |
| 5 | Nina | Sales | 80 | NULL | Aggregates over the bonus column:
| 6 | Omar | Sales | 80 | 2 | COUNT(*) = 7 (rows)
| 7 | Zed | HR | 70 | NULL | COUNT(bonus) = 3 (NULLs ignored)
+----+-------+-------+--------+-------+ SUM(bonus) = 17
AVG(bonus) = 5.67 (17 / 3, not 17 / 7)WHERE filters rows and HAVING filters groups; the interview trap is putting a non-aggregate condition in HAVING, which works but does the filtering after the expensive grouping. Aggregates ignore NULL (except COUNT(*)), which is why AVG(bonus) divides by the three non-NULL values. GROUP BY and DISTINCT both collapse duplicates, and for a plain "list unique values" they produce the same result and are usually planned the same way. GROUP BY is the tool when you need an aggregate per group; DISTINCT is for deduplicating output rows.
NULL semantics (three-valued logic: TRUE, FALSE, UNKNOWN; WHERE keeps only TRUE)
bonus = NULL -> UNKNOWN for every row -> 0 rows (use IS NULL)
bonus IS NULL -> 4 rows
bonus <> 5 -> 2 rows (Asha, Omar): NULL rows are UNKNOWN, not "different from 5"
bonus <> 5 OR bonus IS NULL -> 6 rows
x NOT IN (1, 2, NULL) -> never TRUE (each comparison with NULL is UNKNOWN) => returns 0 rows
NOT EXISTS (...) -> unaffected by NULLs, the safe form of the anti-patternNULL means "unknown", so any comparison with it is UNKNOWN, and WHERE only keeps rows that evaluate to TRUE. The most expensive real-world bug in this area is NOT IN (subquery) where the subquery returns even one NULL: the whole predicate can never be true and the query silently returns nothing. Prefer NOT EXISTS or filter the NULLs out.
Scalar subquery : returns ONE value, usable wherever a value is
SELECT name, salary - (SELECT AVG(salary) FROM employees) AS diff FROM employees;
IN subquery : "is this value in the set the subquery returns?"
SELECT name FROM employees
WHERE dept IN (SELECT dept FROM employees GROUP BY dept HAVING COUNT(*) >= 3);
EXISTS subquery : "does at least one matching row exist?" (stops at the first match)
SELECT name FROM employees e
WHERE EXISTS (SELECT 1 FROM employees x WHERE x.dept = e.dept AND x.salary > e.salary);
Correlated : the inner query refers to the OUTER row, so it is logically re-evaluated per outer row
SELECT name FROM employees e
WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept = e.dept);A correlated subquery reads like a per-row loop, but modern optimizers often rewrite it into a join or semi-join, so do not assume it is slow (or fast) without checking the plan. Subquery vs JOIN: for filtering ("rows that have a match") a semi-join written as EXISTS or IN is clearer and cannot multiply rows; for pulling columns from the other table you need a JOIN. Duplicates are the practical difference: joining to a table with several matches returns several rows, while EXISTS returns each outer row once.
Data: A=120, B=120, C=100 (a tie at the top)
-- Approach 1: ORDER BY ... OFFSET (WRONG with ties)
SELECT salary FROM t ORDER BY salary DESC LIMIT 1 OFFSET 1; -- 120 (the second ROW, not second VALUE)
-- Approach 2: DISTINCT first, then OFFSET
SELECT DISTINCT salary FROM t ORDER BY salary DESC LIMIT 1 OFFSET 1; -- 100
-- Approach 3: MAX below the MAX (portable, returns NULL when there is no second value)
SELECT MAX(salary) FROM t WHERE salary < (SELECT MAX(salary) FROM t); -- 100
-- Approach 4: DENSE_RANK (extends to the Nth highest; needs window functions)
SELECT DISTINCT salary FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM t
) WHERE r = 2; -- 100
Find duplicates (by email):
SELECT email, COUNT(*) AS n FROM employees GROUP BY email HAVING COUNT(*) > 1;
-- to see the offending ROWS, not just the key: use COUNT(*) OVER (PARTITION BY email) > 1
-- to delete extras while keeping the lowest id:
-- DELETE FROM employees WHERE emp_id > (SELECT MIN(emp_id) FROM employees e2 WHERE e2.email = employees.email);The second-highest question is really a test of whether you thought about ties and about "no answer". The naive OFFSET returns the second row, which is 120 when two people share the top salary. Approaches 2 to 4 all work on distinct values. Approach 3 is the most portable and yields NULL when there is no second value, which is usually what the caller wants; a bare LIMIT 1 OFFSET 1 returns no row at all, which some checkers treat differently (LeetCode wants NULL, so wrap it in a scalar subquery). For duplicates, GROUP BY plus HAVING finds the duplicated keys, and a window count or a self-join finds the actual rows. Add a UNIQUE constraint afterwards; cleaning duplicates without one only postpones the next batch.
COUNT(*); AVG divides by the count of non-NULL values.IS NULL, and beware NOT IN with a subquery that can return NULL (use NOT EXISTS).-- Run with: sqlite3 :memory:
CREATE TABLE employees (emp_id INTEGER PRIMARY KEY, name TEXT, dept TEXT, salary INTEGER, bonus INTEGER, email TEXT);
INSERT INTO employees VALUES
(1,'Asha','Eng',120,10,'[email protected]'), (2,'Ravi','Eng',100,NULL,'[email protected]'),
(3,'Meera','Eng',100,5,'[email protected]'), (4,'Kabir','Sales',90,NULL,'[email protected]'),
(5,'Nina','Sales',80,NULL,'[email protected]'), (6,'Omar','Sales',80,2,'[email protected]'),
(7,'Zed','HR',70,NULL,'[email protected]');
SELECT dept, COUNT(*) AS n, AVG(salary) AS avg_sal FROM employees
WHERE salary >= 80 GROUP BY dept HAVING COUNT(*) >= 2 ORDER BY dept;
-- Eng 3 106.666666666667
-- Sales 3 83.3333333333333
SELECT COUNT(*), COUNT(bonus), SUM(bonus), AVG(bonus) FROM employees;
-- 7 3 17 5.66666666666667
SELECT COUNT(*) FROM employees WHERE bonus = NULL; -- 0
SELECT COUNT(*) FROM employees WHERE bonus IS NULL; -- 4
SELECT COUNT(*) FROM employees WHERE bonus <> 5; -- 2
SELECT COUNT(*) FROM employees WHERE bonus <> 5 OR bonus IS NULL; -- 6
SELECT name, salary, CASE WHEN salary >= 110 THEN 'high' WHEN salary >= 90 THEN 'mid' ELSE 'low' END AS band
FROM employees ORDER BY emp_id;
-- Asha high | Ravi mid | Meera mid | Kabir mid | Nina low | Omar low | Zed low
-- Second highest salary (values: 120, 100, 90, 80, 70)
SELECT MAX(salary) AS second_highest FROM employees WHERE salary < (SELECT MAX(salary) FROM employees); -- 100
SELECT DISTINCT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS r FROM employees) WHERE r = 2; -- 100
SELECT name, salary FROM employees
WHERE salary = (SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees));
-- Ravi 100, Meera 100 (both people at the second-highest value)
-- Duplicates
SELECT email, COUNT(*) AS n FROM employees GROUP BY email HAVING COUNT(*) > 1; -- [email protected] 2
SELECT emp_id FROM employees e WHERE emp_id > (SELECT MIN(emp_id) FROM employees WHERE email = e.email); -- 6 (the row to delete)
-- Subqueries
SELECT name FROM employees WHERE dept IN (SELECT dept FROM employees GROUP BY dept HAVING COUNT(*) >= 3) ORDER BY name;
-- Asha Kabir Meera Nina Omar Ravi
SELECT name, salary, dept FROM employees e WHERE salary > (SELECT AVG(salary) FROM employees WHERE dept = e.dept) ORDER BY emp_id;
-- Asha 120 Eng | Kabir 90 Sales
-- NOT IN trap: bonus contains NULLs
SELECT COUNT(*) FROM employees WHERE emp_id NOT IN (SELECT bonus FROM employees); -- 0
SELECT COUNT(*) FROM employees WHERE emp_id NOT IN (SELECT bonus FROM employees WHERE bonus IS NOT NULL); -- 5Q: What is the difference between WHERE and HAVING?
A: WHERE filters individual rows before grouping and cannot use aggregates; HAVING filters groups after aggregation and can. Logically WHERE runs before GROUP BY and HAVING after it, so a condition that does not need an aggregate belongs in WHERE, where it also reduces the rows the grouping step has to process. Using HAVING for row-level conditions is valid but wastes work, although some optimizers push such predicates down anyway.
Q: What is the difference between GROUP BY and DISTINCT?
A: DISTINCT removes duplicate rows from the output; GROUP BY forms groups so you can compute aggregates per group. For a plain list of unique values they return the same rows and are typically executed by the same hash or sort strategy. Reach for GROUP BY when you need COUNT, SUM or similar per group, and remember that DISTINCT applies to the whole select list, not just the first column.
Q: Subquery vs JOIN, which do you choose?
A: They can express the same logic, and the optimizer often rewrites one into the other, so readability and correctness come first. Use a JOIN when you need columns from both tables; use EXISTS or IN when you only need to test for matches, since that cannot multiply rows the way a one-to-many join does. Correlated subqueries can be slow on engines that cannot decorrelate them, so read the plan rather than guessing.
Q: How would you find the second-highest salary?
A: The robust version is SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees), or DENSE_RANK with rank 2 if you also need the Nth highest or the employees involved. A bare ORDER BY salary DESC LIMIT 1 OFFSET 1 is wrong with ties because it returns the second row, not the second distinct value, and the fix is to add DISTINCT. Mention the edge case that a table with a single distinct salary has no second-highest, and the MAX approach returns NULL there.
Q: How would you find duplicate records?
A: Group by the columns that define a duplicate and keep groups with HAVING COUNT(*) > 1. To see or delete the actual extra rows, use a window function (COUNT(*) OVER (PARTITION BY email) or ROW_NUMBER) or a subquery against MIN(id) so you keep one row per key. The lasting fix is a UNIQUE constraint; otherwise the duplicates return, and if a race causes them you also need an upsert or a transaction.
Follow-up: Why does NOT IN sometimes return nothing when rows clearly qualify?
A: If the subquery returns any NULL, then x NOT IN (..., NULL) evaluates to UNKNOWN for every x that is not otherwise matched, and WHERE discards UNKNOWN. Use NOT EXISTS or filter the NULLs inside the subquery. In the demo above, the same predicate returns 0 rows with NULLs present and 5 rows once they are filtered out.
Follow-up: Can you use a SELECT alias in WHERE?
A: Under standard logical order you cannot, because WHERE is evaluated before SELECT creates the alias, and PostgreSQL rejects it. ORDER BY runs after SELECT, so it can use aliases. Some engines are lenient (SQLite accepted it in my test), so the portable rule is to repeat the expression or wrap the query in a subquery or CTE.
Follow-up: What is the difference between COUNT(*), COUNT(col) and COUNT(DISTINCT col)?
A: COUNT(*) counts rows, COUNT(col) counts non-NULL values in that column, and COUNT(DISTINCT col) counts distinct non-NULL values. On the demo table, COUNT(*) is 7 while COUNT(bonus) and COUNT(DISTINCT bonus) are both 3 (the values are 10, 5 and 2). The trade-off is that COUNT(DISTINCT) needs deduplication (sorting or hashing), so it is more expensive on large inputs.
Knowing the logical order and NULL semantics is what lets you predict what an ORM-generated query does and why a report disagrees with a dashboard: a filter placed in the wrong clause, an inner-versus-outer difference, or a COUNT that skipped NULLs will all produce plausible but wrong numbers. In system design, the duplicate-finding pattern is the checking side of idempotency: at-least-once delivery from a queue or a retried API call will eventually insert the same logical record twice unless a UNIQUE constraint blocks it, and the GROUP BY plus HAVING query is how you audit whether your protection works. For large tables, remember that aggregations like GROUP BY and DISTINCT scan and sort or hash a lot of data, so analytics-style questions are usually moved to a read replica or a warehouse instead of the primary that serves user traffic.
SQL is declarative, so you trade control over how the query runs for the optimizer's freedom to pick a plan; the cost is that two logically equivalent formulations can perform differently on a given engine and statistics, and you must read the plan to know. Correlated subqueries and SELECT DISTINCT used to hide join duplicates are readable shortcuts that can be expensive on large tables or mask a modelling mistake; a join-then-DISTINCT is the wrong choice when an EXISTS states the intent exactly. Similarly, DENSE_RANK solutions are flexible but sort the whole set, while MAX-below-MAX is cheap but only answers the second-highest question; choose based on whether you will need the Nth or the rows themselves. Finally, engine leniency (aliases in WHERE, implicit casts, non-aggregated columns in GROUP BY in older MySQL modes) makes code that works in one database fail in another, which matters when the interview question says "portable".