[LeetCode] 1873. Calculate Special Bonus

1873. Calculate Special Bonus

Easy

SQL Schema

Table: Employees

+-------------+---------+
| Column Name | Type    |
+-------------+---------+
| employee_id | int     |
| name        | varchar |
| salary      | int     |
+-------------+---------+
employee_id is the primary key for this table.
Each row of this table indicates the employee ID, employee name, and salary.

 

Write an SQL query to calculate the bonus of each employee. The bonus of an employee is 100% of their salary if the ID of the employee is an odd number and the employee name does not start with the character 'M'. The bonus of an employee is 0 otherwise.

Return the result table ordered by employee_id.

The query result format is in the following example.

 

Example 1:

Input: 
Employees table:
+-------------+---------+--------+
| employee_id | name    | salary |
+-------------+---------+--------+
| 2           | Meir    | 3000   |
| 3           | Michael | 3800   |
| 7           | Addilyn | 7400   |
| 8           | Juan    | 6100   |
| 9           | Kannon  | 7700   |
+-------------+---------+--------+
Output: 
+-------------+-------+
| employee_id | bonus |
+-------------+-------+
| 2           | 0     |
| 3           | 0     |
| 7           | 7400  |
| 8           | 0     |
| 9           | 7700  |
+-------------+-------+
Explanation: 
The employees with IDs 2 and 8 get 0 bonus because they have an even employee_id.
The employee with ID 3 gets 0 bonus because their name starts with 'M'.
The rest of the employees get a 100% bonus.

---

문제 풀이

• employees 테이블에서 employee_id가 홀수가 아니고 이름이 M으로 시작하지 않는 사람은 보너스를 샐러리 만큼 준다.
• employee_id가 짝수이거나 이름이 M으로 시작하면 보너스를 0만 준다.
• 위 조건으로 테이블을 만들고 employee_id로 정렬해야한다. 

소스 코드

# Write your MySQL query statement below
select employee_id, salary as bonus
from Employees
where employee_id % 2 <> 0 and name not like 'M%'

union

select employee_id, 0 as bonus
from Employees
where employee_id % 2 = 0 or name like 'M%'
order by employee_id