SQL interview questions and answers for 2027: from basics to joins

SQL interview questions fall into six groups: basics, joins, aggregation, subqueries and common table expressions, window functions, and database design. The 20 questions below give a short answer and a query for each, run on SQLite 3.54.0 (released 9 October 2026) with the real output pasted, and mark a difference from PostgreSQL or MySQL only where the vendor's manual states it.

Last updated: 11 November 2026.

How are the 20 SQL interview questions organised?

The table maps each group to its questions and to what a correct answer shows.

Three hypothetical tables carry every example: departments, employees (with a manager_id for reporting lines) and orders (each with the emp_id of the salesperson). The data holds deliberate traps: two employees tie for the top salary, order 4 repeats order 3, order 7 has no salesperson and Finance has no employees. Salaries are in Rs thousand per month and budgets in Rs lakh, both invented.

CREATE TABLE departments (dept_id INTEGER PRIMARY KEY, dept_name TEXT, budget INTEGER);
CREATE TABLE employees (emp_id INTEGER PRIMARY KEY, name TEXT,
  dept_id INTEGER REFERENCES departments(dept_id), salary INTEGER, manager_id INTEGER);
CREATE TABLE orders (order_id INTEGER PRIMARY KEY,
  emp_id INTEGER REFERENCES employees(emp_id), amount INTEGER, order_date TEXT);
INSERT INTO departments VALUES (1,'Sales',500),(2,'Engineering',900),(3,'HR',200),(4,'Finance',300);
INSERT INTO employees VALUES (1,'Meera',2,120,NULL),(2,'Rohan',2,100,1),(3,'Sana',1,120,1),
  (4,'Tarun',1,90,3),(5,'Esha',1,90,3),(6,'Farid',3,70,1),(7,'Gita',2,100,2);
INSERT INTO orders VALUES (1,3,500,'2026-01-10'),(2,3,300,'2026-02-05'),(3,4,400,'2026-02-18'),
  (4,4,400,'2026-02-18'),(5,5,250,'2026-03-02'),(6,3,600,'2026-03-21'),(7,NULL,150,'2026-03-30');

Output blocks show columns separated by pipes, copied from a run through the Python 3.9.6 sqlite3 module. A wider list of analyst questions sits in data analyst interview questions for freshers.

SQL basics interview questions

1. What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping, and HAVING filters groups after the aggregates are computed, so WHERE cannot contain an aggregate such as COUNT. PostgreSQL's documentation states the same split and says conditions that need no aggregate belong in WHERE, which avoids grouping rows that will be discarded.

SELECT dept_id, COUNT(*) AS headcount, ROUND(AVG(salary), 1) AS avg_salary
FROM employees
WHERE salary >= 100          -- row filter, runs first
GROUP BY dept_id
HAVING COUNT(*) >= 2;        -- group filter, runs after COUNT

dept_id | headcount | avg_salary
2 | 3 | 106.7

Department 1 has one employee at 100 or more, so HAVING removes it. Moving COUNT(*) >= 2 into WHERE fails on SQLite with misuse of aggregate: COUNT().

2. How does SQL treat NULL, and how do you test for it?

NULL marks an unknown or missing value, and an ordinary comparison with it yields NULL, not true, so IS NULL and IS NOT NULL are the correct tests. PostgreSQL's manual gives 7 = NULL as an expression that yields null.

SELECT name FROM employees WHERE manager_id = NULL;   -- compares with NULL
SELECT name FROM employees WHERE manager_id IS NULL;  -- tests for NULL

name
(no rows)
name
Meera

3. What is the difference between COUNT(*), COUNT(column) and COUNT(DISTINCT column)?

COUNT(*) counts rows, COUNT(column) counts rows where the column is not NULL, and COUNT(DISTINCT column) counts the different non-null values.

SELECT COUNT(*) AS all_rows, COUNT(manager_id) AS with_manager,
       COUNT(DISTINCT manager_id) AS distinct_managers
FROM employees;

all_rows | with_manager | distinct_managers
7 | 6 | 3

4. What is the difference between UNION and UNION ALL?

