9/18 每日一題(MYSQL 員工號為奇數且名稱不是M開頭的員工將頒發獎金)

Table: Employees

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

 

Write a solution 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's 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 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.

# Write your MySQL query statement below

SELECT employee_id ,

case when name not like 'M%' and employee_id %2 =1 then salary else 0 END bonus

FROM Employees

order by employee_id

-------------------------------------------

# Write your MySQL query statement below
select employee_id, if(employee_id % 2 = 1 and name not like "M%", salary, 0) as bonus
-- if函数，if(判断条件，满足结果，不满足结果)
from employees
order by employee_id

-----------------------------

import pandas as pd
def calculate_special_bonus(employees: pd.DataFrame) -> pd.DataFrame:
    #appley(func,axis)
    #axis=0對應列, 1表示對應行
    #返回值,如果對於單個軸(行或列)應用,apply()返回一個包含應用數結果的Serise
    #如果是對整個DataFrame,則返回一個新的DataFrame        
    
    employees['bonus']=employees.apply(func=lambda row: row['salary'] if (row['employee_id']%2==1 and not row['name'].startswith('M'))else 0,axis=1)
    return employees[['employee_id','bonus']].sort_values('employee_id')
    

 -----------------------------------

import pandas as pd

def calculate_special_bonus(employees: pd.DataFrame) -> pd.DataFrame:
    employees['bonus'] = employees.apply(lambda row: row['salary'] if row['employee_id'] % 2 == 1 and row['name'][0] != 'M' else 0, axis=1)
    result = employees[['employee_id', 'bonus']].sort_values(by='employee_id')
    return result

----------------------------

import pandas as pd


def cal_bonus(df):
    if df['employee_id'] % 2 == 1 and not df['name'].startswith('M'):
        return df['salary']
    else:
        return 0

def calculate_special_bonus(employees: pd.DataFrame) -> pd.DataFrame:
    employees['bonus'] = employees.apply(cal_bonus, axis=1)
    employees.sort_values('employee_id', ascending=True, inplace=True)
    return employees[['employee_id', 'bonus']]