20 SQL Interview Questions for Freshers (With Clear Answers)

Gulab Chand Tejwani
PMP · PL-300 · Microsoft Certified Trainer
· 12 min read

SQL interviews for freshers usually test four things: the basics (SELECT, WHERE, GROUP BY, HAVING), joins, simple aggregation problems like "second highest salary" or "find duplicates", and at least one window function such as ROW_NUMBER or RANK. Interviewers care less about perfect syntax and more about whether you can explain why your query works and spot where it could go wrong. The twenty questions below, with answers, cover what most fresher rounds ask.
I'm Gulab Chand Tejwani. I teach SQL and data analysis at TechNet Consultancy in Vallabh Vidyanagar, Anand, and online, and I'm a Microsoft Certified: Power BI Data Analyst Associate and Microsoft Certified Trainer. I wrote these questions myself, based on the topics learners tell me they faced and the gaps I see in mock interviews. Try answering each one before reading mine.
For the query questions, assume an employees table with emp_id, name, dept, salary, manager_id and join_date.

Basics
1. What is the difference between WHERE and HAVING?
WHERE filters rows before grouping. HAVING filters groups after GROUP BY, so it can use aggregates.
SELECT dept, AVG(salary) AS avg_salary
FROM employees
WHERE join_date >= DATE '2024-01-01'
GROUP BY dept
HAVING AVG(salary) > 40000;
Follow-up they like: "Can you use HAVING without GROUP BY?" Yes. The whole table becomes one group.
2. What is the logical order in which a SELECT query runs?
FROM (and joins), WHERE, GROUP BY, HAVING, SELECT, DISTINCT, ORDER BY, then LIMIT or TOP. That's why you usually can't use a column alias from SELECT inside WHERE: WHERE runs first. (ORDER BY runs after SELECT, so aliases work there. MySQL also allows them in HAVING.)
Explaining this order well is one of the clearest signs to an interviewer that you understand SQL rather than memorising it.
3. What is the difference between DELETE, TRUNCATE and DROP?
- DELETE removes rows, can use WHERE, and can be rolled back inside a transaction.
- TRUNCATE removes all rows quickly, can't filter, and resets identity counters in most databases.
- DROP removes the table itself, structure and all.
Rollback behaviour for TRUNCATE differs between databases, so say that if they push.
4. What is a primary key, and how is it different from a unique key?
A primary key uniquely identifies each row and can't be NULL. A table has only one. A unique constraint also prevents duplicates, but a table can have several, and most databases allow NULL in a unique column (rules about how many NULLs vary by database).
5. What is a foreign key?
A column that refers to the primary key of another table, such as orders.customer_id pointing to customers.customer_id. It stops you inserting an order for a customer who doesn't exist.
6. What does NULL mean, and why doesn't = NULL work?
NULL means unknown or missing. Any comparison with NULL using = gives unknown, not true, so the row is filtered out. Use IS NULL or IS NOT NULL. Also remember that COUNT(column) skips NULLs while COUNT(*) counts all rows.
7. What is the difference between UNION and UNION ALL?
UNION combines results and removes duplicates. UNION ALL keeps duplicates and is faster, because it skips the de-duplication step. Use UNION ALL unless you actually need duplicates removed.
| DELETE | TRUNCATE | DROP | |
|---|---|---|---|
| Removes | Chosen rows | All rows | The whole table |
| WHERE allowed | Yes | No | No |
| Table structure kept | Yes | Yes | No |
| Speed on big tables | Slower, row by row | Fast | Fast |
| Rollback | Yes, in a transaction | Depends on the database | Depends on the database |
Joins
8. Explain INNER JOIN and LEFT JOIN with an example.
INNER JOIN returns only matching rows from both tables. LEFT JOIN returns all rows from the left table, with NULLs where the right table has no match. I go through both with small tables in SQL joins explained with examples. If you can draw the result of a join by hand for two four-row tables, you're ready for this question.
9. Find employees who don't have a manager listed in the table.
SELECT e.name
FROM employees e
LEFT JOIN employees m ON m.emp_id = e.manager_id
WHERE m.emp_id IS NULL;
That's a self join plus an anti-join. It returns the top person (manager_id is NULL) and anyone whose manager_id points to someone not in the table.
10. Show each employee with their manager's name.
SELECT e.name AS employee, m.name AS manager
FROM employees e
LEFT JOIN employees m ON m.emp_id = e.manager_id;
Use LEFT JOIN so the person at the top still appears.
11. Why might a join return more rows than the left table has?
Because the join key isn't unique on the right side, so a single left row matches several right rows. Mentioning that you check row counts before and after joins is a strong answer.
Aggregation problems
12. Find the second highest salary.
SELECT MAX(salary) AS second_highest
FROM employees
WHERE salary < (SELECT MAX(salary) FROM employees);
Or with a window function, which extends to "Nth highest":
SELECT DISTINCT salary
FROM (
SELECT salary, DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
FROM employees
) t
WHERE rnk = 2;
Follow-up: "What if two people share the top salary?" DENSE_RANK handles ties correctly. A plain ORDER BY salary DESC LIMIT 1 OFFSET 1 doesn't.
13. Find duplicate names in the table.
SELECT name, COUNT(*) AS cnt
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;
14. Count employees and average salary in each department, highest average first.
SELECT dept, COUNT(*) AS headcount, ROUND(AVG(salary), 0) AS avg_salary
FROM employees
GROUP BY dept
ORDER BY avg_salary DESC;
15. Find departments where every employee earns more than 30,000.
SELECT dept
FROM employees
GROUP BY dept
HAVING MIN(salary) > 30000;
A nice trick: "every row satisfies X" is often the same as "the minimum satisfies X".
Window functions
16. What's the difference between ROW_NUMBER, RANK and DENSE_RANK?
For salaries 90, 80, 80, 70:
- ROW_NUMBER gives 1, 2, 3, 4 (always unique)
- RANK gives 1, 2, 2, 4 (ties share, then a gap)
- DENSE_RANK gives 1, 2, 2, 3 (ties share, no gap)

