Forget the endless list of SQL questions — these 6 advanced queries are the ones interviewers actually care about

When it comes to developer interviews, SQL always shows up like that one relative who never misses a family function. No matter if you are applying for a backend role, a data engineer position, or even a full-stack job, sooner or later, the interviewer will throw SQL questions at you.

And here’s the thing: most candidates prepare only the basics — SELECT, JOIN, maybe a little GROUP BY. But the real curveballs come from advanced SQL. These are the questions that separate someone who can just write queries from someone who truly understands databases.

In this article, I’ll share 6 advanced SQL interview questions that appear often in real interviews. But instead of just listing them, I’ll break them down, explain the “why” behind each, and also show examples with sample data.

1. What is the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN?

This is one of the most popular questions, and trust me, interviewers love to test whether you really know how joins work.

Imagine we have two tables:

Employees

EmpIDNameDeptID1John1012Alice1023Bob1034EmmaNULL

Departments

DeptIDDeptName101HR102IT104Finance

Now, let’s see how joins behave:

  • INNER JOIN → Matches only when both sides have data.
SELECT Name, DeptName
FROM Employees e
INNER JOIN Departments d
ON e.DeptID = d.DeptID;

Result:
John → HR, Alice → IT

  • LEFT JOIN → Keeps all from the left table, even if no match.
    Bob will vanish here, but Emma stays with NULL because her DeptID doesn’t match.
  • RIGHT JOIN → Opposite of left join. Departments without employees will still appear.
  • FULL OUTER JOIN → Combines both, keeping everything, even if no match on either side.

Interview Tip: Don’t just say definitions. Give a small example like above. It shows that you can visualize joins, not just memorize.

2. How do you find the second highest salary in a table?

Classic question. Sounds simple, but there are multiple ways to solve it, and interviewers want to see if you can think beyond MAX().

Example table:

Salaries

EmpIDNameSalary1John50002Alice70003Bob60004Emma8000

Solution 1: Using LIMIT / OFFSET (MySQL, PostgreSQL)

SELECT DISTINCT Salary
FROM Salaries
ORDER BY Salary DESC
LIMIT 1 OFFSET 1;

Solution 2: Using Subquery

SELECT MAX(Salary)
FROM Salaries
WHERE Salary < (SELECT MAX(Salary) FROM Salaries);

Solution 3: Using ROW_NUMBER()

SELECT Salary
FROM (
   SELECT Salary, ROW_NUMBER() OVER (ORDER BY Salary DESC) AS row_num
   FROM Salaries
) t
WHERE row_num = 2;

Interview Tip: Always mention that ROW_NUMBER() is cleaner and works best when interviewer wants scalable queries.

3. What is the difference between ROW_NUMBER(), RANK(), and DENSE_RANK()?

This is a favorite because it tests your knowledge of window functions.

Let’s say we have employee salaries again:

NameSalaryJohn5000Alice7000Bob7000Emma8000

  • ROW_NUMBER() → Always gives unique numbers, even if values are same.
ROW_NUMBER() OVER (ORDER BY Salary DESC)  
Emma → 1, Alice → 2, Bob → 3, John → 4
  • RANK() → Gives same rank for ties, but skips numbers.
    Emma → 1, Alice → 2, Bob → 2, John → 4
  • DENSE_RANK() → Same as rank, but no gaps.
    Emma → 1, Alice → 2, Bob → 2, John → 3

Interview Tip: Draw it out, or explain with real numbers. This shows you’ve used it in practice.

4. How do you detect and remove duplicate rows in SQL?

Duplicates are like cockroaches in databases — once they appear, they cause chaos.

Example:

EmpIDNameDeptID1John1011John1012Alice1022Alice102

Step 1: Detect duplicates

SELECT Name, DeptID, COUNT(*)
FROM Employees
GROUP BY Name, DeptID
HAVING COUNT(*) > 1;

Step 2: Delete duplicates (keeping one)

DELETE FROM Employees
WHERE EmpID NOT IN (
   SELECT MIN(EmpID)
   FROM Employees
   GROUP BY Name, DeptID
);

Interview Tip: Always mention that you must be careful while deleting — run a SELECT first to confirm.

5. Explain CTEs (Common Table Expressions) and when to use them.

CTEs are like giving your query a nickname. Instead of writing nested subqueries that make your head spin, you can break queries into readable steps.

Example: Find employees with salary above average.

Without CTE:

SELECT Name, Salary
FROM Employees
WHERE Salary > (
   SELECT AVG(Salary) FROM Employees
);

With CTE:

WITH AvgSalary AS (
   SELECT AVG(Salary) AS avg_sal FROM Employees
)
SELECT Name, Salary
FROM Employees, AvgSalary
WHERE Salary > avg_sal;

Interview Tip: Say that CTEs improve readability, help debug queries, and are reusable. Also, mention recursive CTEs (used in hierarchy queries like org charts).

6. How do you handle performance tuning in SQL?

This is where interviews get real. They don’t just want queries — they want optimization thinking.

Some points to mention:

  • Use indexes wisely (but not on every column).
  • Avoid SELECT * — fetch only required columns.
  • Use EXPLAIN PLAN to check query execution.
  • Prefer joins over subqueries (most of the time).
  • Partition large tables if needed.

Example:
Bad query:

SELECT * FROM Employees WHERE YEAR(HireDate) = 2020;

Better query (uses index):

SELECT * FROM Employees
WHERE HireDate BETWEEN '2020-01-01' AND '2020-12-31';

Interview Tip: Always mention indexes and explain plans. That’s the real-world knowledge interviewers look for.

BÀI VIẾT LIÊN QUANXEM THÊM