SQL Interview Questions

Think about when each one runs relative to GROUP BY.

WHERE filters individual rows before they are grouped. HAVING filters groups after GROUP BY and aggregate functions have been calculated.

SELECT department, AVG(salary) AS avg_salary
FROM employees
WHERE city <> 'Delhi'          -- removes rows
GROUP BY department
HAVING AVG(salary) > 50000;    -- removes groups

Because of this order, you cannot use an aggregate like COUNT(*) in WHERE.

What happens to a row in the first table that has no match?

• INNER JOIN returns only rows that have a match in both tables.
• LEFT JOIN returns every row from the left (first) table. Where there is no match, the right table's columns are NULL.

A good follow-up to mention: conditions on the right table in a WHERE clause turn a LEFT JOIN into an inner join in practice, because they remove the NULL rows. Put such conditions in the ON clause instead.

Which of them ignore NULL values?

• COUNT(*) counts rows, including rows where every column is NULL.
• COUNT(column) counts rows where that column is not NULL.
• COUNT(DISTINCT column) counts the unique non-NULL values in the column.

After a LEFT JOIN, this difference matters: COUNT(*) reports 1 for an unmatched row, while COUNT(right_table.id) correctly reports 0.

The second highest salary is the highest salary that is less than the maximum. Watch out for duplicate salaries.

A portable solution using a subquery:

SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);

It handles ties at the top correctly (two people earning the maximum do not count as first and second), and returns NULL when there is no second value.

With window functions you can generalise to the N-th highest:

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 that have more than one row.

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

If emails may differ only by letter case, group by LOWER(email) instead.

Join customers to orders in a way that keeps customers without a match, then keep only those.

Using a LEFT JOIN anti-join:

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

Or with NOT EXISTS, which many people find clearer:

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

Avoid NOT IN (SELECT customer_id FROM orders): if that subquery returns even one NULL, NOT IN returns no rows at all.

It is not the order you write them in. SELECT comes surprisingly late.

The logical order is:

1. FROM (and JOINs)
2. WHERE
3. GROUP BY
4. HAVING
5. SELECT
6. DISTINCT
7. ORDER BY
8. LIMIT / OFFSET (or TOP / FETCH)

This explains common errors, such as why a column alias defined in SELECT cannot be used in WHERE but can be used in ORDER BY. The database's optimiser may physically execute things differently, but the result is as if this order were followed.

One removes some rows, one removes all rows, and one removes the table itself.

Whether TRUNCATE can be rolled back depends on the database (it can in PostgreSQL and SQL Server inside a transaction; in MySQL and Oracle it commits implicitly).

One of them does extra work to remove something.

Both stack the results of two queries on top of each other (the queries must return the same number of columns, with compatible types).

• UNION removes duplicate rows from the combined result.
• UNION ALL keeps every row, including duplicates.

Because removing duplicates needs a sort or a hash step, UNION ALL is faster. Use UNION ALL whenever duplicates are impossible or wanted, and UNION only when you really need them removed.

What is the result of comparing an unknown value with anything?

NULL means "unknown". Any comparison with NULL, even NULL = NULL, gives unknown, not true, and WHERE keeps only rows where the condition is true.

SELECT * FROM employees WHERE manager_id = NULL;   -- always 0 rows
SELECT * FROM employees WHERE manager_id IS NULL;  -- correct

Related points interviewers like to hear:

• Aggregates such as SUM, AVG and COUNT(column) ignore NULLs.
• COALESCE(column, default) replaces NULL with a value.
• NOT IN with a list or subquery that contains a NULL returns no rows.

Think about NULLs and how many of each a table can have.

Both are enforced with an index, so lookups on either column are fast.

The difference only shows up when two rows tie.

All three number rows in the order given by OVER (ORDER BY ...). For scores 95, 92, 92, 88:

• ROW_NUMBER always gives unique numbers (ties are broken arbitrarily unless you add a tie-breaker column).
• RANK gives ties the same number and then skips numbers.
• DENSE_RANK gives ties the same number and does not skip.

Use ROW_NUMBER to pick exactly one row per group, and RANK or DENSE_RANK when ties should be treated equally.

Number the rows within each department, then filter on that number. You cannot filter a window function in WHERE directly.

WITH ranked AS (
  SELECT department, name, salary,
         ROW_NUMBER() OVER (
           PARTITION BY department
           ORDER BY salary DESC
         ) AS rn
  FROM employees
)
SELECT department, name, salary
FROM ranked
WHERE rn <= 2
ORDER BY department, salary DESC;

PARTITION BY department restarts the numbering for each department. If employees with equal salaries should both be included, use DENSE_RANK() instead of ROW_NUMBER().

An aggregate function can be used with OVER and an ORDER BY.

SELECT sale_date, revenue,
       SUM(revenue) OVER (ORDER BY sale_date) AS running_total
FROM daily_sales
ORDER BY sale_date;

With ORDER BY inside OVER, the sum covers all rows from the first one up to the current row. Add PARTITION BY region to restart the running total for each region. Before window functions existed, this needed a slow correlated subquery or self join.

The manager is also a row in the employees table. Join the table to itself.

SELECT e.name AS employee, e.salary,
       m.name AS manager, m.salary AS manager_salary
FROM employees e
JOIN employees m ON e.manager_id = m.id
WHERE e.salary > m.salary;

This is a self join: the same table appears twice with different aliases, one for the employee and one for the manager. An inner join is right here, because an employee without a manager cannot earn more than one.

Can the subquery run on its own, without the outer query?

A correlated subquery refers to a column of the outer query, so it is (logically) evaluated once for every outer row:

