코딩테스트 SQL 코드카타 (Employee Bonus)

문제

Employee Bonus - LeetCode

 

 

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:
+-------+--------+------------+--------+
| 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   |
+------+-------+

 

 

---

풀이

1. 테이블의 결합
2. 보너스를 1000초과하여 받은 인원 제외 하기
3. 나머지 프린트
4. 없다면 null

 

---

코드

 

bonus가 없을떄 null을 할당하기 위해 left join 사용.

 

bonus테이블의 값이  null 이거나 1000미만일때 표시

select e.name, b.bonus
from Employee e
left join Bonus b
on e.empID = b.empID
where b.empID is null or b.bonus < 1000

 

 

---

링크

LeetCode/0577-employee-bonus/0577-employee-bonus.sql at main · K-MarkLee/LeetCode

LeetCode/0577-employee-bonus/0577-employee-bonus.sql at main · K-MarkLee/LeetCode