PostgreSQL Update Table - Exercises, Practice, Solution
PostgreSQL Update Table [ 8 exercises with solution]
1. Write a sql statement to change the email column of employees table with 'not available' for all employees.
Sample table : employees
UPDATE employees SET email='not available';
See the result. Only two rows have been displayed.
postgres=# SELECT * FROM employees LIMIT 2;
employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id
-------------+------------+-----------+---------------+--------------+------------+---------+----------+----------------+------------+---------------
100 | Steven | King | not available | 515.123.4567 | 1987-06-17 | AD_PRES | 24000.00 | 0.00 | 0 | 90
101 | Neena | Kochhar | not available | 515.123.4568 | 1987-06-18 | AD_VP | 17000.00 | 0.00 | 100 | 90
2. Write a sql statement to change the email and commission_pct column of employees table with 'not available' and 0.10 for all employees.
Sample table : employees
UPDATE employees SET email='not available', commission_pct=0.10;
See the result. Only two rows have been displayed.
postgres=# SELECT * FROM employees LIMIT 2;
employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id
-------------+------------+-----------+---------------+--------------+------------+----------+---------+----------------+------------+---------------
128 | Steven | Markle | not available | 650.124.1434 | 1987-07-15 | ST_CLERK | 2200.00 | 0.10 | 120 | 50
129 | Laura | Bissot | not available | 650.124.5234 | 1987-07-16 | ST_CLERK | 3300.00 | 0.10 | 121 | 50
3. Write a sql statement to change the email and commission_pct column of employees table with 'not available' and 0.10 for those employees whose department_id is 110.
Sample table : employees
UPDATE employees SET email='not available', commission_pct=0.10 WHERE department_id=110;
See the result. Only the effected rows have been displayed.
postgres=# SELECT * FROM employees WHERE department_id=110;
employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id
-------------+------------+-----------+---------------+--------------+------------+------------+----------+----------------+------------+---------------
205 | Shelley | Higgins | not available | 515.123.8080 | 1987-09-30 | AC_MGR | 12000.00 | 0.10 | 101 | 110
206 | William | Gietz | not available | 515.123.8181 | 1987-10-01 | AC_ACCOUNT | 8300.00 | 0.10 | 205 | 110
(2 rows)
4. Write a sql statement to change the email column of employees table with 'not available' for those employees whose department_id is 80 and gets a commission is less than .20%
Sample table : employees
UPDATE employees SET email='not available' WHERE department_id=80 AND commission_pct<.20;
See the result. Only the effected rows have been displayed.
postgres=# SELECT * FROM employees WHERE email='not available' AND department_id=80 AND commission_pct<.20;
employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id
-------------+------------+-----------+---------------+--------------------+------------+--------+---------+----------------+------------+---------------
155 | Oliver | Tuvault | not available | 011.44.1344.486508 | 1987-08-11 | SA_REP | 7000.00 | 0.15 | 145 | 80
163 | Danielle | Greene | not available | 011.44.1346.229268 | 1987-08-19 | SA_REP | 9500.00 | 0.15 | 147 | 80
164 | Mattea | Marvins | not available | 011.44.1346.329268 | 1987-08-20 | SA_REP | 7200.00 | 0.10 | 147 | 80
165 | David | Lee | not available | 011.44.1346.529268 | 1987-08-21 | SA_REP | 6800.00 | 0.10 | 147 | 80
166 | Sundar | Ande | not available | 011.44.1346.629268 | 1987-08-22 | SA_REP | 6400.00 | 0.10 | 147 | 80
167 | Amit | Banda | not available | 011.44.1346.729268 | 1987-08-23 | SA_REP | 6200.00 | 0.10 | 147 | 80
171 | William | Smith | not available | 011.44.1343.629268 | 1987-08-27 | SA_REP | 7400.00 | 0.15 | 148 | 80
172 | Elizabeth | Bates | not available | 011.44.1343.529268 | 1987-08-28 | SA_REP | 7300.00 | 0.15 | 148 | 80
173 | Sundita | Kumar | not available | 011.44.1343.329268 | 1987-08-29 | SA_REP | 6100.00 | 0.10 | 148 | 80
179 | Charles | Johnson | not available | 011.44.1644.429262 | 1987-09-04 | SA_REP | 6200.00 | 0.10 | 149 | 80
5. Write a sql statement to change the email column of employees table with 'not available' for those employees who belongs to the 'Accouning' department.
Sample table : employees
Sample table : departments
UPDATE employees SET email='not available' WHERE department_id=( SELECT department_id FROM departments WHERE department_name='Accounting');
See the result. Only the effected rows have been displayed.
SELECT * FROM employees
WHERE email='not available' AND department_id=
(SELECT department_id
FROM dep
WHERE department_name='Accounting');
employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id
-------------+------------+-----------+---------------+--------------+------------+------------+----------+----------------+------------+---------------
205 | Shelley | Higgins | not available | 515.123.8080 | 1987-09-30 | AC_MGR | 12000.00 | 0.00 | 101 | 110
206 | William | Gietz | not available | 515.123.8181 | 1987-10-01 | AC_ACCOUNT | 8300.00 | 0.00 | 205 | 110
(2 rows)
6. Write a sql statement to change salary of employee to 8000 whose ID is 105, if the existing salary is less than 5000.
Sample table : employees
UPDATE employees SET SALARY = 8000 WHERE employee_id = 105 AND salary < 5000;
See the result. Only the effected rows have been displayed.
postgres=# SELECT * FROM employees WHERE SALARY = 8000 AND employee_id = 105;
employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id
-------------+------------+-----------+---------+--------------+------------+---------+---------+----------------+------------+---------------
105 | David | Austin | DAUSTIN | 590.423.4569 | 1987-06-22 | IT_PROG | 8000.00 | 0.00 | 103 | 60
(1 row)
7. Write a sql statement to change job ID of employee which ID is 118 to SH_CLERK if the employee belongs to department which ID is 30 and the existing job ID does not start with SH.
Sample table : employees
UPDATE employees SET JOB_ID= 'SH_CLERK' WHERE employee_id=118 AND department_id=30 AND NOT JOB_ID LIKE 'SH%';
See the result. Only the effected rows have been displayed.
postgres=# SELECT * FROM employees WHERE employee_id=118 AND department_id=30 AND JOB_ID LIKE 'SH%';
employee_id | first_name | last_name | email | phone_number | hire_date | job_id | salary | commission_pct | manager_id | department_id
-------------+------------+-----------+---------+--------------+------------+----------+---------+----------------+------------+---------------
118 | Guy | Himuro | GHIMURO | 515.127.4565 | 1987-07-05 | SH_CLERK | 2600.00 | 0.00 | 114 | 30
(1 row)
8. Write a sql statement to increase the salary of employees under the department 40, 90 and 110 according to the company rules that, salary will be increased by 25% for the department 40, 15% for department 90 and 10% for the department 110 and the rest of the departments will remain same.
Sample table : employees
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);
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)