-- employees paid more than the average of their own department
SELECT e.name, e.department, e.salary
FROM employees e
WHERE e.salary > (
  SELECT AVG(e2.salary)
  FROM employees e2
  WHERE e2.department = e.department   -- refers to the outer row
);

A normal (non-correlated) subquery, such as (SELECT AVG(salary) FROM employees), can run on its own and is evaluated once. Correlated subqueries are powerful but can be slow on large tables; the same result can often be written with a join to a grouped subquery or with AVG(salary) OVER (PARTITION BY department).

Think about NOT IN and NULL values, and about whether you need the subquery's values at all.

IN checks whether a value is in a list; EXISTS checks whether a subquery returns any row.

• For positive checks (IN vs EXISTS), modern optimisers usually produce the same plan, so choose the clearer one.
• For negative checks, prefer NOT EXISTS. If the subquery of NOT IN returns a single NULL, the condition is never true and the query returns nothing:

-- returns no rows if any orders.customer_id is NULL
SELECT name FROM customers
WHERE customer_id NOT IN (SELECT customer_id FROM orders);

-- always correct
SELECT name FROM customers c
WHERE NOT EXISTS (
  SELECT 1 FROM orders o WHERE o.customer_id = c.customer_id
);

EXISTS can also stop at the first matching row, because it only needs to know that one exists.

Combine GROUP BY with an aggregate of a CASE expression.

Use conditional aggregation. For example, the number of orders per month for each status, with one column per status:

SELECT order_month,
       SUM(CASE WHEN status = 'delivered' THEN 1 ELSE 0 END) AS delivered,
       SUM(CASE WHEN status = 'cancelled' THEN 1 ELSE 0 END) AS cancelled,
       SUM(CASE WHEN status = 'returned'  THEN 1 ELSE 0 END) AS returned
FROM orders
GROUP BY order_month
ORDER BY order_month;

Each CASE returns 1 for the rows that belong in that column, and SUM counts them. This works in every database. Some databases also have a PIVOT operator (SQL Server, Oracle) or FILTER (WHERE ...) (PostgreSQL), but the CASE version is portable.

Number the rows within each group of duplicates and delete every row whose number is greater than 1.

Assuming each row has a unique id and duplicates share the same email:

DELETE FROM users
WHERE id IN (
  SELECT id
  FROM (
    SELECT id,
           ROW_NUMBER() OVER (PARTITION BY email ORDER BY id) AS rn
    FROM users
  ) AS numbered
  WHERE rn > 1
);

This keeps the row with the smallest id for each email. A version without window functions:

DELETE FROM users
WHERE id NOT IN (
  SELECT keep_id
  FROM (SELECT MIN(id) AS keep_id FROM users GROUP BY email) AS keepers
);

Run the inner SELECT first to check which rows will be deleted, do it inside a transaction, and afterwards add a UNIQUE constraint so duplicates cannot come back.

If you subtract a row number (in days) from each login date, consecutive dates produce the same result.

This is the classic "gaps and islands" problem. With one row per login in logins(user_id, login_date), in PostgreSQL:

WITH days AS (
  SELECT DISTINCT user_id, login_date
  FROM logins
),
numbered AS (
  SELECT user_id, login_date,
         login_date - CAST(ROW_NUMBER() OVER (
           PARTITION BY user_id ORDER BY login_date
         ) AS INTEGER) AS grp
  FROM days
)
SELECT DISTINCT user_id
FROM numbered
GROUP BY user_id, grp
HAVING COUNT(*) >= 3;

For logins on 1, 2, 3 and 7 June, the row numbers are 1, 2, 3, 4, and "date minus row number" gives 31 May, 31 May, 31 May and 3 June. The three consecutive days share the same grp, forming an "island" of size 3.

Date arithmetic differs by database: in PostgreSQL date - integer subtracts days; MySQL uses DATE_SUB(login_date, INTERVAL rn DAY) and SQL Server DATEADD(day, -rn, login_date).

Which one stores data?

• A view is a saved query. It stores no data; every time you select from it, the underlying query runs, so the results are always current.
• A materialized view stores the query's result on disk, like a table. Reading it is fast, but the data is only as fresh as the last refresh (REFRESH MATERIALIZED VIEW in PostgreSQL).

Use views to simplify and control access to data (for example, to hide sensitive columns). Use materialized views for expensive reports where slightly stale data is acceptable. Support varies: PostgreSQL and Oracle have materialized views, SQL Server has indexed views, and MySQL has neither built in.

One of them decides the physical order of the table's rows.

• A clustered index stores the table's rows themselves in the index order. Because rows can only be stored in one order, a table has at most one clustered index. In SQL Server and MySQL (InnoDB), the primary key is clustered by default.
• A non-clustered (secondary) index is a separate structure holding the indexed values and a pointer to each row (its address or its clustered key). A table can have many.

Range queries on the clustered key (WHERE order_date BETWEEN ...) are very efficient because matching rows are stored together. A lookup through a non-clustered index needs an extra step to fetch the row, unless the index covers every column the query needs.

A recursive CTE starts with the manager's direct reports and repeatedly joins to find their reports.

WITH RECURSIVE reports AS (
  -- anchor: direct reports of manager 1
  SELECT id, name, manager_id, 1 AS level
  FROM employees
  WHERE manager_id = 1

  UNION ALL

  -- recursive step: reports of the people found so far
  SELECT e.id, e.name, e.manager_id, r.level + 1
  FROM employees e
  JOIN reports r ON e.manager_id = r.id
)
SELECT id, name, level
FROM reports
ORDER BY level, name;

The recursion stops when a step finds no new rows. If the data could contain a cycle (A manages B, B manages A), add a depth limit such as WHERE r.level < 20 to the recursive part. SQL Server and Oracle write WITH without RECURSIVE.