Description
Write a SQL query to calculate bonus: 100% of salary if employee_id is odd AND name doesn't start with 'M', otherwise 0. Return employee_id and bonus.
employees| Column | Type |
|---|---|
| employee_id | INT |
| name | TEXT |
| salary | INT |
Examples
CREATE TABLE employees (employee_id INT, name TEXT, salary INT); INSERT INTO employees VALUES (1, 'Alice', 1000), (2, 'Bob', 2000), (3, 'Mike', 3000);1|1000
2|0
3|0Employee 1 (Alice) has odd ID and name doesn't start with 'M', so gets full salary (1000). Employee 2 (Bob) has even ID, so gets 0. Employee 3 (Mike) has odd ID but name starts with 'M', so gets 0.
CREATE TABLE employees (employee_id INT, name TEXT, salary INT); INSERT INTO employees VALUES (5, 'Sarah', 4500), (6, 'Mark', 3200), (7, 'David', 2800), (8, 'Maria', 5000);5|4500
6|0
7|2800
8|0Employee 5 (Sarah) has odd ID and name doesn't start with 'M', so gets 100% salary bonus (4500). Employee 6 (Mark) has even ID, so gets 0. Employee 7 (David) has odd ID and name doesn't start with 'M', so gets 100% salary bonus (2800). Employee 8 (Maria) has even ID, so gets 0.
CREATE TABLE employees (employee_id INT, name TEXT, salary INT); INSERT INTO employees VALUES (9, 'Michelle', 6000), (11, 'Tom', 3500), (13, 'Max', 4200);9|0
11|3500
13|0Employee 9 (Michelle) has odd ID but name starts with 'M', so gets 0 bonus. Employee 11 (Tom) has odd ID and name doesn't start with 'M', so gets 100% salary bonus (3500). Employee 13 (Max) has odd ID but name starts with 'M', so gets 0 bonus. This demonstrates that BOTH conditions must be met for the bonus.
Constraints
- •
Use CASE statement