Oracle Setup Code
Main Page >> Oracle and SQL >> Workbook >> Oracle Setup Code
Demo tables setup code
host echo "Oracle Version 12c"
host echo "Building Demonstration tables. Please wait..."
rem
rem Emp TABLE
rem =========
rem
rem 1. Primary key (Enforces NOT NULL too)
rem 2. Ensure all data entered in upper case.
rem 3. Foreign key must be in related table or be NULL.
rem 4. Salary must be greater than 0
rem 5. Commission must be greater than 0 or null.
rem
host echo "Droping previous version of demonstration tables"
DROP TABLE Emp;
DROP TABLE Dept;
DROP TABLE Bonus;
DROP TABLE Salgrade;
DROP SEQUENCE ProjSeq;
host echo "Previous Demonstration tables removed"
host echo "Building DEPT table"
CREATE TABLE Dept
(deptno NUMBER(2)
CONSTRAINT pk_dept PRIMARY KEY,
dname VARCHAR2(14) NOT NULL
CONSTRAINT uc_dname CHECK (dname=UPPER(dname)),
loc VARCHAR2(13)
CONSTRAINT uc_loc CHECK (loc=UPPER(loc))
);
rem Now insert the data
rem
host echo "Inserting data into DEPT table"
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');
host echo "Building Emp table"
CREATE TABLE Emp
(empno NUMBER(4)
CONSTRAINT pk_emp PRIMARY KEY,
ename VARCHAR2(10) NOT NULL
CONSTRAINT uc_ename CHECK (ename = UPPER(ename)),
job VARCHAR2(9) NOT NULL
CONSTRAINT uc_job CHECK (job = UPPER(job)),
mgr NUMBER(4)
CONSTRAINT fk_mgr references Emp(empno),
hiredate DATE,
sal NUMBER(7,2) NOT NULL
CONSTRAINT BD_SAL CHECK (SAL > 0),
comm NUMBER(7,2)
CONSTRAINT BD_COMM CHECK (COMM >= 0),
deptno NUMBER(2)
CONSTRAINT FK_DEPTNO references DEPT(DEPTNO)
);
host echo "Inserting data into Emp table"
rem Management Structure is as follows:-
rem
rem LEVEL EmpLOYEE JOB
rem =========================================
rem
rem 1 KING PRESIDENT
rem 2 JONES MANAGER
rem 3 SCOTT ANALYST
rem 4 ADAMS CLERK
rem 3 FORD ANALYST
rem 4 SMITH CLERK
rem 2 BLAKE MANAGER
rem 3 ALLEN SALESMAN
rem 3 WARD SALESMAN
rem 3 MARTIN SALESMAN
rem 3 TURNER SALESMAN
rem 3 JAMES SALESMAN
rem 2 CLARK MANAGER
rem 3 MILLER
INSERT INTO Emp VALUES
(7839,'KING','PRESIDENT',NULL,'17-NOV-1980',5000,NULL,10);
INSERT INTO Emp VALUES
(7566,'JONES','MANAGER',7839,'2-APR-1991',2975,NULL,20);
INSERT INTO Emp VALUES
(7788,'SCOTT','ANALYST',7566,SYSDATE-85,3000,NULL,20);
INSERT INTO Emp VALUES
(7876,'ADAMS','CLERK',7788,SYSDATE-51,1100,NULL,20);
INSERT INTO Emp VALUES
(7902,'FORD','ANALYST',7566,'3-DEC-1991',3000,NULL,20);
INSERT INTO Emp VALUES
(7369,'SMITH','CLERK',7902,'17-DEC-1990',800,NULL,20);
INSERT INTO Emp VALUES
(7698,'BLAKE','MANAGER',7839,'01-MAY-1981',2850,NULL,30);
INSERT INTO Emp VALUES
(7499,'ALLEN','SALESMAN',7698,'20-FEB-1995',1600,300,30);
INSERT INTO Emp VALUES
(7521,'WARD','SALESMAN',7698,'22-FEB-1994',1250,500,30);
INSERT INTO Emp VALUES
(7654,'MARTIN','SALESMAN',7698,'28-SEP-1993',1250,1400,30);
INSERT INTO Emp VALUES
(7844,'TURNER','SALESMAN',7698,'8-SEP-1981',1500,0,30);
INSERT INTO Emp VALUES
(7900,'JAMES','CLERK',7698,'3-DEC-1981',950,NULL,30);
INSERT INTO Emp VALUES
(7782,'CLARK','MANAGER',7839,'09-JUN-1989',2450,NULL,10);
INSERT INTO Emp VALUES
(7934,'MILLER','CLERK',7782,'23-JAN-1985',1300,NULL,10);
INSERT INTO Emp VALUES
(8000,'REEVE','MANAGER',7839,'14-MAR-81',4000,0,90);
host echo "Building Salgrade table"
CREATE TABLE Salgrade
(grade NUMBER
CONSTRAINT PK_GRADE PRIMARY KEY,
losal NUMBER NOT NULL
CONSTRAINT BD_LOSAL CHECK (losal > 0),
hisal NUMBER NOT NULL,
CONSTRAINT BD_HISAL CHECK (hisal >= losal)
);
host echo "Inserting data into Salgrade table"
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);
CREATE TABLE Customer
(cno NUMBER(4), orderdate DATE, name VARCHAR2(15),
city VARCHAR2(15), country VARCHAR2(15),
pno NUMBER(4), description VARCHAR2(20),
qty NUMBER(4) CHECK (qty >= 1));
INSERT INTO Customer VALUES
(0001,'01-JAN-1997','J BOND','STOCKPORT','ENGLAND',1,'BONNET',20);
INSERT INTO Customer VALUES
(0001,'11-JUN-1997','J BOND','STOCKPORT','ENGLAND',2,'WIDGET',10);
INSERT INTO Customer VALUES
(0001,'21-APR-1998','J BOND','STOCKPORT','ENGLAND',1,'BONNET',40);
INSERT INTO Customer VALUES
(0001,'15-AUG-1998','J BOND','STOCKPORT','ENGLAND',3,'PAINT: WHITE',4);
INSERT INTO Customer VALUES
(0002,'22-APR-1996','B TURPIN','MANCHESTER','ENGLAND',2,'WIDGETS',41);
INSERT INTO Customer VALUES
(0002,'11-JUN-1997','B TURPIN','MANCHESTER','ENGLAND',1,'BONNET',1);
INSERT INTO Customer VALUES
(0003,'06-FEB-1996','I PERRY','WOLVERHAMPTON','ENGLAND',5,'NAILS',60);
INSERT INTO Customer VALUES
(0003,'17-NOV-1997','I PARRY','WOLVERHAMPTON','ENGLAND',3,'PAINT: WHITE',2);
INSERT INTO Customer VALUES
(0004,'14-JUL-1997','P FOLWER','BIRMINGHAM','ENGLAND',6,'HAMMER',1);
INSERT INTO Customer VALUES
(0004,'30-MAY-1999','P FOLWER','BIRMIGNAM','ENGLAND',7,'PAINT: BLUE',2);
INSERT INTO Customer VALUES
(0004,'28-AUG-2000','P FOLWER','BIRMINGHAM','UNITED KINGDOM',11,'WIDGET',40);
host echo "Demonstration tables build"
COMMIT;
DROP TABLE EmpProj;
DROP TABLE Proj;
DROP VIEW Emp10;
DROP VIEW ProjStaff;
Return to the Workbook.
