Challenging Interview Questions in MySQL, Spark SQl

To find the second-highest salary for each department in SQL?

Here are multiple methods to find the second-highest salary for each department in SQL, using different approaches.

1. Using Correlated Subquery

This approach involves using a subquery to find the highest salary for each department and then excluding it.

SELECT Department, MAX(Salary) AS SecondHighestSalary
FROM Employee e1
WHERE Salary < (SELECT MAX(Salary)
                FROM Employee e2
                WHERE e1.Department = e2.Department)
GROUP BY Department;

The subquery in SQL statement:

(SELECT MAX(Salary)
 FROM Employee e2
 WHERE e1.Department = e2.Department)

is calculating the maximum salary within the same department for each employee’s department in the outer query. This subquery is evaluated for each department that the employee e1 belongs to.

Explanation:

Outer Query (e1): The outer query is grouped by the Department and is responsible for finding the second-highest salary in each department.
Subquery (e2): The subquery is used to find the maximum salary in the department that the current row (from the outer query) belongs to. The WHERE e1.Department = e2.Department clause ensures that the subquery’s MAX(Salary) is only considering salaries within the same department as the current employee in e1.

To Clarify:

MAX(Salary) in Subquery: The MAX(Salary) is calculated for each department individually, not for the entire table. This happens because the subquery is correlated with the outer query by the department condition (e1.Department = e2.Department).
Execution Flow: For each row in the outer query, the subquery finds the maximum salary for the department of that row. The outer query then checks if the employee’s salary is less than this maximum salary and identifies the maximum salary among those that are less than the highest salary (which is the second-highest salary).

Example:

Consider the following data in the Employee table:

EmployeeID	Department	Salary
1	HR	5000
2	HR	7000
3	HR	6000
4	IT	8000
5	IT	12000
6	IT	9000

For the HR department:

The subquery for each e1 in HR will compute the maximum salary (7000) within HR.
The outer query will then find the highest salary less than 7000, which is 6000.

For the IT department:

The subquery for each e1 in IT will compute the maximum salary (12000) within IT.
The outer query will then find the highest salary less than 12000, which is 9000.

The final result will show the second-highest salary per department:

Department	SecondHighestSalary
HR	6000
IT	9000

2. Using Window Functions

Window functions provide a powerful way to solve this problem.

WITH RankedSalaries AS (
    SELECT 
        Department,
        Salary,
        DENSE_RANK() OVER (PARTITION BY Department ORDER BY Salary DESC) AS Rank
    FROM Employee
)
SELECT 
    Department,
    Salary AS SecondHighestSalary
FROM RankedSalaries
WHERE Rank = 2;

3. Using Common Table Expressions (CTE) and ROW_NUMBER()

This approach uses a CTE to rank salaries and then selects the second-highest salary.

WITH RankedSalaries AS (
    SELECT 
        Department,
        Salary,
        ROW_NUMBER() OVER (PARTITION BY Department ORDER BY Salary DESC) AS RowNum
    FROM Employee
)
SELECT 
    Department,
    Salary AS SecondHighestSalary
FROM RankedSalaries
WHERE RowNum = 2;

4. Using JOIN and Subquery

This method uses a join and subquery to find the second-highest salary for each department.

SELECT e1.Department, MAX(e1.Salary) AS SecondHighestSalary
FROM Employee e1
JOIN Employee e2 ON e1.Department = e2.Department AND e1.Salary < e2.Salary
GROUP BY e1.Department
HAVING COUNT(DISTINCT e2.Salary) = 1;

5. Using LIMIT and OFFSET (MySQL/MariaDB Specific)

If you’re using a database that supports LIMIT and OFFSET, such as MySQL or MariaDB, you can use them to directly fetch the second-highest salary.

SELECT Department, Salary AS SecondHighestSalary
FROM (
    SELECT Department, Salary
    FROM Employee
    ORDER BY Department, Salary DESC
    LIMIT 2 OFFSET 1
) AS SecondHighest
GROUP BY Department;

6. Using DISTINCT and TOP (SQL Server Specific)

In SQL Server, you can use DISTINCT with TOP and ORDER BY to achieve this.

SELECT Department, MAX(Salary) AS SecondHighestSalary
FROM (
    SELECT DISTINCT Salary, Department
    FROM Employee
) AS DistinctSalaries
WHERE Salary < (SELECT MAX(Salary)
                FROM Employee e2
                WHERE DistinctSalaries.Department = e2.Department)
GROUP BY Department;

7. Using CROSS APPLY (SQL Server Specific)

SQL Server’s CROSS APPLY can be useful for this kind of problem.

SELECT Department, MAX(Salary) AS SecondHighestSalary
FROM (
    SELECT e1.Department, e1.Salary
    FROM Employee e1
    CROSS APPLY (
        SELECT TOP 2 Salary
        FROM Employee e2
        WHERE e1.Department = e2.Department
        ORDER BY Salary DESC
    ) AS TopSalaries
    WHERE TopSalaries.Salary != e1.Salary
) AS SecondHighestSalaries
GROUP BY Department;

Example Table and Query Results

Assuming we have the following Employee table:

EmployeeID	Name	Department	Salary
1	Alice	HR	70000
2	Bob	HR	80000
3	Charlie	IT	60000
4	David	IT	75000
5	Eve	IT	90000
6	Frank	Sales	50000
7	Grace	Sales	60000
8	Helen	Sales	55000

Each method would yield:

Department	SecondHighestSalary
HR	70000
IT	75000
Sales	55000

These methods provide various ways to find the second-highest salary for each department using different SQL techniques and functions. Choose the one that best fits your SQL dialect and requirements.

Pages: 1 2 3 4 5 6

Pages ( 1 of 6 ): 1 2 3 ... 6 Next »