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.