UNION removes duplicate rows from the combined result and UNION ALL keeps every row. PostgreSQL's reference adds that UNION ALL is usually significantly quicker, so it suits cases where duplicates do not matter.

SELECT 'UNION' AS kind, COUNT(*) AS n FROM (
  SELECT manager_id AS emp_id FROM employees WHERE manager_id IS NOT NULL
  UNION
  SELECT emp_id FROM orders WHERE emp_id IS NOT NULL)
UNION ALL
SELECT 'UNION ALL', COUNT(*) FROM (
  SELECT manager_id AS emp_id FROM employees WHERE manager_id IS NOT NULL
  UNION ALL
  SELECT emp_id FROM orders WHERE emp_id IS NOT NULL);

kind | n
UNION | 5
UNION ALL | 12

5. How do you count by category in separate columns?

Put a CASE expression inside SUM, so each row adds 1 to the column whose condition it meets.

SELECT dept_id,
       SUM(CASE WHEN salary >= 100 THEN 1 ELSE 0 END) AS high_paid,
       SUM(CASE WHEN salary < 100 THEN 1 ELSE 0 END) AS others
FROM employees GROUP BY dept_id ORDER BY dept_id;

dept_id | high_paid | others
1 | 1 | 2
2 | 3 | 0
3 | 0 | 1

SQL joins interview questions

The SQL joins explained post covers every join type. These four questions are the ones that go wrong under pressure.

6. How does an inner join differ from a left join?

An inner join returns only rows that match on both sides, while a left join returns every left-table row and fills the right-side columns with NULL when nothing matches.

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 ORDER BY d.dept_name;

dept_name | headcount
Engineering | 3
Finance | 0
HR | 1
Sales | 3

An inner join returns three rows because Finance has no employees. COUNT(*) instead of COUNT(e.emp_id) would report 1 for Finance, because the NULL-filled row is still a row. PostgreSQL's manual adds that a condition in ON runs before the join and one in WHERE runs after it, so filtering the right table in WHERE turns a left join into an inner join.

7. How do you find rows that have no match, such as employees with no orders?

Join with LEFT JOIN and keep the rows where the right-side key IS NULL, or use NOT EXISTS. NOT IN is the risky form: PostgreSQL's manual says NOT IN yields null, not true, when no right-hand value matches and the subquery returns a NULL.

SELECT e.name FROM employees e
LEFT JOIN orders o ON o.emp_id = e.emp_id
WHERE o.order_id IS NULL ORDER BY e.name;

SELECT name FROM employees WHERE emp_id NOT IN (SELECT emp_id FROM orders);

name
Farid
Gita
Meera
Rohan
name
(no rows)

The NOT IN query returns nothing because order 7 has a NULL emp_id; NOT EXISTS returned the same four names as the left join.

8. Why does a join inflate a SUM, and how do you fix it?

A join repeats the parent row once per matching child row, so summing a parent column after the join counts it several times. Aggregate before joining, or test for existence instead of joining.

SELECT SUM(d.budget) AS inflated_total
FROM departments d JOIN employees e ON e.dept_id = d.dept_id;

SELECT SUM(budget) AS correct_total FROM departments d
WHERE EXISTS (SELECT 1 FROM employees e WHERE e.dept_id = d.dept_id);

inflated_total
4400
correct_total
1600

Each budget repeats once per employee, which gives 4400 against a true 1600 for the three departments that have staff.

9. What is a self join, and does every database support FULL JOIN?

A self join joins a table to itself, so it needs two aliases; PostgreSQL's manual says aliases are necessary in that case. FULL JOIN support differs: SQLite added RIGHT and FULL OUTER JOIN in version 3.39.0 on 25 June 2022, while the MySQL 8.4 manual's JOIN page lists LEFT and RIGHT joins and does not mention FULL JOIN.

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

employee | manager
Meera | NULL
Rohan | Meera
Sana | Meera
Tarun | Sana
Esha | Sana
Farid | Meera
Gita | Rohan

On SQLite 3.54.0, departments FULL JOIN employees returned 8 rows, and Finance was the only unmatched row.

SQL aggregation interview questions

10. How do you find duplicate rows?

