Most Repeated SQL Interview Questions for Tier-3

College Placements
A Focused Guide to the SQL Questions That Actually Get Asked at TCS,
Infosys, Wipro, Accenture, Capgemini, Cognizant, and Other Mass-Recruiter
& Product Companies

SQL Placement Preparation Guide

2026

Contents
How to Use This Guide 3

Section A: Easy Questions 4

Q1. Select All Columns From a Table . . . . . . . . . . . . . . . . . . . . . 4
Q2. Select Specific Columns . . . . . . . . . . . . . . . . . . . . . . . . . . 5
Q3. Filter Rows Using WHERE . . . . . . . . . . . . . . . . . . . . . . . . . 5
Q4. Get Unique Values Using DISTINCT . . . . . . . . . . . . . . . . . . . . 6
Q5. Sort Results Using ORDER BY . . . . . . . . . . . . . . . . . . . . . . . 7
Q6. Limit the Number of Rows Returned . . . . . . . . . . . . . . . . . . . 8
Q7. Filter Using Pattern Matching (LIKE) . . . . . . . . . . . . . . . . . . . 9
Q8. Filter Using IN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
Q9. Filter Using BETWEEN . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
Q10. Find Rows With NULL Values . . . . . . . . . . . . . . . . . . . . . . 11
Q11. Count Total Rows (COUNT) . . . . . . . . . . . . . . . . . . . . . . . . 12
Q12. Find Total Salary Using SUM . . . . . . . . . . . . . . . . . . . . . . . 13
Q13. Find Average Salary Using AVG . . . . . . . . . . . . . . . . . . . . . 13
Q14. Find Minimum Salary Using MIN . . . . . . . . . . . . . . . . . . . . 14
Q15. Find Maximum Salary Using MAX . . . . . . . . . . . . . . . . . . . . 15
Q16. Group Employees by Department (GROUP BY) . . . . . . . . . . . . . 15
Q17. Filter Groups Using HAVING . . . . . . . . . . . . . . . . . . . . . . . 16
Q18. Count Employees Department-Wise . . . . . . . . . . . . . . . . . . . 17
Q19. CASE WHEN — Categorize Salary Into Bands . . . . . . . . . . . . . . 18

Section B: Medium Questions 20

Q20. INNER JOIN — Combine Two Related Tables . . . . . . . . . . . . . . 20
Q21. LEFT JOIN — Keep All Rows From the Left Table . . . . . . . . . . . . 21
Q22. Customers With No Orders . . . . . . . . . . . . . . . . . . . . . . . . 22
Q23. SELF JOIN — Employees and Their Managers . . . . . . . . . . . . . 23
Q24. Multiple JOINs Across Three Tables . . . . . . . . . . . . . . . . . . . 24

1
 Q25. Subquery — Employees Earning More Than the Average Salary . . . . 25
Q26. EXISTS — Customers Who Have Placed at Least One Order . . . . . . 26
Q27. NOT EXISTS — Customers Who Never Placed an Order . . . . . . . . 27
Q28. Correlated Subquery — Employees Earning More Than Their Depart-
ment’s Average . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
Q29. Simple CTE — Rewrite a Subquery Using WITH . . . . . . . . . . . . 30
Q30. Date Functions — Orders Placed in the Last 30 Days . . . . . . . . . . 31
Q31. Find Duplicate Records in a Table . . . . . . . . . . . . . . . . . . . . 31
Q32. Delete Duplicate Rows (Query Only) . . . . . . . . . . . . . . . . . . . 32
Q33. Odd and Even Numbered Rows . . . . . . . . . . . . . . . . . . . . . 34
Q34. Second Highest Salary . . . . . . . . . . . . . . . . . . . . . . . . . . 34
Q35. Third Highest Salary . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
Q36. Nth Highest Salary . . . . . . . . . . . . . . . . . . . . . . . . . . . . 37
Q37. Highest Salary in Each Department . . . . . . . . . . . . . . . . . . . 38
Q38. Employees Earning More Than Their Managers . . . . . . . . . . . . . 39
Q39. Top 3 Salaries (Overall) . . . . . . . . . . . . . . . . . . . . . . . . . . 40
Q40. Running Total of Salaries (Simple Version) . . . . . . . . . . . . . . . 41
Q41. Employees Who Joined in the Last N Months (Combining WHERE, Date
Functions, and ORDER BY) . . . . . . . . . . . . . . . . . . . . . . . . 42

Section C: Interview Revision Kit 44

Top 20 SQL Interview Tips . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
SQL Query-Writing Strategy . . . . . . . . . . . . . . . . . . . . . . . . . . 45
SQL Execution Order (Logical Order of Operations) . . . . . . . . . . . . . 45
GROUP BY vs HAVING . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
WHERE vs HAVING . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
JOIN Cheat Sheet . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46
Aggregate Function Cheat Sheet . . . . . . . . . . . . . . . . . . . . . . . . 47
Common SQL Interview Mistakes (Summary) . . . . . . . . . . . . . . . . . 47
One-Page SQL Revision Sheet . . . . . . . . . . . . . . . . . . . . . . . . . 48

2
How to Use This Guide
This guide does not try to cover all of SQL. It covers exactly one thing: the SQL ques-
tions that get repeated, year after year, in campus placement interviews at mass-
recruiter IT services companies (TCS, Infosys, Wipro, Accenture, Capgemini, Cog-
nizant, Deloitte, HCL, Tech Mahindra, LTIMindtree, IBM, PwC, EY, Zensar, Persistent,
Hexaware, Nagarro, Publicis Sapient) and in the basic SQL rounds of product-based
companies.
What you will find here
• 41 questions, arranged from easiest to hardest, covering every topic that shows
up again and again in interview experiences shared by students from Tier-3 col-
leges.
• For every question: the question itself, its difficulty, the concepts it tests, a ta-
ble schema, sample data, a working SQL solution, a line-by-line explanation, the
expected output, common mistakes candidates make, and similar questions in-
terviewers ask as follow-ups.
• A revision section at the end with 20 interview tips, a query-writing strategy,
SQL execution order, comparison tables (GROUP BY vs HAVING, WHERE vs
HAVING), a JOIN cheat sheet, an aggregate function cheat sheet, common mis-
takes, and a one-page revision sheet.
What you will not find here
Recursive CTEs, complex window function problems, PIVOT/UNPIVOT, dynamic SQL,
stored procedures, triggers, views, transactions, and hard LeetCode-style SQL. These
are rarely, if ever, asked to freshers in a first or second round SQL interview, and
including them would only distract you from what actually gets asked.
How to practice with this guide
1. Go through the questions in order — they are sequenced from easiest to hardest.
2. For each question, read only the question and try to write the query yourself
before looking at the solution.
3. Compare your query with the given solution and read the explanation.
4. Pay close attention to the “Common Interview Mistakes” box — this is usually
exactly where candidates lose marks.
5. Once you’re comfortable, revise using the one-page revision sheet before your
interview.
All examples use standard SQL (MySQL/SQL Server style syntax, with notes wherever
a database-specific function is used, such as DATE_SUB in MySQL vs DATEADD in SQL
Server).

3
Section A: Easy Questions
These are almost always the first few questions in any written SQL test or the opening
questions of a technical interview. They test whether you know the basic syntax.

Q1. Select All Columns From a Table

Difficulty: Easy Concepts Tested: SELECT, basic syntax
Table Schema

Column Type
emp_id INT (PK)
emp_name VARCHAR(50)
department VARCHAR(30)
salary INT
join_date DATE

Sample Data (Employee)

emp_id emp_name department salary join_date

1 Aman IT 45000 2022-01-10
2 Riya HR 38000 2021-06-15
3 Kabir IT 52000 2020-03-22
4 Sneha Finance 41000 2023-02-01

SQL Solution
SELECT * FROM Employee;

Line-by-Line Explanation
• SELECT * chooses every column in the table.
• FROM Employee tells the database which table to read from.
• The semicolon ends the statement.
Expected Output
All 4 rows shown above, with all 5 columns, exactly as stored.
Common Interview Mistakes
• Using SELECT * in real production code (interviewers often ask “is this a good
practice?” — the correct answer is no, because it fetches unnecessary columns
and hurts performance).
• Forgetting the semicolon (usually not an error, but good habit to mention).
Similar Questions Interviewers Ask
• Why is SELECT * considered bad practice?

4
 • How do you select all columns except one?

