SQL interview questions

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.

A common table expression, introduced with WITH, names an intermediate result so the main query reads like a pipeline of steps. Functionally it overlaps with a derived-table subquery, but a CTE is more readable, can be referenced more than once, and is the only way to express recursion, such as walking an org chart or a category tree. Interviewers often accept either but read CTE usage as a sign you write SQL other people have to maintain. Note that some engines materialise CTEs while others inline them, which can matter for performance.

WITH monthly AS (
  SELECT customer_id,
         DATE_TRUNC('month', order_date) AS month,
         SUM(amount) AS total
  FROM orders
  GROUP BY customer_id, DATE_TRUNC('month', order_date)
)
SELECT customer_id, AVG(total) AS avg_monthly_spend
FROM monthly
GROUP BY customer_id;

The standard pattern is ROW_NUMBER() partitioned by the group and ordered by the timestamp descending, then filtering to row number 1 in an outer query or CTE. It expresses 'each customer's most recent order' in one pass and generalises to top-N by changing the filter. Alternatives exist, like a join against a MAX subquery or DISTINCT ON in PostgreSQL, but the window-function version is portable and handles ties predictably if you switch to RANK. This is among the most common practical SQL interview tasks.

WITH ranked AS (
  SELECT o.*,
         ROW_NUMBER() OVER (
           PARTITION BY customer_id
           ORDER BY order_date DESC
         ) AS rn
  FROM orders o
)
SELECT * FROM ranked WHERE rn = 1;

Both stack the results of two queries with compatible columns. UNION removes duplicate rows across the combined result, which requires sorting or hashing everything and costs accordingly. UNION ALL keeps every row, duplicates included, and is therefore cheaper. The interview signal is defaulting to UNION ALL unless you specifically need deduplication, and being able to say why: asking the engine to dedupe millions of rows you already know are distinct is a silent performance bug that shows up in real pipelines.

A transaction groups statements so they succeed or fail as a unit: BEGIN, then COMMIT to keep the changes or ROLLBACK to discard them. ACID summarises the guarantees. Atomicity: all or nothing. Consistency: the database moves between valid states, with constraints holding. Isolation: concurrent transactions do not see each other's half-done work, tunable via isolation levels. Durability: once committed, changes survive a crash. The classic example is a money transfer, where debiting one account and crediting another must never be split, and a strong answer also names the isolation-level tradeoffs.