Doctors Earning More Than Their Supervisors
Write an SQL query to find the doctors who earn more than their supervisors.
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 |
Doctor |
Kiran |
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