LEETCODE 1873 - Calculate Special Bonus - Solution in MySQL | LEETCODE 1873 Database SQL Solution

 LEETCODE | 1873
Calculate Special Bonus

LEETCODE 1873 - Calculate Special Bonus - Solution in MySQL | LEETCODE 1873 Database SQL Solution:

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.

Solution of LEETCODE 1873 - Calculate Special Bonus:

MySQL:

SELECT employee_id,                                                    CASE WHEN employee_id%2 <> 0 AND name not like 'M%' THEN salary     ELSE 0                                                             END as bonus                                                    FROM Employees                                                     ORDER BY employee_id;

or,

 SELECT employee_id ,                                                 IF(employee_id % 2 = 1 AND name NOT LIKE 'M%',salary,0 )            AS bonus  FROM Employees                                          ORDER BY employee_id;

Here we use IF to check for the odd emplyee_ids and also use AND, NOT and LIKE to check for names not starting with M.

The format of the IF statement is

IF( condition , value_if_true , value_if_false)

Also for the names

LIKE 'M%' means that starting letter must be M the rest may be of any length and any letter.

Next Problem:

LEETCODE 627 - Swap Salary - Solution in MySQL | LEETCODE 627 Database SQL Solution

Previous Problem: 

LEETCODE 183 - Customers Who Never Order - Solution in MySQL | LEETCODE 183 Database SQL Solution 

Ashraf LEETCODE Solutions