[leetcode] Day 2. SELECT & Order

1873. Special Bonus 

 

출처 -  https://leetcode.com/problems/calculate-special-bonus/

sql

열기 줄바꿈

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.

• id 가 홀수이면서 이름이 M으로 시작하지 않는 멤버에 대해서만 보너스를 지급한다.

 

  Sol 1) 다시 작성한 쿼리

sql

열기 줄바꿈

SELECT employee_id,    
       CASE WHEN employee_id % 2 = 0 OR SUBSTR(NAME, 1, 1) = "M" THEN 0
            ELSE salary
            END AS bonus
FROM Employees 
ORDER BY employee_id

 

  Sol 2) 처음에 복잡하게 짠 로직

• CASE 문 안에 조건을 AND나 OR로 묶을 수 있다는 걸 알고서 쿼리를 위와 같이 다시 짰다.

sql

열기 줄바꿈

WITH TEMP AS 
(
SELECT *, SUBSTR(NAME, 1, 1) "STARTCHAR"
FROM Employees 
),
tem AS
(
SELECT *
,     case STARTCHAR
        WHEN "M" then 0
        ELSE salary
        END AS bonus
FROM TEMP
)
SELECT employee_id,
        CASE 
            WHEN employee_id % 2 = 0 OR STARTCHAR = "M" THEN 0
            ELSE salary
            END AS bonus
FROM tem

 

 

627. Swap Salary

출처 - https://leetcode.com/problems/swap-salary/

 

 Sol)

 

 

 

 

 

196. Delete Duplicate Emails

출처 - https://leetcode.com/problems/delete-duplicate-emails/

 

 

 Sol)