30 SQL Interview Questions and Answers (With Real Queries)

Thirty SQL interview questions with clear answers, from 'what is a primary key?' to window functions. Every query was run against a sample database, so the results shown are real.

By Matthew Rathbone · October 26, 2026

SQL interviews follow a pattern. First come a handful of concept questions (keys, joins, WHERE vs HAVING), then you are asked to write queries, and for analyst and data engineering roles, a few window function or optimization questions. This page covers all three, in that order, so you can work down it as a study plan.

Jump to: basic concepts · joins · query questions · advanced · interview tips · FAQ

Set up the practice database

The query questions use a small online shop: customers, products, orders and order_items. Paste this into a free SQLite client such as Beekeeper Studio and run it, then try each question yourself before reading the answer. Prefer zero setup? The interactive lessons run in your browser.

CREATE TABLE customers (id INTEGER PRIMARY KEY, name TEXT, country TEXT, signup_date TEXT);
CREATE TABLE products (id INTEGER PRIMARY KEY, name TEXT, category TEXT, price REAL);
CREATE TABLE orders (id INTEGER PRIMARY KEY, customer_id INTEGER, order_date TEXT, status TEXT);
CREATE TABLE order_items (order_id INTEGER, product_id INTEGER, quantity INTEGER);

INSERT INTO customers VALUES
  (1, 'Ana Silva', 'Brazil', '2025-01-15'),
  (2, 'Ben Carter', 'USA', '2025-02-03'),
  (3, 'Chloe Dubois', 'France', '2025-02-20'),
  (4, 'Dev Patel', 'India', '2025-03-11'),
  (5, 'Emma Jones', 'USA', '2025-04-02'),
  (6, 'Felix Wagner', 'Germany', '2025-05-18'),
  (7, 'Grace Kim', 'USA', '2025-06-07'),
  (8, 'Hiro Tanaka', 'Japan', '2025-06-30');

INSERT INTO products VALUES
  (1, 'Laptop', 'Electronics', 899.00),
  (2, 'Headphones', 'Electronics', 129.50),
  (3, 'Desk Lamp', 'Home', 34.99),
  (4, 'Notebook', 'Stationery', 4.25),
  (5, 'Pen Set', 'Stationery', 12.00),
  (6, 'Office Chair', 'Home', 189.00),
  (7, 'Webcam', 'Electronics', 59.90),
  (8, 'Backpack', 'Accessories', 45.00);

INSERT INTO orders VALUES
  (1, 1, '2025-07-01', 'shipped'),
  (2, 2, '2025-07-03', 'shipped'),
  (3, 2, '2025-07-15', 'cancelled'),
  (4, 3, '2025-07-20', 'shipped'),
  (5, 5, '2025-08-02', 'shipped'),
  (6, 1, '2025-08-09', 'pending'),
  (7, 4, '2025-08-12', 'shipped'),
  (8, 5, '2025-08-21', 'shipped'),
  (9, 7, '2025-09-04', 'shipped'),
  (10, 2, '2025-09-10', 'shipped'),
  (11, 3, '2025-09-18', 'pending'),
  (12, 7, '2025-09-25', 'cancelled');

INSERT INTO order_items VALUES
  (1, 1, 1), (1, 4, 3),
  (2, 2, 1), (2, 7, 1),
  (3, 6, 1),
  (4, 3, 2), (4, 5, 2),
  (5, 8, 1), (5, 4, 5),
  (6, 2, 2),
  (7, 1, 1), (7, 2, 1),
  (8, 5, 3),
  (9, 6, 1), (9, 3, 1),
  (10, 4, 10), (10, 8, 2),
  (11, 7, 1),
  (12, 1, 1);

Basic SQL interview questions (concepts)

Every interview starts here. Keep answers short, then give a one-line example.

1. What is SQL?

SQL (Structured Query Language) is the standard language for storing, reading and changing data in relational databases such as PostgreSQL, MySQL, SQL Server and SQLite. You describe what data you want, and the database works out how to fetch it. New to it? Start with what is SQL.

2. What is the difference between DDL, DML, DQL and DCL?

They are categories of SQL commands. DDL defines structure (CREATE, ALTER, DROP). DML changes data (INSERT, UPDATE, DELETE). DQL reads data (SELECT). DCL controls permissions (GRANT, REVOKE). Details in DDL, DML, DQL and DCL explained.

3. What is a primary key? What is a foreign key?

A primary key uniquely identifies each row in a table. It cannot be NULL and cannot repeat. A foreign key is a column that points at the primary key of another table, which is how tables are related and how the database enforces that an order cannot reference a customer that does not exist. See primary keys and foreign keys.

4. What is the difference between WHERE and HAVING?

WHERE filters individual rows before grouping. HAVING filters groups after GROUP BY, so it can use aggregates like COUNT(*) or SUM(). Example: customers with more than one order.

