Loading
JAVA
SPRING
ANGULAR
SQL
HTML
CSS
JAVASCRIPT
Data Structure
Interview Queries
VideosDiscussion
In SQL interviews, interviewers rarely test only syntax. Instead, they focus on how you think, how you approach data problems, and how well you understand SQL fundamentals.

That is why certain questions appear again and again:

  • Nth highest salary
  • Finding duplicate records
  • Removing duplicates
  • Highest salary per department
  • Employees without managers

These problems may look simple, but they reveal your understanding of:

  • Subqueries
  • JOINs
  • GROUP BY
  • Window functions
  • Data reasoning
This chapter covers the most common SQL interview queries, explained step by step with logic not shortcuts.


1. Nth Highest Salary

This is one of the most frequently asked SQL interview questions. The goal is to find the N-th highest distinct salary from a table.

Using Subquery

SELECT MAX(salary)
FROM employees
WHERE salary < (
    SELECT MAX(salary)
    FROM employees
);

This query finds the second highest salary.


General Form (Nth Salary)

SELECT salary
FROM (
    SELECT DISTINCT salary
    FROM employees
    ORDER BY salary DESC
    LIMIT 1 OFFSET N-1
) AS temp;


Using Window Function (Preferred)

SELECT salary
FROM (
    SELECT salary,
           DENSE_RANK() OVER (ORDER BY salary DESC) AS rnk
    FROM employees
) t
WHERE rnk = N;

Why interviewers like this approach:

  • Clean
  • Scalable
  • Uses modern SQL


2. Find Duplicate Records

Duplicate data is a real-world problem, so this question tests data quality skills.

Find Duplicate Rows Based on a Column

SELECT email, COUNT(*)
FROM users
GROUP BY email
HAVING COUNT(*) > 1;

This identifies duplicate email entries.


Find Complete duplicate Records

SELECT name, email, COUNT(*)
FROM users
GROUP BY name, email
HAVING COUNT(*) > 1;


3. Remove Duplicate Records (Keep One)

This question tests whether you understand ROW_NUMBER().

Using Window Function

DELETE FROM users
WHERE id IN (
    SELECT id
    FROM (
        SELECT id,
               ROW_NUMBER() OVER (
                   PARTITION BY email
                   ORDER BY id
               ) AS rn
        FROM users
    ) t
    WHERE rn > 1
);

This keeps one record per email and deletes the rest.


4. Highest Salary in Each Department

This tests your understanding of GROUP BY vs window functions.


Using GROUP BY

SELECT department_id, MAX(salary)
FROM employees
GROUP BY department_id;


Using Window Function

SELECT emp_name, department_id, salary
FROM (
    SELECT emp_name, department_id, salary,
           RANK() OVER (
               PARTITION BY department_id
               ORDER BY salary DESC
           ) AS rnk
    FROM employees
) t
WHERE rnk = 1;

This returns employee details, not just salary.


5. Employees Earning More Than Average Salary

A classic subquery-based logic test.

SELECT emp_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
);

This shows understanding of correlated data comparison.


6. Second Highest Salary in Each Department

A slightly advanced version of earlier problems.

SELECT emp_name, department_id, salary
FROM (
    SELECT emp_name, department_id, salary,
           DENSE_RANK() OVER (
               PARTITION BY department_id
               ORDER BY salary DESC
           ) AS rnk
    FROM employees
) t
WHERE rnk = 2;

Interviewers love this because it combines:

  • PARTITION BY
  • Ranking
  • Filtering


7. Employees Without a Manager

This checks your understanding of NULL values.

SELECT emp_name
FROM employees
WHERE manager_id IS NULL;

Simple but very common.


8. Count Records in Each Category

Tests aggregation basics.

SELECT department_id, COUNT(*) AS total_employees
FROM employees
GROUP BY department_id;


9. Fetch Alternate Records

Often asked to test ROW_NUMBER logic.

SELECT *
FROM (
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY id) AS rn
    FROM employees
) t
WHERE rn % 2 = 0;


10. Find Records Present in One Table but Not Another

Tests JOIN understanding.


SELECT a.*
FROM tableA a
LEFT JOIN tableB b ON a.id = b.id
WHERE b.id IS NULL;


What Interviewers Actually Check

Behind these questions, interviewers evaluate:

  • Query clarity
  • Logical thinking
  • Handling of edge cases
  • Modern SQL usage
  • Performance awareness
They prefer correct logic over clever tricks.


Common Beginner Mistakes

  • Forgetting DISTINCT in salary problems
  • Using GROUP BY incorrectly
  • Ignoring NULL values
  • Overcomplicating simple queries
  • Memorizing queries without understanding


Two Minute Drill

  • Nth salary - Use DENSE_RANK
  • Duplicates - GROUP BY + HAVING
  • Remove duplicates - ROW_NUMBER
  • Department ranking - PARTITION BY
  • Average comparison - Subquery
  • NULL checks - IS NULL