SQL Interview Questions and Answers for Data Analyst and Engineering Roles

WHERE filters individual rows before any grouping happens; HAVING filters groups after GROUP BY, which is why it can reference aggregates like COUNT(*) or SUM(amount) and WHERE cannot. Logical order: FROM, WHERE, GROUP BY, HAVING, SELECT. Practical consequence: push conditions into WHERE whenever they apply to single rows — filtering early shrinks the data before the expensive grouping step. HAVING COUNT(*) > 5 is legitimate; HAVING country = 'IN' runs in most databases but wastes work grouping rows you were always going to discard. Follow-ups interviewers probe: column aliases defined in SELECT are visible in neither WHERE nor HAVING (they have not been computed yet), and most engines allow HAVING without GROUP BY, treating the entire result as one group.

INNER JOIN returns only rows with a match on the join condition. LEFT JOIN keeps every row from the left table, filling the right side with NULLs where nothing matches; RIGHT JOIN is the mirror image; FULL OUTER JOIN keeps unmatched rows from both sides. CROSS JOIN produces the Cartesian product (every combination), and a SELF JOIN is a table joined to itself under two aliases — the classic employee-to-manager hierarchy. The trap interviewers set: putting a condition on the right table in the WHERE clause of a LEFT JOIN (WHERE r.status = 'active') silently converts it into an INNER JOIN, because the NULL rows fail the test — put that condition in the ON clause instead. Also worth saying: join order in the query text does not dictate execution order; the optimizer chooses.

UNION combines two result sets and removes duplicate rows from the combined output; UNION ALL simply concatenates and keeps everything. The dedupe forces a sort or hash over the whole result, so UNION is strictly more expensive — on large sets, dramatically so. Default to UNION ALL and reach for UNION only when duplicates are genuinely possible and unwanted. Both require the same number of columns with compatible types, and column names come from the first SELECT. Nuances worth volunteering: UNION deduplicates across the inputs as well as within each one, so it can return fewer rows than either side alone; ORDER BY applies to the final combined result, not to individual branches; and if the two sides can never overlap — say, disjoint date ranges — UNION buys nothing but cost.

DELETE is DML: it removes rows one at a time (optionally filtered with WHERE), logs each removal, fires row-level triggers, and can be rolled back. TRUNCATE is DDL: it deallocates the table's data pages in one operation — much faster, minimally logged, no WHERE clause, typically resets identity/auto-increment counters, and refuses to run if foreign keys reference the table. DROP removes the table itself: data, structure, indexes, and privileges. The nuance interviewers probe: TRUNCATE is transactional and rollback-able in some engines (PostgreSQL, SQL Server) but triggers an implicit commit in others (MySQL, Oracle) — say 'it depends on the engine' rather than the folklore 'TRUNCATE cannot be rolled back'. Also: DELETE leaves the table's space fragmented; TRUNCATE returns it.

A primary key uniquely identifies each row: unique, NOT NULL, and one per table, though it may be composite (multi-column). A foreign key is a column (or columns) referencing a primary or unique key in another table, enforcing referential integrity: you cannot insert an order whose customer_id has no matching customer. ON DELETE / ON UPDATE clauses define what happens to children when the parent changes — CASCADE, SET NULL, or RESTRICT. Details that earn credit: a foreign key column may be NULL (an optional relationship) and may repeat; declaring a PK creates a unique index automatically, but most databases do NOT index foreign key columns for you, which makes joins and cascading deletes slow until you add one. Contrast surrogate keys (auto-increment ids) with natural keys.

NULL means unknown, and SQL uses three-valued logic: any comparison with NULL yields UNKNOWN, never TRUE — so both col = NULL and col <> NULL filter out every row. Test with IS NULL / IS NOT NULL, substitute defaults with COALESCE(col, fallback), and turn sentinels into NULL with NULLIF(a, b). The traps interviewers check: NOT IN against a subquery that returns even one NULL yields zero rows (use NOT EXISTS instead); COUNT(*) counts all rows but COUNT(col) skips NULLs, and AVG ignores them too, which silently changes denominators; NULLs never equal each other in join conditions, yet DISTINCT and GROUP BY treat them as a single group; and whether NULLs sort first or last under ORDER BY is engine-specific — use NULLS FIRST/LAST where supported rather than relying on the default.

