Calculate Special Bonus Sql Leetcode

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

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.

The query result format is in the following example.

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.

 Calculate Special Bonus Sql Leetcode

SELECT

employee_id,

CASE WHEN employee_id % 2 = 1

AND LEFT(name, 1)!= "M" THEN salary ELSE 0 END As bonus

FROM

Employees

ORDER BY

employee_id

Explanation

• The SELECT statement selects the employee_id column and a calculated bonus column.
• The CASE statement checks if the employee_id is odd and the first letter of the name is not "M". If both conditions are true, then the bonus column will be set to the employee's salary. Otherwise, the bonus column will be set to 0.
• The FROM statement specifies the table Employees where the data is being selected from.
• The ORDER BY statement sorts the result set in ascending order based on the employee_id column.

Disclaimer: The above Problem is generated by Leetcode but the Solution is provided by ShouterFolk