PostgreSQL Update Table: Increase the salary of employees under the department 40, 90 and 110 according to the company rules in the HR database
8. Write a SQL statement to increase the salary of employees under the department 40, 90 and 110 according to the company rules that, the salary will be increased by 25% of the department 40, 15% for department 90 and 10% of the department 110 and the rest of the department will remain same.
Sample table: employees
Sample Solution:
Code:
UPDATE employees SET salary= CASE department_id
WHEN 40 THEN salary+(salary*.25)
WHEN 90 THEN salary+(salary*.15)
WHEN 110 THEN salary+(salary*.10)
ELSE salary
END
WHERE department_id IN (40,50,50,60,70,80,90,110);
Output:
See the result before update. Only the effected rows have been displayed.
postgres=# SELECT * FROM emp WHERE department_id IN (40,90,110); employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id -------------+------------+-----------+----------+--------------+------------+------------+----------+----------------+------------+--------------- 100 | Steven | King | SKING | 515.123.4567 | 1987-06-17 | AD_PRES | 24000.00 | 0.00 | 0 | 90 101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 1987-06-18 | AD_VP | 17000.00 | 0.00 | 100 | 90 102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 1987-06-19 | AD_VP | 17000.00 | 0.00 | 100 | 90 203 | Susan | Mavris | SMAVRIS | 515.123.7777 | 1987-09-28 | HR_REP | 6500.00 | 0.00 | 101 | 40 205 | Shelley | Higgins | SHIGGINS | 515.123.8080 | 1987-09-30 | AC_MGR | 12000.00 | 0.00 | 101 | 110 206 | William | Gietz | WGIETZ | 515.123.8181 | 1987-10-01 | AC_ACCOUNT | 8300.00 | 0.00 | 205 | 110 (6 rows)
See the result. Only the effected rows have been displayed.
postgres=# SELECT * FROM emp WHERE department_id IN (40,90,110);
employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id
-------------+------------+-----------+----------+--------------+------------+------------+----------+----------------+------------+---------------
100 | Steven | King | SKING | 515.123.4567 | 1987-06-17 | AD_PRES | 27600.00 | 0.00 | 0 | 90
101 | Neena | Kochhar | NKOCHHAR | 515.123.4568 | 1987-06-18 | AD_VP | 19550.00 | 0.00 | 100 | 90
102 | Lex | De Haan | LDEHAAN | 515.123.4569 | 1987-06-19 | AD_VP | 19550.00 | 0.00 | 100 | 90
203 | Susan | Mavris | SMAVRIS | 515.123.7777 | 1987-09-28 | HR_REP | 8125.00 | 0.00 | 101 | 40
205 | Shelley | Higgins | SHIGGINS | 515.123.8080 | 1987-09-30 | AC_MGR | 13200.00 | 0.00 | 101 | 110
206 | William | Gietz | WGIETZ | 515.123.8181 | 1987-10-01 | AC_ACCOUNT | 9130.00 | 0.00 | 205 | 110
(6 rows)
Have another way to solve this solution? Contribute your code (and comments) through Disqus.
Previous: Write a SQL statement to change the job ID of the employee which ID is 118 to SH_CLERK if the employee belongs to a department which ID is 30 and the existing job ID does not start with SH.
Next: PostgreSQL Insert Records - Exercises, Practice, Solution
What is the difficulty level of this exercise?
- Weekly Trends
- Python Interview Questions and Answers: Comprehensive Guide
- Scala Exercises, Practice, Solution
- Kotlin Exercises practice with solution
- MongoDB Exercises, Practice, Solution
- SQL Exercises, Practice, Solution - JOINS
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join