17. Find the highest-paid employee in each department.
WITH ranked AS (
SELECT name, dept, salary,
RANK() OVER (PARTITION BY dept ORDER BY salary DESC) AS rnk
FROM employees
)
SELECT name, dept, salary
FROM ranked
WHERE rnk = 1;
Using RANK returns all tied top earners. Mention that choice; interviewers like hearing that you thought about ties.
18. Show each employee's salary next to their department's average.
SELECT name, dept, salary,
AVG(salary) OVER (PARTITION BY dept) AS dept_avg
FROM employees;
Unlike GROUP BY, the window function keeps every row. That's the core idea of window functions, so say it out loud.
Practical and design questions
19. What is an index, and when could it slow things down?
An index is a separate structure, like the index at the back of a book, that helps the database find rows without scanning the whole table. It speeds up reads on the indexed columns but slows down inserts, updates and deletes, because the index has to be updated too, and it takes storage. Index the columns you filter and join on often, not everything.
20. What is normalisation, in one or two sentences?
Organising tables so each fact is stored once, which avoids update mistakes. For example, store a customer's city in the customers table, not repeated on every order. Analysts often work with denormalised tables for reporting speed, and it's fine to mention that trade-off.
Test yourself: four quick multiple-choice questions
Some screening tests use multiple choice rather than live coding. Try these before opening the answers.
A table has 10 rows. The column bonus is NULL in 3 of them. What does SELECT COUNT(bonus) FROM employees return?
- A10
- B3
- C7
- DAn error, because of the NULLs
Show answer and explanation
Correct answer: C
COUNT(column) skips NULLs, so it counts the 7 rows that have a bonus. COUNT(*) would return 10. This difference comes up often in "why don't my numbers match" questions.
customers has 100 rows. Every customer has between 0 and 3 orders, and 20 customers have none. How many rows can customers LEFT JOIN orders return?
- AExactly 100
- BAt least 100, possibly more
- CExactly 80
- DAt most 100
Show answer and explanation
Correct answer: B
A LEFT JOIN keeps every customer, so you get at least 100 rows. Customers with two or three orders appear two or three times, so the count can go higher. Exactly 100 only happens if nobody has more than one order.
You want departments whose average salary is above 50,000. Which clause should hold the condition AVG(salary) > 50000?
- AWHERE
- BON
- CORDER BY
- DHAVING
Show answer and explanation
Correct answer: D
Aggregates aren't available in WHERE, because WHERE runs before grouping. HAVING filters groups after GROUP BY, so it can use AVG.
Salaries are 90, 80, 80 and 70. You need everyone with the second-highest salary, including ties. Which function, filtered to = 2, does that?
- ADENSE_RANK() OVER (ORDER BY salary DESC)
- BROW_NUMBER() OVER (ORDER BY salary DESC)
- CRANK() OVER (ORDER BY salary ASC)
- DCOUNT(*) OVER (ORDER BY salary DESC)
Show answer and explanation
Correct answer: A
DENSE_RANK gives 1, 2, 2, 3, so filtering to 2 returns both people earning 80. ROW_NUMBER would return only one of them. RANK in ascending order ranks from the lowest salary, which answers a different question.
How do I prepare for a fresher SQL round?
A few things that help more than reading another list:
- Write every query by hand on a real database. PostgreSQL and MySQL are free.
- Practise saying your reasoning out loud while you type. Many rounds are on a shared screen.
- Before running a query, predict roughly how many rows it should return.
- Learn the logical query order (question 2). It answers a surprising number of "why doesn't this work" questions.
- Do a timed mock: three questions in thirty minutes.
For broader practice, my post on 15 SQL queries for data analysis covers the patterns behind most business questions. Our SQL interview preparation page has more practice material, and the SQL training course covers all of this with mock interviews, in the Vallabh Vidyanagar, Anand classroom or live online.

What to do when you get stuck
It happens to everyone, and how you handle it is part of the assessment. Say what you've got so far and what's missing: "I have the totals per department; now I need to keep only the top one." Then try the simplest version first, such as solving it for one department with a WHERE clause before generalising. Interviewers would much rather see a working partial answer and clear thinking than silence while you search your memory for perfect syntax.
FAQ
What SQL topics are asked in fresher interviews?
Mostly SELECT and filtering, GROUP BY with HAVING, joins (especially INNER and LEFT), simple subqueries, and common problems like second highest salary and finding duplicates. Many interviews now include one window function question.
Which database should I practise on for interviews?
PostgreSQL or MySQL are both fine and free. Most interview questions use standard SQL, and the few differences (LIMIT versus TOP, date functions) are easy to mention when they come up.
How many SQL questions should I practise before an interview?
Quality beats quantity. If you can solve and explain the twenty here without help, plus a few variations of each, you are in good shape for most fresher rounds.
Are window functions asked for fresher roles?
Increasingly, yes, especially for data analyst positions. ROW_NUMBER, RANK, DENSE_RANK and simple PARTITION BY questions come up often enough that it's worth learning them before you apply.
Do I need to memorise syntax exactly?
Not perfectly. Interviewers usually forgive a small syntax slip if your logic is right and you can explain it. They are much less forgiving of a query that runs but gives the wrong answer.