Sunday, September 13, 2020

SQL Self Join Example - SQL Query to Find Employees Earning More Than Managers - LeetCode Solution

Hello guys, are you looking for a simple example of how to use SELF JOIN in SQL? If yes, then you have come to the right place. This article will show you how to use Self join in solving interesting SQL problems from LeetCode. Along the way, you will also learn this useful SQL concept. So, what are you waiting for? Let's first check the problem, and then we'll write an SQL query using SELF Join to solve this problem.

Write an SQL Query to Find Employees Earning More Than Managers
The Employee table holds all employees, including their managers. Every employee has an Id, and there is also a column for the manager Id, as shown below:

+----+-------+--------+-----------+
| Id | Name  | Salary | ManagerId |
+----+-------+--------+-----------+
| 1  | Joe   | 70000  | 3         |
| 2  | Henry | 80000  | 4         |
| 3  | Sam   | 60000  | NULL      |
| 4  | Max   | 90000  | NULL      |
+----+-------+--------+-----------+

Given the Employee table, write a SQL query that finds employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager because Henry's Manager is Max, who earns 90000, which is more than Henry's salary of 80000.

Saturday, September 12, 2020

Difference between row_number(), rank() and dense_rank() window functions in SQL

Though all three are ranking functions in SQL, also known as a window function in Microsoft SQL Server, the difference between rank(), dense_rank(), and row_number() comes when you have ties on ranking i.e. duplicate records. For example, if you are ranking employees by their salaries then what would be the rank of two employees of the same salaries? It depends on which ranking function you are using like row_number, rank, or dense_rank.