100%(1)1 out of 1 people found this document helpful
This preview shows page 7 - 11 out of 20 pages.
Ex Central 5. Given that the bonus of staff is calculated by Bonus=SQRT(Salary* Award) Write a SQL statement to print a list of salary and bonus for each staff. 6. Write a SQL statement to show date of birth of staff in the following format Name Day Month Week Year Jeffrey Lee 23 February Thursday 1978 7. Write a SQL statement to show those staff born in the months between September and December. Display the dates 10 days before these dates of birth so that manager has enough time to prepare present for the staff. Arrange dates from nearest to furthest
8 Experiment 6 Working with Triggers using PL/SQL: Develop Programs using BEFORE and AFTER Triggers, Row and Statement Triggers and INSTEAD OF Triggers Q1:Write a trigger that checks the value of SAL before insert or update statement and ensures that SAL below 500 is not inserted.It acts BEFORE insertion or updation. create table emp_trig(ename varchar2(20),sal number(10)); create or replace trigger min_sal_check before insert or update on emp_trig for each row when (new.sal<500) begin raise_application_error(-2000,'Sal must be above 500'); end; Q2:Write a trigger that keeps backup of deleted records of emp_trig table. Deleted records of emp_trig are inserted in emp_backup table. create or replace trigger bkup_rec after delete on emp_trig for each row begin insert into emp_bkup values(:old.ename,:old.sal,sysdate); end; Q3:Write a trigger that checks for any duplicate value and disallows insertion. create or replace trigger unq_val before insert on emp_trig for each row declare vcnt number(2); begin select count(*) into vcnt from emp_trig where ename=:new.ename; if vcnt=1 then raise_application_error(-20001,'you have entered duplicate ename'); end if; end; Q4:Write a trigger that disallows any DML operations performed after 13.00Hrs.on emp table. create or replace trigger restrict_access before insert or update or delete on emp_trig begin if to_char(sysdate,'HH24')>12 then if inserting then raise_application_error(-20002,'insertion not allowed after 13000hrs'); elsif updating then raise_application_error(-20002,'updation not allowed after 13000hrs'); elsif deleting then raise_application_error(-20002,'deletion not allowed after 13000hrs'); end if; end if; end
9 Q5:Write an INSTEAD OF trigger that disallows insertion and updation of SAL if same SAL is present in any record. create view v1 as select * from emp_trig where sal<10000 create or replace trigger m_sal_check instead of insert or update on v1 for each row declare vcnt number(2); begin select count(*) into vcnt from v1 where sal=:new.sal; if vcnt=1 then raise_application_error(-20000,'duplicate sal not allowed'); end if; end;
10 Experiment 7 Working with PL/SQL Procedures :Programs Development using Creation of Procedures, Passing Parameters IN and OUT of PROCEDURES Q1:Create a procedure which displays employee name and salary for given employee number.