SELECT customer_id, COUNT(*) AS orders
FROM orders
GROUP BY customer_id
HAVING COUNT(*) > 1;

Result (5 rows):

customer_id	orders
1	2
2	3
3	2
5	2
7	2

5. What is the difference between DELETE, TRUNCATE and DROP?

DELETE removes rows (optionally with a WHERE) and can be rolled back. TRUNCATE empties the whole table quickly and keeps its structure. DROP removes the table itself. Full comparison: TRUNCATE vs DELETE vs DROP. Note SQLite has no TRUNCATE; use DELETE FROM table.

6. What is NULL, and how is it different from zero or an empty string?

NULL means "unknown or missing". It is not equal to anything, not even another NULL, so WHERE col = NULL never matches. Use IS NULL / IS NOT NULL. Aggregates such as AVG and COUNT(col) skip NULLs. More in working with NULL.

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

UNION combines two result sets and removes duplicates; UNION ALL keeps every row and is faster because it skips the de-duplication. Both need the same number of columns with compatible types. See UNION.

8. What is an index, and when would you not use one?

An index is a sorted lookup structure that lets the database find rows without scanning the whole table, like a book index. It speeds up reads on columns used in WHERE, JOIN and ORDER BY, but slows down writes and uses storage. Skip indexes on tiny tables or columns with few distinct values. See how to create an index.

9. What is normalization?

Organizing tables to reduce duplicated data, typically by splitting data into related tables so each fact is stored once. First normal form: one value per cell. Second: no partial dependency on part of a composite key. Third: no dependency between non-key columns. Read normalization explained.

10. What are ACID properties?

Guarantees for transactions: Atomicity (all of it happens or none), Consistency (data stays valid), Isolation (concurrent transactions do not see each other half-done) and Durability (committed data survives a crash).

JOIN interview questions

Joins appear in almost every SQL interview. The examples use the online shop sample database (setup script above).

11. What are the different types of JOIN?

INNER JOIN returns only matching rows from both tables. LEFT JOIN returns every row from the left table plus matches from the right (NULL where none). RIGHT JOIN is the mirror image. FULL OUTER JOIN returns everything from both sides. CROSS JOIN returns every combination. A self join joins a table to itself. See SQL joins explained.

SELECT c.name, o.id AS order_id, o.status
FROM customers c
INNER JOIN orders o ON o.customer_id = c.id
WHERE c.country = 'USA'
ORDER BY c.name, o.id;

Result (7 rows):

name	order_id	status
Ben Carter	2	shipped
Ben Carter	3	cancelled
Ben Carter	10	shipped
Emma Jones	5	shipped
Emma Jones	8	shipped
Grace Kim	9	shipped
Grace Kim	12	cancelled

12. Find customers who have never placed an order.

The classic LEFT JOIN ... IS NULL anti-join. Keep every customer, then keep only the ones where the join found no order.

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

Result (2 rows):

name
Felix Wagner
Hiro Tanaka

13. What is the difference between INNER JOIN and LEFT JOIN? When does the difference matter?

It matters when rows have no match. Counting orders per customer with an INNER JOIN silently drops customers with zero orders; a LEFT JOIN keeps them with a count of 0.

SELECT c.name, COUNT(o.id) AS orders
FROM customers c
LEFT JOIN orders o ON o.customer_id = c.id
GROUP BY c.id, c.name
ORDER BY orders DESC, c.name;

Result (8 rows):

name	orders
Ben Carter	3
Ana Silva	2
Chloe Dubois	2
Emma Jones	2
Grace Kim	2
Dev Patel	1
Felix Wagner	0
Hiro Tanaka	0

14. Join three tables: list each product sold with the customer who bought it.

Chain joins through the linking table. Here order_items connects orders to products. Walk the foreign keys one hop at a time.

SELECT c.name AS customer, p.name AS product, oi.quantity
FROM order_items oi
JOIN orders o ON o.id = oi.order_id
JOIN customers c ON c.id = o.customer_id
JOIN products p ON p.id = oi.product_id
WHERE o.id IN (1, 2, 4)
ORDER BY o.id, p.name;

Result (6 rows):

customer	product	quantity
Ana Silva	Laptop	1
Ana Silva	Notebook	3
Ben Carter	Headphones	1
Ben Carter	Webcam	1
Chloe Dubois	Desk Lamp	2
Chloe Dubois	Pen Set	2

SQL query interview questions

These are the "write a query" questions. Say your approach out loud before typing, and check the result.

15. Find the second highest priced product.

Sort descending, skip one row, take one. This is one of the most-asked questions. Mention that LIMIT ... OFFSET is MySQL/PostgreSQL/SQLite syntax; SQL Server uses OFFSET ... FETCH. Ties are a good follow-up: use DENSE_RANK() (question 25) if prices can repeat.

