REM set commands set the SQL*Plus environment
REM echo on anables displaying SQL commands on screen 
REM when they are executed
REM spool command opens a text file to duplicate output 
REM here. It is usefull for long SQL scripts for back tracking.

set echo on
spool dept_emp.lst

REM In case that tables already exist 
REM we try drop them first.

REM CASCADE CONSTRAINTS means drooping
REM table and FOREIGN KEY(s) reference pointing
REM to this table. If there any foreign key exist
REM and you try drop table without CASCADE CONSTRAINTS
REM clause, you receive an ERROR and table is not drooped.

DROP TABLE EMP CASCADE CONSTRAINTS;

DROP TABLE DEPT CASCADE CONSTRAINTS;



REM Now we can create DEPT table. Name of columns little bit 
REM differ from name which we assigned on last seminar...


CREATE TABLE DEPT
       (DEPTNO NUMBER(2),
        DNAME VARCHAR2(14),
        LOC VARCHAR2(13),
     CONSTRAINT PK_DEPT PRIMARY KEY (DEPTNO));

REM Then we can create EMP table. The order is important because
REM EMP table creates a constraint FK_DEPT_EMP which points to table
REM DEPT. This table must exist in time of FK_DEPT_EMP creation.

CREATE TABLE EMP
       (EMPNO NUMBER(4) NOT NULL,
        ENAME VARCHAR2(10),
        JOB VARCHAR2(9),
        MGR NUMBER(4),
        HIREDATE DATE,
        SAL NUMBER(7,2),
        COMM NUMBER(7,2),
        DEPTNO NUMBER(2),
     CONSTRAINT PK_EMP PRIMARY KEY (EMPNO),
     CONSTRAINT FK_MGR_EMP FOREIGN KEY (MGR) REFERENCES EMP(EMPNO),
     CONSTRAINT FK_DEPT_EMP FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO));

REM We can enter a start set of data to both tables. 
REM In SQL you can insert only one row (record) in one time
REM so we have to repeat INSERT INTO syntax for every row. 

REM The value of string columns (CHAR, VARCHAR2) and data columns
REM are enclosed in apostrophes.


INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

REM DDL commands like CREATE TABLE are auto-commit commands, but
REM DML commands (like INSERT) are not. So we have to finish transaction
REM by COMMIT to make changes in DB permanent.

COMMIT;

REM There is column HIREDATE with data type date in table EMP. The date 
REM format depends on actual NLS (National Language Support) setting. 
REM But we can use SQL function to_date to be independent on actual NLS 
REM date format setting. 

INSERT INTO EMP VALUES
(7839,'KING','PRESIDENT',NULL,to_date('17-11-81','DD-MM-RR'),5000,NULL,10);

INSERT INTO EMP VALUES
(7566,'JONES','MANAGER',7839,to_date('02-04-81','DD-MM-RR'),2975,NULL,20);

INSERT INTO EMP VALUES
(7902,'FORD','ANALYST',7566,to_date('03-12-81','DD-MM-RR'),3000,NULL,20);

INSERT INTO EMP VALUES
(7369,'SMITH','CLERK',7902,to_date('17-12-80','DD-MM-RR'),800,NULL,20); 

INSERT INTO EMP VALUES
(7698,'BLAKE','MANAGER',7839,to_date('01-05-81','DD-MM-RR'),2850,NULL,30); 

INSERT INTO EMP VALUES
(7499,'ALLEN','SALESMAN',7698,to_date('20-02-81','DD-MM-RR'),1600,300,30); 

INSERT INTO EMP VALUES
(7521,'WARD','SALESMAN',7698,to_date('22-02-81','DD-MM-RR'),1250,500,30); 

INSERT INTO EMP VALUES
(7654,'MARTIN','SALESMAN',7698,to_date('28-09-81','DD-MM-RR'),1250,1400,30);

INSERT INTO EMP VALUES
(7788,'SCOTT','ANALYST',7566,to_date('09-12-82','DD-MM-RR'),3000,NULL,20); 

INSERT INTO EMP VALUES
(7844,'TURNER','SALESMAN',7698,to_date('08-09-81','DD-MM-RR'),1500,0,30); 

INSERT INTO EMP VALUES 
(7876,'ADAMS','CLERK',7788,to_date('12-01-83','DD-MM-RR'),1100,NULL,20); 

INSERT INTO EMP VALUES 
(7900,'JAMES','CLERK',7698,to_date('03-12-81','DD-MM-RR'),950,NULL,30); 

INSERT INTO EMP VALUES 
(7782,'CLARK','MANAGER',7839,to_date('09-06-81','DD-MM-RR'),2450,NULL,10);

INSERT INTO EMP VALUES 
(7934,'MILLER','CLERK',7782,to_date('23-01-82','DD-MM-RR'),1300,NULL,10); 

COMMIT;


REM Becouse we used command spool on the start of script we have to 
REM close open file at the end.

spool off