SQL interview questions with answers

These are the SQL questions that interviewers ask most often for analyst, support, testing and junior developer jobs, with answers in plain words. The query questions use the practice database, and every query was run to produce the result shown. Tap Try it to run and change one yourself.

Reading answers is not enough to pass an interview. Interviewers change the question slightly to see whether you understand it. So for each query, ask yourself how you would alter it for "third highest" or "per department", and try that in the practice box.

Questions about the basics

What is SQL?

SQL, Structured Query Language, is the standard language for working with relational databases. It is used to read data (SELECT), to change it (INSERT, UPDATE, DELETE) and to define tables (CREATE, ALTER, DROP).

What is the difference between SQL and MySQL?

SQL is the language. MySQL is one database product that understands it. PostgreSQL, SQL Server, Oracle and SQLite are others. Compare the English language with one particular publisher of English books.

What are DDL, DML, DCL and TCL?

They are four families of SQL statements.

Some books put SELECT in a family of its own, called DQL, for data query language.

What is a primary key? How is it different from a unique key?

A primary key is the column, or set of columns, that identifies each row of a table. Its values must be unique and cannot be NULL, and a table has only one. A unique key also forbids repeated values, but a table can have several, and a unique column may be allowed to hold NULL.

What is a foreign key?

A column whose values must match the primary key of a row in another table. In our database, orders.customer_id is a foreign key to customers.id. It stops an order from pointing to a customer who does not exist. See creating tables.

What is the difference between WHERE and HAVING?

WHERE filters single rows, before any grouping. HAVING filters groups, after GROUP BY, and can use aggregate functions such as COUNT. See the lesson on HAVING.

SELECT customer_id, COUNT(*) AS delivered
FROM orders
WHERE status = 'delivered'
GROUP BY customer_id
HAVING COUNT(*) >= 2;

What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes rows, can take a WHERE, and can be rolled back inside a transaction. TRUNCATE removes all rows quickly, cannot be filtered, and resets the table. DROP removes the table itself, with its structure. See changing tables.

What is the difference between UNION and UNION ALL?

Both stack the results of two queries. UNION removes duplicate rows. UNION ALL keeps them and is faster for that reason. See UNION.

What is the difference between CHAR and VARCHAR?

CHAR(10) always stores exactly ten characters, padding shorter text with spaces. VARCHAR(10) stores up to ten and uses only the space the text needs. CHAR suits fixed-length codes. VARCHAR suits names and anything of varying length.

What is NULL? Is it the same as zero or an empty string?

NULL means that a value is missing or unknown. It is not zero and not empty text. It cannot be tested with =. Use IS NULL and IS NOT NULL. Aggregate functions other than COUNT(*) skip it. See NULL.

Questions about joins

What kinds of join are there?

Find the customers who have never placed an order.

The standard answer is a left join that keeps the rows with no match. NOT EXISTS is equally good.

SELECT c.name
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
WHERE o.id IS NULL;

Show each employee with the name of their manager.

A self join. The left join keeps the person who has no manager.

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

Find the employees who earn more than their manager.

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

In our data there are none, so the result is empty. Say so in an interview, and explain that the query is still correct.

The classic query questions

Find the second highest salary.

There are three common answers. Know at least two, and know which handles ties.

With a subquery: the highest salary below the highest.

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

With LIMIT and OFFSET. The DISTINCT matters when the top salary is shared.

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

With DENSE_RANK, which extends to the third, fourth or Nth by changing one number, and can show the names too.

WITH r AS (
  SELECT name, salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS place
  FROM employees
)
SELECT name, salary FROM r WHERE place = 2;

Find the highest-paid employee in each department.

SELECT e.name, e.department, e.salary
FROM employees e
WHERE e.salary = (SELECT MAX(x.salary) FROM employees x WHERE x.department = e.department)
ORDER BY e.department;

A window function does the same and is the answer for "top two in each department".

WITH r AS (
  SELECT name, department, salary,
         ROW_NUMBER() OVER (PARTITION BY department ORDER BY salary DESC) AS rn
  FROM employees
)
SELECT name, department, salary FROM r WHERE rn <= 2 ORDER BY department, salary DESC;

Find duplicate values in a column.

Group by the column and keep the groups with more than one row.

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

Count the employees in each department, and show only departments with more than one.

SELECT department, COUNT(*) AS staff
FROM employees
GROUP BY department
HAVING COUNT(*) > 1
ORDER BY staff DESC;