SELECT name, price
FROM products
ORDER BY price DESC
LIMIT 1 OFFSET 1;

Result (1 row):

name	price
Office Chair	189.0

16. Find duplicate values in a column.

Group by the column and keep groups that appear more than once. Here: countries with more than one customer.

SELECT country, COUNT(*) AS customers
FROM customers
GROUP BY country
HAVING COUNT(*) > 1;

Result (1 row):

country	customers
USA	3

17. Count orders per status.

A straightforward GROUP BY. Interviewers use it to check you know every non-aggregated column must be in the GROUP BY.

SELECT status, COUNT(*) AS orders
FROM orders
GROUP BY status
ORDER BY orders DESC;

Result (3 rows):

status	orders
shipped	8
pending	2
cancelled	2

18. Total revenue per product category, highest first.

Join, multiply price by quantity, then group.

SELECT p.category, ROUND(SUM(p.price * oi.quantity), 2) AS revenue
FROM order_items oi
JOIN products p ON p.id = oi.product_id
GROUP BY p.category
ORDER BY revenue DESC;

Result (4 rows):

category	revenue
Electronics	3334.8
Home	482.97
Stationery	136.5
Accessories	135.0

19. Find products that have never been ordered.

Two good answers: NOT IN with a subquery, or NOT EXISTS. Prefer NOT EXISTS when the subquery column could contain NULL, because NOT IN returns nothing if any value is NULL.

SELECT name
FROM products p
WHERE NOT EXISTS (
  SELECT 1 FROM order_items oi WHERE oi.product_id = p.id
);

Result (0 rows):

name

20. Find products priced above the average.

A subquery in WHERE computes the average once.

SELECT name, price
FROM products
WHERE price > (SELECT AVG(price) FROM products)
ORDER BY price DESC;

Result (2 rows):

name	price
Laptop	899.0
Office Chair	189.0

21. Return only the shipped orders in September 2025, using CASE to label order size.

CASE adds if/else logic to a query. Useful for bucketing and for conditional counts.

SELECT o.id, o.order_date,
  SUM(oi.quantity) AS items,
  CASE WHEN SUM(oi.quantity) >= 5 THEN 'large' ELSE 'small' END AS size
FROM orders o
JOIN order_items oi ON oi.order_id = o.id
WHERE o.status = 'shipped' AND o.order_date BETWEEN '2025-09-01' AND '2025-09-30'
GROUP BY o.id, o.order_date;

Result (2 rows):

id	order_date	items	size
9	2025-09-04	2	small
10	2025-09-10	12	large

22. What is a subquery, and how is it different from a JOIN?

A subquery is a query nested inside another. Often a join expresses the same thing and is easier to read and optimize, but subqueries shine for "compare to an aggregate" and "exists / not exists" questions. Here, customers whose total spend is above the average spend per customer, using a CTE (question 24):

WITH spend AS (
  SELECT o.customer_id, SUM(p.price * oi.quantity) AS total
  FROM orders o
  JOIN order_items oi ON oi.order_id = o.id
  JOIN products p ON p.id = oi.product_id
  WHERE o.status <> 'cancelled'
  GROUP BY o.customer_id
)
SELECT c.name, ROUND(s.total, 2) AS total
FROM spend s JOIN customers c ON c.id = s.customer_id
WHERE s.total > (SELECT AVG(total) FROM spend)
ORDER BY s.total DESC;

Result (2 rows):

name	total
Ana Silva	1170.75
Dev Patel	1028.5

Advanced SQL interview questions

Expect these for analyst, data engineer and senior roles. The ideas matter more than memorizing syntax.

23. What is the SQL order of execution?

Not the order you write it. The database logically runs FROM/JOIN, then WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, and finally LIMIT. That is why you cannot use a SELECT alias in WHERE, but you can in ORDER BY.

24. What is a CTE, and when do you use one?

A Common Table Expression (WITH name AS (...)) is a named temporary result you can reference like a table within one query. Use it to break a long query into readable steps or to reuse the same subquery. See using multiple CTEs. Example: the busiest month by order count.

WITH monthly AS (
  SELECT SUBSTR(order_date, 1, 7) AS month, COUNT(*) AS orders
  FROM orders
  GROUP BY SUBSTR(order_date, 1, 7)
)
SELECT month, orders
FROM monthly
ORDER BY orders DESC, month
LIMIT 1;

Result (1 row):

month	orders
2025-07	4

25. Explain ROW_NUMBER, RANK and DENSE_RANK.

All number rows within an ordered window. ROW_NUMBER gives unique numbers. RANK gives ties the same number then skips ahead (1, 1, 3). DENSE_RANK gives ties the same number with no gaps (1, 1, 2). Below, products ranked by price within each category:

