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.

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.

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 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.

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.