Database Language: SQL Server
Difficulty: Easy
| Column Name | Type | | ----------- | ------- | | empId | int | | name | varchar | | supervisor | int | | salary | int |
empId is the column with unique VALUES for this table.
Each row of this table indicates the name and the ID of an employee in addition to their salary and the id of their manager.
| Column Name | Type | | ----------- | ---- | | empId | int | | bonus | int |
empId is the column of unique VALUES for this table.
empId is a foreign key (reference column) to empId from the Employee table.
Each row of this table contains the id of an employee and their respective bonus.
Write a solution to report the name and bonus amount of each employee with a bonus less than 1000.
Return the result table in any order.
The result format is in the following example.
Employee table:
| empId | name | supervisor | salary | | ----- | ------ | ---------- | ------ | | 3 | Brad | null | 4000 | | 1 | John | 3 | 1000 | | 2 | Dan | 3 | 2000 | | 4 | Thomas | 3 | 4000 |
Bonus table:
| empId | bonus | | ----- | ----- | | 2 | 500 | | 4 | 2000 |
| name | bonus | | ---- | ----- | | Brad | null | | John | null | | Dan | 500 |
CREATE TABLE [dbo].[Employee] (empId INT PRIMARY KEY, name VARCHAR(255), supervisor INT, salary INT);
CREATE TABLE [dbo].[Bonus] (empId INT PRIMARY KEY, bonus INT);
TRUNCATE TABLE [dbo].[Employee];
INSERT INTO [dbo].[Employee] (empId, name, supervisor, salary) VALUES ('3', 'Brad', NULL, '4000');
INSERT INTO [dbo].[Employee] (empId, name, supervisor, salary) VALUES ('1', 'John', '3', '1000');
INSERT INTO [dbo].[Employee] (empId, name, supervisor, salary) VALUES ('2', 'Dan', '3', '2000');
INSERT INTO [dbo].[Employee] (empId, name, supervisor, salary) VALUES ('4', 'Thomas', '3', '4000');
TRUNCATE TABLE [dbo].[Bonus];
INSERT INTO [dbo].[Bonus] (empId, bonus) VALUES ('2', '500');
INSERT INTO [dbo].[Bonus] (empId, bonus) VALUES ('4', '2000');
Given that there are 2 tables provided and data from each table is needed to come out of the output, this means that a JOIN will be used to join the `Employee` table with the `Bonus` table. Looking at the `Bonus` table, it can be seen that not all employees have an entry in this table. Based on the sample output, if an employee does not have an entry in the `Bonus` table, then that employee should be included in the output. From this information, we can determine that a `LEFT OUTER JOIN` will be used instead of an `INNER JOIN`. If the output will not include those employees who do not have a record in the `Bonus` table, then the `INNER JOIN` will be used. But since the output also wants those employees who do not have bonus aside from those who had less than 1000, then the `LEFT OUTER JOIN` will be used with the `Employee` table on the left side of the join. So the join clause will look as follows:
FROM Employee LEFT OUTER JOIN Bonus ON Employee.empId = Bonus.empId
Since we are only interested on those employees with no bonus at all (`Bonus.bonus IS NULL`) and those who got less than 1000 bonus (`Bonus.bonus < 1000`), then we can add those conditions in the `WHERE` clause:
FROM Employee LEFT OUTER JOIN Bonus ON Employee.empId = Bonus.empId WHERE Bonus.bonus IS NULL OR Bonus.bonus < 1000
Lastly, the requirement is to return just the name of the employee as well as the bonus received (if any):
# Final Solution Query SELECT Employee.name, Bonus.bonus FROM Employee LEFT OUTER JOIN Bonus ON Employee.empId = Bonus.empId WHERE Bonus.bonus IS NULL OR Bonus.bonus < 1000
The query plan generated by SQL Server for the query above is as follows:
|--Filter(WHERE:([leetcode].[dbo].[Bonus].[bonus] IS NULL OR [leetcode].[dbo].[Bonus].[bonus]<(1000)))
|--Nested Loops(Left Outer Join, WHERE:([leetcode].[dbo].[Employee].[empId]=[leetcode].[dbo].[Bonus].[empId]))
|--Clustered Index Scan(OBJECT:([leetcode].[dbo].[Employee].[PK_Employee]))
|--Clustered Index Scan(OBJECT:([leetcode].[dbo].[Bonus].[PK_Bonus]))
And here's the faster runtime for this query:
Runtime: 591ms
Beats: 98.12% as of July 16, 2024