Skip to content

20 SQL Interview Questions for Freshers (With Clear Answers)

Gulab Chand Tejwani

Gulab Chand Tejwani

PMP · PL-300 · Microsoft Certified Trainer

· 12 min read

SQLSQL InterviewFreshersData Analyst InterviewInterview Preparation
20 SQL Interview Questions for Freshers (With Clear Answers)

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.

Four areas fresher SQL interviews test: basics, joins, aggregation and window functions
The four areas the twenty questions below cover, based on mock interviews and what learners report from real rounds.

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.

DELETETRUNCATEDROP
RemovesChosen rowsAll rowsThe whole table
WHERE allowedYesNoNo
Table structure keptYesYesNo
Speed on big tablesSlower, row by rowFastFast
RollbackYes, in a transactionDepends on the databaseDepends on the database
DELETE vs TRUNCATE vs DROP (question 3 at a glance)

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)
Bar chart comparing ROW_NUMBER, RANK and DENSE_RANK values for salaries 90, 80, 80 and 70
The three ranking functions only differ when there are ties. RANK leaves a gap after a tie; DENSE_RANK doesn't.

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.

Question 1Basics · NULL

A table has 10 rows. The column bonus is NULL in 3 of them. What does SELECT COUNT(bonus) FROM employees return?

  1. A10
  2. B3
  3. C7
  4. 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.

Question 2Joins · LEFT JOIN

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?

  1. AExactly 100
  2. BAt least 100, possibly more
  3. CExactly 80
  4. 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.

Question 3Aggregation · HAVING

You want departments whose average salary is above 50,000. Which clause should hold the condition AVG(salary) > 50000?

  1. AWHERE
  2. BON
  3. CORDER BY
  4. 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.

Question 4Window functions · Ties

Salaries are 90, 80, 80 and 70. You need everyone with the second-highest salary, including ties. Which function, filtered to = 2, does that?

  1. ADENSE_RANK() OVER (ORDER BY salary DESC)
  2. BROW_NUMBER() OVER (ORDER BY salary DESC)
  3. CRANK() OVER (ORDER BY salary ASC)
  4. 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.

Five steps to answer a SQL question on a shared screen: clarify, predict, write, check, explain
A routine for live SQL rounds. Most candidates skip Clarify and Predict, and those two steps catch most mistakes.

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.

Choose optional cookies

We use necessary cookies for account security. With your permission, we also use analytics and advertising cookies on marketing pages. See our Privacy Policy.