SQL interview questions: the complete guide

In this article

1. The vocabulary of SQL interviews
2. What SQL rounds test
3. Start here: the learning path
4. NULL and filtering
5. Joins
6. Aggregation: GROUP BY and HAVING
7. Window functions and the second-highest question
8. Subqueries, common table expressions and self-joins
9. Indexes and query plans
10. Questions interviewers ask on SQL
11. All 15 guides in SQL
12. Follow-up questions
13. Sources

The vocabulary of SQL interviews

SQL

SQL, short for Structured Query Language, is the standard language for asking a relational database for rows and for changing them. A query describes the rows you want, and the database decides how to fetch them.

NULL

NULL is the marker SQL stores when a value is unknown or missing. It is not zero and not an empty string, and comparing anything to it with an equals sign gives unknown, not true or false.

Primary key

A primary key is a column, or set of columns, whose value is unique for every row in a table and never NULL, so it identifies exactly one row.

Foreign key

A foreign key is a column whose values must match a primary key in another table, which is how one table points at the rows of another.

What SQL rounds test

SQL rounds test six skills that every report and data pipeline depends on. We group them into six topics, one section each: handling missing values, combining tables without losing or duplicating rows, summarising groups, ranking within a group, nesting one query inside another, and making queries fast.

Every example below uses one small illustrative table of eight employees in three departments (one has no department, and one department has no staff), run in an in-memory SQLite database.¹

Text description of the diagram

• SQL interview questions fall into six topics.
• The order is NULL and filtering, joins, aggregation, window functions, subqueries, then indexes and query plans.

Start here: the learning path

Take the topics in this order. Each section gives the core idea and one result from the sample table.

1. NULL and filtering: why = NULL returns nothing.
2. Joins: inner, left and the duplicate-row trap.
3. Aggregation: GROUP BY and HAVING.
4. Window functions: ranking and the second-highest question.
5. Subqueries, common table expressions and self-joins.
6. Indexes and query plans.

1. How do you answer query optimisation questions in SQL?
2. What SQL do data engineers need to know for interviews?
3. Window functions interview questions, visualised

NULL and filtering

NULL breaks the habit of treating every comparison as true or false, and interviewers use it to test whether you know how SQL treats missing values. The PostgreSQL 18 documentation says ordinary comparison operators yield null, "signifying unknown", when either input is null, and advises never writing expression = NULL (PostgreSQL comparison operators, read 2026-10-24).

On the eight-row table, WHERE dept_id = NULL returns 0 rows, while WHERE dept_id IS NULL returns the 1 employee with no department. COUNT(*) counts 8 rows, but COUNT(dept_id) counts only the 7 non-null values.¹ The sharper trap is NOT IN: when the list contains a NULL, NOT IN returns no rows at all, because the result is null instead of true (PostgreSQL subquery expressions). In the test it returned 0 rows, while NOT EXISTS with the same intent returned all 8.¹

Joins

A join combines two tables by a condition (it pairs rows whose key values match, where a key is the column that identifies a row), and the join type decides what happens to unmatched rows. In PostgreSQL's definition, an inner join keeps only matching pairs, while a left outer join first does the inner join and then adds every unmatched left row with nulls in the right-hand columns, so it has at least one row for each left row (PostgreSQL join types).

On the sample data an inner join of employees to departments returns 7 rows, because the employee without a department drops out. A left join returns 8. To find departments with no staff, left join from departments and keep rows where the employee id is null: it returns Legal.¹ The classic failure is a join on a key that is not unique. If the department table holds two rows for department 1, the join returns 11 rows instead of 7, and every total that includes department 1 is inflated, because its four employees are counted twice.¹ The SQL join Venn diagram page shows where the picture misleads, and data modeling interview questions covers why keys must be unique.

Aggregation: GROUP BY and HAVING

Aggregation collapses rows into one row per group, and the filter on groups is HAVING, not WHERE. WHERE removes rows before grouping; HAVING removes groups after the aggregate is computed. A question such as "which departments have at least three employees" therefore needs GROUP BY dept_id HAVING COUNT(*) >= 3.

On the sample table, grouping by department returns three groups: the employee with no department as a group of one, department 1 with four employees and an average salary of 80, and department 2 with three and an average of about 63.3. The HAVING filter keeps departments 1 and 2.¹ Two habits prevent most errors: say the grain of the result aloud before you write the query ("one row per department"), and remember that every selected column must be a grouped column or an aggregate. The analytics hub shows the same ideas in the analyst round.

Window functions and the second-highest question

A window function calculates across related rows without merging them. PostgreSQL's tutorial says window functions "do not cause rows to become grouped into a single output row" and that rows keep their separate identities (PostgreSQL window-function tutorial); SQLite added them in version 3.25.0 and lists row_number, rank and dense_rank among its 11 built-in ones (SQLite docs, May 2025).

