REM drop tables if exsist.

drop table Workin;
drop table Employee;
drop table Department;

REM 

create table Employee(
       eid       int not null constraint manager_pk primary key,
       ename     varchar2(20),
       salary    decimal(10,2),
       check (salary>100)
);
create table Department(
       did       int not null constraint dept_pk primary key,
       dname     varchar2(20),
       daddress   varchar2(20)
);
create table Workin(
       eid       int,
       did       int,
       stime      date,
       primary key (eid,did),
       foreign key (eid) references Employee(eid)
               ON DELETE CASCADE,
       foreign key (did) references Department(did)             
);
              
insert into Employee (eid,ename,salary)
values(22,'Dustin',10000.0);
insert into Employee (eid,ename,salary)
values(23,'Bob',15000.0);


REM can I insert 24, Charlie, 50?

insert into Employee (eid,ename,salary)
values(24,'Charlie',50.0);


insert into Department (did,dname,daddress)
values(101,'cs','keenedy');
insert into Department (did,dname,daddress)
values(102,'ee','smith');

insert into Workin(eid,did,stime)
values(22,101,'10-OCT-98');
insert into Workin(eid,did,stime)
values(23,102,'15-OCT-98');

REM How about deleting (22, 101, '10-OCT-98') from Workin table?

delete from Workin where eid = 22 and did = 101;


REM let us insert back (22,101,...)

insert into Workin(eid,did,stime)
values(22,101,'10-OCT-98');

REM How about deleting (22,'Dustin',10000.0) from Employee? Is there any effect on Workin Table?

delete from Employee where eid = 22;

REM How about deleting (102, 'ee',...) from Department table? Is there any effect on Workin Table?

delete from Department where did = 102;



REM PL/SQL is Oracle's procedure extension to SQL. It supplements SQL with several high-level
REM programming language features such as block structure, variables, constants and types, the assignment statement,
REM conditional statement, loops, customized error handling, and structured data. 


REM CREATE SEQUENCE

create sequence EmpSeq start with 1000;

insert into Employee (eid,ename,salary)
values(EmpSeq.nextval,'Jones',10000.0);

insert into Employee (eid,ename,salary)
values(EmpSeq.currval+1,'Jones',20000.0);

drop sequence EmpSeq;



REM stored procedures

CREATE PROCEDURE remove_emp (employee_id NUMBER) AS
tot_emps NUMBER;
BEGIN
   DELETE FROM employee
   WHERE employee.eid = remove_emp.employee_id;
tot_emps := tot_emps - 1;
END;
/

exec remove_emp(22);

REM functions

create or replace function increase_salary(empid in employee.eid%type)
return employee.salary%type as ssalary  
employee.salary%type;
begin
select employee.salary+ 100
into  ssalary
from employee
where employee.eid =empid;
return(ssalary);
end;
/

select eid, ename, increase_salary(eid) from employee;

select eid, ename, salary from employee;



REM one more function example

create or replace function decrease_salary(empid in employee.eid%type)
return employee.salary%type as ssalary  
employee.salary%type;
begin
select employee.salary
into  ssalary
from employee
where employee.eid =empid;
if (ssalary > 12000) then
  ssalary := ssalary - 5000;
else
  ssalary := ssalary - 10000;
end if;
return(ssalary);
end;
/

select eid, ename, decrease_salary(eid) from employee;




REM if we want to enforce total participation constraint such as each employee must work at 
REM at least one department, we can use CREATE ASSERTION as follows. However, ORACLE does not
REM suport CREATE ASSERTION. We may use CREATE TRIGGER instead.

REM create assertion emptotal check
REM (not exists (select * from Emplyee E where not exists ( select * from Workin where E.eid = W.eid)));

REM please run the following two commands to see difference

CREATE TRIGGER emptotal AFTER INSERT ON Employee
FOR EACH ROW
WHEN ((SELECT count(*) FROM Workin W where W.eid = new.eid) = 0)
BEGIN
INSERT INTO Workin(eid,did,stime) VALUES(:new.eid,101,'10-OCT-98');
END ;
.
run;

CREATE TRIGGER emptotal AFTER INSERT ON Employee
FOR EACH ROW
WHEN (new.eid > 0)
BEGIN
INSERT INTO Workin(eid,did,stime) VALUES(:new.eid,101,'10-OCT-98');
END ;
.
run;

REM now run the following insertion to see how trigger works, you may 
REM check workin table.

insert into Employee (eid,ename,salary)
values(330,'Bob',10000.0);



REM transaction

create or replace procedure insert_employee(empid in employee.eid%type) AS
begin
insert into Employee (eid,ename,salary)
values(empid,'Bob',10000.0);
insert into Workin(eid,did,stime)
values(empid,101,'10-OCT-98');
commit;
end;
/
exec insert_employee(99);

create or replace procedure change_employee(empid in employee.eid%type) AS
begin
update employee set eid = eid + 100 where eid = empid ;
update workin set eid = eid + 100 where eid = empid;
commit;
end;
/
exec change_employee(99);

REM user_catalog table

select * from cat where table_name not like 'B%';
select * from user_objects;
select * from tabs;
select * from user_tab_columns;
select * from user_views;