Group by every column that defines a duplicate and keep the groups with more than one row, using HAVING COUNT(*) > 1.

SELECT emp_id, amount, order_date, COUNT(*) AS copies
FROM orders GROUP BY emp_id, amount, order_date
HAVING COUNT(*) > 1;

emp_id | amount | order_date | copies
4 | 400 | 2026-02-18 | 2

11. How do you find departments whose average salary is above the company average?

Compare each group's aggregate with a scalar subquery inside HAVING. A group-against-benchmark comparison is the shape of many KPI reports, covered in the OKRs and KPIs post.

SELECT dept_id, ROUND(AVG(salary), 1) AS avg_salary
FROM employees GROUP BY dept_id
HAVING AVG(salary) > (SELECT AVG(salary) FROM employees);

dept_id | avg_salary
1 | 100.0
2 | 106.7

The company average is 98.571 and HR averages 70, so only departments 1 and 2 qualify.

12. How do you calculate a percentage of a total without getting a rounded-down value?

Multiply by 100.0, a decimal, before dividing, because SQLite and PostgreSQL truncate integer division toward zero. The MySQL 8.4 manual shows its / operator returning a decimal (3/5 gives 0.60) and offers DIV for integer division.

SELECT dept_id,
       SUM(salary) * 100 / (SELECT SUM(salary) FROM employees) AS pct_integer,
       ROUND(SUM(salary) * 100.0 / (SELECT SUM(salary) FROM employees), 1) AS pct
FROM employees GROUP BY dept_id;

dept_id | pct_integer | pct
1 | 43 | 43.5
2 | 46 | 46.4
3 | 10 | 10.1

SQL subquery and CTE interview questions

13. How do you find the second highest salary in SQL?

Take the maximum salary below the overall maximum, or sort the distinct salaries in descending order and skip one row with LIMIT 1 OFFSET 1, or filter a DENSE_RANK window function on rank 2. Ties are the trap: without DISTINCT, the OFFSET form returns the top salary when two employees share it.

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

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

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

-- query 1
second_highest
100
-- query 2
salary
100
-- query 3
salary
120

The third query is wrong because Meera and Sana both earn the top salary. A DENSE_RANK subquery filtered to rank 2 also returned 100. For HR, which has one salary, the MAX form returns NULL and the OFFSET form returns no row. PostgreSQL's manual says LIMIT and OFFSET are PostgreSQL and MySQL syntax, while the SQL:2008 standard uses OFFSET with FETCH.

14. What is a correlated subquery?

A correlated subquery refers to a column of the outer query, so it is evaluated against each outer row. PostgreSQL's manual says a scalar subquery can refer to variables from the surrounding query, which act as constants during one evaluation.

SELECT e.name, e.salary FROM employees e
WHERE e.salary > (SELECT AVG(salary) FROM employees WHERE dept_id = e.dept_id)
ORDER BY e.name;

name | salary
Meera | 120
Sana | 120

15. What is a CTE, and when do you need a recursive one?

A common table expression (CTE) is a named temporary result, written with WITH, that exists for a single statement. A recursive CTE refers to its own output, which lets it walk a hierarchy such as a reporting chain. The MySQL 8.4 manual requires WITH RECURSIVE when any CTE refers to itself, PostgreSQL calls RECURSIVE an optional modifier that enables self-reference, and SQLite says it does not enforce that rule.

WITH RECURSIVE chain(emp_id, name, depth) AS (
  SELECT emp_id, name, 0 FROM employees WHERE manager_id IS NULL
  UNION ALL
  SELECT e.emp_id, e.name, c.depth + 1
  FROM employees e JOIN chain c ON e.manager_id = c.emp_id)
SELECT name, depth FROM chain ORDER BY depth, name;

name | depth
Meera | 0
Farid | 1
Rohan | 1
Sana | 1
Esha | 2
Gita | 2
Tarun | 2

An ordinary CTE works like a named subquery: question 14 rewritten with WITH dept_avg AS (...) returned the same two rows.

SQL window function interview questions

Frames, LAG and LEAD get a longer treatment in the SQL window functions explained post. SQLite has supported window functions since version 3.25.0 (15 September 2018), using PostgreSQL's documentation as its reference.

