--Creating the employee table.
CREATE TABLE employee
(emp_id INTEGER,
emp_name VARCHAR2(32),
supervised_by INTEGER,
pay_rate NUMBER(9,2),
pay_type CHAR,
emp_dept_id INTEGER);
ALTER TABLE employee
ADD CONSTRAINT pk_emp primary key (emp_id);
insert into employee values (1,'Jack Chen',3,100.50, H, 3);
insert into employee values (2,'Jason Lee',1,300.50, H, 3);
insert into employee values (3,'Ala Wang',5,200.50, H, 3);
-- Creating the department table.
CREATE TABLE department
(dept_id INTEGER,
dept_name VARCHAR2(32));
ALTER TABLE department
ADD CONSTRAINT PRIMARY KEY (dept_id);
insert into department values (1,'Computer');
insert into department values (2,'Publish');
insert into department values (3,'Management');
-- Creating the emp_dept table.
CREATE TABLE emp_dept
(emp_id INTEGER,
dept_id INTEGER,
CONSTRAINT unq_1 unique (emp_id, dept_id));
insert into emp_dept values(1,3);
insert into emp_dept values(2,3);
insert into emp_dept values(3,3);
select * from employee;
select * from department;
select * from emp_dept;
-- Inserting records with PL/SQL code.
DECLARE
i_dept_id INTEGER,
i_dept_name,
BEGIN
INSERT into department values (&i_dept_id,'&dept_name');
END;
COMMIT;
DECLARE
i_id INTEGER;
e_id INTEGER;
i_name VARCHAR2(32);
i_super INTEGER;
i_rate NUMBER(9,2);
i_type CHAR;
i_emp_dept INTEGER;
e_emp_dept INTEGER;
BEGIN
e_id:=12;
e_emp_dept:=12;
INSERT into employee values (e_id, '&i_name',&i_super,&i_rate,'&i_type',e_emp_dept);
END;
/
select * from employee;
select * from department;
select * from emp_dept;
drop table employee;
drop table department;
drop table emp_dept;