Simple SQL Query Example

Question

I am having trouble figuring out an elegant way to complete an example query. I have come close, but I think my answer is not correct.

Here is a snippet of code that outlines the table we will be working with:

create table instructor
    (ID         varchar(5),
     name           varchar(20) not null,
     dept_name      varchar(20),
     salary         numeric(8,2) check (salary > 29000),
     primary key (ID),
     foreign key (dept_name) references department(dept_name)
        on delete set null
    ) ENGINE = INNODB;

Here is the Example data in the table I am working with:

insert into instructor values ('10101', 'Srinivasan', 'Comp. Sci.', '65000');
insert into instructor values ('12121', 'Wu', 'Finance', '90000');
insert into instructor values ('15151', 'Mozart', 'Music', '40000');
insert into instructor values ('22222', 'Einstein', 'Physics', '95000');
insert into instructor values ('32343', 'El Said', 'History', '60000');
insert into instructor values ('33456', 'Gold', 'Physics', '87000');
insert into instructor values ('45565', 'Katz', 'Comp. Sci.', '75000');
insert into instructor values ('58583', 'Califieri', 'History', '62000');
insert into instructor values ('76543', 'Singh', 'Finance', '80000');
insert into instructor values ('76766', 'Crick', 'Biology', '72000');
insert into instructor values ('83821', 'Brandt', 'Comp. Sci.', '92000');

The Query I am looking for is: 3. For each instructor, their name, their salary, and the number of instructors who earn more than they do.

My original answer to this was:

Select DISTINCT T.ID, T.name, T.salary, count(S.ID) as num FROM instructor as T, instructor as S Where S.salary>T.salary group by T.ID;

This returns close to what I am looking for. It returns the list of every instructor, their salary, and the number of instructors who earn more than they do, but it leaves out the highest earning instructor named "Einstein" because there is no one who earns more than him. Is there a way to add this instructor in the returned relation with a count of "0"?

Answer 1

You could use a sub query like so

select i.id,i.name,i.salary,
(select count(*) from instructor as i2 where i2.salary>i.salary and i2.id<>i.id) as COUNT_HIGHER
from instructor as i

Answer 2

Your query is not returning Einstein because your where clause is S.salary>T.salary Since no one earns more than him, he is filtered out.

To include, you can change it to S.salary>=T.salary and subtract 1 from count to give correct result.

Select DISTINCT T.ID, T.name, T.salary, count(S.ID)-1 as num 
FROM instructor as T, instructor as S 
Where S.salary>=T.salary 
group by T.ID;

Answer 3

Firstly, you should not be using distinct with group by since grouping means each row will be distinct.

Your logic is not correct since, if I understand your question correctly, you are not trying to filter rows, only count rows.

The following correlated aggregate should give you the results you're expecting:

select Id, Name, Salary, 
  (select Count(*) from instructor ii where ii.salary > i.salary) as num
from instructor i;