SQL interview question: UPDATE With CASE
Tiered Annual Bonus
Annual bonuses are tiered by role: VPs get $10,000, anyone with 'Manager' in their title gets $5,000, and everyone else gets $2,000.
Write a single UPDATE statement adding the right bonus to every employee's salary using CASE.
Note: Check VP titles first - some managers report to VPs, but a 'VP Sales' is not a 'Manager'. Your change is verified automatically and rolled back afterwards.
This challenge changes data. Your statement runs on a private copy of the database, then we check the resulting table.
Tables
employees 37 rows
- id integer PK
- first_name text
- last_name text
- email text
- department_id integer → departments
- salary integer
- hire_date text
- manager_id integer → employees
- title text
| id | first_name | last_name | department_id | salary | hire_date | manager_id | title | |
|---|---|---|---|---|---|---|---|---|
| 1 | Alice | Chen | [email protected] | 1 | 250000 | 2015-01-15 | NULL | CEO |
| 2 | Robert | Martinez | [email protected] | 2 | 180000 | 2016-03-20 | NULL | CEO |
| 3 | Sarah | Johnson | [email protected] | 1 | 150000 | 2017-06-10 | 1 | VP Engineering |