SQL Interview Questions and Answers

INNER JOIN returns rows with matches in both tables. LEFT JOIN returns all rows from the left table plus matches from the right, with NULLs where there is no match; RIGHT JOIN is the mirror image. FULL OUTER JOIN returns all rows from both sides. CROSS JOIN returns every combination of rows. A self join joins a table to itself, for example employees to their managers.

WHERE filters individual rows before grouping and cannot use aggregate functions. HAVING filters groups after GROUP BY and can use aggregates such as COUNT or SUM. Put as much filtering as possible in WHERE so fewer rows are aggregated.

SELECT customer_id, SUM(amount) AS total
FROM orders
WHERE status = 'paid'
GROUP BY customer_id
HAVING SUM(amount) > 1000;

Logically, a query is processed as FROM and JOINs, WHERE, GROUP BY, HAVING, SELECT (including window functions), DISTINCT, ORDER BY, and finally LIMIT/OFFSET. That is why a column alias defined in SELECT cannot be used in WHERE, but can usually be used in ORDER BY. The optimiser may physically execute steps differently, but results must match this order.

DELETE removes selected rows, can use a WHERE clause, fires triggers and is fully logged. TRUNCATE removes all rows quickly by deallocating storage, usually resets identity counters and cannot filter. DROP removes the table structure itself. In PostgreSQL and SQL Server TRUNCATE is transactional and can be rolled back; in MySQL it causes an implicit commit.

A primary key uniquely identifies each row and cannot be NULL; a table has one. A foreign key is a column that references a primary or unique key in another table, enforcing referential integrity so you cannot insert orphaned rows. ON DELETE options such as CASCADE, SET NULL or RESTRICT define what happens when the referenced row is deleted.

NULL means unknown, so any comparison with NULL, including NULL = NULL, yields unknown rather than true, and the row is filtered out. Use IS NULL and IS NOT NULL instead. Aggregates such as SUM and AVG ignore NULLs, while COUNT(*) counts rows and COUNT(column) counts non-NULL values. COALESCE returns the first non-NULL argument. NOT IN with a subquery that returns any NULL returns no rows, a classic trap.

Both combine the results of two queries with compatible columns. UNION removes duplicate rows, which requires a sort or hash step; UNION ALL keeps all rows and is faster. Use UNION ALL unless you specifically need de-duplication.

Window functions compute a value across a set of rows related to the current row without collapsing them into one row, unlike GROUP BY. They use OVER with optional PARTITION BY and ORDER BY clauses and frame specifications. Common ones are ROW_NUMBER, RANK, DENSE_RANK, LAG, LEAD, and aggregates like SUM used as running totals.

SELECT employee_id, department_id, salary,
       RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS dept_rank
FROM employees;

ROW_NUMBER gives each row a unique sequential number, breaking ties arbitrarily unless the ORDER BY is unique. RANK gives tied rows the same rank and leaves gaps afterwards (1, 1, 3). DENSE_RANK gives tied rows the same rank without gaps (1, 1, 2). Choose based on whether ties should share a position and whether gaps matter.

A common table expression (WITH name AS (...)) defines a named temporary result used by the following statement. CTEs make complex queries readable by breaking them into steps, and recursive CTEs traverse hierarchies such as org charts or category trees. Modern PostgreSQL inlines simple CTEs into the main query, so they usually have no performance penalty.

WITH RECURSIVE reports AS (
  SELECT id, manager_id, name FROM employees WHERE id = 1
  UNION ALL
  SELECT e.id, e.manager_id, e.name
  FROM employees e JOIN reports r ON e.manager_id = r.id
)
SELECT * FROM reports;

A non-correlated subquery runs independently of the outer query, conceptually once. A correlated subquery references columns from the outer query, so it is logically evaluated per outer row. Optimisers often rewrite correlated subqueries into joins, but EXISTS with a correlated subquery is a clear, efficient way to test for related rows.

Most indexes are B-trees that keep column values sorted with pointers to rows, turning full table scans into logarithmic lookups and supporting range queries and ORDER BY. They cost storage and slow down inserts, updates and deletes, since every index must be maintained. Index columns used in selective WHERE filters, joins and sorts, and avoid indexing everything.

