Intermediate SQL Interview Questions and Answers

A subquery that refers to a column of the outer query, so it is evaluated once per outer row. Example: employees who earn more than their own department's average:

SELECT name, department, salary
FROM employees e
WHERE salary > (
  SELECT AVG(salary)
  FROM employees e2
  WHERE e2.department = e.department
);

name  | department  | salary
------+-------------+-------
Meera | Engineering | 81000
Priya | Sales       | 61000

See Subqueries.

Both filter rows based on another query. IN compares a value with a list; EXISTS checks whether a correlated subquery returns any row. The important practical difference is with negation: NOT IN returns no rows if the subquery yields a NULL, while NOT EXISTS is unaffected. Modern optimisers often execute IN and EXISTS similarly. See EXISTS and IN.

Both stack the results of queries with the same number of columns. UNION removes duplicate rows, which requires extra work; UNION ALL keeps every row and is faster. Use UNION ALL unless you specifically need de-duplication, and always when the result will be counted or summed. See UNION ALL.

A Common Table Expression (WITH name AS (...)) is a named, temporary result used within one statement. It makes multi-step queries readable top to bottom, can be referenced more than once, and can be recursive for hierarchies. See CTEs.

A view is a saved query that can be queried like a table. It simplifies complex logic, standardises definitions, and can hide sensitive columns. A normal view does not store data or make queries faster; it re-runs its query every time. A materialized view stores the result and must be refreshed. See Views.

An index is a sorted structure (usually a B-tree) that lets the database find rows without scanning the whole table. It speeds up filters, joins and sorts on the indexed columns, but slows down inserts, updates and deletes, and uses storage. Composite indexes are used from the leftmost column. See Indexes.

LEFT JOIN from customers, and count a column from orders, not *.

SELECT c.name, COUNT(o.order_id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY orders DESC, c.name;

name  | orders
------+-------
Asha  | 2
Ravi  | 1
John  | 0
Meera | 0

COUNT(*) would report 1 for John and Meera, because the left join still produces a row for them.

SELECT c.name
FROM customers c
WHERE NOT EXISTS (
  SELECT 1
  FROM orders o
  WHERE o.customer_id = c.customer_id
    AND o.order_date >= '2026-02-01'
);

name
-----
Meera
John

Asha (order on 11 February) and Ravi (20 February) are excluded. This "inactive customers" pattern is common in retention analysis.

Conditional aggregation: SUM(CASE WHEN region = 'North' THEN revenue ELSE 0 END).

SELECT month,
       SUM(CASE WHEN region = 'North' THEN revenue ELSE 0 END) AS north,
       SUM(CASE WHEN region = 'South' THEN revenue ELSE 0 END) AS south
FROM monthly_sales
GROUP BY month
ORDER BY month;

month   | north | south
--------+-------+------
2026-01 | 120   | 90
2026-02 | 150   | 90
2026-03 | 110   | 130
2026-04 | 170   | 125

See CASE.

With a correlated subquery:

SELECT p.category, p.name, p.price
FROM products p
WHERE p.price = (
  SELECT MAX(p2.price)
  FROM products p2
  WHERE p2.category = p.category
)
ORDER BY p.category;

category    | name       | price
------------+------------+------
Electronics | Laptop     | 55000
Furniture   | Desk Chair | 7000
Stationery  | Notebook   | 60

Be ready to also write it with ROW_NUMBER() or RANK() (see Advanced SQL Questions), and to explain that this version returns every product tied for the top price.

-- SQLite; use DATE_TRUNC('month', order_date) in PostgreSQL
SELECT strftime('%Y-%m', order_date) AS month,
       COUNT(DISTINCT customer_id) AS active_customers
FROM orders
GROUP BY month
ORDER BY month;

month   | active_customers
--------+-----------------
2026-01 | 1
2026-02 | 2
2026-03 | 1

COUNT(DISTINCT ...) matters: a customer with two orders in a month is still one active customer.