Search code examples
mysqlsqlselectgroup-by

Find the highest salary of instructors in that department

SQL query: For each department, find the highest salary of instructors in that department.

Instructor schema: Instructor(ID , name, dept_name, salary) Id primary key, dept_name foreign key referencing department.

department(dept_name, building, budget)

Instructor table values:

ID       Iname     dept_name    salary
10101   Srinivasan  Comp. Sci.  65000
12121   Wu          Finance     90000
15151   Mozart      Music       40000
22222   Einstein    Physics     95000
32343   El Said     History     60000
33456   Gold        Physics     87000
45565   Katz        Comp. Sci.  75000
58583   Califieri   History     62000
76543   Singh       Finance     80000
76766   Crick       Biology     72000
83821   Brandt      Comp. Sci.  92000
98345   Kim         Elec. Eng.  80000

If we try to find out highest salary manually,

Brandt      Comp. Sci
Wu          Finance
Mozart      Music
Einstein    Physics
Califieri   History
Crick       Biology 
Kim         Elec. Eng.

Now I run this query,

select distinct 
       T.Iname, 
       T.dept_name 
  from instructor as T, 
       instructor as S 
 where T.salary > S.salary 
 group by T.dept_name;

I got

Kim Elec. Eng.
Brandt  Comp. Sci.
Crick   Biology
Singh   Finance
Gold    Physics
Califieri   History

I got incorrect result for Finance and Physics and music is not even included.

I can not find my mistake. I would like to know where I need to modify?

Thank you.

Solution

  • I think the below SQL will work.

    select Iname, dept_name from instructor as t1 where t1.salary=(select max(salary) from instructor as t2 where t1.dept_name = t2.dept_name);

    or

    select t1.Iname, t1.dept_name from instructor as t1 left join instructor as t2 on t1.dept_name = t2.dept_name and t1.salary < t2.salary where t2.dept_name is NULL;