DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE
SITE SEARCH SQLUSA.com HEADLINES NEWS
SQL E/BOOKS   SQL 2014 PROGRAMMING   DOWNLOADS
SCRIPTS SQL 2005 SQL 2008 ARTICLES
SQL JOBS TWITTER FORMAT VIDEOS
How to obtain the minimum salary for each deparment?

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

-- Microsoft SQL Server lowest salary by department - minimum salary
-- MSSQL inner join - correlated subquery
USE AdventureWorks;
SELECT Lowest = '$'+convert(varchar,eph.Rate,1),
       Staff = 'Lowest 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 MIN(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
 
Lowest      Staff
$40.87      Lowest salaried employee in Research and Development is Matthew, Gigi
$40.87      Lowest salaried employee in Research and Development is Margheim, Diane
$39.06      Lowest salaried employee in Executive is Norman, Laura
$32.69      Lowest salaried employee in Engineering is Erickson, Gail
$32.69      Lowest salaried employee in Engineering is Goldberg, Jossef
$32.69      Lowest salaried employee in Engineering is Salavaria, Sharon
$27.40      Lowest salaried employee in Information Services is Bueno, Janaina
$27.40      Lowest salaried employee in Information Services is Bacon, Dan
$27.40      Lowest salaried employee in Information Services is Meyyappan, Ramesh
$27.40      Lowest salaried employee in Information Services is Berg, Karen
$23.08      Lowest salaried employee in Sales is Blythe, Michael
$23.08      Lowest salaried employee in Sales is Mitchell, Linda
$23.08      Lowest salaried employee in Sales is Carson, Jillian
$23.08      Lowest salaried employee in Sales is Vargas, Garrett
$23.08      Lowest salaried employee in Sales is Reiter, Tsvi
$23.08      Lowest salaried employee in Sales is Ansman-Wolfe, Pamela
$23.08      Lowest salaried employee in Sales is Ito, Shu
$23.08      Lowest salaried employee in Sales is Saraiva, José
$23.08      Lowest salaried employee in Sales is Campbell, David
$23.08      Lowest salaried employee in Sales is Pak, Jae
$23.08      Lowest salaried employee in Sales is Varkey Chudukatil, Ranjit
$23.08      Lowest salaried employee in Sales is Mensa-Annan, Tete
$23.08      Lowest salaried employee in Sales is Valdez, Rachel
$23.08      Lowest salaried employee in Sales is Tsoflias, Lynn
$13.94      Lowest salaried employee in Human Resources is Culbertson, Grant
$13.94      Lowest salaried employee in Human Resources is Chen, Hao
$13.46      Lowest salaried employee in Finance is Barber, David
$13.46      Lowest salaried employee in Marketing is Benshoof, Wanida
$13.46      Lowest salaried employee in Marketing is Dempsey, Mary
$13.46      Lowest salaried employee in Marketing is Brown, Kevin
$10.58      Lowest salaried employee in Quality Assurance is Alexander, Sean
$10.58      Lowest salaried employee in Quality Assurance is Harrington, Mark
$10.58      Lowest salaried employee in Quality Assurance is Charncherngkha, Sootha
$10.58      Lowest salaried employee in Quality Assurance is Berglund, Andreas
$10.25      Lowest salaried employee in Document Control is Chai, Sean
$10.25      Lowest salaried employee in Document Control is Berge, Karen
$9.86       Lowest salaried employee in Purchasing is Word, Sheela
$9.25       Lowest salaried employee in Facilities and Maintenance is Penor, Lori
$9.25       Lowest salaried employee in Facilities and Maintenance is Macrae, Stuart
$9.25       Lowest salaried employee in Facilities and Maintenance is Berry, Jo
$9.25       Lowest salaried employee in Facilities and Maintenance is Coleman, Pat
$9.00       Lowest salaried employee in Shipping and Receiving is Bischoff, Jimmy
$9.00       Lowest salaried employee in Shipping and Receiving is Eaton, Susan
$9.00       Lowest salaried employee in Shipping and Receiving is Ralls, Kim
$8.62       Lowest salaried employee in Production Control is Vong, William
$8.62       Lowest salaried employee in Tool Design is Walters, Rob
$6.50       Lowest salaried employee in Production is Koch, Reed
$6.50       Lowest salaried employee in Production is Lawrence, David
$6.50       Lowest salaried employee in Production is King, Russell
$6.50       Lowest salaried employee in Production is Frum, John
$6.50       Lowest salaried employee in Production is Ingle, Marc
$6.50       Lowest salaried employee in Production is Johnson, David
$6.50       Lowest salaried employee in Production is Li, George

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

Exam Prep 70-461
Exam 70-461
DATETIME SELECT SELECT INTO DATE PAD STRING DYNAMIC SQL CURSOR MONEY FORMAT PERCENT STORED PROCEDURE SQL SERVER AGENT JOB OPTIMIZATION WHILE LOOP OVER PARTITION BY UPDATE