SQL for Interviews: Concepts, Tricky Queries and Practice Questions with Answers

Why SQL matters

SQL is tested in interviews for developers, data analysts, data engineers, QA and business analysts. Interviewers want to see that you can retrieve and summarise data correctly, and that you think about edge cases such as NULLs, ties and duplicates. Syntax differs slightly between MySQL, PostgreSQL, SQL Server and Oracle, so say which one you are using, and mention the alternative if you know it. The theory side of databases (keys, normalisation, transactions) is in the DBMS interview guide.

The sample database

employees(emp_id, name, dept_id, salary, manager_id, hire_date)
departments(dept_id, dept_name)
orders(order_id, customer_id, order_date, amount)
customers(customer_id, name, city)

Create these in SQLite, DB Fiddle or PostgreSQL and insert a few rows (include ties in salary, a department with no employees and a customer with no orders). Testing on awkward data is how you catch mistakes.

1. Logical order of execution (explains most "why does this fail?" questions)

SQL is written in one order but evaluated in another:

1. FROM and JOIN
2. WHERE (filters rows)
3. GROUP BY
4. HAVING (filters groups)
5. SELECT (computes columns and aliases)
6. DISTINCT
7. ORDER BY
8. LIMIT / TOP

Consequences: you cannot use a SELECT alias in WHERE (it does not exist yet), but you can in ORDER BY. You cannot use an aggregate in WHERE; use HAVING. Window functions run after GROUP BY, so you cannot filter on them directly in the same level; wrap them in a subquery or CTE.

2. Basics you should write without thinking

SELECT name, salary
FROM employees
WHERE dept_id = 10 AND salary BETWEEN 40000 AND 80000
ORDER BY salary DESC
LIMIT 5;                     -- SQL Server: SELECT TOP 5 ...

SELECT * FROM customers WHERE city IN ('Pune', 'Delhi') AND name LIKE 'A%';

Know DISTINCT, IN, BETWEEN, LIKE (% any characters, _ one character), CASE for conditional columns, and string and date functions in your database.

SELECT name,
       CASE WHEN salary >= 100000 THEN 'High'
            WHEN salary >= 50000  THEN 'Medium'
            ELSE 'Low' END AS band
FROM employees;

3. NULL: the source of many interview traps

NULL means "unknown", so comparisons with it are neither true nor false.

• salary = NULL never matches; use salary IS NULL
• NULL = NULL is not true
• COUNT(*) counts rows, COUNT(col) ignores NULLs
• SUM, AVG, MAX ignore NULLs, so AVG is over non-null values only
• NOT IN (subquery) returns no rows if the subquery contains a NULL; prefer NOT EXISTS
• COALESCE(col, 0) replaces NULL with a default

4. Joins

• INNER JOIN: only matching rows
• LEFT JOIN: all rows from the left table, matched rows from the right (NULL when no match)
• RIGHT JOIN is the mirror image; FULL JOIN keeps both sides
• CROSS JOIN: every combination (a Cartesian product)
• Self join: a table joined to itself (employee and manager)

-- employees with their department name, including those with no department
SELECT e.name, d.dept_name
FROM employees e
LEFT JOIN departments d ON d.dept_id = e.dept_id;

Two common mistakes: putting a filter on the right table in WHERE after a LEFT JOIN (it turns the join into an inner join; put the condition in ON instead), and joining one-to-many tables without realising the row count multiplies.

5. Grouping and aggregates

SELECT dept_id, COUNT(*) AS headcount, AVG(salary) AS avg_salary
FROM employees
GROUP BY dept_id
HAVING COUNT(*) > 5
ORDER BY avg_salary DESC;

Every non-aggregated column in SELECT must appear in GROUP BY. WHERE filters rows before grouping, HAVING filters groups after.

6. Subqueries, EXISTS and CTEs

-- employees earning above their department's average (correlated subquery)
SELECT e.name, e.salary
FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id);

-- same idea with a CTE: easier to read
WITH dept_avg AS (
  SELECT dept_id, AVG(salary) AS avg_sal FROM employees GROUP BY dept_id
)
SELECT e.name, e.salary
FROM employees e
JOIN dept_avg d ON d.dept_id = e.dept_id
WHERE e.salary > d.avg_sal;

EXISTS stops at the first match and handles NULLs safely. Recursive CTEs can walk hierarchies such as an organisation chart.

7. Window functions

Window functions calculate across related rows without collapsing them.

SELECT name, dept_id, salary,
       ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS row_num,
       RANK()       OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk,
       DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS dense_rnk
FROM employees;

For salaries 90, 90, 80: ROW_NUMBER gives 1, 2, 3; RANK gives 1, 1, 3; DENSE_RANK gives 1, 1, 2. Also learn LAG and LEAD (previous and next row), running totals with SUM(...) OVER (ORDER BY ...), NTILE and moving averages.

8. Twenty practice queries with answers

1. Employees earning more than 50,000

SELECT name, salary FROM employees WHERE salary > 50000;

2. Headcount per department (with names)

SELECT d.dept_name, COUNT(e.emp_id) AS headcount
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
GROUP BY d.dept_name;

3. Departments with more than five employees

SELECT dept_id, COUNT(*) AS headcount
FROM employees GROUP BY dept_id HAVING COUNT(*) > 5;

4. Second highest salary (works with ties)

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

5. Nth highest salary using DENSE_RANK (here N = 3)

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

6. Top three earners in each department

SELECT name, dept_id, salary
FROM (
  SELECT name, dept_id, salary,
         DENSE_RANK() OVER (PARTITION BY dept_id ORDER BY salary DESC) AS rnk
  FROM employees
) t
WHERE rnk <= 3;

7. Departments with no employees

