How can you write an SQL query to find employees with the second highest salary in each department? Assume you have an employees table with columns employee_id, department, employee_name, and salary.

Answer:

To find employees with the second highest salary in each department, we can use the DENSE_RANK() window function to rank salaries within each department and filter for the second rank.

Solution: Using DENSE_RANK()

Assume the employees table structure is as follows:

To find the second highest salary in each department:

SQL Query:

Explanation:

1. DENSE_RANK() with PARTITION BY department: This assigns a rank to each salary within each department, ordering from highest to lowest.
2. Filtering for Rank 2: The WHERE salary_rank = 2 clause retrieves only employees ranked second in salary within their department.

Expected Output:

Based on the example data, the result would look like:

Alternative Solution: Using a Subquery (if DENSE_RANK() is unavailable)

In cases where DENSE_RANK() is unsupported, a subquery can be used to retrieve the distinct top two salaries in each department and then select the second highest.

SQL Query for Alternative Solution:

Explanation of Alternative Solution:

1. Subquery for Top 2 Salaries: The inner query finds the second-highest salary in each department by ordering salaries in descending order and skipping the highest.
2. Main Query Filters by Salary: The main query then selects employees who match the second-highest salary for each department.