Q2. Select Specific Columns

Difficulty: Easy Concepts Tested: SELECT with column list
Table Schema
Same Employee table as Q1.
SQL Solution
SELECT emp_name, department, salary
FROM Employee;

Line-by-Line Explanation
• Only emp_name, department, and salary are listed after SELECT, so only these
three columns are returned.
• Column order in the output follows the order you list them in, not the table’s
original order.
Expected Output

emp_name department salary

Aman IT 45000
Riya HR 38000
Kabir IT 52000
Sneha Finance 41000

Common Interview Mistakes

• Mixing up column order and expecting the original table order.
• Forgetting commas between column names.
Similar Questions Interviewers Ask
• How do you rename a column in the output (use AS)?
• How do you select columns from two tables with the same column name?

Q3. Filter Rows Using WHERE

Difficulty: Easy Concepts Tested: WHERE, comparison operators
Table Schema
Same Employee table.
SQL Solution

5
SELECT emp_name, salary
FROM Employee
WHERE department = 'IT';

Line-by-Line Explanation
• WHERE department = 'IT' filters rows so only employees whose department is
IT are returned.
• The WHERE clause runs before columns are selected, so it can filter on columns
that aren’t even in the SELECT list.
Expected Output

emp_name salary
Aman 45000
Kabir 52000

Common Interview Mistakes

• Using WHERE with an aggregate function like WHERE COUNT(*) > 5 (this is invalid
— HAVING must be used instead).
• Using = with NULL (should use IS NULL).
Similar Questions Interviewers Ask
• What is the difference between WHERE and HAVING?
• Can WHERE be used with GROUP BY?

Q4. Get Unique Values Using DISTINCT

Difficulty: Easy Concepts Tested: DISTINCT
Table Schema
Same Employee table.
SQL Solution
SELECT DISTINCT department
FROM Employee;

Line-by-Line Explanation
• DISTINCT removes duplicate values from the result set.
• Applied here on a single column, it returns each department name only once, no
matter how many employees belong to it.
Expected Output

6
 department
IT
HR
Finance

Common Interview Mistakes

• Thinking DISTINCT works only on the whole row when multiple columns are se-
lected — in fact SELECT DISTINCT col1, col2 removes duplicate combinations
of col1 and col2, not duplicates of col1 alone.
• Forgetting that DISTINCT can be slow on very large tables since it usually requires
a sort or hash operation.
Similar Questions Interviewers Ask
• How do you count the number of distinct departments? (COUNT(DISTINCT de-
partment))
• Difference between DISTINCT and GROUP BY?

Q5. Sort Results Using ORDER BY

Difficulty: Easy Concepts Tested: ORDER BY, ASC/DESC
Table Schema
Same Employee table.
SQL Solution
SELECT emp_name, salary
FROM Employee
ORDER BY salary DESC;

Line-by-Line Explanation
• ORDER BY salary DESC sorts the result set by the salary column from highest to
lowest.
• If DESC were removed, the default sort order is ascending (ASC).
Expected Output

emp_name salary
Kabir 52000
Aman 45000
Sneha 41000
Riya 38000

Common Interview Mistakes

7
 • Assuming ORDER BY runs before WHERE — it actually runs almost last, after filtering
and grouping.
• Forgetting that without ORDER BY, SQL does not guarantee any particular row
order.
Similar Questions Interviewers Ask
• How do you sort by multiple columns?
• How do you sort in descending order and then break ties using another column?

Q6. Limit the Number of Rows Returned

Difficulty: Easy Concepts Tested: LIMIT / TOP
Table Schema
Same Employee table.
SQL Solution
-- MySQL / PostgreSQL
SELECT emp_name, salary
FROM Employee
ORDER BY salary DESC
LIMIT 2;

-- SQL Server
SELECT TOP 2 emp_name, salary
FROM Employee
ORDER BY salary DESC;

Line-by-Line Explanation
• The rows are first sorted by salary in descending order.
• LIMIT 2 (or TOP 2 in SQL Server) then keeps only the first 2 rows of that sorted
result — effectively the two highest-paid employees.
Expected Output

emp_name salary
Kabir 52000
Aman 45000

Common Interview Mistakes

• Using LIMIT without ORDER BY — this returns an arbitrary set of rows, not neces-
sarily the “top” ones.
• Confusing LIMIT 2 with “row number 2” — LIMIT 2 returns the first two rows,
not the second row.

8
Similar Questions Interviewers Ask
• How would you get the top 3 salaries? (covered later as its own question)
• What is the syntax difference between MySQL’s LIMIT and SQL Server’s TOP?

Q7. Filter Using Pattern Matching (LIKE)

Difficulty: Easy Concepts Tested: LIKE, wildcards (%, _)
Table Schema
Same Employee table.
SQL Solution
SELECT emp_name
FROM Employee
WHERE emp_name LIKE 'A%';

Line-by-Line Explanation
• LIKE 'A%' matches any name that starts with the letter “A”.
• % is a wildcard for zero or more characters; _ (not used here) matches exactly
one character.
Expected Output

emp_name
Aman

Common Interview Mistakes

• Confusing % (any number of characters) with _ (exactly one character).
• Forgetting that LIKE is case-insensitive in some databases (MySQL default) but
case-sensitive in others (PostgreSQL) — worth mentioning if asked.
Similar Questions Interviewers Ask
• How do you find names that end with a specific letter?
• How do you find names containing a substring anywhere in the middle?

Q8. Filter Using IN

Difficulty: Easy Concepts Tested: IN operator
Table Schema
Same Employee table.
SQL Solution

9
SELECT emp_name, department
FROM Employee
WHERE department IN ('IT', 'HR');

Line-by-Line Explanation
• IN ('IT', 'HR') checks if department matches any value in the given list.
• This is a shorter, more readable way of writing department = 'IT' OR department
= 'HR'.
Expected Output

emp_name department
Aman IT
Riya HR
Kabir IT

Common Interview Mistakes

• Writing multiple OR conditions instead of a cleaner IN clause when asked to sim-
plify a query.
• Forgetting IN can also take a subquery, e.g. WHERE dept_id IN (SELECT dept_id
FROM Department WHERE ...).
Similar Questions Interviewers Ask
• What is the difference between IN and EXISTS?
• How do you use NOT IN, and what danger does it have with NULL values?

Q9. Filter Using BETWEEN

Difficulty: Easy Concepts Tested: BETWEEN (inclusive range)
Table Schema
Same Employee table.
SQL Solution
SELECT emp_name, salary
FROM Employee
WHERE salary BETWEEN 40000 AND 50000;

Line-by-Line Explanation
• BETWEEN 40000 AND 50000 keeps rows where salary is greater than or equal to
40000 and less than or equal to 50000.
• BETWEEN is always inclusive of both boundary values.

10
Expected Output

emp_name salary
Aman 45000
Sneha 41000

Common Interview Mistakes

• Assuming BETWEEN is exclusive of the boundaries (it is inclusive).
• Using BETWEEN on dates without matching the exact time component (e.g., a date-
time column at midnight can silently exclude a whole day) — worth mentioning
as a “gotcha”.
Similar Questions Interviewers Ask
• How would you write the same query using >= and <=?
• How does BETWEEN behave with date ranges?

Q10. Find Rows With NULL Values

Difficulty: Easy Concepts Tested: IS NULL / IS NOT NULL
Table Schema

Column Type
emp_id INT (PK)
emp_name VARCHAR(50)
manager_id INT (nullable)

Sample Data (Employee2)

emp_id emp_name manager_id

1 Aman 3
2 Riya 3
3 Kabir NULL
4 Sneha 1

SQL Solution
SELECT emp_name
FROM Employee2
WHERE manager_id IS NULL;

Line-by-Line Explanation

11
 • IS NULL is the only correct way to check for missing values; = NULL never works
because NULL means “unknown” and is not equal to anything, not even itself.
• Here it finds the employee who has no manager, i.e., the top of the hierarchy.
Expected Output

emp_name
Kabir

Common Interview Mistakes

• Writing WHERE manager_id = NULL (this always returns zero rows).
• Forgetting IS NOT NULL for the opposite condition.
Similar Questions Interviewers Ask
• Why doesn’t = NULL work in SQL?
• How do aggregate functions like COUNT and AVG handle NULL values?

Q11. Count Total Rows (COUNT)