16. How do row_number, rank and dense_rank differ?

ROW_NUMBER numbers rows 1, 2, 3 without repeats, RANK gives tied rows the same rank and then skips numbers, and DENSE_RANK gives tied rows the same rank without skipping. PostgreSQL's function table defines rank as the row_number of the first row in its peer group and dense_rank as the rank without gaps.

SELECT name, dept_id, salary,
  ROW_NUMBER() OVER (PARTITION BY dept_id ORDER BY salary DESC, name) 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 ORDER BY dept_id, row_num;

name | dept_id | salary | row_num | rnk | dense_rnk
Sana | 1 | 120 | 1 | 1 | 1
Esha | 1 | 90 | 2 | 2 | 2
Tarun | 1 | 90 | 3 | 2 | 2
Meera | 2 | 120 | 1 | 1 | 1
Gita | 2 | 100 | 2 | 2 | 2
Rohan | 2 | 100 | 3 | 2 | 2
Farid | 3 | 70 | 1 | 1 | 1

A window function cannot sit in WHERE: PostgreSQL and MySQL both permit them only in the select list and ORDER BY, and SQLite reports misuse of window function ROW_NUMBER(). Filter in an outer query instead; DENSE_RANK = 2 returned Esha and Tarun in department 1 and Gita and Rohan in department 2.

17. How do you calculate a running total and a month-on-month change?

Use SUM(...) OVER (ORDER BY ...) for the running total and LAG for the previous row's value. PostgreSQL's manual says lag returns NULL when no earlier row exists.

WITH monthly AS (
  SELECT substr(order_date, 1, 7) AS month, SUM(amount) AS total
  FROM orders GROUP BY 1)
SELECT month, total,
       total - LAG(total) OVER (ORDER BY month) AS change,
       SUM(total) OVER (ORDER BY month) AS running_total
FROM monthly;

month | total | change | running_total
2026-01 | 500 | NULL | 500
2026-02 | 1100 | 600 | 1600
2026-03 | 1000 | -100 | 2600

18. How do you delete duplicate rows but keep one?

Number the rows inside each duplicate group with ROW_NUMBER, then delete every row numbered above 1. Choosing the ORDER BY inside the window decides which copy survives.

DELETE FROM orders WHERE order_id IN (
  SELECT order_id FROM (
    SELECT order_id, ROW_NUMBER() OVER (
             PARTITION BY emp_id, amount, order_date ORDER BY order_id) AS rn
    FROM orders)
  WHERE rn > 1);

The inner SELECT returned order_id 4 as the extra copy, and after the DELETE on a fresh copy of the data, 6 of the 7 order rows remained.

SQL database design interview questions

Broader questions on normalisation and transactions appear in mastering DBMS interviews.

19. What are primary, foreign and unique key constraints?

A primary key identifies each row and must be unique and not null, a foreign key forces a column to match a row in another table, and a unique constraint blocks repeated values in one column or group. PostgreSQL's manual describes a foreign key as maintaining referential integrity between two tables.

import sqlite3
# SCHEMA is the CREATE TABLE and INSERT script printed at the top, held in one string
con = sqlite3.connect(":memory:", isolation_level=None)   # autocommit
con.executescript(SCHEMA)
print("foreign_keys:", con.execute("PRAGMA foreign_keys").fetchone()[0])
con.execute("INSERT INTO orders VALUES (8, 99, 100, '2026-04-01')")
print("orphan order accepted")
con.execute("DELETE FROM orders WHERE order_id = 8")
con.execute("PRAGMA foreign_keys = ON")
for sql in ("INSERT INTO orders VALUES (9, 99, 100, '2026-04-02')",
            "INSERT INTO employees VALUES (1, 'Copy', 1, 50, NULL)"):
    try:
        con.execute(sql)
    except sqlite3.IntegrityError as e:
        print("rejected:", e)

foreign_keys: 0
orphan order accepted
rejected: FOREIGN KEY constraint failed
rejected: UNIQUE constraint failed: employees.emp_id