FROM (with JOINs) runs first, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and finally LIMIT/OFFSET. Each stage only sees what earlier stages produced, and that single fact explains most 'why doesn't this work' errors: a SELECT alias cannot be used in WHERE or HAVING because SELECT has not run yet (most engines do allow aliases in ORDER BY, which runs after); WHERE cannot reference aggregates because grouping has not happened; and window functions are evaluated with SELECT, so you cannot filter on ROW_NUMBER() directly — wrap the query in a CTE or subquery first. The caveat that shows depth: this is the logical order, not the physical one. The optimizer freely reorders actual execution — pushing predicates down, reordering joins — as long as the result is identical.

All three number rows within an OVER (ORDER BY ...) window; they differ only in tie handling. ROW_NUMBER assigns unique sequential numbers, breaking ties arbitrarily — nondeterministically unless the ORDER BY is unique. RANK gives tied rows the same rank, then skips: 1, 1, 3. DENSE_RANK gives ties the same rank with no gaps: 1, 1, 2. Choosing correctly is the real interview question: 'top 3 salaries including ties' wants DENSE_RANK; 'exactly one row per group' (deduplication) wants ROW_NUMBER with PARTITION BY; leaderboard standings where a tie consumes places want RANK. And because window functions cannot appear in WHERE, filter through a subquery: SELECT * FROM (SELECT ..., ROW_NUMBER() OVER (PARTITION BY dept ORDER BY salary DESC) rn FROM emp) t WHERE rn = 1.

Cleanest: SELECT DISTINCT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) rk FROM employees) t WHERE rk = 2. DENSE_RANK matters: with salaries 90, 90, 80, RANK labels 80 as rank 3 and returns nothing for rank 2. The classic window-free alternative: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees) — it handles duplicates naturally and returns NULL when no second-highest exists. The LIMIT/OFFSET version (SELECT DISTINCT salary FROM employees ORDER BY salary DESC LIMIT 1 OFFSET 1) also works but returns an empty set rather than NULL when there is only one distinct salary — an edge case interviewers probe deliberately. Be ready to extend to 'Nth highest per department' by adding PARTITION BY department to the window.

GROUP BY collapses rows into one output row per group, and every selected column must be grouped or aggregated. A window function — an aggregate or ranking function with an OVER clause — computes over related rows while keeping every detail row, so you can show each order beside its customer's total: SUM(amount) OVER (PARTITION BY customer_id). PARTITION BY is the window analogue of GROUP BY; adding ORDER BY inside OVER makes the computation cumulative, and a frame clause (ROWS BETWEEN ...) defines sliding ranges for moving averages. Use GROUP BY when only the summary matters; use windows when you need detail and aggregate together — 'each employee's salary versus department average' in one pass, no self-join. Trap: window results cannot be filtered in WHERE; wrap in a subquery first.

Both produce an intermediate result for the outer query. A CTE (WITH clause) is named and defined up front, can be referenced several times in one statement, can chain — one CTE reading another — and is the only way to write recursive queries, walking org charts or graphs via WITH RECURSIVE. A subquery is inline: fine for one-off use, and the correlated form (referencing outer-query columns) has no CTE equivalent. The honest performance answer interviewers want: a CTE is primarily a readability tool, not an optimization — most engines inline CTEs exactly like subqueries. Some engines (PostgreSQL before v12, or with MATERIALIZED) evaluate a CTE once and reuse it, which can help repeated references or hurt by blocking predicate pushdown. Measure with EXPLAIN rather than assume.