Difficulty: Easy Concepts Tested: COUNT aggregate function
Table Schema
Same Employee table (Q1).
SQL Solution
SELECT COUNT(*) AS total_employees
FROM Employee;

Line-by-Line Explanation
• COUNT(*) counts every row in the table, including rows that contain NULLs in
some columns.
• AS total_employees renames the output column so it’s readable.
Expected Output

total_employees
4

Common Interview Mistakes

• Confusing COUNT(*) (counts all rows) with COUNT(column_name) (counts only non-
NULL values in that column) — a very common follow-up question.
• Using COUNT(DISTINCT column) incorrectly, e.g. COUNT(DISTINCT col1, col2) is
not standard in every database.

12
Similar Questions Interviewers Ask
• What is the difference between COUNT(*), COUNT(1), and COUNT(column_name)?
• How do you count employees per department? (covered later)

Q12. Find Total Salary Using SUM

Difficulty: Easy Concepts Tested: SUM aggregate function
Table Schema
Same Employee table.
SQL Solution
SELECT SUM(salary) AS total_salary
FROM Employee;

Line-by-Line Explanation
• SUM(salary) adds up the salary value from every row.
• NULL values are automatically ignored by SUM, so they don’t need special han-
dling.
Expected Output

total_salary
176000

Common Interview Mistakes

• Forgetting SUM ignores NULLs (some candidates wrongly assume it errors out or
treats NULL as 0).
• Applying SUM on a non-numeric column, which throws a type error.
Similar Questions Interviewers Ask
• How do you find total salary department-wise? (needs GROUP BY, covered later)
• What happens if all values in the column are NULL? (SUM returns NULL, not 0)

Q13. Find Average Salary Using AVG

Difficulty: Easy Concepts Tested: AVG aggregate function
Table Schema
Same Employee table.
SQL Solution

13
SELECT AVG(salary) AS avg_salary
FROM Employee;

Line-by-Line Explanation
• AVG(salary) computes the mean of all salary values, ignoring NULLs both in
the sum and in the count of rows used for division.
Expected Output

avg_salary
44000

Common Interview Mistakes

• Assuming AVG divides by the total row count of the table rather than the count
of non-NULL values.
• Not rounding the result when asked (ROUND(AVG(salary), 2)).
Similar Questions Interviewers Ask
• How do you find employees earning more than the average salary? (covered
later, using a subquery)
• How is AVG affected by NULL values compared to SUM?

Q14. Find Minimum Salary Using MIN

Difficulty: Easy Concepts Tested: MIN aggregate function
Table Schema
Same Employee table.
SQL Solution
SELECT MIN(salary) AS min_salary
FROM Employee;

Line-by-Line Explanation
• MIN(salary) scans all rows and returns the smallest salary value.
• MIN works on numbers, dates, and even strings (alphabetical order).
Expected Output

min_salary
38000

Common Interview Mistakes

14
 • Trying to also fetch the employee’s name in the same SELECT without GROUP BY
or a subquery, e.g. SELECT emp_name, MIN(salary) FROM Employee — this is
invalid/misleading in strict SQL modes because emp_name isn’t aggregated or
grouped.
Similar Questions Interviewers Ask
• How do you find the employee(s) who earn the minimum salary? (needs a sub-
query: WHERE salary = (SELECT MIN(salary) FROM Employee))
• Can MIN be used on a date column?

Q15. Find Maximum Salary Using MAX

Difficulty: Easy Concepts Tested: MAX aggregate function
Table Schema
Same Employee table.
SQL Solution
SELECT MAX(salary) AS max_salary
FROM Employee;

Line-by-Line Explanation
• MAX(salary) returns the largest value found in the salary column across all rows.
Expected Output

max_salary
52000

Common Interview Mistakes

• Same trap as MIN — trying to select non-aggregated columns alongside MAX with-
out grouping or a subquery.
Similar Questions Interviewers Ask
• How do you find the name of the highest-paid employee? (WHERE salary = (SE-
LECT MAX(salary) FROM Employee))
• What’s the difference between using MAX versus ORDER BY ... LIMIT 1?

Q16. Group Employees by Department (GROUP BY)

Difficulty: Easy Concepts Tested: GROUP BY with aggregate functions
Table Schema

15
Same Employee table.
SQL Solution
SELECT department, SUM(salary) AS dept_total_salary
FROM Employee
GROUP BY department;

Line-by-Line Explanation
• GROUP BY department collects all rows with the same department value into one
group.
• SUM(salary) is then calculated separately for each group instead of for the whole
table.
Expected Output

department dept_total_salary
IT 97000
HR 38000
Finance 41000

Common Interview Mistakes

• Selecting a column that is neither aggregated nor part of the GROUP BY list —
this causes an error in strict SQL databases (MySQL with ONLY_FULL_GROUP_BY,
PostgreSQL, SQL Server).
• Forgetting that GROUP BY happens after WHERE but before ORDER BY.
Similar Questions Interviewers Ask
• Can you GROUP BY more than one column?
• What’s the difference between GROUP BY and DISTINCT?

Q17. Filter Groups Using HAVING

Difficulty: Easy Concepts Tested: HAVING with GROUP BY
Table Schema
Same Employee table.
SQL Solution
SELECT department, SUM(salary) AS dept_total_salary
FROM Employee
GROUP BY department
HAVING SUM(salary) > 40000;

Line-by-Line Explanation

16
 • Rows are grouped by department and SUM(salary) is computed per group, ex-
actly as in Q16.
• HAVING SUM(salary) > 40000 then removes any group whose total salary is 40000
or less — this filtering happens after grouping, unlike WHERE.
Expected Output

department dept_total_salary
IT 97000
Finance 41000

Common Interview Mistakes

• Using WHERE SUM(salary) > 40000 instead of HAVING — this is a syntax error
because WHERE cannot contain aggregate functions.
• Forgetting HAVING runs after GROUP BY, so it can filter on the aggregated value,
while WHERE can only filter on raw column values before grouping.
Similar Questions Interviewers Ask
• Difference between WHERE and HAVING? (also covered in the cheat sheet sec-
tion)
• Can you use both WHERE and HAVING in the same query? (yes — WHERE filters rows
first, then grouping happens, then HAVING filters groups)

Q18. Count Employees Department-Wise

Difficulty: Easy Concepts Tested: GROUP BY, COUNT
Table Schema
Same Employee table.
SQL Solution
SELECT department, COUNT(*) AS num_employees
FROM Employee
GROUP BY department;

Line-by-Line Explanation
• Employees are grouped by department.
• COUNT(*) then counts how many rows (employees) fall into each department
group.
Expected Output

department num_employees
IT 2

17
 department num_employees
HR 1
Finance 1

Common Interview Mistakes

• Using COUNT(department) instead of COUNT(*) when some department values
could be NULL — this would silently give a wrong (lower) count.
• Forgetting to add ORDER BY num_employees DESC when the interviewer specifi-
cally asks for departments sorted by headcount.
Similar Questions Interviewers Ask
• Which department has the most employees? (add ORDER BY num_employees DESC
LIMIT 1)
• How do you list only departments with more than 1 employee? (add HAVING
COUNT(*) > 1)

Q19. CASE WHEN — Categorize Salary Into Bands

Difficulty: Easy Concepts Tested: CASE WHEN, conditional logic
Table Schema
Same Employee table.
SQL Solution
SELECT emp_name, salary,
CASE
WHEN salary >= 50000 THEN 'High'
WHEN salary >= 40000 THEN 'Medium'
ELSE 'Low'
END AS salary_band
FROM Employee;

Line-by-Line Explanation
• CASE WHEN condition1 THEN result1 WHEN condition2 THEN result2 ELSE
default END works like an if-else chain, evaluated top to bottom for each row.
• For each employee, the first matching condition decides the label; if none match,
ELSE 'Low' is used.
• AS salary_band names this new computed column.
Expected Output

emp_name salary salary_band

Aman 45000 Medium
Riya 38000 Low

18
 emp_name salary salary_band
Kabir 52000 High
Sneha 41000 Medium

Common Interview Mistakes

• Writing conditions in the wrong order (e.g., checking salary >= 40000 before
salary >= 50000) — since CASE stops at the first true condition, a 52000 salary
would wrongly get labeled ‘Medium’.
• Forgetting the END keyword, which is mandatory.
Similar Questions Interviewers Ask
• How is CASE WHEN different from IF() in MySQL?
• Can CASE WHEN be used inside ORDER BY or WHERE? (yes, it’s an expression, so it
can go almost anywhere a value can)

