Mumbai Educational Trust’s
Institute of Information Technology Database Technologies using Oracle9i Assignment # 7 (PL/SQL) Date of the Assignment : 13th Oct 2006 Date of Submission : 14th Oct 2006 1800hrs Note: For taking values from user’s you can use &variable name. (Interactive inputs by sql* plus) 1)
Write a cursor and use it to raise the employee salaries as follows: ✦ All employees of department 20 get 5% raise ✦ All employees of department 30 get 10% raise ✦ Remaining employees get a raise of 7.5%
2)
Write a cursor to insert increment details for employees into INCR table(columns empno, IncrAmt) and update his incremented salary in EMP table as per following criteria: ✦ All SALESMAN get 15 % increment. ✦ All ANALYST get 10 % increment. ✦ Remaining employees get an increment of 500.
3)
Using PL/SQL block and cursor display the employee data as follow: 10 ACCOUNTS NEW YORK KING
PRESIDENT
17-NOV-81
5000
CLARK
MANAGER
9-JUN-81
2450
MILLER
CLERK
23-JAN-82
1300
20 RESEARCH DALLAS JONES
MANAGER
02-APR-81
2975
FORD
ANALYST
03-DEC-81
3000
SMITH
CLERK
17-DEC-80
800
SCOTT
ANALYST
09-DEC-82
3000
ADAMS
CLERK
12-JAN-83
1100
4)
Write a PL/SQL code to add an employee to the EMP table. Handle the error encountered if the employee number of the new one already exists.
5)
Write a PL/SQL code to update the salary of employees with employee numbers as 7639 and 7989 by 20% and 15% respectively. Handle the error encountered if the employee does not exists