IN tests membership against a literal list or subquery result; EXISTS tests whether a correlated subquery returns at least one row, short-circuiting on the first match. The semantic difference that decides interviews is NULL handling: NOT IN against a subquery that returns even one NULL yields zero rows — every comparison collapses to UNKNOWN — while NOT EXISTS behaves as expected, so for anti-joins prefer NOT EXISTS or explicitly exclude NULLs. On performance, modern optimizers usually rewrite both into the same semi-join plan, so 'EXISTS is always faster' is folklore; the durable advice is that EXISTS states 'does a matching row exist' more directly and more safely. One more detail: SELECT * inside EXISTS is fine — the engine checks only row existence and never materializes the columns.

A correlated subquery references columns from the outer query, so logically it re-executes once per outer row — e.g. employees paid above their department average: WHERE salary > (SELECT AVG(salary) FROM employees e2 WHERE e2.dept_id = e1.dept_id). It is the natural tool for per-row existence checks (EXISTS is almost always correlated) and compare-to-my-group logic. The cost model is the interview point: naively it is O(outer rows x inner scan), though optimizers often decorrelate it into a join, or the inner query stays cheap via an index. When slow, rewrite: compute per-group aggregates once in a CTE and join to it, or use a window — AVG(salary) OVER (PARTITION BY dept_id) yields the same comparison in a single pass. Showing that rewrite is worth more than reciting the definition.

SUM(amount) OVER (ORDER BY order_date ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) — a window aggregate whose frame grows row by row. Add PARTITION BY customer_id to restart the total per customer. The nuance that separates candidates: with ORDER BY alone the default frame is RANGE, not ROWS, and RANGE treats tied ORDER BY values as one unit — every row sharing a date shows the same running value, an apparent jump. Spelling out ROWS gives strict row-by-row accumulation; which is correct depends on whether ties should accumulate together, and per-row results are only deterministic when the ORDER BY is unique. Follow-ups to pre-empt: a moving average just changes the frame (ROWS BETWEEN 6 PRECEDING AND CURRENT ROW), and pre-window SQL required a self-join or correlated subquery.

Find them with GROUP BY on the defining columns: SELECT email, COUNT(*) FROM users GROUP BY email HAVING COUNT(*) > 1. Remove them while keeping one survivor per group with ROW_NUMBER() OVER (PARTITION BY email ORDER BY id), deleting rows where the number exceeds 1. In SQL Server you can DELETE from the CTE directly; in PostgreSQL or MySQL delete WHERE id IN (SELECT id FROM the numbered set WHERE rn > 1), since a CTE cannot be the delete target there. The ORDER BY inside the window is your keep policy — oldest id, or most recently updated — and interviewers like hearing that stated explicitly. Then say the part that shows judgment: deletion fixes the symptom; the cure is a UNIQUE constraint or unique index on the key so duplicates cannot return, added right after the cleanup.

A clustered index defines the physical order of the table — the index's leaf level IS the row data — so each table can have exactly one. In SQL Server the primary key clusters by default, and MySQL's InnoDB always clusters on the primary key. A non-clustered index is a separate B-tree whose leaves hold key values plus a locator (a row pointer, or the clustering key), and you can create many. Consequences worth naming: range scans along the clustering key are fast; every secondary-index lookup on a clustered table takes an extra hop through the clustering key, so a wide primary key bloats every other index; and a covering non-clustered index (with INCLUDE columns) can answer a query without touching the table. The trade-off: every additional index taxes INSERT, UPDATE, DELETE and storage.

LAG(col, n, default) reads the value n rows before the current row within the window's ordering; LEAD reads ahead. Both require OVER (ORDER BY ...), usually with PARTITION BY: month-over-month change is amount - LAG(amount) OVER (PARTITION BY product ORDER BY month). The optional third argument substitutes a default for NULL at partition edges — the first row has no predecessor, and forgetting that NULL silently drops the first period from delta calculations. Canonical uses: period-over-period comparisons; gap detection, where LEAD(date) minus date greater than 1 exposes missing entries; and sessionization — flag a new session when the gap since LAG(event_time) exceeds 30 minutes, then running-SUM the flags to number sessions. Pre-window SQL needed an awkward self-join on row position.