19
Section B: Medium Questions
This is the section that decides most interviews. JOINs, subqueries, and the “Nth
highest salary” family of questions are asked at almost every single company on the
list.

Q20. INNER JOIN — Combine Two Related Tables

Difficulty: Medium Concepts Tested: INNER JOIN
Table Schema
Employee(emp_id, emp_name, dept_id) — Department(dept_id, dept_name)

emp_id emp_name dept_id

1 Aman 10
2 Riya 20
3 Kabir 10
4 Sneha NULL

dept_id dept_name
10 IT
20 HR
30 Finance

SQL Solution
SELECT e.emp_name, d.dept_name
FROM Employee e
INNER JOIN Department d
ON e.dept_id = d.dept_id;

Line-by-Line Explanation
• INNER JOIN Department d ON e.dept_id = d.dept_id matches rows from Em-
ployee to rows in Department wherever the dept_id values are equal.
• Only rows that have a match in both tables are returned; Sneha (NULL dept_id)
and Finance (no employees) are both dropped.
• e and d are table aliases used to keep column references short.
Expected Output

emp_name dept_name
Aman IT
Riya HR
Kabir IT

20
Common Interview Mistakes
• Forgetting the ON condition, which produces a cross join (every row combined
with every row) instead of a proper match.
• Assuming INNER JOIN returns all rows from the left table like LEFT JOIN does.
Similar Questions Interviewers Ask
• What’s the difference between INNER JOIN and LEFT JOIN?
• Can you join more than two tables? (yes, covered later)

Q21. LEFT JOIN — Keep All Rows From the Left Table
Difficulty: Medium Concepts Tested: LEFT JOIN
Table Schema
Same Employee and Department tables as Q20.
SQL Solution
SELECT e.emp_name, d.dept_name
FROM Employee e
LEFT JOIN Department d
ON e.dept_id = d.dept_id;

Line-by-Line Explanation
• LEFT JOIN keeps every row from the left table (Employee), regardless of whether
a match is found in Department.
• Where there is no match (Sneha’s dept_id is NULL), the columns coming from
the right table (dept_name) are filled with NULL.
Expected Output

emp_name dept_name
Aman IT
Riya HR
Kabir IT
Sneha NULL

Common Interview Mistakes

• Confusing LEFT JOIN with RIGHT JOIN — the “left” table is whichever table is
written first (or before LEFT JOIN).
• Placing a filter on the right-hand table in the WHERE clause instead of the ON clause,
e.g. WHERE d.dept_name = 'IT' — this silently turns a LEFT JOIN back into an
INNER JOIN because WHERE removes the NULL rows.
Similar Questions Interviewers Ask

21
 • How do you find rows that exist only in the left table and not in the right table?
(LEFT JOIN ... WHERE right_table.key IS NULL — this is exactly the pattern
used for “customers with no orders”)
• What is a RIGHT JOIN, and can it always be rewritten as a LEFT JOIN? (yes, by
swapping table order)

Q22. Customers With No Orders

Difficulty: Medium Concepts Tested: LEFT JOIN + IS NULL
Table Schema
Customers(customer_id, customer_name) — Orders(order_id, customer_id, or-
der_date)

customer_id customer_name
1 Ravi
2 Meena
3 Farhan

order_id customer_id order_date

101 1 2026-07-01
102 1 2026-07-15

SQL Solution
SELECT c.customer_name
FROM Customers c
LEFT JOIN Orders o
ON c.customer_id = o.customer_id
WHERE o.order_id IS NULL;

Line-by-Line Explanation
• LEFT JOIN Orders keeps every customer, matching them to their orders where
possible.
• Customers with no orders end up with NULL in every column that came from
Orders, including order_id.
• WHERE o.order_id IS NULL keeps only those customers — i.e., customers who
never placed a single order.
Expected Output

customer_name
Meena

22
 customer_name
Farhan

Common Interview Mistakes

• Using INNER JOIN instead of LEFT JOIN — an inner join can never find “no match”
rows, so it would return an empty or wrong result.
• Checking IS NULL on a column that could genuinely be NULL for a matched row
(better to check the join key or primary key of the right table, e.g. order_id, not
a nullable business column).
Similar Questions Interviewers Ask
• How do you find customers who placed at least one order? (use INNER JOIN or
EXISTS)
• Can this be solved with NOT IN or NOT EXISTS instead? (yes, shown later)

Q23. SELF JOIN — Employees and Their Managers

Difficulty: Medium Concepts Tested: SELF JOIN, table aliases
Table Schema
Employee(emp_id, emp_name, manager_id)

emp_id emp_name manager_id

1 Aman 3
2 Riya 3
3 Kabir NULL
4 Sneha 1

SQL Solution
SELECT e.emp_name AS employee, m.emp_name AS manager
FROM Employee e
LEFT JOIN Employee m
ON e.manager_id = m.emp_id;

Line-by-Line Explanation
• The Employee table is joined to itself, so it needs two different aliases (e for the
employee row, m for the manager row) to tell the two “copies” apart.
• ON e.manager_id = m.emp_id matches each employee’s manager_id to the emp_id
of their manager in the second copy of the table.
• LEFT JOIN is used so that Kabir (who has no manager) still appears, with manager
shown as NULL.

23
Expected Output

employee manager
Aman Kabir
Riya Kabir
Kabir NULL
Sneha Aman

Common Interview Mistakes

• Forgetting to use two different aliases for the same table, which makes column
references ambiguous.
• Using INNER JOIN instead of LEFT JOIN, which would silently drop the top-level
employee with no manager.
Similar Questions Interviewers Ask
• How would you find employees who earn more than their managers? (covered
later)
• How do you find employees who have no one reporting to them?

Q24. Multiple JOINs Across Three Tables

Difficulty: Medium Concepts Tested: Multiple JOINs, JOIN chaining
Table Schema
Employee(emp_id, emp_name, dept_id) — Department(dept_id, dept_name) —
Project(project_id, project_name, emp_id)

emp_id emp_name dept_id

1 Aman 10
2 Riya 20

dept_id dept_name
10 IT
20 HR

project_id project_name emp_id

501 Portal Revamp 1
502 Payroll System 2

SQL Solution

24
SELECT e.emp_name, d.dept_name, p.project_name
FROM Employee e
INNER JOIN Department d ON e.dept_id = d.dept_id
INNER JOIN Project p ON e.emp_id = p.emp_id;

Line-by-Line Explanation
• The query starts from Employee and joins to Department using dept_id, bringing
in the department name.
• It then joins that intermediate result to Project using emp_id, bringing in the
project each employee works on.
• Each JOIN is evaluated in order, and the alias from an earlier join can be reused
in a later one.
Expected Output

emp_name dept_name project_name

Aman IT Portal Revamp
Riya HR Payroll System

Common Interview Mistakes

• Chaining JOINs with the wrong keys (mixing up which foreign key belongs to
which table).
• Using INNER JOIN throughout when some rows genuinely need to be preserved
with LEFT JOIN (e.g., employees not yet assigned to any project).
Similar Questions Interviewers Ask
• How would you rewrite this using LEFT JOIN to also include employees without
a project?
• Can you join more than three tables? Is there a performance concern with too
many joins?

Q25. Subquery — Employees Earning More Than the Average Salary

Difficulty: Medium Concepts Tested: Subqueries (scalar subquery in WHERE)
Table Schema
Same Employee table as Q1 (emp_id, emp_name, department, salary, join_date).
SQL Solution
SELECT emp_name, salary
FROM Employee
WHERE salary > (SELECT AVG(salary) FROM Employee);

Line-by-Line Explanation

25
 • The inner query SELECT AVG(salary) FROM Employee runs first and produces a
single number — the average salary (44000, using the Q1 data).
• The outer query then compares every employee’s salary against that single
value and keeps only the rows where it’s greater.
• Because the inner query returns exactly one value, it can be used directly after
a comparison operator like >.
Expected Output

emp_name salary
Aman 45000
Kabir 52000

Common Interview Mistakes

• Trying to use a subquery that returns multiple rows with = instead of IN (causes
a runtime error: “subquery returns more than one row”).
• Recomputing the average inside a WHERE clause using GROUP BY incorrectly in-
stead of a clean scalar subquery.
Similar Questions Interviewers Ask
• How would you find employees earning more than the average salary of their
own department? (this becomes a correlated subquery, covered next)
• What’s the difference between a subquery and a JOIN — could this be solved
with a JOIN instead?

