SQL Interview Questions and What to Learn Next

You have now covered the core of SQL: reading data, summarising it, joining tables, changing data, designing tables and speeding up queries. This last lesson turns that knowledge into interview practice. The questions below are the types that commonly appear in fresher and junior analyst, developer and tester interviews. Interviewers differ, so use these as practice and do not expect an exact match. Answer the SQL ones on the shop database.

Concept questions, with short answers

What is the difference between WHERE and HAVING? WHERE filters rows before grouping and cannot use aggregates. HAVING filters groups after grouping and is used with aggregates like COUNT(*).

What is the difference between INNER JOIN and LEFT JOIN? An inner join returns only rows that match in both tables. A left join returns all rows from the left table, with NULLs on the right when nothing matches.

What is a primary key? A foreign key? A primary key uniquely identifies a row and cannot be NULL. A foreign key is a column that references the primary key of another table and keeps the data linked and consistent.

What is the difference between DELETE, TRUNCATE and DROP? DELETE removes chosen rows (with WHERE) and can be rolled back in a transaction. TRUNCATE removes all rows quickly, and DROP removes the whole table, including its structure.

What is NULL, and how do you test for it? NULL means a missing or unknown value. It is not zero and not empty text. Use IS NULL and IS NOT NULL, because = NULL never matches.

What is the difference between UNION and UNION ALL? Both stack the results of two queries with the same columns. UNION removes duplicate rows, which costs time. UNION ALL keeps everything and is faster.

What is an index and what is the downside? A sorted structure that speeds up lookups. It uses space and slows down writes.

What is normalisation? Organising data so each fact is stored once, by splitting it into related tables, to avoid repeated data and inconsistencies.

Query questions on the shop database

Try each one yourself before reading the answer. Write it, run it and check the output.

1. Find the second most expensive product.

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

Backpack | 1400

OFFSET 1 skips the dearest product. A good follow-up answer: with ties you may prefer DENSE_RANK, so mention it.

2. Find customers who have never placed an order.

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

Hari Singh

3. Find the most expensive product in each category.

SELECT p.category, p.name, p.price
FROM products AS p
WHERE p.price = (
  SELECT MAX(p2.price) FROM products AS p2 WHERE p2.category = p.category
)
ORDER BY p.category;

Bags       | Backpack     | 1400
Home       | Desk lamp    | 650
Office     | Laptop stand | 1800
Stationery | Notebook     | 120

The same answer can be given with RANK() or ROW_NUMBER(). Interviewers like to hear that you know two ways.

4. Find customers with more than one order.

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

1 | 3
2 | 2

5. What percentage of the total price does each of the top 3 products represent?

SELECT name, price,
  ROUND(price * 100.0 / (SELECT SUM(price) FROM products), 1) AS pct
FROM products
ORDER BY price DESC
LIMIT 3;

Laptop stand | 1800 | 35.2
Backpack     | 1400 | 27.4
Mouse        | 700  | 13.7

Note the 100.0. Using 100 with whole numbers could give a rounded-down whole number result in some engines.

6. Show the value of each order. This needs three tables.

SELECT o.id, SUM(p.price * oi.quantity) AS total
FROM orders AS o
JOIN order_items AS oi ON oi.order_id = o.id
JOIN products AS p ON p.id = oi.product_id
GROUP BY o.id
ORDER BY o.id;

1 | 520    6 | 700
2 | 1400   7 | 840
3 | 2500   8 | 650
4 | 1300   9 | 2100
5 | 1200  10 | 320

7. Find the latest order for each customer. Use ROW_NUMBER() with PARTITION BY customer_id ORDER BY order_date DESC and keep rn = 1, as in lesson 13.

How to answer in an interview

1. Repeat the question in your own words and ask about unclear parts: What counts as an active customer? What should happen with ties? Are NULLs possible?
2. Say your plan before typing: “I will join orders to customers, group by customer and count.”
3. Start simple, run it, then add conditions. Build the query in small steps and check each step.
4. Check your result against what you expect. Count a few rows by hand.
5. Mention the edge cases you thought about, such as NULLs, ties and customers with no orders.
6. If you do not know, say so and explain how you would find out. Honesty and clear thinking are valued more than memorised phrases.

What to learn next

• Practise every week. Rewrite the lesson tasks from memory, then invent your own questions about the shop.
• Learn one real engine. Install PostgreSQL or MySQL and run the setup script there. You will see the small differences, such as LIMIT, date functions and text joining.
• Date and time functions for reports: month, year, difference between dates.
• Views and stored procedures for saving queries and repeated logic.
• Connect SQL to a language. Python with pandas for analysis, or Node.js and Express for apps. This site's Node.js and Python courses are good next steps.
• A BI tool such as a free dashboard tool, if you are moving toward analyst work.
• Build a small project: a library, a gym or a clinic database with five tables, a setup script and ten reports. Put it on GitHub. It shows real skill better than a certificate.

SQL rewards practice more than reading. A reasonable plan is half an hour a day for a month. By then the structure of SELECT, JOIN and GROUP BY will feel natural. If you are preparing for interviews more broadly, our interview questions pages and resume tools can help you present this skill honestly: list what you have really built, with small examples, rather than a long list of keywords.

Your task

1. Answer all seven query questions above without looking, then compare with the answers.
2. Write three new questions of your own about the shop data and answer them.
3. Explain WHERE versus HAVING and INNER versus LEFT JOIN out loud, as if to an interviewer.

How to check it worked

• Your answers match the outputs shown.
• You can explain each answer in one or two sentences.
• You have a plan for what to practise next week.