I did an easy Leetcode challenge today. For the moment, I am quite happy with Leetcode cause the challenges although easy is helping me understand the basic concepts very well. I think for the start I will stick to doing easy challenges and then move on to doing medium challenges to curb my frustration of not being able to solve the medium ranked challenges. Doing the easy challenge will first allow me to understand the basic concepts which I can then apply to the medium ranked challenges. Hoping this strategy works well.

Challenge: For today’s challenge, I had to find out Employees who were being paid more than their Managers.

Id	Name	Salary	ManagerId
1	Joe	70000	3
2	Henry	80000	4
3	Sam	60000	Null
4	Max	90000	Null

Expected Output:

Employee
Joe

My solution:

 SELECT b.Name AS Employee FROM Employee a
 INNER JOIN Employee b
 ON a.Id = b.ManagerId
 WHERE a.Salary < b.Salary;
 Runtime: 756 ms, 15.46% faster 

Breaking down the code:

Query 1

 SELECT * FROM Employee a
 INNER JOIN Employee b
 ON a.Id = b.ManagerId 

Output

Id	Name	Salary	ManagerId	Id	Name	Salary	ManagerId
4	Max	90000	–	2	Henry	80000	4
3	Sam	60000	–	1	Joe	70000	3

I used an INNER JOIN on self to find the managers with their employees and finally used ‘WHERE’ clause to find manager’s salary that are less than employee’s salary .

Query 2

SELECT * FROM Employee a
INNER JOIN Employee b
ON a.Id = b.ManagerId 
WHERE a.Salary < b.Salary;

Output

Id	Name	Salary	ManagerId	Id	Name	Salary	ManagerId
3	Sam	60000	–	1	Joe	70000	3

Other Submitted Solution 1:

SELECT Name as Employee 
FROM employee a 
WHERE EXISTS(SELECT 1 FROM employee b 
WHERE a.managerid = b.id 
AND a.salary>b.salary)
Runtime: 932 ms, 5.02% 

The ‘EXISTS’ function used here is an operator is used to test for the existence of any record in a subquery.

Syntax

SELECT column_name(s)
FROM table_name
WHERE EXISTS
(SELECT column_name FROM table_name WHERE condition); 

Breaking down the code

 SELECT * FROM employee a WHERE EXISTS(SELECT * FROM employee b) 

Output

ID	Name	Salary	ManagerId
4	Max	90000	–
3	Sam	60000	–
2	Henry	80000	4
1	Joe	70000	3

Query 2

SELECT * FROM employee a 
WHERE EXISTS (SELECT * FROM employee b 
WHERE a.managerid = b.id); 

Output

Id	Name	Salary	ManagerId
2	Henry	80000	4
1	Joe	70000	3

Query 3

SELECT * FROM employee a 
WHERE EXISTS (SELECT Name FROM employee b 
WHERE a.managerid = b.id 
AND a.salary>b.salary); 

Output

Id	Name	Salary	ManagerId
1	Joe	70000	3

Other Submitted Solution 2:

 SELECT emp.name "Employee"
 FROM employee emp, employee mgr
 WHERE mgr.id = emp.managerId
 AND emp.salary > mgr.salary;
 Runtime: 574 ms, 69.88% 

Breaking down the code

Query 1

SELECT * FROM employee emp, employee mgr
ORDER BY emp.Id; 

Output

Id	Name	Salary	ManagerId	Id	Name	Salary	ManagerId
1	Joe	70000	3	2	Henry	80000	4
1	Joe	70000	3	3	Sam	60000	–
1	Joe	70000	3	4	Max	90000	–
1	Joe	70000	3	1	Joe	70000	3
2	Henry	80000	4	2	Henry	80000	4
2	Henry	80000	4	3	Sam	60000	–
2	Henry	80000	4	4	Max	90000	–
2	Henry	80000	4	1	Joe	70000	3
3	Sam	60000	–	2	Henry	80000	4
3	Sam	60000	–	3	Sam	60000	–
3	Sam	60000	–	4	Max	90000	–
3	Sam	60000	–	1	Joe	70000	3
4	Max	90000	–	2	Henry	80000	4
4	Max	90000	–	3	Sam	60000	–
4	Max	90000	–	4	Max	90000	–
4	Max	90000	–	1	Joe	70000	3

Query 2

 SELECT * FROM employee emp, employee mgr
 WHERE mgr.id = emp.managerId; 

Output

Id	Name	Salary	ManagerId	Id	Name	Salary	ManagerId
1	Joe	70000	3	3	Sam	60000	–
2	Henry	80000	4	4	Max	90000	–

Query 3

 SELECT * FROM employee emp, employee mgr
 WHERE mgr.id = emp.managerId
 AND emp.salary > mgr.salary; 

Output

Id	Name	Salary	ManagerId	Id	Name	Salary	ManagerId
1	Joe	70000	3	3	Sam	60000

• Facebook
• Instagram
• LinkedIn
• GitHub