Top 20 SQL Interview Questions and Answers (2026 Guide)

Quick answer: This guide compiles 20 SQL questions consistently asked across data analyst, data engineer and data scientist interviews, from basic SELECT queries through joins, aggregation, window functions and database design. It is written for anyone preparing for a technical SQL round at any company, since interviewers across the industry tend to draw from the same well-established pool of concepts. Each answer explains the underlying idea, not just the syntax, so it can be adapted under interview pressure rather than recited.

Query Fundamentals

1. What is a SELECT statement and how is it structured?

SELECT retrieves rows from one or more tables. A typical query names the columns to return, the table to read from, an optional filter, and an optional sort order:

SELECT column1, column2
FROM table_name
WHERE condition
ORDER BY column1;

2. What does WHERE do?

WHERE filters individual rows before any grouping happens, based on conditions against column values (comparison operators, BETWEEN, IN, LIKE, IS NULL). Rows that fail the condition are excluded before aggregation or sorting is applied.

3. What does DISTINCT do and when would you use it?

DISTINCT removes duplicate rows from the result set, comparing all selected columns. It is useful for "which distinct cities do our customers live in" type questions, but can be costly on large tables since the database typically has to sort or hash the result, so it should not replace a correctly designed query.

4. What is the logical order of execution of a SQL query?

Although a query is written as SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, the database evaluates it in a different order: FROM/JOIN first, then WHERE, then GROUP BY, then HAVING, then SELECT, and finally ORDER BY and LIMIT. This is why a SELECT-defined alias cannot be used in WHERE, but often can be in ORDER BY.

Joins

5. What is an INNER JOIN?

INNER JOIN returns only rows where the join condition matches in both tables. Unmatched rows on either side are excluded from the result.

SELECT o.order_id, c.customer_name
FROM orders o
INNER JOIN customers c ON o.customer_id = c.customer_id;

6. What is a LEFT JOIN, and what is a RIGHT JOIN?

LEFT JOIN returns every row from the left table plus matching rows from the right table, with NULLs where nothing matches. RIGHT JOIN is the mirror image, keeping every row from the right table. Most developers write everything as LEFT JOIN by reordering tables, so RIGHT JOIN shows up far less in practice.

7. What is the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL OUTER JOIN?

This is the classic "explain the joins" question, best answered by describing unmatched rows on each side:

• INNER JOIN keeps only rows matched on both sides.

• LEFT JOIN keeps every left-table row, NULL-filling unmatched right-side columns.

• RIGHT JOIN keeps every right-table row, NULL-filling unmatched left-side columns.

• FULL OUTER JOIN keeps every row from both tables, NULL on whichever side did not match, equivalent to a LEFT and RIGHT JOIN combined.

Worth adding: MySQL has no native FULL OUTER JOIN; it is emulated with a LEFT JOIN UNION a RIGHT JOIN.

8. What is a self-join, and when would you use one?

A self-join joins a table to itself using two aliases, treating it as two separate tables. It is used when rows in the same table relate to each other, such as employees and their managers both living in one "employees" table:

SELECT e.employee_name, m.employee_name AS manager_name
FROM employees e
LEFT JOIN employees m ON e.manager_id = m.employee_id;

Aggregation and Grouping

9. What do aggregate functions do?

Aggregate functions collapse multiple rows into one summary value: COUNT (row count), SUM (total), AVG (mean), MIN and MAX. Most ignore NULLs, except COUNT(*), which counts rows regardless of NULLs, versus COUNT(column_name), which counts only non-NULL values, a distinction interviewers frequently probe.

10. What does GROUP BY do?

GROUP BY partitions rows into buckets sharing the same value in the grouped column(s); any aggregate in the SELECT list is then computed per bucket rather than across the whole table.

SELECT department, AVG(salary) AS avg_salary
FROM employees
GROUP BY department;

Every non-aggregated SELECT column must appear in GROUP BY, otherwise the query is ambiguous about which row's value to show.

11. What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping and cannot reference an aggregate. HAVING filters groups after GROUP BY has run, for conditions like "departments with more than 10 employees":

SELECT department, COUNT(*) AS headcount
FROM employees
GROUP BY department
HAVING COUNT(*) > 10;

Window Functions

12. What is a window function, and how is it different from GROUP BY?

A window function computes a value across a set of related rows (a "window," defined by OVER, PARTITION BY and ORDER BY) without collapsing them into one output row the way GROUP BY does. Every input row still appears, now carrying a value such as its rank or a running total.

13. What is ROW_NUMBER() and how is it used?

