Josh Josh - 6 months ago 177
SQL Question

SQL Query: "Write one SQL Query to calculate the maximum salaries for employees by Job Classification

The task is to "Write one SQL Query to calculate the maximum salaries for employees by Job Classification. (Output shows Alias).

There were two tables created, the Employee and Job_Title. The Employee table consist of the Salary while the the Job_Title table consist of the Job Classification such as 'Manager'.

The current code I have displays the employee who has the max salary in that classification, however, the Alias is not showing. It just displays all of the information for that Employee.

Here is my code:

SELECT *
FROM Employee
WHERE Salary IN (
SELECT MAX(Salary) AS 'Maximum_Salary_Class'
FROM Employee
WHERE JobID IN ( SELECT JobID
FROM Job_Title_Table_
WHERE Job_Classification = 'Manager' ) );

Answer

Something like this:

select t.Job_Classification, max(e.salary) as 'Maximum_Salary_Class' 
from Employee e join Job_Title_Table_ t on e.JobID = t.JobId
group by t.Job_Classification;