SQL Exercise – 7

SQL Exercise:

You are given two tables: Employees and Departments. Write a query to find the highest-paid employee in each department. If a department does not have any employees, include it in the result with NULL values for the employee details.

Create Table Scripts:

CREATE TABLE Departments (
DepartmentID INT PRIMARY KEY,
DepartmentName VARCHAR(50)
);

CREATE TABLE Employees (
EmployeeID INT PRIMARY KEY,
Name VARCHAR(50),
Salary DECIMAL(10, 2),
DepartmentID INT,
FOREIGN KEY (DepartmentID) REFERENCES Departments(DepartmentID)
);

Insert Sample Data:

INSERT INTO Departments (DepartmentID, DepartmentName)
VALUES 
(1, 'HR'),
(2, 'Finance'),
(3, 'Engineering'),
(4, 'Sales');


INSERT INTO Employees (EmployeeID, Name, Salary, DepartmentID)
VALUES 
(101, 'Raj Kumar', 60000, 1),
(102, 'Ajay Kumar', 75000, 2),
(103, 'Ramesh Singh', 50000, 3),
(104, 'Mirdul', 80000, 2),
(105, 'Sanjay', 90000, 3);

Solution:

WITH RankedSalaries AS (
SELECT
e.Name,
e.Salary,
e.DepartmentID,
DENSE_RANK() OVER (PARTITION BY e.DepartmentID ORDER BY e.Salary DESC) AS SeqNo
FROM
Employees e
)
SELECT
d.DepartmentName,
rs.Name AS HighestPaidEmployee,
rs.Salary
FROM
Departments d
LEFT JOIN
RankedSalaries rs
ON
d.DepartmentID = rs.DepartmentID AND rs.SeqNo = 1;

Output:

Explanation:

• RankedSalaries CTE:
  • Ranks employees within each department based on their salary in descending order, here we have used DENSE_RANK() to rank the employees which ensures that if two or more employees have the same salary in a department, they are assigned the same rank.
  • It assigns the rank =1 to the highest-paid employee in each department.
• Main Query:
  • Performs a LEFT JOIN between Departments and the ranked employee data.
  • Ensures all departments are included, even those without employees.