--  NAME
--    demobld.sql
--
-- DESCRIPTION
--   This script creates the SQL*Plus demonstration tables in the
--   current schema.  It should be STARTed by each user wishing to
--   access the tables.
--
--  USAGE
--       SQL> START demobld.sql
--
--

SET TERMOUT ON
PROMPT Building demonstration tables.  Please wait.
SET TERMOUT OFF

DROP TABLE DEPT CASCADE CONSTRAINTS;

CREATE TABLE DEPT
       (DEPTNO NUMBER(2),
        DNAME VARCHAR2(14) NOT NULL,
        LOC VARCHAR2(13),
        CONSTRAINT PK_DEPT_DEPTNO PRIMARY KEY (DEPTNO),
        CONSTRAINT UK_DEPT_DNAME UNIQUE (DNAME));

COMMENT ON TABLE DEPT is ‘Master table for Departments’;

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');

DROP TABLE EMP;

CREATE TABLE EMP
       (EMPNO NUMBER(4),
        ENAME VARCHAR2(10) NOT NULL,
        JOB VARCHAR2(9) NOT NULL,
        MGR NUMBER(4),
        HIREDATE DATE NOT NULL,
        SAL NUMBER(7, 2) NOT NULL,
        COMM NUMBER(7, 2),
        DEPTNO NUMBER(2),
        CONSTRAINT PK_EMP_EMPNO PRIMARY KEY (EMPNO),
        CONSTRAINT FK_EMP_DEPTNO FOREIGN KEY (DEPTNO) REFERENCES DEPT(DEPTNO),
        CONSTRAINT CHK_EMP_SALESMAN CHECK ((JOB='SALESMAN' AND COMM IS NOT NULL) OR (JOB<>'SALESMAN' AND COMM IS NULL)));

COMMENT ON COLUMN EMP.COMM is 'Commission given to SALESMAN';

INSERT INTO EMP VALUES(7369, 'SMITH',  'CLERK', 7902,TO_DATE('17-DEC-1980', 'DD-MON-YYYY'),  800, NULL, 20);
INSERT INTO EMP VALUES(7499, 'ALLEN',  'SALESMAN',  7698, TO_DATE('20-FEB-1981', 'DD-MON-YYYY'), 1600,  300, 30);
INSERT INTO EMP VALUES(7521, 'WARD',   'SALESMAN',  7698, TO_DATE('22-FEB-1981', 'DD-MON-YYYY'), 1250,  500, 30);
INSERT INTO EMP VALUES(7566, 'JONES',  'MANAGER',   7839, TO_DATE('2-APR-1981', 'DD-MON-YYYY'),  2975, NULL, 20);
INSERT INTO EMP VALUES(7654, 'MARTIN', 'SALESMAN',  7698,TO_DATE('28-SEP-1981', 'DD-MON-YYYY'), 1250, 1400, 30);
INSERT INTO EMP VALUES(7698, 'BLAKE',  'MANAGER',   7839,TO_DATE('1-MAY-1981', 'DD-MON-YYYY'),  2850, NULL, 30);
INSERT INTO EMP VALUES(7782, 'CLARK',  'MANAGER',   7839,TO_DATE('9-JUN-1981', 'DD-MON-YYYY'),  2450, NULL, 10);
INSERT INTO EMP VALUES(7788, 'SCOTT',  'ANALYST',  7566, TO_DATE('09-DEC-1982', 'DD-MON-YYYY'), 3000, NULL, 20);
INSERT INTO EMP VALUES(7839, 'KING',   'PRESIDENT', NULL,TO_DATE('17-NOV-1981', 'DD-MON-YYYY'), 5000, NULL, 10);
INSERT INTO EMP VALUES	(7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('8-SEP-1981', 'DD-MON-YYYY'),  1500, 0, 30);
INSERT INTO EMP VALUES(7876, 'ADAMS',  'CLERK', 7788, TO_DATE('12-JAN-1983', 'DD-MON-YYYY'), 1100, NULL, 20);
INSERT INTO EMP VALUES(7900, 'JAMES',  'CLERK', 7698, TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),   950, NULL, 30);
INSERT INTO EMP VALUES(7902, 'FORD',   'ANALYST', 7566,TO_DATE('3-DEC-1981', 'DD-MON-YYYY'),  3000, NULL, 20);
INSERT INTO EMP VALUES(7934, 'MILLER', 'CLERK', 7782,TO_DATE('23-JAN-1982', 'DD-MON-YYYY'), 1300, NULL, 10);

DROP TABLE SALGRADE;

CREATE TABLE SALGRADE
        (GRADE NUMBER,
         LOSAL NUMBER,
         HISAL NUMBER,
         CONSTRAINT PK_SALGRADE_GRADE PRIMARY KEY (GRADE));

INSERT INTO SALGRADE VALUES (1,  700, 1200);
INSERT INTO SALGRADE VALUES (2, 1201, 1400);
INSERT INTO SALGRADE VALUES (3, 1401, 2000);
INSERT INTO SALGRADE VALUES (4, 2001, 3000);
INSERT INTO SALGRADE VALUES (5, 3001, 9999);

COMMIT;

DROP TABLE BONUS;

CREATE TABLE BONUS
        (ENAME VARCHAR2(10),
         JOB   VARCHAR2(9),
         SAL   NUMBER,
         COMM  NUMBER,
	 CONSTRAINT PK_BONUS_ENAME PRIMARY KEY (ENAME));

COMMIT;

SET TERMOUT ON
PROMPT Demonstration table build is complete.