w3resource

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.Employee


Click to view Full table

Sample table: HumanResources.EmployeePayHistory


Click to view Full table

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.



Follow us on Facebook and Twitter for latest update.

SQL: Tips of the Day

Difference between natural join and inner join

One significant difference between INNER JOIN and NATURAL JOIN is the number of columns returned-

Consider:

TableA                           TableB
+------------+----------+        +--------------------+    
|Column1     | Column2  |        |Column1  |  Column3 |
+-----------------------+        +--------------------+
| 1          |  2       |        | 1       |   3      |
+------------+----------+        +---------+----------+

The INNER JOIN of TableA and TableB on Column1 will return

SELECT * FROM TableA AS a INNER JOIN TableB AS b USING (Column1);
SELECT * FROM TableA AS a INNER JOIN TableB AS b ON a.Column1 = b.Column1;

+------------+-----------+---------------------+    
| a.Column1  | a.Column2 | b.Column1| b.Column3|
+------------------------+---------------------+
| 1          |  2        | 1        |   3      |
+------------+-----------+----------+----------+

The NATURAL JOIN of TableA and TableB on Column1 will return:

SELECT * FROM TableA NATURAL JOIN TableB
+------------+----------+----------+    
|Column1     | Column2  | Column3  |
+-----------------------+----------+
| 1          |  2       |   3      |
+------------+----------+----------+

Ref: https://bit.ly/3AG5CId

 

We are closing our Disqus commenting system for some maintenanace issues. You may write to us at reach[at]yahoo[dot]com or visit us at Facebook