Department Highest Salary SQL Challenge - Oracle Solution

1. Introduction

In this blog post, we'll delve into a common Oracle SQL query problem: identifying the highest salary within each department. This type of analysis is crucial in HR and payroll management.

Problem

The objective is to write an Oracle SQL query that retrieves the employee with the highest salary from each department.

Database Table:

Employee Table:

Id	Name	Salary	DepartmentId
1	John	9000	101
2	Doe	8500	102

Department Table:

Id	Name
101	HR
102	IT

3. Oracle Solution

SELECT d.Name AS Department, e.Name AS Employee, e.Salary
FROM Employee e
JOIN Department d ON e.DepartmentId = d.Id
WHERE (e.DepartmentId, e.Salary) IN (
    SELECT DepartmentId, MAX(Salary)
    FROM Employee
    GROUP BY DepartmentId
);

Output:

Department	Employee	Salary
HR	John	9000
IT	Doe	8500

Explanation:

1. The query joins the Employee and Department tables on the DepartmentId.

2. It uses a subquery to find the maximum salary in each department.

3. The WHERE clause ensures that only those employees are selected whose salaries match the maximum salary found in the subquery for their respective departments.

4. This results in a list of employees who are the top earners in their departments.