SQL Interview Questions and Answers

SELECT COUNT(*), COUNT(manager_id)
FROM employees;

Answer: 5, 3

COUNT(*) counts rows; COUNT(column) counts non-NULL values in that column. This is the cleanest demonstration that aggregate functions skip NULLs — the same applies to SUM, AVG and MAX, which is why AVG can differ from SUM/COUNT(*).

SELECT COUNT(*)
FROM customers c
JOIN orders o ON o.customer_id = c.id;

Answer: The total number of orders, not 4

An inner join emits one row per matching pair, so the count is the number of orders — not customers. Counting customers needs `COUNT(DISTINCT c.id)`. Confusing these two is the most common cause of inflated numbers in reports.

SELECT COUNT(*) FROM users;

Answer: One row containing 10

An aggregate with no GROUP BY collapses the whole table into a single row. `COUNT(*)` counts rows; `COUNT(column)` counts only rows where that column is not NULL, which is a distinction worth internalising early.

1  SELECT department, COUNT(*) AS n
2  FROM employees
3  WHERE COUNT(*) > 5
4  GROUP BY department;

Answer: Line 3 — aggregates cannot appear in WHERE; use HAVING after GROUP BY

WHERE is evaluated before rows are grouped, so no aggregate exists yet. HAVING runs after grouping and is where aggregate conditions belong. The rule follows directly from the clause evaluation order: FROM → WHERE → GROUP BY → HAVING → SELECT.

SELECT name FROM users WHERE age > 18 ORDER BY name;

Answer: Names of users older than 18, sorted alphabetically

WHERE filters rows before they are returned, and ORDER BY sorts the result. Note `> 18` excludes exactly 18 — use `>=` to include it. Sorting is ascending by default; add DESC to reverse it.

SELECT COUNT(*)
FROM orders o
LEFT JOIN customers c ON o.customer_id = c.id
WHERE c.id IS NULL;

Answer: 3

A LEFT JOIN keeps every order and fills NULLs where no customer matched, so filtering on `c.id IS NULL` isolates exactly the orphans. This anti-join pattern is the standard way to find rows missing a counterpart.

SELECT ____ country FROM users;

Answer: DISTINCT

DISTINCT removes duplicate rows from the result. UNIQUE exists in SQL but as a table constraint, not a SELECT modifier. With several columns, DISTINCT deduplicates the whole combination rather than each column separately.

1  SELECT o.id, c.name
2  FROM orders o
3  LEFT JOIN customers c ON o.customer_id = c.id
4  WHERE c.country = 'EG';

Answer: Line 4 — filtering the right table in WHERE discards the NULL-padded rows

Unmatched rows get NULL for every right-table column, and `NULL = 'EG'` is UNKNOWN, so WHERE drops them — turning the outer join into an inner one. Move the predicate into the ON clause, which filters before padding rather than after.

SELECT salary + bonus AS total FROM employees;

Answer: NULL

NULL propagates through arithmetic — anything plus NULL is NULL. Use `salary + COALESCE(bonus, 0)` to treat a missing bonus as zero. This silently zeroes out totals in payroll and reporting queries.

SELECT p.name, COUNT(r.id) AS reviews
FROM products p
____ JOIN reviews r ON r.product_id = p.id
GROUP BY p.name;

Answer: LEFT

A LEFT JOIN keeps every row from the left table and pads the right with NULLs. Because `COUNT(r.id)` counts non-NULL values, products with no reviews correctly show 0 — using `COUNT(*)` there would wrongly show 1.

SELECT name, department,
       ROW_NUMBER() OVER (____ BY department ORDER BY salary DESC) AS rank
FROM employees;

Answer: PARTITION

PARTITION BY divides rows into windows and the function restarts for each one. GROUP BY would collapse rows into one per department; a window function keeps every row and adds the computed column alongside it — which is exactly why window functions exist.

SELECT COUNT(*) FROM users WHERE email = NULL;

Answer: 0

NULL means unknown, so `email = NULL` evaluates to UNKNOWN rather than true — no row ever matches. Use `IS NULL` (and `IS NOT NULL`), which are the only operators that test for it.

SELECT name FROM products WHERE name LIKE 'A%';

Answer: Products whose name starts with A