Q26. EXISTS — Customers Who Have Placed at Least One Order

Difficulty: Medium Concepts Tested: EXISTS, correlated subquery
Table Schema
Same Customers and Orders tables as Q22.
SQL Solution
SELECT c.customer_name
FROM Customers c
WHERE EXISTS (
SELECT 1
FROM Orders o
WHERE o.customer_id = c.customer_id
);

Line-by-Line Explanation
• The subquery is run once per customer row from the outer query — this is what
makes it a “correlated” subquery, because it refers back to c.customer_id.

26
 • EXISTS only checks whether the subquery returns any row at all; it doesn’t mat-
ter what the row’s actual values are, which is why SELECT 1 is commonly used
instead of listing real columns.
• If at least one order matches that customer’s ID, the customer is included in the
result.
Expected Output

customer_name
Ravi

Common Interview Mistakes

• Writing SELECT * inside the EXISTS subquery and thinking it changes perfor-
mance — it doesn’t matter what’s selected, since EXISTS only checks for row
presence.
• Confusing EXISTS with IN when the subquery could return NULL values (IN be-
haves incorrectly with NULLs, EXISTS does not have this problem).
Similar Questions Interviewers Ask
• What is the difference between EXISTS and IN?
• Rewrite this same query using an INNER JOIN and DISTINCT.

Q27. NOT EXISTS — Customers Who Never Placed an Order

Difficulty: Medium Concepts Tested: NOT EXISTS, correlated subquery
Table Schema
Same Customers and Orders tables as Q22.
SQL Solution
SELECT c.customer_name
FROM Customers c
WHERE NOT EXISTS (
SELECT 1
FROM Orders o
WHERE o.customer_id = c.customer_id
);

Line-by-Line Explanation
• This is the exact opposite of Q26: for every customer, the subquery checks if any
order row matches their customer_id.
• NOT EXISTS keeps the customer only when zero matching order rows are found.
• This is generally considered the safest way to answer “which customers never
ordered”, safer than NOT IN.

27
Expected Output

customer_name
Meena
Farhan

Common Interview Mistakes

• Using NOT IN (SELECT customer_id FROM Orders) instead — if even one cus-
tomer_id in Orders is NULL, NOT IN silently returns zero rows for the entire query,
which is a classic interview trap.
• Not knowing why NOT EXISTS is preferred over NOT IN when NULLs might be
present — this is one of the most frequently asked “gotcha” follow-ups in inter-
views.
Similar Questions Interviewers Ask
• Why is NOT EXISTS safer than NOT IN when the subquery column can contain
NULLs?
• Solve the same problem using LEFT JOIN ... WHERE IS NULL (already shown in
Q22).

Q28. Correlated Subquery — Employees Earning More Than Their De-

partment’s Average
Difficulty: Medium Concepts Tested: Correlated subquery, GROUP BY logic with-
out GROUP BY
Table Schema
Same Employee table as Q1.
SQL Solution
SELECT e1.emp_name, [Link], [Link]
FROM Employee e1
WHERE [Link] > (
SELECT AVG([Link])
FROM Employee e2
WHERE [Link] = [Link]
);

Line-by-Line Explanation
• The inner query calculates the average salary, but only for rows in e2 where
the department matches the current outer row’s department ([Link] =
[Link]) — this correlation is what makes it re-run for every employee.
• The outer query then keeps the employee only if their own salary is above that
department-specific average.

28
 • This is a very common way interviewers test whether a candidate actually un-
derstands correlated subqueries versus plain subqueries.
Expected Output
Using the Q1 sample data (IT average = 48500, HR average = 38000, Finance average
= 41000):

emp_name department salary

Kabir IT 52000

Common Interview Mistakes

• Forgetting the correlation condition ([Link] = [Link]), which
turns it back into an overall average like Q25 instead of a per-department aver-
age.
• Not realizing this can also be solved without a correlated subquery, using a win-
dow function (AVG(salary) OVER (PARTITION BY department)) — but since that’s
an advanced topic here, the subquery version is the expected fresher-level an-
swer.
Similar Questions Interviewers Ask
• What is a correlated subquery, and how is it different from a normal subquery?
• Can this be rewritten using a JOIN with a grouped subquery instead? (yes: join
Employee to a subquery that computes department, AVG(salary))

29
Q29. Simple CTE — Rewrite a Subquery Using WITH
Difficulty: Medium Concepts Tested: Simple CTE (Common Table Expression)
Table Schema
Same Employee table as Q1.
SQL Solution
WITH DeptAvg AS (
SELECT department, AVG(salary) AS avg_salary
FROM Employee
GROUP BY department
)
SELECT e.emp_name, [Link], [Link], d.avg_salary
FROM Employee e
JOIN DeptAvg d
ON [Link] = [Link]
WHERE [Link] > d.avg_salary;

Line-by-Line Explanation
• WITH DeptAvg AS (...) defines a temporary, named result set (a CTE) that exists
only for the duration of this query — here it calculates the average salary per
department.
• The main query then simply joins Employee to DeptAvg on department, which
reads much more clearly than nesting the same logic inside a WHERE clause.
• WHERE [Link] > d.avg_salary keeps only employees whose salary beats their
department’s average — the same result as Q28’s correlated subquery, but writ-
ten differently.
Expected Output

emp_name department salary avg_salary

Kabir IT 52000 48500

Common Interview Mistakes

• Thinking a CTE is a permanent object like a view — it only exists for the single
query it’s attached to and is recomputed every time the query runs.
• Forgetting a CTE must be immediately followed by a SELECT, INSERT, UPDATE, or
DELETE statement that actually uses it.
Similar Questions Interviewers Ask
• What is the difference between a CTE and a subquery? Between a CTE and a
view?
• Can a CTE reference itself? (yes, that’s called a recursive CTE — out of scope
for this guide, but interviewers sometimes ask if you’ve heard of it)

30
Q30. Date Functions — Orders Placed in the Last 30 Days
Difficulty: Medium Concepts Tested: Date functions, CURDATE/GETDATE,
DATE_SUB/DATEADD
Table Schema
Same Orders table as Q22 (order_id, customer_id, order_date).
SQL Solution
-- MySQL
SELECT *
FROM Orders
WHERE order_date >= CURDATE() - INTERVAL 30 DAY;

-- SQL Server
SELECT *
FROM Orders
WHERE order_date >= DATEADD(DAY, -30, GETDATE());

Line-by-Line Explanation
• CURDATE() (MySQL) or GETDATE() (SQL Server) returns today’s date.
• Subtracting 30 days from today gives the cutoff date exactly one month back.
• The WHERE clause then keeps only orders whose order_date is on or after that
cutoff — i.e., placed within the last 30 days.
Expected Output
Depends on the current date and the data, but structurally it’s a subset of the Orders
table containing only recent rows, with all original columns intact.
Common Interview Mistakes
• Hardcoding a specific date instead of using the database’s “today” function,
which breaks the query the next time it’s run.
• Mixing up date function names across databases — MySQL uses DATE_SUB()/CURDATE(),
SQL Server uses DATEADD()/GETDATE(), and interviewers often just want to see
that you know one syntax well and are aware the other exists.
Similar Questions Interviewers Ask
• How do you extract just the year or month from a date column? (YEAR(order_date),
MONTH(order_date))
• How do you find orders placed in a specific month, regardless of year?

Q31. Find Duplicate Records in a Table

Difficulty: Medium Concepts Tested: GROUP BY + HAVING for duplicate detection
Table Schema

31
Student(student_id, student_name, email)

student_id student_name email

1 Priya priya@[Link]
2 Priya priya@[Link]
3 Arjun arjun@[Link]
4 Neha neha@[Link]

SQL Solution
SELECT email, COUNT(*) AS occurrences
FROM Student
GROUP BY email
HAVING COUNT(*) > 1;

Line-by-Line Explanation
• Rows are grouped by email, since that’s the column being checked for duplica-
tion.
• COUNT(*) counts how many rows share each email.
• HAVING COUNT(*) > 1 keeps only the groups (emails) that appear more than once
— i.e., actual duplicates.
Expected Output

email occurrences
priya@[Link] 2

Common Interview Mistakes

