SQL interview questions

WHERE filters rows before grouping. HAVING filters groups after GROUP BY, so it can use aggregates like SUM or COUNT.

FROM and JOIN, WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, then TOP or LIMIT. That is why a SELECT alias cannot be used in WHERE.

DELETE removes chosen rows and supports WHERE. TRUNCATE quickly removes all rows but keeps the table. DROP removes the table completely.

A primary key identifies each row, cannot be NULL and there is only one per table. A table can have several unique keys, and they usually allow NULL.

NULL means unknown, so NULL = NULL is not true. Use IS NULL. COUNT(column) skips NULLs, COUNT(*) counts every row.

UNION removes duplicates (extra work). UNION ALL keeps every row and is faster.

INNER returns matching rows only. LEFT returns all left rows plus matches. FULL returns all rows from both sides, with NULLs where there is no match.

LEFT JOIN orders and keep rows where the order side is NULL, or use NOT EXISTS.

SELECT c.*
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
WHERE o.order_id IS NULL;

A WHERE filter on a right-table column removes the NULL rows. Move that condition into the ON clause.

EXISTS stops at the first match and handles NULLs safely. NOT IN returns nothing if the subquery has a NULL, so prefer NOT EXISTS.

Group by the columns that should be unique and keep groups with COUNT(*) > 1.

SELECT email, COUNT(*)
FROM customers
GROUP BY email
HAVING COUNT(*) > 1;

Use DENSE_RANK so ties are handled correctly.

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

For ties: ROW_NUMBER gives 1,2,3. RANK gives 1,1,3. DENSE_RANK gives 1,1,2.

ROW_NUMBER partitioned by customer, ordered by date descending, keep row 1.

SUM as a window function ordered by date.

SUM(amount) OVER (ORDER BY order_date ROWS UNBOUNDED PRECEDING)

A named subquery written with WITH. It makes long queries readable and can be recursive.

A subquery that uses a value from the outer query, so it runs per row. A join or window function is often faster.

A sorted structure that lets the database find rows without scanning the whole table. It speeds up reads and slightly slows writes.

A filter that can use an index. Wrapping the column in a function, like YEAR(order_date) = 2026, usually breaks it. Use a date range instead.

A central fact table with measures, joined to dimension tables like date, customer and product. It is simple and fast for reporting.