Skip to content

Latest commit

 

History

History
119 lines (99 loc) · 3.04 KB

577. Employee Bonus.md

File metadata and controls

119 lines (99 loc) · 3.04 KB

577. Employee Bonus

1. Description

SQL Schema: 
CREATE TABLE IF NOT EXISTS Employee (
    empId int,
    name varchar(255),
    supervisor int,
    salary int
)
CREATE TABLE IF NOT EXISTS Bonus (
    empId int,
    bonus int
)

TRUNCATE TABLE Employee
INSERT INTO Employee (empId, name, supervisor, salary) VALUES ('3', 'Brad', 'None', '4000')
INSERT INTO Employee (empId, name, supervisor, salary) VALUES ('1', 'John', '3', '1000')
INSERT INTO Employee (empId, name, supervisor, salary) VALUES ('2', 'Dan', '3', '2000')
INSERT INTO Employee (empId, name, supervisor, salary) VALUES ('4', 'Thomas', '3', '4000')

TRUNCATE TABLE Bonus
INSERT INTO Bonus (empId, bonus) VALUES ('2', '500')
INSERT INTO Bonus (empId, bonus) VALUES ('4', '2000')

Table: Employee

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| empId       | int     |
| name        | varchar |
| supervisor  | int     |
| salary      | int     |
+-------------+---------+
empId is the primary key column 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.

Table: Bonus

+-------------+------+
| Column Name | Type |
+-------------+------+
| empId       | int  |
| bonus       | int  |
+-------------+------+
empId is the primary key column for this table.
empId is a foreign key to empId from the Employee table.
Each row of this table contains the id of an employee and their respective bonus.

Write an SQL query to report the name and bonus amount of each employee with a bonus less than 1000.

Return the result table in any order.

The query result format is in the following example.

Example 1:

Input:
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  |
+-------+-------+
Output:
+------+-------+
| name | bonus |
+------+-------+
| Brad | null  |
| John | null  |
| Dan  | 500   |
+------+-------+

2. Solutions

Solution 1: Language: SQL JOIN + IS NULL

LEFT OUTER JOIN could be replaced by LEFT JOIN.

The LEFT JOIN keyword returns all records from the left table, even if there are no matches in the right table (as NULL).

  • Wednesday, 8 December, 2022
  • Runtime: 755 ms, faster than 97.29% of MySQL online submissions for Employee Bonus.
  • Memory Usage: 0B, less than 100.00% of MySQL online submissions for Employee Bonus.
SELECT
    Employee.name, Bonus.bonus
FROM
    Employee
        LEFT OUTER JOIN
    Bonus
        ON Employee.empID = Bonus.empID
WHERE
    Bonus.bonus < 1000 OR Bonus.bonus IS NULL;