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)