-- 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.