577 Employee Bonus

Description

Table: Employee

• 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.

Table: Bonus

• 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.

Problem Statement

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.

Example 1:

Input:

• Employee table:

• Bonus table:

Output:

Solution

In this case, we need to return two fields in the output: name and bonus of an employee. The problem can be divided into following parts:

1. Find empId from Bonus table who have less than 1000 as bonus amount.
2. Associate name to these employees using Employee table. You will have to perform join in this case.
3. One caveat with this problem is that if the employee is not present in Bonus table, that employee had 0 as bonus amount. So, in order to consider this, you either have to mark those employee bonus as 0 or you can use perform Employee LEFT OUTER JOIN Bonus to retrieve all employees and not just employees present in the Bonus table.

1SELECT e.name, b.bonus
2    FROM Employee e
3    LEFT OUTER JOIN Bonus b
4    ON e.empId = b.empId
5    WHERE b.bonus IS NULL OR b.bonus < 1000;