• Using WHERE COUNT(*) > 1 instead of HAVING (same trap as Q17 — aggregates
cannot go in WHERE).
• Grouping by the wrong column — if the interviewer wants full-row duplicates,
you may need to GROUP BY every column, not just one.
Similar Questions Interviewers Ask
• How do you find the actual duplicate row IDs, not just the email? (add a subquery
or STRING_AGG/GROUP_CONCAT of student_id)
• How would you find rows that are complete duplicates across all columns?

Q32. Delete Duplicate Rows (Query Only)

Difficulty: Medium Concepts Tested: Self-reference DELETE, subqueries with
MIN/MAX
Table Schema

32
Same Student table as Q31.
SQL Solution
DELETE FROM Student
WHERE student_id NOT IN (
SELECT MIN(student_id)
FROM Student
GROUP BY email
);

Line-by-Line Explanation
• The subquery groups all rows by email and picks the smallest student_id in each
group — effectively choosing one row to “keep” per duplicate email.
• The outer DELETE then removes every row whose student_id is not one of those
kept IDs, leaving exactly one row per unique email.
• This pattern (keep the MIN or MAX id, delete the rest) is the standard, most com-
monly expected answer to this classic question.
Expected Output
After running this query, the Student table would contain only:

student_id student_name email

1 Priya priya@[Link]
3 Arjun arjun@[Link]
4 Neha neha@[Link]

Common Interview Mistakes

• Writing a DELETE that references the same table it’s deleting from in a naive
subquery — in MySQL this actually throws an error (“You can’t specify target
table for update in FROM clause”) and requires wrapping the subquery in an
extra derived table alias.
• Forgetting this is a destructive operation — many interviewers accept a SELECT
version of the logic (as shown in Q31) if you explain you’d want to verify before
actually deleting.
Similar Questions Interviewers Ask
• How would you write this same logic using ROW_NUMBER()? (an easy window
function version, though this guide keeps to the non-window approach as the
expected fresher answer)
• What’s the MySQL workaround for “can’t specify target table for update in
FROM clause”?

33
Q33. Odd and Even Numbered Rows
Difficulty: Medium Concepts Tested: MOD, row numbering logic
Table Schema
Same Employee table as Q1, assume emp_id values are 1, 2, 3, 4.
SQL Solution
-- Odd-numbered rows (by emp_id)
SELECT * FROM Employee WHERE emp_id % 2 = 1;

-- Even-numbered rows (by emp_id)

SELECT * FROM Employee WHERE emp_id % 2 = 0;

Line-by-Line Explanation
• % is the modulo operator — emp_id % 2 gives the remainder after dividing emp_id
by 2.
• A remainder of 1 means the emp_id is odd; a remainder of 0 means it’s even.
• This is commonly asked using emp_id, but interviewers sometimes really mean
the row position in the result set, which needs ROW_NUMBER() — worth clarifying
out loud in the interview which one is meant.
Expected Output
Odd query returns emp_id 1 and 3 (Aman, Kabir); even query returns emp_id 2 and 4
(Riya, Sneha).
Common Interview Mistakes
• Assuming emp_id is always sequential with no gaps — if rows have been deleted,
emp_id % 2 no longer matches the actual row position.
• Confusing this with ROW_NUMBER() % 2, which is what’s needed if the interviewer
means “every alternate row” rather than “rows with odd/even IDs”.
Similar Questions Interviewers Ask
• How would you solve this if emp_id had gaps and you truly needed alternate rows
by position? (ROW_NUMBER() — mentioned as an advanced follow-up, not expected
in detail at fresher level)
• What does the % operator do in SQL versus in general programming languages?

Q34. Second Highest Salary

Difficulty: Medium Concepts Tested: Subquery with LIMIT/OFFSET, DISTINCT
Table Schema
Same Employee table as Q1.
SQL Solution

34
-- Method 1: Subquery with MAX
SELECT MAX(salary) AS second_highest_salary
FROM Employee
WHERE salary < (SELECT MAX(salary) FROM Employee);

-- Method 2: DISTINCT + ORDER BY + LIMIT/OFFSET (MySQL/PostgreSQL)

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

Line-by-Line Explanation (Method 1)

• The inner query finds the single highest salary in the whole table.
• The outer query then finds the maximum salary among only those rows that are
strictly less than that highest value — which, by definition, is the second-highest
salary.
Line-by-Line Explanation (Method 2)
• DISTINCT salary removes duplicate salary values so that two employees tied for
the highest salary don’t distort the ranking.
• ORDER BY salary DESC puts the highest salary first.
• LIMIT 1 OFFSET 1 skips the first row (the highest) and returns the next one (the
second-highest).
Expected Output

second_highest_salary
45000

Common Interview Mistakes

• Forgetting DISTINCT in Method 2 — if two employees share the highest salary,
plain ORDER BY ... LIMIT 1 OFFSET 1 would return that same top salary twice
instead of the true second-highest.
• Hardcoding the answer with a fixed row count that breaks the moment the table
changes.
Similar Questions Interviewers Ask
• How do you find the third-highest salary? (covered next)
• How do you generalize this to the Nth-highest salary? (covered next)

35
Q35. Third Highest Salary
Difficulty: Medium Concepts Tested: Subquery nesting, LIMIT/OFFSET
Table Schema
Same Employee table as Q1.
SQL Solution
-- Method 1: Nested subquery
SELECT MIN(salary) AS third_highest_salary
FROM (
SELECT DISTINCT salary
FROM Employee
ORDER BY salary DESC
LIMIT 3
) AS top3;

-- Method 2: OFFSET
SELECT DISTINCT salary
FROM Employee
ORDER BY salary DESC
LIMIT 1 OFFSET 2;

Line-by-Line Explanation (Method 1)

• The inner query picks the top 3 distinct salaries, sorted highest to lowest.
• Taking the MIN of just those 3 values gives the smallest among the top three —
which is exactly the third-highest overall.
Line-by-Line Explanation (Method 2)
• Same logic as Q34’s Method 2, but OFFSET 2 skips the top two salaries and
returns the next one — the third-highest.
Expected Output
Using the Q1 sample data (52000, 45000, 41000, 38000):

third_highest_salary
41000

Common Interview Mistakes

• Forgetting DISTINCT, which breaks the ranking the same way it did for the
second-highest salary question if there are salary ties.
• Not realizing this pattern (top-N subquery + MIN, or OFFSET) is exactly how
the general “Nth highest salary” question is solved — interviewers often ask
this right after to see if the candidate spots the pattern.
Similar Questions Interviewers Ask

36
 • Can you write a single query that works for both second and third highest by
just changing one number?
• What if two employees are tied for the second-highest salary — does that affect
who counts as “third”?

Q36. Nth Highest Salary

Difficulty: Medium Concepts Tested: Generalizing subqueries with a parameter,
LIMIT/OFFSET
Table Schema
Same Employee table as Q1.
SQL Solution
-- General pattern for the Nth highest salary (replace N with a number, e.g. 4)
SELECT DISTINCT salary
FROM Employee
ORDER BY salary DESC
LIMIT 1 OFFSET (N - 1);

-- Example for N = 2 (second highest)

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

Line-by-Line Explanation
• DISTINCT salary guarantees that tied salaries are treated as a single rank rather
than counted twice.
• ORDER BY salary DESC ranks salaries from highest to lowest.
• OFFSET (N - 1) skips the first N - 1 salaries and LIMIT 1 returns the very next
one — for N = 1 this returns the highest salary, for N = 2 the second-highest, and
so on.
• This single, parameterized pattern is exactly what Q34 and Q35 are special cases
of, which is why interviewers like to ask “second highest” first and then immedi-
ately ask “can you generalize that?”.
Expected Output
For N = 4 using the Q1 data (52000, 45000, 41000, 38000), the result is:

salary
38000

Common Interview Mistakes

37
 • Writing a completely different query for every value of N instead of recognizing
the reusable OFFSET pattern.
• Forgetting that OFFSET counts from 0, so the Nth highest needs OFFSET (N - 1),
not OFFSET N.
Similar Questions Interviewers Ask
• How would you solve this without LIMIT/OFFSET, using only a correlated subquery
that counts how many distinct salaries are greater? (an alternative fresher-
friendly approach interviewers sometimes expect: WHERE N - 1 = (SELECT
COUNT(DISTINCT salary) FROM Employee e2 WHERE [Link] > [Link]))
• Which is more portable across databases — LIMIT/OFFSET or the correlated sub-
query approach?