SELECT d.dept_name
FROM departments d
LEFT JOIN employees e ON e.dept_id = d.dept_id
WHERE e.emp_id IS NULL;

8. Employees and their manager's name (self join)

SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.emp_id = e.manager_id;

9. Employees who earn more than their manager

SELECT e.name
FROM employees e
JOIN employees m ON m.emp_id = e.manager_id
WHERE e.salary > m.salary;

10. Find duplicate names

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

11. Delete duplicate rows but keep one (keeps the lowest emp_id)

DELETE FROM employees
WHERE emp_id NOT IN (
  SELECT MIN(emp_id) FROM employees GROUP BY name, dept_id
);
-- MySQL needs the subquery wrapped in a derived table to avoid error 1093

12. Customers who never placed an order

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

13. Customers with the highest total spend

SELECT c.name, SUM(o.amount) AS total
FROM customers c
JOIN orders o ON o.customer_id = c.customer_id
GROUP BY c.customer_id, c.name
ORDER BY total DESC
LIMIT 5;

14. Monthly revenue

SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
FROM orders GROUP BY 1 ORDER BY 1;
-- MySQL: DATE_FORMAT(order_date, '%Y-%m')

15. Running total per customer

SELECT customer_id, order_date, amount,
       SUM(amount) OVER (PARTITION BY customer_id ORDER BY order_date) AS running_total
FROM orders;

16. Month-over-month change with LAG

WITH m AS (
  SELECT DATE_TRUNC('month', order_date) AS month, SUM(amount) AS revenue
  FROM orders GROUP BY 1
)
SELECT month, revenue,
       revenue - LAG(revenue) OVER (ORDER BY month) AS change_from_last_month
FROM m;

17. Latest order for each customer

SELECT order_id, customer_id, order_date, amount
FROM (
  SELECT *, ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY order_date DESC) AS rn
  FROM orders
) t
WHERE rn = 1;

18. Customers who ordered in three consecutive months (a harder pattern)

WITH months AS (
  SELECT DISTINCT customer_id, DATE_TRUNC('month', order_date) AS m FROM orders
),
numbered AS (
  SELECT customer_id, m,
         ROW_NUMBER() OVER (PARTITION BY customer_id ORDER BY m) AS rn
  FROM months
)
SELECT customer_id
FROM numbered
GROUP BY customer_id, (m - (rn * INTERVAL '1 month'))
HAVING COUNT(*) >= 3;

The idea: subtracting the row number from the month gives the same value for consecutive months, so you can group them (islands technique). The date arithmetic differs by database.

19. Median salary (PostgreSQL)

SELECT PERCENTILE_CONT(0.5) WITHIN GROUP (ORDER BY salary) AS median_salary
FROM employees;

20. Pivot: orders per year as columns

SELECT customer_id,
       SUM(CASE WHEN EXTRACT(YEAR FROM order_date) = 2024 THEN amount ELSE 0 END) AS y2024,
       SUM(CASE WHEN EXTRACT(YEAR FROM order_date) = 2025 THEN amount ELSE 0 END) AS y2025
FROM orders
GROUP BY customer_id;

9. DDL, DML and constraints

• DDL: CREATE, ALTER, DROP, TRUNCATE (structure). DML: INSERT, UPDATE, DELETE, SELECT. TCL: COMMIT, ROLLBACK.
• DELETE removes chosen rows and can be rolled back, TRUNCATE removes all rows quickly, DROP removes the table itself.
• Constraints: PRIMARY KEY, FOREIGN KEY, UNIQUE, NOT NULL, CHECK, DEFAULT.
• UNION removes duplicates, UNION ALL keeps them and is faster.
• Views (saved queries), stored procedures and triggers: know what they are and when you would use them.

CREATE TABLE jobs (
  id         SERIAL PRIMARY KEY,
  title      VARCHAR(120) NOT NULL,
  company_id INT NOT NULL REFERENCES companies(id),
  salary     INT CHECK (salary >= 0),
  posted_at  TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

10. Indexes and performance

• An index speeds up lookups, joins and sorting on the indexed columns but slows writes and uses space
• Index columns used in WHERE, JOIN and ORDER BY; a composite index follows the left-most prefix rule
• Functions on an indexed column (WHERE YEAR(order_date) = 2025) can prevent index use; compare against a date range instead
• SELECT * fetches unneeded data; select only the columns you need
• Use EXPLAIN (or EXPLAIN ANALYZE) to read the plan and spot full table scans
• Beware the N+1 query problem in application code: one query for a list, then one per item

11. Common mistakes

• Using WHERE instead of HAVING on aggregates
• Joining without thinking about duplicates, which inflates counts and sums
• Comparing to NULL with =
• Using NOT IN with a subquery that can return NULL
• Forgetting ORDER BY when the order matters; without it, row order is not guaranteed
• Writing LIMIT for "top N per group" instead of using a window function

12. How to practise

1. Type each query yourself and add rows that test edge cases (ties, NULLs, empty groups)
2. Predict the result in words before you run it
3. Rewrite queries a second way (a subquery, a join and a window function) to see the trade-offs
4. Solve problems on SQLZoo, HackerRank and LeetCode SQL with a 10-minute timer
5. In interviews, think aloud: restate the question, name the tables and keys, write the query step by step and check it against a tiny example

13. Theory questions that come with SQL

• DELETE vs TRUNCATE vs DROP; WHERE vs HAVING; UNION vs UNION ALL
• INNER vs LEFT join; RANK vs DENSE_RANK vs ROW_NUMBER
• What is a primary key, foreign key and unique key?
• What is an index, and what is its cost?
• What is normalisation, and when would you denormalise?
• ACID properties and isolation levels (DBMS guide)

Next steps

Learn how SQL fits into a larger role: data analyst, backend or data engineer roadmaps. See also the core CS subjects guide.