SQL Puzzle: Top and Bottom Salaries

Determine, from the provided employee data, which employees have the highest and lowest salaries within each department.

Input Data:

Expected Output:

Data Script:

CREATE TABLE EmployeeInfo
(
	EmployeeID INT,
	EmpName NVARCHAR(250) NULL,
	EmpSalary DECIMAL(36,4) NULL,
	EmpDepartment NVARCHAR(75) NULL
)
INSERT INTO EmployeeInfo VALUES
	(100,'Jhon',45000,'HR'),
	(101,'Alex',50000,'HR'),
	(102,'Kim',5000,'HR'),
	(103,'Sas',50000,'HR'),
	(104,'Chari',100000,'Sales'),
	(105,'Vasu',86000,'Sales'),
	(106,'Sri',72000,'Sales'),
	(107,'Mahesh',8915,'Sales'),
	(108,'Asad',1200,'Marketing'),
	(109,'Ghani',1250,'Marketing'),
	(110,'KingS',10000,'Marketing'),
	(111,'Pushpa',50000,'Marketing')


CopyCopy

Please try to answer the question, if you still not able to get it click below show button to see the answer.

;WITH CTE AS
(
	SELECT EmpDepartment,
	MIN(EmpSalary) MinSalary,
	MAX(EmpSalary) MaxSalary
	FROM EmployeeInfo
	GROUP BY EmpDepartment
)

SELECT EMP.* FROM EmployeeInfo EMP
INNER JOIN CTE 
	ON EMP.EmpDepartment = CTE.EmpDepartment
	AND EMP.EmpSalary = CTE.MinSalary
UNION ALL
SELECT EMP.* FROM EmployeeInfo EMP
INNER JOIN CTE 
	ON EMP.EmpDepartment = CTE.EmpDepartment
	AND EMP.EmpSalary = CTE.MaxSalary
ORDER BY EMP.EmpDepartment