SQLite's documentation says foreign key constraints are disabled by default and must be switched on for each connection with PRAGMA foreign_keys = ON. The pragma does nothing inside a transaction, which is why the script uses autocommit.

20. What is an index, and when does it not help?

An index is a separate search structure that lets the database find rows without scanning the whole table, at the cost of overhead on data changes: PostgreSQL's manual says every index must be kept synchronised. It helps only when the query can use it: wrapping the indexed column in an expression, as in emp_id + 0 = 3, sends SQLite back to a full scan.

import sqlite3
# SCHEMA is the same script as in the previous example
con = sqlite3.connect(":memory:")
con.executescript(SCHEMA)
def plan(sql):
    return [row[3] for row in con.execute("EXPLAIN QUERY PLAN " + sql)]
print(plan("SELECT * FROM orders WHERE emp_id = 3"))
con.execute("CREATE INDEX idx_orders_emp ON orders(emp_id)")
print(plan("SELECT * FROM orders WHERE emp_id = 3"))
print(plan("SELECT * FROM orders WHERE emp_id + 0 = 3"))

['SCAN orders']
['SEARCH orders USING INDEX idx_orders_emp (emp_id=?)']
['SCAN orders']

SQLite's documentation reads SCAN as a full-table scan and SEARCH as a visit to a subset of rows. Indexes, caches and database choice return in the system design basics post.

What are the most common SQL interview mistakes?

The table lists each mistake, what happened in the runs above and the fix.

The non-grouped column row is a dialect difference. SQLite's documentation calls bare columns an SQLite-specific extension that most other engines disallow, and the MySQL 8.4 manual says ONLY_FULL_GROUP_BY is enabled by default. SELECT dept_id, name, MAX(salary) FROM employees GROUP BY dept_id ran without error on SQLite, which took name from the row holding the maximum.

Where does SQL practice fit in a data analytics programme?

The Certification Program in Data Analytics with AI and Gen AI is an online programme from E&ICT Academy, IIT Roorkee, run with Masai as the implementation partner, with live sessions and an optional campus immersion, covering SQL, Python, Google Sheets, prompt engineering, LLM tools and agentic workflows with LangChain and CrewAI, and a capstone project. Its curriculum lists relational databases and SQL queries among the topics, alongside a first module named Core Data Architecture & Analytical Foundations.

Frequently asked questions

What are the most common SQL interview questions?

A practical set of SQL interview questions covers WHERE versus HAVING, NULL handling, the join types, GROUP BY with aggregates, duplicates, the second highest salary, subqueries and CTEs, and window functions such as ROW_NUMBER and RANK. Common traps include a NULL inside NOT IN, a tie for the top salary and a join that inflates a SUM.

How do you find the second highest salary in SQL?

Select the maximum salary below the overall maximum, or sort the distinct salaries in descending order and skip one row with LIMIT 1 OFFSET 1, or filter DENSE_RANK on rank 2. DISTINCT or DENSE_RANK matters because two employees tied at the top would otherwise make the top salary look second.

What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping and cannot contain aggregate functions, while HAVING filters groups after aggregates such as COUNT or AVG are computed. Conditions that need no aggregate belong in WHERE, which avoids grouping rows that would be discarded.

Which SQL dialect should a fresher practise on?

Any one engine is enough for the shared core: SELECT, WHERE, GROUP BY, joins, subqueries, CTEs and window functions. Documented differences include LIMIT versus OFFSET with FETCH, FULL JOIN, which MySQL's JOIN page does not list, integer division and whether a non-grouped column is rejected. Read the manual of the engine an employer names.

What does a SQL transaction do?

A SQL transaction bundles several steps into one all-or-nothing operation: if a failure stops it midway, none of the steps change the database, and other sessions do not see the intermediate states. In a SQLite test, an UPDATE that set every salary in a table to 0 inside BEGIN was undone by ROLLBACK.

Next step

Rewrite each query above on a blank editor without looking at the answer, then change the data so a tie, a NULL or a missing match breaks it. The Certification Program in Data Analytics with AI and Gen AI page lists the SQL, Python and AI topics for analysts who want to go beyond interview practice.