Find the employees who earn more than the average salary.

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Show the total sales of each product category.

A three-table join with grouping.

SELECT p.category, SUM(oi.quantity * p.price) AS sales
FROM order_items oi
JOIN products p ON oi.product_id = p.id
JOIN orders o   ON oi.order_id = o.id
WHERE o.status <> 'cancelled'
GROUP BY p.category
ORDER BY sales DESC;

Find the customer who has spent the most.

SELECT c.name, SUM(oi.quantity * p.price) AS spent
FROM customers c
JOIN orders o       ON o.customer_id = c.id
JOIN order_items oi ON oi.order_id = o.id
JOIN products p     ON oi.product_id = p.id
WHERE o.status <> 'cancelled'
GROUP BY c.id, c.name
ORDER BY spent DESC
LIMIT 1;

Show a running total of orders by month.

WITH monthly AS (
  SELECT strftime('%Y-%m', order_date) AS month, COUNT(*) AS orders
  FROM orders
  GROUP BY strftime('%Y-%m', order_date)
)
SELECT month, orders, SUM(orders) OVER (ORDER BY month) AS running_total
FROM monthly;

Show the odd-numbered rows of a table.

A trick question that tests whether you know ROW_NUMBER and the remainder operator.

WITH n AS (
  SELECT name, ROW_NUMBER() OVER (ORDER BY id) AS rn FROM employees
)
SELECT rn, name FROM n WHERE rn % 2 = 1;

Questions about design and speed

What is normalisation?

Organising tables so that each fact is stored once. The usual steps are called normal forms.

• First normal form: each cell holds one value, and there are no repeating groups of columns such as phone1, phone2.
• Second normal form: every column that is not part of the key depends on the whole key, not just a part of it.
• Third normal form: columns that are not part of the key depend only on the key, not on one another. A customer's city belongs in the customer table, not copied into every order.

The benefit is that a change is made in one place and the data cannot disagree with itself. The cost is more joins. Reporting databases sometimes go the other way on purpose, which is called denormalisation.

What is an index? What are its drawbacks?

An index is a sorted structure, kept beside a table, that lets the database find rows by a column's value without reading the whole table. It speeds up searching, joining and sorting on that column. It takes space, and it slows down inserts, updates and deletes, because the index must be kept up to date. See the lesson on indexes.

What is the difference between a clustered and a non-clustered index?

A clustered index decides the physical order of the rows in the table, so a table can have only one. Usually it is the primary key. A non-clustered index is a separate structure that points to the rows, and a table can have many. The terms come from SQL Server and MySQL.

What is a view?

A saved query with a name, used like a table. It stores no data of its own. See views and WITH.

What is a transaction? What does ACID mean?

A transaction is a group of statements that succeed or fail together. Moving money between two accounts is the usual example: the debit and the credit must both happen or neither. ACID names the four promises a database makes about transactions.

• Atomicity: all of the transaction happens, or none of it.
• Consistency: the data obeys all its rules before and after.
• Isolation: transactions running at the same time do not see one another's unfinished work.
• Durability: once committed, a change survives a crash or power cut.

What is the difference between a subquery and a join?

A join combines columns from two tables in one result. A subquery uses the result of one query inside another, often just to decide which rows to keep. Many questions can be answered either way. See subqueries.

What is the order of execution of a query?

FROM and joins, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and last LIMIT. It explains why a column alias works in ORDER BY but not in WHERE.

What is the difference between RANK, DENSE_RANK and ROW_NUMBER?

All three number rows in an order you give. ROW_NUMBER gives every row a different number. RANK gives tied rows the same number and then skips. DENSE_RANK gives tied rows the same number and does not skip.

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

How to answer a query question in an interview

1. Repeat the question in your own words and ask about the edge cases. What should happen with ties? Should customers with no orders be shown?
2. Name the tables and the keys that link them before writing anything.
3. Build the query in steps and say what each step does. Interviewers are listening to your reasoning more than checking your syntax.
4. Mention NULL and duplicates where they could change the answer. It shows experience.
5. If you know a second way, say so, and say which you would choose and why.

The fastest preparation is to solve the exercises without looking at the answers.

Examples on this page were run with SQLite 3.50.4 on 12 November 2026. The results shown are the real results.

This is an independent learning site. It is not connected with the makers of any database product. MySQL, PostgreSQL, SQL Server, Oracle and SQLite are named only so that you know where a piece of SQL works. The shop, its customers and its staff in the practice database are made up.