Interview questions by language
Joins, aggregation, window functions, indexes, and query optimisation.
What it really tests
A SQL interview is usually hands-on: you are given a schema and asked to write queries, escalating from simple filters to multi-table joins, aggregation, and window functions. Interviewers are checking three things: can you express the business question correctly in set-based logic, do you understand how the engine will execute your query, and do you reach for the right tool (a window function rather than a self-join, a CTE rather than a nested subquery) to keep the query readable and fast. SQL is declarative, so the seniority signal is knowing what the optimiser does with what you wrote.
The conceptual backbone is joins and grouping. You must be fluent in INNER vs LEFT vs FULL joins and what a join does to row counts, and you must understand the difference between WHERE (filters rows before grouping) and HAVING (filters groups after aggregation). A surprising number of candidates lose the offer on a subtle LEFT JOIN that silently drops rows because a filter on the right table was placed in WHERE instead of the ON clause.
The advanced tier is window functions and performance. Window functions (ROW_NUMBER, RANK, LAG/LEAD, running totals with SUM OVER) let you compute per-row analytics without collapsing rows, and they are the single most common 'separates juniors from seniors' SQL topic. On performance, you should be able to explain what an index is, why a query on an unindexed column is slow, what a covering index is, and how to read an execution plan to find a full table scan.
SQL is tested in almost every data, analytics, and backend interview across the US and EU, and is the core screen for data-engineer and analytics-engineer roles; it is the most broadly applicable technical skill on this list.
The map
INNER vs LEFT vs FULL, how joins affect row counts, and the ON-vs-WHERE trap that silently drops rows in outer joins.
GROUP BY, the WHERE-vs-HAVING distinction, and aggregate functions. The most common source of subtle wrong answers.
ROW_NUMBER, RANK, LAG/LEAD, running totals, and PARTITION BY. The top 'senior SQL' signal because they replace clumsy self-joins.
How indexes speed lookups, covering indexes, and reading an execution plan to spot a full table scan or a missing index.
Question bank
The questions that come up most often, each with a sample answer you can adapt into your own words. Read them out loud until the explanation feels natural.
An INNER JOIN returns only rows that match in both tables. A LEFT JOIN returns every row from the left table and the matching rows from the right, filling NULLs where there is no match. Use a LEFT JOIN when you need to keep all left-side rows regardless of whether a related right-side row exists, for example all customers including those with no orders.
WHERE filters individual rows before grouping and cannot reference aggregate functions. HAVING filters groups after GROUP BY and can use aggregates like COUNT or SUM. So you use WHERE to restrict which rows enter the aggregation and HAVING to restrict which aggregated groups appear in the result.
A window function computes a value across a set of rows related to the current row without collapsing them into one, using the OVER clause. Examples are ROW_NUMBER() for ranking within a partition, LAG/LEAD to access previous or next rows, and SUM(...) OVER (...) for running totals. Use one whenever you need per-row analytics like 'each employee's salary rank within their department' that a GROUP BY would flatten away.
The cleanest modern approach uses a window function: SELECT salary FROM (SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk FROM employees) t WHERE rnk = 2. DENSE_RANK handles ties correctly. A classic alternative is a correlated subquery or SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees), though that does not generalise to the Nth value as cleanly.
An index is an auxiliary data structure (usually a B-tree) that lets the engine find rows by a column's value without scanning the whole table, turning a lookup from O(n) into roughly O(log n). The tradeoff is that indexes consume storage and slow down writes, because every INSERT, UPDATE, and DELETE must also maintain the index. So you index columns used in WHERE, JOIN, and ORDER BY, but do not index everything.
DELETE removes rows (optionally filtered by WHERE), is logged row by row, and can be rolled back. TRUNCATE removes all rows quickly by deallocating pages, is minimally logged, resets identity counters, and cannot be filtered. DROP removes the entire table including its structure. In short: DELETE for selective removal, TRUNCATE to empty a table fast, DROP to delete the table itself.
Both enforce uniqueness, but a primary key additionally disallows NULLs and there can be only one per table; it is the canonical row identifier and typically the clustered index. A unique constraint enforces uniqueness on a column or set of columns, permits one NULL (in most databases), and you can have several per table. Use the primary key for the row's identity and unique constraints for other naturally unique columns like email.
Run EXPLAIN (or EXPLAIN ANALYZE) and look for the expensive operations: a full table scan or sequential scan on a large table usually means a missing index on the filtered or joined column. Check the join order and method (hash vs nested loop) and the estimated vs actual rows, since a big mismatch points to stale statistics. The common fixes are adding or adjusting an index, rewriting to avoid functions on indexed columns, and updating table statistics.
Avoid these
The plan
An external resource we recommend. Educative is not affiliated with us and we earn nothing from this link.
Apply it to a role
Keep exploring
Language fluency gets you through the screen. Pair it with the coding interview patterns, rehearse the behavioral round with worked STAR answers, and when the offer comes, check it is competitive with the salary comparison tool.