Employees Earning More Than Their Managers

Write an SQL query to find the employees who earn more than their managers. Return the result table in any order.

• Organization structure in general
• Join in SQL in general
• Self Join

In every organization, there are multiple people working all together. Some of might have in higher positions like manager, CEO etc. and some of them might be working as fresher, junior employees etc. But in general all are termed as employee. And to maintain the details a company need to create a single table common for all.

But to get the idea of relationship of employees like who is CEO, manager or junior employee table employee might be have some specific column to denote this.

Like for example the above employee table contains id, name, salary and managerId. MangerId is the column by which we can identify the who is the manager of the particular employee.

And managerId value will be nothing but the id of the other employee who is manager.

Join in General

It is not a good practice to store all the information in a single table. It will cause lots of problem like redundancy of data, difficul to manage data etc. To overcome these problems generally what we do is create different tables to store different kind of data and connect all the table via some foreign key relationship. So, in later point of time when we need to get the information of some particular entity (employee/student) we can look into the other tables via matching the unique value of that entity.

For example, consider a table named student where we have information like ( studentId, name, classid,score) and another table class (classid, className).

So, to get the class name of a particular student we can connect the tables student and class with the help of classid column which is present in both the table.

In student table classid is foreign key and in class table classid is primary key.

To get complete information of students we can join these two tables.

select * from student as s
join class as c on s.class = c.class

Self Join

Self join is similar to other joins in sql. In which a column is required to connect the table(s). But in case of self join instead of connecting to some other tables it will connect to the same table with other column.

For example, consider our employee table, in which we have two ids employeeid and managerid. For both the values will be same. 

• Let's say Sonu is manager whose details are (1,'Sonu Kumar',500000,NULL)
• Note: managerid column value is null because sonu is himself a manger.
• Khusboo is a junior employee whose manager is Sonu then in the database the details will be (2,'Khushboo Kumari', 4000, 1)
• Note: manager id is 1 because khushboo's manager name is sonu whose employeeid is 1.

To get complete information of employees we need to join the same table with two different alias name.

Get employee information

select * from Employee

• Joe's manager is Sam and Henry's manager is Max.

Now, join the same table with managerid as foreign key and id as primary key.

select * from employee as e
join Employee as m on e.managerId = m.id

Now, our main question was to select all names of employees whose salary is greater than their manager.

• Joe salary is 70000 and his manager Sam salary is 60000
• Henry salary is 80000 and his manager Max salary is 90000

So, according to the question our output will be Joe. We just need to apply a where clause, where employee.salary > manager.salary

Solution:

SELECT e.name from employee as e
left join employee as e1 on e.mangerId = e1.id
where e.salary > e1.salary

CREATE TABLE [dbo].[employee](
	[id] [int] NOT NULL,
	[name] [varchar](50) NULL,
	[salary] [int] NULL,
	[mangerId] [int] NULL,
 CONSTRAINT [PK_employee] PRIMARY KEY CLUSTERED 
(
	[id] ASC
)
) ON [PRIMARY]
GO
INSERT [dbo].[employee] ([id], [name], [salary], [mangerId]) VALUES (1, N'Joe', 70000, 3)
GO
INSERT [dbo].[employee] ([id], [name], [salary], [mangerId]) VALUES (2, N'Henry', 80000, 4)
GO
INSERT [dbo].[employee] ([id], [name], [salary], [mangerId]) VALUES (3, N'Sam', 60000, NULL)
GO
INSERT [dbo].[employee] ([id], [name], [salary], [mangerId]) VALUES (4, N'Max', 90000, NULL)
GO