`%` matches any sequence of characters, so 'A%' anchors at the start. `%A%` would match anywhere and `_` matches exactly one character. Note LIKE is case-sensitive in PostgreSQL — ILIKE is the case-insensitive version.

SELECT MAX(salary), MIN(salary), AVG(salary), COUNT(*)
FROM employees;

Answer: 70, 50, 60, 3

Aggregates with no GROUP BY collapse the whole table to a single row. AVG is the mean, 180/3 = 60. Note that if `salary` were an integer column, integer division in some databases could truncate the average — cast to a decimal when precision matters.

SELECT salary,
       RANK()       OVER (ORDER BY salary DESC) AS r,
       DENSE_RANK() OVER (ORDER BY salary DESC) AS dr
FROM employees;

Answer: r = 1,1,3 and dr = 1,1,2

RANK leaves gaps after a tie — two firsts means the next is third. DENSE_RANK does not, so the next is second. ROW_NUMBER is a third option that never ties, assigning 1,2,3 arbitrarily among equal values.

SELECT 'abc' LIKE 'a%', 'abc' LIKE '_bc', 'abc' LIKE 'A%';

Answer: true, true, false in a case-sensitive database

`%` matches any sequence of characters and `_` matches exactly one. LIKE is case-sensitive in PostgreSQL — use ILIKE, or compare on LOWER() — whereas MySQL's default collation makes it case-insensitive. That difference bites when porting queries between databases.

SELECT COUNT(*)
FROM tickets
WHERE status NOT IN ('open', 'closed');

Answer: 0

For a NULL status the expression becomes `NULL NOT IN (...)`, which evaluates to UNKNOWN, not TRUE — so those rows never satisfy WHERE. This is the classic NOT IN trap: it silently returns nothing when the list or column contains NULL. Use NOT EXISTS, or add `OR status IS NULL`.

1  UPDATE users
2  SET status = 'active';

Answer: Line 2 — there is no WHERE clause, so every row matches

An UPDATE or DELETE with no WHERE applies to the whole table, and there is no confirmation prompt. The habit that prevents this: write the WHERE first, run it as a SELECT to check the row count, then convert it to an UPDATE inside a transaction.

SELECT * FROM users WHERE LOWER(email) = 'a@b.com';

Answer: Wrapping the column in a function makes the predicate non-sargable

The index stores `email`, not `LOWER(email)`, so the planner cannot use it and falls back to scanning every row. Either create a functional index on `LOWER(email)`, or store the value already normalised. The same applies to `WHERE date_col + interval '1 day' > now()` — keep the column bare on one side.

SELECT c.name, o.total
FROM customers c
JOIN orders o ON o.customer_id = c.id;

Answer: 5 rows — one per order, with the customer name repeated

A join produces one row per matching pair, so the 'one' side repeats across its matches. This is why counting customers after joining to orders needs `COUNT(DISTINCT c.id)` rather than `COUNT(*)`.

SELECT * FROM orders ORDER BY total DESC LIMIT 2 OFFSET 1;

Answer: The 2nd and 3rd highest totals

OFFSET skips rows before LIMIT takes them, so skipping 1 and taking 2 gives ranks 2 and 3. Without a unique tiebreaker in ORDER BY, rows with equal totals could shuffle between runs — always add something like `, id` for stable pagination.

1  SELECT customer_id, order_date, total
2  FROM orders
3  GROUP BY customer_id
4  HAVING order_date = MAX(order_date);

Answer: Lines 1 and 3 — selecting ungrouped columns is invalid; use a window function or DISTINCT ON

`order_date` and `total` are neither grouped nor aggregated. Strict databases reject this outright; MySQL historically returned an arbitrary row per group, which is where the silent wrong answers come from. The correct approach is `ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC)` filtered to 1, or Postgres's `DISTINCT ON`.

SELECT department, COUNT(*)
FROM employees
GROUP BY department;

Answer: One row per department, with its headcount

GROUP BY collapses rows into one per distinct value of the grouping column, and aggregates are computed per group. Every non-aggregated column in the SELECT must appear in the GROUP BY, or the query is invalid.

SELECT department, COUNT(*) AS n
FROM employees
GROUP BY department
____ COUNT(*) > 5;

Answer: HAVING

WHERE filters individual rows before grouping; HAVING filters the groups afterwards, which is the only place an aggregate condition can go. Putting `COUNT(*)` in WHERE is an error because no groups exist yet at that point.