ROW_NUMBER() assigns a unique, sequential integer to each row within a partition based on ORDER BY, with no ties. It is commonly used to pick the top row per group, such as each customer's most recent order:

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

14. What is the difference between RANK() and DENSE_RANK()?

Both rank rows within a partition and tie equally-ordered rows. RANK() then leaves a gap equal to the number of ties (1, 2, 2, 4); DENSE_RANK() leaves no gap (1, 2, 2, 3). ROW_NUMBER() never ties at all, always producing a distinct number.

15. What do LAG() and LEAD() do?

LAG() returns a column's value from a previous row in the same partition and ordering; LEAD() returns it from a following row, without needing a self-join. Both are used to compare a row to its neighbor, such as month-over-month change:

SELECT month, revenue,
  LAG(revenue) OVER (ORDER BY month) AS prev_month_revenue
FROM monthly_revenue;

Database Design Fundamentals

16. What is normalization, and what do 1NF, 2NF and 3NF mean?

Normalization organizes tables to reduce redundancy and avoid update anomalies by splitting data into related tables connected by keys:

• 1NF requires atomic column values, no repeating groups, and a unique row identifier.

• 2NF requires 1NF plus every non-key column depending on the whole primary key, not part of a composite key.

• 3NF requires 2NF plus no non-key column depending on another non-key column.

17. What is the difference between a primary key and a foreign key?

A primary key uniquely identifies each row in its own table and disallows NULLs or duplicates. A foreign key is a column in one table that references a primary key in another, enforcing that the referenced value exists. Primary keys enforce uniqueness within a table; foreign keys enforce relationships between tables.

18. What are the ACID properties of a transaction?

Atomicity: a transaction's statements succeed or fail together. Consistency: a transaction moves the database between valid states. Isolation: concurrent transactions do not see each other's uncommitted changes. Durability: a committed transaction survives a later crash. A common follow-up covers isolation levels (read uncommitted, read committed, repeatable read, serializable).

Practice Query Questions

19. How do you find the second-highest salary in a table?

Exclude the maximum with a subquery, then take the maximum of what remains:

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

A version that also handles ties or any "Nth highest" uses DENSE_RANK():

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

20. How do you find duplicate rows in a table?

Group by the column(s) that define a duplicate, then filter to groups appearing more than once:

SELECT email, COUNT(*) AS occurrences
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

A related pattern worth knowing: a running total via SUM(amount) OVER (ORDER BY order_date) gives a cumulative sum without collapsing rows, the same window-function technique from questions 12 through 15.

How to Prepare

• Write queries by hand, not just read them. Recognizing a correct answer differs from producing correct syntax under time pressure with no autocomplete.

• Practice against a real dataset. Load a sample schema into MySQL, PostgreSQL or SQLite and run every question above against it.

• Explain your query, not just write it. Interviewers weigh reasoning about JOIN direction, NULL handling and execution order as much as a working query.

• Know one database's quirks well. FULL OUTER JOIN support, LIMIT versus TOP, and window function syntax vary across MySQL, PostgreSQL and SQL Server, so know the one your target role actually uses.

FAQ

Frequently Asked Questions

What is the difference between WHERE and HAVING?

WHERE filters individual rows before any grouping happens and cannot reference an aggregate function. HAVING filters groups after GROUP BY has run and is used specifically for conditions on aggregated values, such as COUNT(*) > 10.

What is a window function in SQL?

A window function performs a calculation across a set of related rows, defined by an OVER clause with PARTITION BY and ORDER BY, without collapsing those rows into a single output row the way GROUP BY does. Every input row still appears in the result, now carrying a computed value such as a rank or running total.

How do you find the second-highest salary in SQL?

The simplest approach filters out the maximum value with a subquery, then takes the maximum of what remains: SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees). For ties or an Nth-highest value, DENSE_RANK() in a subquery is the more general solution.

What is the difference between INNER JOIN and LEFT JOIN?

INNER JOIN returns only rows that have a match in both tables. LEFT JOIN returns every row from the left table plus matching rows from the right table where they exist, filling in NULL for right-table columns when there is no match.

What are the ACID properties of a database transaction?

ACID stands for Atomicity (a transaction succeeds or fails as a whole), Consistency (it moves the database between valid states), Isolation (concurrent transactions do not see each other's uncommitted changes), and Durability (a committed transaction survives a subsequent crash).

What is database normalization?

Normalization is the process of organizing tables to reduce data redundancy and avoid update anomalies by splitting data into related tables connected by keys. 1NF requires atomic column values, 2NF requires non-key columns to depend on the whole primary key, and 3NF removes dependencies between non-key columns.