Employee earning more that their managers is very simple and easy question asked in many interviews. This is similar to our last question Delete Duplicate Emails.


Lets start with SQL Schema of table named Employee:


CREATE Table Employee (id int, name varchar(255), salary int, managerId int)


INSERT INTO Employee (id, name, salary, managerId) values ('1', 'Joe', '70000', '3')

INSERT INTO Employee (id, name, salary, managerId) values ('2', 'Henry', '80000', '4')

INSERT INTO Employee (id, name, salary, managerId) values ('3', 'Sam', '60000', '0')

INSERT INTO Employee (id, name, salary, managerId) values ('4', 'Max', '90000', '0')



Table will look like this:



id name salary managerId
1 Joe 70000 3
2 Henry 80000 4
3 Sam 60000 0
4 Max 90000 0


Output we require:


Employee
Joe


Let's start with SQL Algorithm to find the query which will provide this output:

First Step is to get the data which are having managerId and id related data with self join. So the query will be:

SELECT * FROM Employee e1 JOIN Employee e2 WHERE e1.managerId=e2.id;


This will provide the output:


id name salary managerId id name salary managerId
1 Joe 70000 3 3 Sam 60000 0
2 Henry 80000 4 4 Max 90000 0


Now we can see all the related data in table above.
We want to find the employee having maximum salary than their manager.

Here is the query to get that:

SELECT * FROM Employee e1 JOIN Employee e2 WHERE e1.managerId=e2.id and e1.salary>e2.salary;

Now the table will look like this:




id name salary managerId id name salary managerId
1 Joe 70000 3 3 Sam 60000 0


As you can see 'Joe' the more earning than his manager Sam. Burt this is not the output which is required.

There is just a small change in query then we can have the required output. Here is the query of that:

SELECT e1.name as Employee FROM Employee e1 JOIN Employee e2 WHERE e1.managerId=e2.id and e1.salary>e2.salary;


This is the final query. This is very simple and similar to Delete Duplicate Email.