The standard question is "find the second-highest salary". The sample salaries are 90, 80, 80, 70, 70, 60, 60 and 50, so the answer is 80. row_number numbers the rows 1 to 8, rank gives 1, 2, 2, 4, 4, 6, 6, 8 with gaps, and dense_rank gives 1, 2, 2, 3, 3, 4, 4, 5. At position 2 all three land on 80: dense_rank = 2 and rank = 2 each return both 80 rows (Ben and Cy), and adding SELECT DISTINCT salary returns the value 80 once.

The tie only bites further down. For the third-highest salary, row_number = 3 returns 80 (Cy), which is wrong, rank = 3 returns no row at all because the ranks skip from 2 to 4, and dense_rank = 3 returns 70, which is right. row_number also needs a tiebreaker such as id in its ORDER BY, or the row it picks among tied rows is arbitrary.¹ Per department, PARTITION BY dept_id restarts the ranking: the top earners are Ana (90) in department 1 and Di (70) in department 2.¹

Text description of the diagram

• At position 2 all three functions land on 80; dense_rank = 2 returns both 80 rows and SELECT DISTINCT returns 80 once.
• At position 3, row_number returns 80 (wrong), rank returns no row and dense_rank returns 70 (right).

Subqueries, common table expressions and self-joins

A subquery is a query nested inside another, and a common table expression (CTE) is a named subquery written once with WITH so the main query reads top to bottom. Use a CTE when a result feeds several steps or when nesting hides the logic; the optimiser may treat both the same, so choose for readability. EXISTS asks only whether the subquery returns any row, so NULLs inside it do not matter, which is why it is the safe way to write "not in".

A self-join joins a table to itself, usually with two aliases (an alias is a short temporary name for a table), for hierarchies such as employees and managers. In the sample table, joining each employee to the row whose id equals their manager id returns five employee and manager pairs, such as Ben with Ana and Eli with Di; employees with no manager drop out of an inner join.¹ Switch to a left join if they must stay.

Indexes and query plans

An index is a separate sorted structure that lets the database find rows without reading the whole table, and a query plan shows whether it did. Use The Index, Luke explains that the database combines a doubly linked list (entries chained in order, each pointing to its neighbours) and a search tree (a branching structure that narrows down fast), which is why lookups are fast while inserts and updates must keep the order (Winand, Use The Index, Luke). SQLite contrasts a full scan, which costs time proportional to the number of rows, with a binary search (repeatedly halving a sorted list) (SQLite query planning).

On the sample table, creating an index on dept_id changed the plan for WHERE dept_id = 2 to a search using that index.¹ When a query is slow, read the plan before changing anything. The database interview questions page goes further into index types, composite and covering indexes, and transactions, and the backend interview questions page covers how the application should call the database.

Questions interviewers ask on SQL

For databases in general, the database interview questions page has a longer answered list.

Follow-up questions

How to prepare for SQL interview questions?

Learn the six topics in this order, then practise each on a small table you can inspect, writing the expected result before you run the query. Say the grain of the answer aloud, one row per what, every time.

What are basic SQL interview questions?

Basic questions cover SELECT with WHERE, sorting, COUNT, GROUP BY with HAVING, inner and left joins, and the difference between WHERE and HAVING. Expect one NULL question even at this level.

What are SQL interview questions for freshers?

Freshers are usually asked to define a primary key and a foreign key, explain join types, write a grouped count and describe NULL. Show that you check results on a tiny example, not only that the query runs.

What are common SQL interview questions?

Frequently asked examples include the second-highest salary, duplicate rows, customers with no orders, top N per group, and a running total. Each maps to one topic above: ranking, grouping, left join or window functions.

How to solve SQL interview questions?

State the grain of the result, find the tables and keys, write the join first, add filters, then add grouping or a window function. Check the row count after every join, since a wrong count is the clearest sign of a join on a non-unique key.

Sources

1. PostgreSQL 18 documentation, 3.5 Window Functions — PostgreSQL Global Development Group (version 18.6; date = read date), 24 Oct 2026
2. PostgreSQL 18 documentation, 9.2 Comparison Functions and Operators — PostgreSQL Global Development Group (version 18.6; date = read date), 24 Oct 2026
3. PostgreSQL 18 documentation, 9.24 Subquery Expressions — PostgreSQL Global Development Group (version 18.6; date = read date), 24 Oct 2026
4. PostgreSQL 18 documentation, 7.2 Table Expressions (join types) — PostgreSQL Global Development Group (version 18.6; date = read date), 24 Oct 2026
5. Window Functions, SQLite documentation — SQLite project (page footer: last updated 2025-05-09), 9 May 2025
6. Query Planning, SQLite documentation — SQLite project (page footer: last updated 2026-03-22), 22 Mar 2026
7. Anatomy of an SQL Index, Use The Index, Luke — Markus Winand (page undated; read 2026-10-24), 24 Oct 2026