For a bare deduplication — SELECT DISTINCT city FROM users versus GROUP BY city — optimizers typically produce identical plans, so 'GROUP BY is faster' is not a real rule. The functional difference: GROUP BY defines groups you can aggregate over (COUNT, SUM, MAX per group), while DISTINCT only removes duplicate result rows. Use DISTINCT to state intent when no aggregate is needed; switch to GROUP BY the moment a count or sum enters. Related traps interviewers reach for: DISTINCT applies to the entire selected row, not just the column it visually sits beside; COUNT(DISTINCT col) combines both ideas and can be expensive; and selecting a column that is neither aggregated nor in the GROUP BY is an error in most engines — and was historically allowed but nondeterministic in older MySQL.

Normalization stores each fact exactly once, eliminating redundancy and the anomalies it breeds: update anomalies (an address stored in five places, one missed), insert anomalies (no way to record a course with zero students), delete anomalies (removing the last enrolment erases the course). The ladder: 1NF — atomic values, no repeating groups; 2NF — no partial dependency on part of a composite key; 3NF — no transitive dependencies between non-key columns. Mnemonic: every attribute depends on the key, the whole key, and nothing but the key. The trade-off interviewers fish for: OLTP systems normalize to protect write integrity; analytics deliberately denormalizes into star schemas — fewer joins, faster scans — because redundancy is acceptable where data is written once and read many times.

Atomicity: a transaction is all-or-nothing — if any statement fails, everything rolls back, so a transfer never debits without crediting. Consistency: a transaction moves the database from one valid state to another, honoring constraints, keys and triggers. Isolation: concurrent transactions behave as if run serially — in practice a spectrum of isolation levels (READ COMMITTED, REPEATABLE READ, SERIALIZABLE) trading anomalies such as dirty, non-repeatable and phantom reads against throughput. Durability: once committed, data survives crashes, typically via write-ahead logging. Interview depth lives in isolation: know your engine's default (PostgreSQL and SQL Server: READ COMMITTED; MySQL InnoDB: REPEATABLE READ) and contrast ACID with the BASE/eventual-consistency model of NoSQL stores.

Start with evidence: EXPLAIN (ANALYZE where available) shows the actual plan — look for full scans on large tables, misestimated row counts, and nested-loop joins fed huge inputs. Then, roughly in order of payoff: add or fix indexes on WHERE, JOIN and ORDER BY columns — composite-index column order matters (equality before range); make predicates sargable — WHERE YEAR(created_at) = 2026 defeats an index while a half-open date range uses it, as do leading-wildcard LIKEs and implicit casts; select only needed columns so a covering index can serve the query; replace correlated subqueries with joins or window functions; refresh statistics. Finally ask whether the real fix is upstream — pagination, caching, or a precomputed materialized aggregate — and re-measure after every change.

The portable technique is conditional aggregation — one expression per target column, grouped by the row key: SELECT product, SUM(CASE WHEN quarter = 'Q1' THEN revenue END) AS q1, SUM(CASE WHEN quarter = 'Q2' THEN revenue END) AS q2 FROM sales GROUP BY product. CASE without ELSE yields NULL, which aggregates ignore, so each SUM sees only its own slice; COUNT or MAX work the same way, and COALESCE(..., 0) turns the NULLs into zeros. SQL Server and Oracle offer a PIVOT operator and PostgreSQL has crosstab(), but all share one limitation worth stating unprompted: output columns must be known when the query is written — a dynamic column count requires dynamically building the SQL. Mentioning the reverse operation, UNPIVOT (columns back into rows), pre-empts the standard follow-up.

A regular view is a stored query: it holds no data, runs (or is merged) each time it is referenced, and always reflects live data. A materialized view physically stores the query's result, so reads are fast — often index-backed — but the data is only as fresh as its last refresh. Refresh is the interview substance: complete (recompute all) or incremental where the engine supports change tracking, scheduled or on-commit — each trades staleness against load. Use regular views to encapsulate logic and control column-level access; use materialized views to precompute expensive joins and aggregates for dashboards. Classic follow-up: PostgreSQL needs REFRESH MATERIALIZED VIEW (CONCURRENTLY to avoid blocking readers); SQL Server's analogue is an indexed view, maintained automatically.