AdventureWorks Database: Return the highest hourly wage for each job title
SQL Query - AdventureWorks: Exercise-125 with Solution
125. From the following tables write a query in SQL to return the highest hourly wage for each job title. Restricts the titles to those that are held by men with a maximum pay rate greater than 40 dollars or women with a maximum pay rate greater than 42 dollars.
Sample table: HumanResources.Employeebusinessentityid|nationalidnumber|loginid |jobtitle |birthdate |maritalstatus|gender|hiredate |salariedflag|vacationhours|sickleavehours|currentflag|rowguid |modifieddate |organizationnode| ----------------+----------------+----------------------------+----------------------------------------+----------+-------------+------+----------+------------+-------------+--------------+-----------+------------------------------------+-----------------------+----------------+ 1|295847284 |adventure-works\ken0 |Chief Executive Officer |1969-01-29|S |M |2009-01-14|true | 99| 69|true |f01251e5-96a3-448d-981e-0f99d789110d|2014-06-30 00:00:00.000|/ | 2|245797967 |adventure-works\terri0 |Vice President of Engineering |1971-08-01|S |F |2008-01-31|true | 1| 20|true |45e8f437-670d-4409-93cb-f9424a40d6ee|2014-06-30 00:00:00.000|/1/ | 3|509647174 |adventure-works\roberto0 |Engineering Manager |1974-11-12|M |M |2007-11-11|true | 2| 21|true |9bbbfb2c-efbb-4217-9ab7-f97689328841|2014-06-30 00:00:00.000|/1/1/ | 4|112457891 |adventure-works\rob0 |Senior Tool Designer |1974-12-23|S |M |2007-12-05|false | 48| 80|true |59747955-87b8-443f-8ed4-f8ad3afdf3a9|2014-06-30 00:00:00.000|/1/1/1/ | 5|695256908 |adventure-works\gail0 |Design Engineer |1952-09-27|M |F |2008-01-06|true | 5| 22|true |ec84ae09-f9b8-4a15-b4a9-6ccbab919b08|2014-06-30 00:00:00.000|/1/1/2/ | 6|998320692 |adventure-works\jossef0 |Design Engineer |1959-03-11|M |M |2008-01-24|true | 6| 23|true |e39056f1-9cd5-478d-8945-14aca7fbdcdd|2014-06-30 00:00:00.000|/1/1/3/ | 7|134969118 |adventure-works\dylan0 |Research and Development Manager |1987-02-24|M |M |2009-02-08|true | 61| 50|true |4f46deca-ef01-41fd-9829-0adab368e431|2014-06-30 00:00:00.000|/1/1/4/ | 8|811994146 |adventure-works\diane1 |Research and Development Engineer |1986-06-05|S |F |2008-12-29|true | 62| 51|true |31112635-663b-4018-b4a2-a685c0bf48a4|2014-06-30 00:00:00.000|/1/1/4/1/ | 9|658797903 |adventure-works\gigi0 |Research and Development Engineer |1979-01-21|M |F |2009-01-16|true | 63| 51|true |50b6cdc6-7570-47ef-9570-48a64b5f2ecf|2014-06-30 00:00:00.000|/1/1/4/2/ | 10|879342154 |adventure-works\michael6 |Research and Development Manager |1984-11-30|M |M |2009-05-03|true | 16| 64|true |eaa43680-5571-40cb-ab1a-3bf68f04459e|2014-06-30 00:00:00.000|/1/1/4/3/ | -- more --Sample table: HumanResources.EmployeePayHistory
businessentityid|ratechangedate |rate |payfrequency|modifieddate | ----------------+-----------------------+-------+------------+-----------------------+ 1|2009-01-14 00:00:00.000| 125.5| 2|2014-06-30 00:00:00.000| 2|2008-01-31 00:00:00.000|63.4615| 2|2014-06-30 00:00:00.000| 3|2007-11-11 00:00:00.000|43.2692| 2|2014-06-30 00:00:00.000| 4|2007-12-05 00:00:00.000| 8.62| 2|2007-11-21 00:00:00.000| 4|2010-05-31 00:00:00.000| 23.72| 2|2010-05-16 00:00:00.000| 4|2011-12-15 00:00:00.000|29.8462| 2|2011-12-01 00:00:00.000| 5|2008-01-06 00:00:00.000|32.6923| 2|2014-06-30 00:00:00.000| 6|2008-01-24 00:00:00.000|32.6923| 2|2014-06-30 00:00:00.000| 7|2009-02-08 00:00:00.000|50.4808| 2|2014-06-30 00:00:00.000| 8|2008-12-29 00:00:00.000|40.8654| 2|2014-06-30 00:00:00.000| -- more --
Sample Solution:
SELECT JobTitle, MAX(ph1.Rate)AS MaximumRate
FROM HumanResources.Employee AS e
JOIN HumanResources.EmployeePayHistory AS ph1 ON e.BusinessEntityID = ph1.BusinessEntityID
GROUP BY JobTitle
HAVING (MAX(CASE WHEN Gender = 'M'
THEN ph1.Rate
ELSE NULL END) > 40.00
OR MAX(CASE WHEN Gender = 'F'
THEN ph1.Rate
ELSE NULL END) > 42.00)
ORDER BY MaximumRate DESC;
Sample Output:
jobtitle |maximumrate| --------------------------------+-----------+ Chief Executive Officer | 125.5| Vice President of Production | 84.1346| Vice President of Sales | 72.1154| Vice President of Engineering | 63.4615| Chief Financial Officer | 60.0962| Research and Development Manager| 50.4808| Information Services Manager | 50.4808| North American Sales Manager | 48.101| Pacific Sales Manager | 48.101| European Sales Manager | 48.101| Engineering Manager | 43.2692| Finance Manager | 43.2692|
SQL AdventureWorks Editor:
Practice Online
Contribute your code and comments through Disqus.
Previous: Order the result set by the column TerritoryName using CASE.
Next: Sort the BusinessEntityID in descending or ascending order.
What is the difficulty level of this exercise?
Test your Programming skills with w3resource's quiz.
- Weekly Trends and Language Statistics
- Weekly Trends and Language Statistics