SELECT category, name, price,
  RANK() OVER (PARTITION BY category ORDER BY price DESC) AS price_rank
FROM products
ORDER BY category, price_rank;

Result (8 rows):

category	name	price	price_rank
Accessories	Backpack	45.0	1
Electronics	Laptop	899.0	1
Electronics	Headphones	129.5	2
Electronics	Webcam	59.9	3
Home	Office Chair	189.0	1
Home	Desk Lamp	34.99	2
Stationery	Pen Set	12.0	1
Stationery	Notebook	4.25	2

26. Get the top product per category.

Rank inside a CTE, then filter on the rank. You cannot filter on a window function in the same WHERE, which is a favorite follow-up question.

WITH ranked AS (
  SELECT category, name, price,
    ROW_NUMBER() OVER (PARTITION BY category ORDER BY price DESC) AS rn
  FROM products
)
SELECT category, name, price
FROM ranked
WHERE rn = 1
ORDER BY category;

Result (4 rows):

category	name	price
Accessories	Backpack	45.0
Electronics	Laptop	899.0
Home	Office Chair	189.0
Stationery	Pen Set	12.0

27. Calculate a running total.

A window SUM with an ordered frame. Here, cumulative orders over time.

SELECT id, order_date,
  COUNT(*) OVER (ORDER BY order_date, id) AS running_orders
FROM orders
ORDER BY order_date, id
LIMIT 6;

Result (6 rows):

id	order_date	running_orders
1	2025-07-01	1
2	2025-07-03	2
3	2025-07-15	3
4	2025-07-20	4
5	2025-08-02	5
6	2025-08-09	6

28. Compare each order date with the previous one (LAG).

LAG and LEAD read the previous or next row without a self join. See LAG and LEAD.

SELECT id, order_date,
  LAG(order_date) OVER (ORDER BY order_date, id) AS previous_order_date
FROM orders
ORDER BY order_date, id
LIMIT 5;

Result (5 rows):

id	order_date	previous_order_date
1	2025-07-01	NULL
2	2025-07-03	2025-07-01
3	2025-07-15	2025-07-03
4	2025-07-20	2025-07-15
5	2025-08-02	2025-07-20

29. What is a self join? Give an example.

Joining a table to itself, with aliases, for hierarchical or pairwise data (employees and managers, customers from the same country). Pairs of customers sharing a country:

SELECT a.name AS customer_a, b.name AS customer_b, a.country
FROM customers a
JOIN customers b ON a.country = b.country AND a.id < b.id;

Result (3 rows):

customer_a	customer_b	country
Ben Carter	Emma Jones	USA
Ben Carter	Grace Kim	USA
Emma Jones	Grace Kim	USA

30. How would you speed up a slow query?

Look at the plan first (EXPLAIN), then: add an index on columns in WHERE/JOIN, select only the columns you need, filter early, avoid functions on indexed columns in WHERE (WHERE YEAR(date) = 2025 cannot use an index), and replace correlated subqueries with joins. More in improving SQL query performance and optimizing SQL queries.

How to answer SQL interview questions well

Clarify first. Ask about ties, NULLs and what "revenue" or "active" means. Interviewers reward this.
Think out loud. Say which tables you will join and how, then build the query in small steps.
Check your result. Run it mentally on two or three rows. A JOIN that multiplies rows is the most common silent bug.
Know one dialect well and mention where others differ (LIMIT vs TOP vs FETCH).
Practice writing, not reading. Do the 28 SQL practice problems and keep the SQL cheat sheet handy while you learn.

Where to go next

More worked queries with results: 38 SQL query examples.
Build the fundamentals with the free interactive SQL lessons, especially GROUP BY and joins.
Worried it is too hard? Read Is SQL easy to learn?

FAQ

What SQL topics are asked most in interviews?

Joins, GROUP BY with aggregates, WHERE vs HAVING, subqueries and NULL handling cover most junior interviews. Analyst and engineer roles add CTEs, window functions and query performance.

How do I prepare for a SQL interview in a week?

Days 1 to 2: SELECT, filtering, aggregates. Days 3 to 4: joins and subqueries. Day 5: CTEs and window functions. Days 6 to 7: timed practice on problems you have not seen. Write queries, do not just read them.

What are basic SQL interview questions for freshers?

Questions 1 to 14 above: what SQL is, keys, WHERE vs HAVING, DELETE vs TRUNCATE, NULL, UNION vs UNION ALL and the join types.

Do SQL interviews use a specific database?

Usually not; standard SQL is expected. Mention dialect differences when you know them, such as LIMIT in PostgreSQL, MySQL and SQLite versus TOP in SQL Server.

Where can I practice SQL for interviews for free?

Run the queries above in any free SQLite client, work through our practice problems, and use SQL Easy's free interactive lessons for instant feedback.