PL/SQL Query Practice #2

Challenge

Consider the following relational schema.

• An employee can work in more than one department;

• the pct_time field of the works relation shows the percentage of time that a given employee works in a given department.

employee(employee_id: integer, ename: string, age: integer, salary: real)

works(employee_id: integer, department_id: integer, pct_time: integer)

department(department_id: integer, budget: real, managerid: integer)

Write SQL triggers to ensure each of the following requirements, considered independently.

1. Employees must make a minimum salary of 1000.

2. Every manager must also be an employee.

3. The total percentage of all appointments for an employee must be under 100%.

4. A manager must always have a higher salary than any employee that he or she manages.

Create Tables

create table employee(employee_id int primary key, ename varchar(20), salary number);

create table department(department_id int primary key, budget number, managerid int, foreign key(managerid) references employee(employee_id));

create table works(employee_id int, department_id int, pct_time int, foreign key(employee_id) references employee(employee_id),foreign key(department_id) references department(department_id));

Create Triggers

Employees must make a minimum salary of 1000.

create or replace trigger minSal before insert on employee

for each row

begin

if :new.salary < 1000 then

raise_application_error(-20001,'Salary should be greater than 1000');

end if;

end;

/

Every manager must also be an employee.

create or replace trigger manEmp before insert on department

for each row

declare

n number;

begin

n := 0;

select count(*) into n from employee where employee_id = :new.managerid;

if n = 0 then

raise_application_error(-20001,'Manager Does not Exists');

end if;

end;

/

The total percentage of all appointments for an employee must be under 100%.

create or replace trigger appoint before insert on works

for each row

declare

cursor c is select * from works;

timex number;

begin

timex := 0;

for t in c loop

if t.employee_id = :new.employee_id then

timex := timex + t.pct_time;

end if;

end loop;

timex := timex + :new.pct_time;

if timex > 18 then

raise_application_error(-20001,'Cannot have more appointments');

end if;

end;

/

A manager must always have a higher salary than any employee that he or she manages.

create or replace trigger mange before insert on works

for each row

declare

mansal number;

empsal number;

begin

select salary into empsal from employee where employee_id = :new.employee_id;

select salary into mansal from employee where employee_id = (select managerid from department where department_id = :new.department_id);

if empsal > mansal then

raise_application_error(-20001,'Manager salary cant be less than employee salary');

end if;

end;

/

Testing Triggers Examples

Insert Data to Employee

insert into employee values(1, 'Deepanshu', 1900);

insert into employee values(2, 'Sahil', 134400);

insert into employee values(3, 'Abhilash', 90400);

Output

SQL> select * from employee;

EID ENAME SALARY

---------- -------------------- ----------

1 Deepanshu 1900

2 Sahil 134400

3 Abhilash 90400

Insert Data to Employee with Salary less than 1000

insert into employee values(5, 'Hrithik', 800);

Output

SQL> insert into employee values(5, 'Hrithik', 800);

insert into employee values(5, 'Hrithik', 800)

*

ERROR at line 1:

ORA-20001: Salary should be greater than 1000

ORA-06512: at "SYSTEM.MINSAL", line 3

ORA-04088: error during execution of trigger 'SYSTEM.MINSAL'

Insert Data to Department

insert into department values(1,53466,2);

insert into department values(2,100466,3);

insert into department values(3,23040,1);

Output

DID BUDGET MANAGERID

---------- ---------- ----------

1 53466 2

2 100466 3

3 23040 1

Insert Data to Department with invalid manager id

insert into department values(4,10040,4)

Output

SQL> insert into department values(4,10040,4);

insert into department values(4,10040,4)

*

ERROR at line 1:

ORA-20001: Manager Does not Exists

ORA-06512: at "SYSTEM.MANEMP", line 7

ORA-04088: error during execution of trigger 'SYSTEM.MANEMP'

Insert Data to Works

insert into works values(1,1,12);

insert into works values(3,2,9);

Output

EID DID PCT_TIME

---------- ---------- ----------

1 1 12

3 2 9

Insert Data to Works with more appointments

insert into works values(1,1,7);

Output

SQL> insert into works values(1,1,7);

insert into works values(1,1,7)

*

ERROR at line 1:

ORA-20001: Cannot have more appointments

ORA-06512: at "SYSTEM.APPOINT", line 13

ORA-04088: error during execution of trigger 'SYSTEM.APPOINT'

Insert Data to Works where manager salary is less than employee

insert into works values(2,3,6);

Output

SQL> insert into works values(2,3,6);

insert into works values(2,3,6)

*

ERROR at line 1:

ORA-20001: Manager salary cant be less than employee salary

ORA-06512: at "SYSTEM.MANGE", line 8

ORA-04088: error during execution of trigger 'SYSTEM.MANGE'