Q37. Highest Salary in Each Department

Difficulty: Medium Concepts Tested: GROUP BY + MAX, subquery join
Table Schema
Same Employee table as Q1.
SQL Solution
SELECT department, MAX(salary) AS highest_salary
FROM Employee
GROUP BY department;

Line-by-Line Explanation
• GROUP BY department splits the table into one group per department.
• MAX(salary) is then computed separately within each group, giving the top
salary per department rather than one overall top salary.
Expected Output

department highest_salary
IT 52000
HR 38000
Finance 41000

Common Interview Mistakes

• Stopping here when the interviewer actually wants the employee names too
— since emp_name isn’t grouped or aggregated, it can’t simply be added to this
SELECT list; a join back to the original table (matching on both department and
salary) or a window function is needed.
• Forgetting that if two employees in the same department tie for the highest
salary, the name-lookup version needs to decide whether to show both or just
one.

38
Similar Questions Interviewers Ask
• Now show me the employee name along with the highest salary per department.
(Answer: join Employee to this grouped result on department and salary)
• What if you also need the second-highest salary per department? (This edges
toward window functions, which interviewers usually acknowledge is a step be-
yond the fresher level.)

Q38. Employees Earning More Than Their Managers

Difficulty: Medium Concepts Tested: SELF JOIN with comparison
Table Schema
Employee(emp_id, emp_name, salary, manager_id)

emp_id emp_name salary manager_id

1 Aman 60000 3
2 Riya 35000 3
3 Kabir 50000 NULL
4 Sneha 45000 1

SQL Solution
SELECT e.emp_name AS employee, [Link] AS employee_salary,
m.emp_name AS manager, [Link] AS manager_salary
FROM Employee e
JOIN Employee m
ON e.manager_id = m.emp_id
WHERE [Link] > [Link];

Line-by-Line Explanation
• As in Q23, the table is joined to itself: e represents the employee, m represents
that employee’s manager, matched through e.manager_id = m.emp_id.
• WHERE [Link] > [Link] then keeps only the pairs where the employee’s own
salary exceeds their manager’s salary.
• Employees with no manager (manager_id IS NULL) are automatically excluded
because a plain JOIN (inner join) requires a match.
Expected Output

employee employee_salary manager manager_salary

Aman 60000 Kabir 50000

Common Interview Mistakes

39
 • Using LEFT JOIN and forgetting to also filter out the NULL manager rows, which
would throw a comparison error or silently drop them depending on the database
(comparisons against NULL evaluate to unknown, not true).
• Mixing up which alias represents the employee and which represents the man-
ager, leading to a reversed or meaningless comparison.
Similar Questions Interviewers Ask
• How would you list every employee alongside their manager’s name, even if they
earn less? (switch to LEFT JOIN and drop the WHERE filter)
• Can this same logic be written with a correlated subquery instead of a self join?

Q39. Top 3 Salaries (Overall)

Difficulty: Medium Concepts Tested: ORDER BY + LIMIT, DISTINCT
Table Schema
Same Employee table as Q1.
SQL Solution
SELECT DISTINCT emp_name, salary
FROM Employee
ORDER BY salary DESC
LIMIT 3;

Line-by-Line Explanation
• Rows are sorted by salary from highest to lowest.
• LIMIT 3 then keeps only the first three rows of that sorted list — the three em-
ployees with the highest salaries.
• DISTINCT here guards against duplicate (emp_name, salary) rows rather than
duplicate salary values, since the interviewer usually wants to see actual em-
ployees, not just three salary numbers.
Expected Output

emp_name salary
Kabir 52000
Aman 45000
Sneha 41000

Common Interview Mistakes

• Confusing “top 3 salaries” (three highest salary values, which could belong to
fewer than 3 people if there are ties) with “top 3 employees by salary” (always
3 rows) — worth clarifying with the interviewer which is meant.
• Forgetting ORDER BY — without it, LIMIT 3 returns an arbitrary 3 rows, not the
highest-paid ones.

40
Similar Questions Interviewers Ask
• How is this different from “top 3 distinct salaries”? (would need SELECT DISTINCT
salary ... LIMIT 3 without emp_name)
• How would you get the top 3 salaries per department instead of overall? (this
naturally leads into window functions, mentioned as a step beyond fresher scope)

Q40. Running Total of Salaries (Simple Version)

Difficulty: Medium Concepts Tested: Self join / correlated subquery for a cumula-
tive sum (kept simple, without window functions)
Table Schema
Same Employee table as Q1, ordered by emp_id.
SQL Solution
SELECT e1.emp_id, e1.emp_name, [Link],
(SELECT SUM([Link])
FROM Employee e2
WHERE e2.emp_id <= e1.emp_id) AS running_total
FROM Employee e1
ORDER BY e1.emp_id;

Line-by-Line Explanation
• For every employee row (e1), a correlated subquery adds up the salaries of all
employees (e2) whose emp_id is less than or equal to the current row’s emp_id.
• This means row 1’s running total is just its own salary; row 2’s running total is
rows 1+2’s salaries added together; and so on — a classic cumulative sum.
• ORDER BY e1.emp_id makes sure the output is shown in the same order the run-
ning total was accumulated in.
Expected Output

emp_id emp_name salary running_total

1 Aman 45000 45000
2 Riya 38000 83000
3 Kabir 52000 135000
4 Sneha 41000 176000

Common Interview Mistakes

• Assuming emp_id is always a reliable ordering column — the running total should
really be based on whatever column defines the intended order (e.g., join_date),
not always the primary key.
• Not recognizing that this same result is much more naturally expressed with
SUM(salary) OVER (ORDER BY emp_id) — interviewers are usually happy to hear

41
 that you know the window function exists, even while showing the subquery
version as the fresher-level answer.
Similar Questions Interviewers Ask
• Have you heard of window functions like SUM() OVER()? Can you explain what
they do at a high level?
• How would this query’s performance be affected on a very large table? (the
correlated subquery re-scans the table for every row, which is O(n²) and consid-
erably slower than a window function)

Q41. Employees Who Joined in the Last N Months (Combining WHERE,

Date Functions, and ORDER BY)
Difficulty: Medium Concepts Tested: Date functions, WHERE, ORDER BY — a
common “combine everything” wrap-up question
Table Schema
Same Employee table as Q1 (emp_id, emp_name, department, salary, join_date).
SQL Solution
-- MySQL: employees who joined in the last 12 months
SELECT emp_name, department, join_date
FROM Employee
WHERE join_date >= CURDATE() - INTERVAL 12 MONTH
ORDER BY join_date DESC;

Line-by-Line Explanation
• CURDATE() - INTERVAL 12 MONTH computes the date exactly 12 months before
today.
• WHERE join_date >= ... keeps only employees who joined on or after that date,
i.e., within the last year.
• ORDER BY join_date DESC shows the most recently joined employees first, which
is usually what’s expected when the question is phrased this way.
Expected Output
A subset of the Employee table containing only employees hired within the last 12
months, sorted from most recent join date to least recent.
Common Interview Mistakes
• Using YEAR(join_date) = YEAR(CURDATE()) instead of a proper rolling 12-month
window — these are subtly different (calendar year vs. trailing 365 days) and
interviewers often probe which one was actually intended.
• Forgetting ORDER BY, which leaves the result in an unpredictable order even
though the filtering itself is correct.
Similar Questions Interviewers Ask

42
• How would you find employees who have completed exactly 1 year at the com-
pany?
• How would you group employees by the year they joined and count them?

