sql assignment4.txt

Assignment 4



Q. Write a SQL statement to change the salary of an employee to 8000 whose ID is 105, if the existing salary is less than 5000

	update employees set salary = 8000 where employee_id = 105 and salary < 5000 ;



Q. 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.

	update employees set job_id = 'SH_CLERK' where employee_id = 118 and department_id = 30 and not job_id like 'SH%';



Q. 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.

 	update employees set salary = case department_id when 40 then salary*1.25 when 90 then salary*1.15 when 110 then salary*1.1 else salary end;



Q. Write a SQL statement to change the email column of the employees table with 'not available' for those employees who belongs to the 'Accounting' department.

	update employees set email = 'not available ' where job_id = 'FI_ACCOUNT';



Q. 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%.

 	update employees set email ='not available' where department_id=80 and commission_pct=0.2 ;



Q. Write a SQL statement to change the email and commission_pct column of the employees table with 'not available' and 0.10 for all employees.

	cdac1=# update employees set email ='not available' ;
	UPDATE 107
	cdac1=# update employees set commission_pct=0.10 ;
	UPDATE 107