Solution to TSQL Beginners Challenge 1

DECLARE @Employees TABLE(
	EmployeeID INT IDENTITY,
	EmployeeName VARCHAR(15),
	Department VARCHAR(15),
	Salary NUMERIC(16,2)
)

INSERT INTO @Employees(EmployeeName, Department, Salary) VALUES('T Cook','Finance', 40000)
INSERT INTO @Employees(EmployeeName, Department, Salary) VALUES('D Michael','Finance', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary) VALUES('A Smith','Finance', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary) VALUES('D Adams','Finance', 15000)

INSERT INTO @Employees(EmployeeName, Department, Salary) VALUES('M Williams','IT', 80000)
INSERT INTO @Employees(EmployeeName, Department, Salary) VALUES('D Jones','IT', 40000)
INSERT INTO @Employees(EmployeeName, Department, Salary) VALUES('J Miller','IT', 50000)
INSERT INTO @Employees(EmployeeName, Department, Salary) VALUES('L Lewis','IT', 50000)

INSERT INTO @Employees(EmployeeName, Department, Salary) VALUES('A Anderson','Back-Office', 25000)
INSERT INTO @Employees(EmployeeName, Department, Salary) VALUES('S Martin','Back-Office', 15000)
INSERT INTO @Employees(EmployeeName, Department, Salary) VALUES('J Garcia','Back-Office', 15000)
INSERT INTO @Employees(EmployeeName, Department, Salary) VALUES('T Clerk','Back-Office', 10000)


/*
 EmployeeID EmployeeName    Department                                       Salary
----------- --------------- --------------- ---------------------------------------
          1 T Cook          Finance                                        40000.00
          2 D Michael       Finance                                        25000.00
          3 A Smith         Finance                                        25000.00
          4 D Adams         Finance                                        15000.00
          5 M Williams      IT                                             80000.00
*/


;with a2 as ( -- get maximum salary
select department, max(salary) salary
from @employees
group by department
)
, a3 as ( -- eliminate maximum salary
select department, salary
from @employees a
where not exists (
	select 1 from a2 where a.department = a2.department and a.salary = a2.salary)
group by department, salary
)
, a4 as ( -- get another maximum salary
select department, max(salary) salary
from a3
group by department
)
select b.* -- retrieve all data based on previous result
from @employees b
inner join a4
on b.department = a4.department
and a4.salary = b.salary
order by b.salary

Tags: