SQL Interview Questions, Explained with Simple Examples

The SQL questions that show up in almost every developer interview, explained slowly. Learn the idea behind joins, WHERE versus HAVING, and how to find the Nth highest value.

SQL looks easy until someone asks you to write a query on a whiteboard. The good news is that most interview questions come from the same small group of ideas. If you understand how a query is read, you can solve questions you have never seen.

Every example below uses two small tables. You can paste them into any SQL playground, including our own SQL Practice Playground.

CREATE TABLE employees (id INT, name TEXT, dept_id INT, salary INT);
CREATE TABLE departments (id INT, name TEXT);

INSERT INTO departments VALUES (1, 'Engineering'), (2, 'Design'), (3, 'Legal');
INSERT INTO employees VALUES
  (1, 'Asha', 1, 90000), (2, 'Ravi', 1, 80000),
  (3, 'Meera', 2, 70000), (4, 'Kabir', NULL, 60000);

How the database reads your query

You write SELECT first, but the database does not run it first. Knowing the real order explains most confusing errors, like why you cannot use a SELECT alias inside WHERE.

Question 1: INNER JOIN versus LEFT JOIN

An inner join keeps only rows that have a match in both tables. A left join keeps every row from the left table, and fills in NULL where there is no match on the right.

-- Only employees who have a department (Kabir is missing)
SELECT e.name, d.name AS dept
FROM employees e
INNER JOIN departments d ON e.dept_id = d.id;

-- All employees, with NULL dept for Kabir
SELECT e.name, d.name AS dept
FROM employees e
LEFT JOIN departments d ON e.dept_id = d.id;

Follow-up: “How do you find employees with no department?” Use a left join and filter for NULL: WHERE d.id IS NULL.

Question 2: WHERE versus HAVING

WHERE filters rows before grouping. HAVING filters groups after grouping. If your condition uses an aggregate like COUNT or AVG, it belongs in HAVING.

-- Departments where the average salary is above 75000
SELECT dept_id, AVG(salary) AS avg_salary
FROM employees
WHERE dept_id IS NOT NULL      -- row filter, runs first
GROUP BY dept_id
HAVING AVG(salary) > 75000;    -- group filter, runs after

Question 3: Find the second highest salary

A classic. There are a few correct answers. The simplest to explain is to sort and skip:

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

DISTINCT matters. Without it, two people sharing the top salary would make the “second” row equal the first. A more general way, which also handles “Nth highest”, uses a window function:

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

Question 4: How do NULLs behave?

NULL means “unknown”, not zero and not empty text. Because of that, comparing anything to NULL with = never gives true. Use IS NULL and IS NOT NULL.

Question 5: Find duplicates

SELECT name, COUNT(*) AS times
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;

Group by the column that should be unique, count each group, and keep groups bigger than one. Say that sentence out loud in the interview. It shows you know why the query works.

Question 6: What does an index do?

An index is like the index at the back of a book. Instead of reading every page to find a topic, the database jumps straight to it. Reads get faster. Writes get a bit slower, because the index must be updated, and it uses extra storage.