Doctors Earning More Than Their Supervisors



Write an SQL query to find the doctors who earn more than their supervisors.



Input :-

Doctor table :-
ID is the Primary Key column for the Doctors table.

ID

Name

Compensation

Supervisor_ID

1

Kiran

60000

3

2

Prakash

70000

4

3

Rajesh

50000

null

4

Shekar

80000

null



Output :-

Doctor

Kiran



Query :-
select d2.name as Doctor
from Doctor d1, Doctor d2
where d2.Supervisor_ID=d1.ID and d2.compensation>d1.compensation;

Explanation : - 


This SQL query selects the names of doctors (represented by the name attribute in the Doctor table) who have a supervisor (represented by the Supervisor_ID attribute in the Doctor table) with a higher compensation than themselves.

The query achieves this by joining the Doctor table with itself (using aliases d1 and d2) on the condition that d2.Supervisor_ID matches d1.ID. This means that d2 is a doctor who reports to d1.

The where clause then applies a filter condition to select only those doctors (d2) whose compensation is greater than their supervisor's (d1).

The result of the query is a list of doctors who have higher compensation than their supervisors.






No comments

darkmode