A composite index covers several columns, sorted by the first column, then the second within it, and so on. It can serve queries that filter on a leftmost prefix of its columns, so an index on (customer_id, created_at) helps WHERE customer_id = ? AND created_at > ? but not a filter on created_at alone. Put equality-filtered columns first and range-filtered columns after them.

Normalization organises tables to reduce redundancy and update anomalies. 1NF requires atomic values and no repeating groups. 2NF additionally requires every non-key column to depend on the whole of a composite key. 3NF additionally removes transitive dependencies, so non-key columns depend only on the key. Analytical schemas often denormalize deliberately for read performance.

EXPLAIN shows the plan the optimiser chose: scan types (sequential scan, index scan, index-only scan), join algorithms (nested loop, hash join, merge join), estimated row counts and costs. EXPLAIN ANALYZE in PostgreSQL actually runs the query and adds real timings and row counts. Large differences between estimated and actual rows usually point to stale statistics.

Atomicity: a transaction's changes all apply or none do. Consistency: a transaction moves the database from one valid state to another, respecting constraints. Isolation: concurrent transactions do not see each other's partial work, to a degree set by the isolation level. Durability: once committed, changes survive crashes, typically via a write-ahead log.

Read Uncommitted allows dirty reads. Read Committed prevents dirty reads but allows non-repeatable reads. Repeatable Read prevents non-repeatable reads; the standard still allows phantom rows, though PostgreSQL's snapshot implementation prevents them. Serializable makes concurrent transactions behave as if run one at a time. PostgreSQL and SQL Server default to Read Committed, while MySQL InnoDB defaults to Repeatable Read.

Use pessimistic locking with SELECT ... FOR UPDATE to lock rows until the transaction ends, or optimistic locking with a version column checked in the UPDATE's WHERE clause and retried on conflict. An atomic statement such as UPDATE accounts SET balance = balance - 100 WHERE id = 1 AND balance >= 100 avoids the read-then-write race entirely.

A covering index contains every column a query needs, so the database can answer from the index alone without reading the table rows (an index-only scan). PostgreSQL and SQL Server support INCLUDE to add non-key columns to an index for this purpose. It speeds up hot read queries at the cost of a larger index.

OFFSET n makes the database produce and discard n rows before returning results, so deep pages get progressively slower, and inserted rows shift pages. Keyset (cursor) pagination filters on the last seen sort key, such as WHERE (created_at, id) < (?, ?) ORDER BY created_at DESC, id DESC LIMIT 20, which uses an index and stays fast at any depth.

DENSE_RANK handles ties correctly and generalises to the Nth highest. An alternative is selecting the MAX salary below the overall MAX. Clarify whether ties count as one value and what to return if there is no second salary.

SELECT DISTINCT salary
FROM (
  SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
  FROM employees
) ranked
WHERE rnk = 2;

Group by the column and keep groups with more than one row. To delete duplicates while keeping one row, number the rows per email with ROW_NUMBER and delete those with a number above one.

SELECT email, COUNT(*) AS n
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

Rank within each partition, then filter in an outer query, because window functions cannot appear in WHERE. Use ROW_NUMBER for exactly three rows or DENSE_RANK to include ties.

SELECT category, product, revenue
FROM (
  SELECT category, product, SUM(amount) AS revenue,
         ROW_NUMBER() OVER (PARTITION BY category ORDER BY SUM(amount) DESC) AS rn
  FROM sales
  GROUP BY category, product
) t
WHERE rn <= 3;

Aggregate per day first, then apply SUM as a window function ordered by day. Specify the frame explicitly with ROWS for a strict row-by-row running sum; the default RANGE frame groups rows with equal ordering values.

SELECT day, revenue,
       SUM(revenue) OVER (ORDER BY day ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS running_total
FROM (
  SELECT CAST(created_at AS DATE) AS day, SUM(amount) AS revenue
  FROM orders
  GROUP BY CAST(created_at AS DATE)
) daily
ORDER BY day;

Use NOT EXISTS or a LEFT JOIN with an IS NULL check. Avoid NOT IN against a column that may contain NULLs, because a single NULL makes the predicate unknown for every row and returns nothing.

SELECT c.id, c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.id
);