Execute the following Microsoft SQL Server T-SQL scripts in Management Studio Query Editor to find the maximum salaried employee at each deparment.

-- MSSQL highest salary by department - maximum salary
-- Microsoft T-SQL inner join - correlated subquery
USE AdventureWorks;
SELECT Highest = '$'+convert(varchar,eph.Rate,1),
       Staff = 'Highest salaried employee in ' + d.name + ' is ' +
                c.LastName + ', ' + c.FirstName
FROM   HumanResources.Department d
       INNER JOIN HumanResources.EmployeeDepartmentHistory edhmain
         ON d.DepartmentID = edhmain.DepartmentID
       INNER JOIN HumanResources.EmployeePayHistory eph
         ON eph.EmployeeID = edhmain.EmployeeID
       INNER JOIN HumanResources.Employee emain
         ON edhmain.EmployeeID = emain.EmployeeID
       INNER JOIN Person.Contact c
         ON emain.ContactID = c.ContactID
WHERE  eph.Rate = (SELECT MAX(Rate)
                   FROM   HumanResources.EmployeePayHistory eph
                          INNER JOIN HumanResources.Employee e
                            ON eph.EmployeeID = e.EmployeeID
                          INNER JOIN HumanResources.EmployeeDepartmentHistory edh
                            ON edh.EmployeeID = e.EmployeeID
                   WHERE  edh.DepartmentID = edhmain.DepartmentID
                     AND  edh.EndDate is null)
 AND  edhmain.EndDate is null
 ORDER BY eph.Rate DESC;
GO
 
/* Results
 
Highest     Staff
$125.50     Highest salaried employee in Executive is Sánchez, Ken
$84.13      Highest salaried employee in Production is Hamilton, James
$72.12      Highest salaried employee in Sales is Welcker, Brian
$63.46      Highest salaried employee in Engineering is Duffy, Terri
$50.48      Highest salaried employee in Research and Development is Miller, Dylan
$50.48      Highest salaried employee in Information Services is Trenary, Jean
$43.27      Highest salaried employee in Finance is Kahn, Wendy
$37.50      Highest salaried employee in Marketing is Bradley, David
$30.00      Highest salaried employee in Purchasing is Word, Sheela
$29.85      Highest salaried employee in Tool Design is Walters, Rob
$28.85      Highest salaried employee in Quality Assurance is Abolrous, Hazem
$27.14      Highest salaried employee in Human Resources is Barreto de Mattos, Paula
$24.52      Highest salaried employee in Production Control is Krebs, Peter
$24.04      Highest salaried employee in Facilities and Maintenance is Altman, Gary
$19.23      Highest salaried employee in Shipping and Receiving is Ackerman, Pilar
$17.79      Highest salaried employee in Document Control is Arifin, Zainal

*/
------------