43
Section C: Interview Revision Kit
Top 20 SQL Interview Tips
1. Always clarify the table schema out loud before writing a query if it isn’t fully
given to you.
2. Say your approach in plain English first, then write the SQL — interviewers care
as much about your thought process as the final query.
3. Never use SELECT * in a “real” solution you’re proposing for production — men-
tion this awareness even if you use it for quick testing.
4. Remember WHERE filters rows before grouping; HAVING filters groups after aggre-
gation.
5. Always use IS NULL / IS NOT NULL — never = NULL or != NULL.
6. Know the difference between COUNT(*), COUNT(column), and COUNT(DISTINCT
column) cold — this is asked almost everywhere.
7. Be ready to explain why NOT IN is risky with NULLs, and why NOT EXISTS is the
safer alternative.
8. Practice the “second highest / Nth highest salary” family until you can write it
without hesitation — it is the single most repeated SQL interview question across
all these companies.
9. When joining tables, always double check which table is “left” and which is
“right”, especially for LEFT JOIN.
10. If a LEFT JOIN isn’t giving you the unmatched rows you expect, check whether a
filter accidentally landed in WHERE instead of ON.
11. For self joins, always use two clearly different aliases and say out loud which
alias represents which “role” (e.g., employee vs manager).
12. Be able to explain SQL execution order from memory (see the section below) —
it’s one of the most common theory questions.
13. Know the difference between DELETE, TRUNCATE, and DROP even though this guide
doesn’t cover DDL in depth — it’s a frequent one-line follow-up.
14. When asked to “find duplicates”, default to GROUP BY + HAVING COUNT(*) > 1
unless told otherwise.
15. Practice writing the same query two ways (e.g., subquery vs JOIN, or subquery
vs CTE) — interviewers love asking “can you do this another way?”
16. Read every question twice — “top 3 salaries” and “top 3 distinct salaries” are
not the same thing, and interviewers use this distinction deliberately.
17. If you’re unsure about a database-specific function (LIMIT vs TOP, DATE_SUB vs
DATEADD), say so and explain the general logic — most interviewers accept either
syntax as long as the logic is right.
18. Don’t over-engineer a fresher-level answer with window functions unless asked
— a clean, correct subquery is usually exactly what’s expected.
19. Always mention time complexity or performance briefly if you use a correlated
subquery — showing awareness (e.g., “this re-scans the table for every row”) is
a strong signal to interviewers.
20. Stay calm on the whiteboard/notepad rounds — write the schema down first,
then build the query outward from FROM, not from SELECT.

44
SQL Query-Writing Strategy
When you’re given a fresh SQL problem in an interview, follow this order of thinking
rather than jumping straight to typing:
1. Understand the schema — list the tables, their columns, primary keys, and
foreign keys (draw them if needed).
2. Restate the question in your own words — this catches misunderstandings
early and shows the interviewer you’re thinking, not memorizing.
3. Identify which tables are needed — sometimes a question only needs one
table; don’t join unnecessarily.
4. Decide row-level vs group-level — does the question need GROUP BY, or is it
just filtering individual rows?
5. Write the FROM/JOIN first, even before SELECT — get the correct set of rows
before deciding what to display.
6. Add filters — decide what goes in WHERE (row-level) versus HAVING (group-level).
7. Add sorting and limiting — ORDER BY, LIMIT/TOP, only after the core logic is
right.
8. Sanity-check with sample data — mentally run your query against 2–3 sample
rows to check it behaves as expected, especially for NULLs and ties.

SQL Execution Order (Logical Order of Operations)

Interviewers frequently ask “in what order does SQL actually execute a query?” This
trips up a lot of candidates because it does not match the order you write the clauses
in.

Step Clause What it does

1 FROM Identify the source table(s)
2 JOIN Combine rows from multiple
tables
3 WHERE Filter individual rows
4 GROUP BY Group filtered rows
5 HAVING Filter the groups
6 SELECT Choose/compute the output
columns
7 DISTINCT Remove duplicate rows from
the result
8 ORDER BY Sort the final result
9 LIMIT/OFFSET (or TOP) Restrict the number of rows
returned

Key takeaway: because WHERE runs before SELECT, you cannot use a column alias
defined in SELECT inside a WHERE clause — but you can use it in ORDER BY, since ORDER
BY runs after SELECT.

45
GROUP BY vs HAVING

Aspect GROUP BY HAVING

Purpose Groups rows that share the Filters those groups
same values in specified based on a condition
columns
Runs After WHERE, before SELECT After GROUP BY
Works with aggregates? Creates the groups aggregate Filters using the result
functions operate on of aggregate functions
Can it use raw columns? Yes, on the grouping columns Only on the grouping
columns or aggregate
expressions
Typical use GROUP BY department HAVING COUNT(*) > 5

WHERE vs HAVING

Aspect WHERE HAVING

Filters Individual rows Groups of rows (after
aggregation)
Runs Before grouping After grouping
Can use aggregate No (e.g., WHERE Yes (HAVING COUNT(*) > 5
functions? COUNT(*) > 5 is invalid) is valid)
Can be used without Yes, always Yes, but then it applies to
GROUP BY? the whole result set as one
group
Typical use WHERE salary > 40000 HAVING SUM(salary) >
100000

JOIN Cheat Sheet

JOIN Type Returns Common use case

INNER JOIN Only rows with a Employees with a valid, existing
match in both department
tables
LEFT JOIN All rows from the Customers with no orders; optional
left table, relationships
matched rows (or
NULL) from the
right

46
JOIN Type Returns Common use case
RIGHT JOIN All rows from the Rarely used directly — usually
right table, rewritten as a LEFT JOIN by swapping
matched rows (or table order
NULL) from the
left
FULL OUTER JOIN All rows from both Reconciling two lists where either side
tables, matched may have unmatched rows (not
where possible, supported directly in MySQL;
NULL elsewhere emulated with UNION of LEFT and RIGHT
joins)
SELF JOIN A table joined to Employee-manager relationships,
itself using two hierarchy comparisons
aliases
CROSS JOIN Every row of one Rare in interviews; usually a mistake
table combined when the ON clause is missing
with every row of
another

Aggregate Function Cheat Sheet

Function What it does NULL behavior

COUNT(*) Counts all rows Counts rows even if all columns
are NULL
COUNT(column) Counts non-NULL values in Ignores NULLs
that column
SUM(column) Adds up numeric values Ignores NULLs; returns NULL
if every value is NULL
AVG(column) Mean of numeric values Ignores NULLs both in sum
and in the divisor
MIN(column) Smallest value (works on Ignores NULLs
numbers, dates, strings)
MAX(column) Largest value Ignores NULLs

Common SQL Interview Mistakes (Summary)

• Using = NULL instead of IS NULL.
• Putting an aggregate condition in WHERE instead of HAVING.
• Using NOT IN with a subquery column that can contain NULLs (returns zero rows
unexpectedly) instead of NOT EXISTS.
• Forgetting DISTINCT when ties can affect ranking questions like “second highest
salary”.

47
 • Filtering a LEFT JOIN’s right-hand table in WHERE, which silently turns it into an
INNER JOIN.
• Selecting non-aggregated, non-grouped columns alongside aggregate functions.
• Assuming row order is guaranteed without an explicit ORDER BY.
• Mixing up database-specific syntax (LIMIT/TOP, DATE_SUB/DATEADD) without real-
izing they aren’t universal.
• Forgetting that BETWEEN is inclusive of both endpoints.
• Not using table aliases in a self join, causing ambiguous column errors.

One-Page SQL Revision Sheet

Execution order: FROM → JOIN → WHERE → GROUP BY → HAVING → SELECT → DISTINCT →
ORDER BY → LIMIT
NULL handling: always IS NULL / IS NOT NULL; never = NULL; prefer NOT EXISTS
over NOT IN when NULLs may be present.
Filtering: WHERE = row-level, before grouping. HAVING = group-level, after grouping,
allows aggregate functions.
Joins: - INNER JOIN → only matches. - LEFT JOIN → all of left + matches from right
(NULL if no match). - LEFT JOIN ... WHERE [Link] IS NULL → find left-only,
unmatched rows. - SELF JOIN → same table, two aliases, used for hierarchies (man-
ager/employee).
Aggregates: COUNT(*) counts rows; COUNT(col) ignores NULLs; SUM/AVG/MIN/MAX all
ignore NULLs automatically.
Nth highest salary pattern:
SELECT DISTINCT salary
FROM Employee
ORDER BY salary DESC
LIMIT 1 OFFSET (N - 1);

Duplicate detection pattern:

SELECT col, COUNT(*)
FROM table_name
GROUP BY col
HAVING COUNT(*) > 1;

Delete duplicates, keep lowest ID:

DELETE FROM table_name
WHERE id NOT IN (
SELECT MIN(id) FROM table_name GROUP BY duplicate_col
);

No-match pattern (e.g., customers with no orders):

48
SELECT a.*
FROM TableA a
LEFT JOIN TableB b ON [Link] = b.a_id
WHERE b.a_id IS NULL;

CASE WHEN:
CASE
WHEN condition1 THEN result1
WHEN condition2 THEN result2
ELSE default_result
END

Keep this page open in the last five minutes before your interview — almost every
pattern on this sheet has appeared, in some form, in the 41 questions covered in this
guide.

49