host cls DROP TABLE Emp; DROP TABLE Dept; CREATE TABLE Dept ( deptNo NUMBER(2) NOT NULL, dName CHAR(14), loc CHAR(13) ); ALTER TABLE DEPT ADD CONSTRAINT pk_dept PRIMARY KEY (deptNo); 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'); CREATE TABLE Emp ( empNo NUMBER(4) NOT NULL, eName VARCHAR2(10), job CHAR(9), mgr NUMBER(4), hireDate DATE, sal NUMBER(7,2) CHECK(sal BETWEEN 0 AND 100000), comm NUMBER(7,2), deptNo NUMBER(2) NOT NULL, CONSTRAINT ck_Emp_comm CHECK(comm >=0), CONSTRAINT nn_Emp_hireDate CHECK(hireDate IS NOT NULL), CONSTRAINT fk_Emp_mgr_Emp FoREIGN KEY (mgr) REFERENCES Emp ON DELETE SET NULL, CONSTRAINT fk_Emp_deptNo_Dept FOREIGN KEY (deptNo) REFERENCES Dept ON DELETE CASCADE, CONSTRAINT pk_Emp PRIMARY KEY (empNo) ); INSERT INTO Emp VALUES (7839,'KING','PRESIDENT',NULL,to_date('17/11/2006','DD/MM/YYYY'),5000,NULL,10); INSERT INTO Emp VALUES (7698,'BLAKE','MANAGER',7839, to_date('1/05/2006','DD/MM/YYYY'),2850,NULL,30); INSERT INTO Emp VALUES (7782,'CLARK','MANAGER',7839, to_date('9/06/2006','DD/MM/YYYY'),2450,NULL,10); INSERT INTO Emp VALUES (7566,'JONES','MANAGER',7839, to_date('2/04/2006','DD/MM/YYYY'),2975,NULL,20); INSERT INTO Emp VALUES (7654,'MARTIN','SALESMAN',7698, to_date('28/09/2006', 'DD/MM/YYYY'),1250,1400,30); INSERT INTO Emp VALUES (7499,'ALLEN','SALESMAN',7698, to_date('20/02/2006', 'DD/MM/YYYY'),1600,300,30); INSERT INTO Emp VALUES (7844,'TURNER','SALESMAN',7698, to_date('8/09/2006', 'DD/MM/YYYY'),1500,0,30); INSERT INTO Emp VALUES (7900,'JAMES','CLERK',7698, to_date('3/12/2006','DD/MM/YYYY'), 950,NULL,30); INSERT INTO Emp VALUES (7521,'WARD','SALESMAN',7698, to_date('22/02/2006','DD/MM/YYYY'),1250,500,30); INSERT INTO Emp VALUES (7902,'FORD','ANALYST',7566, to_date('3/12/2006','DD/MM/YYYY'),3000,NULL,20); INSERT INTO Emp VALUES (7369,'SMITH','CLERK',7902, to_date('17/12/2005','DD/MM/YYYY'),800,NULL,20); INSERT INTO Emp VALUES (7788,'SCOTT','ANALYST',7566, to_date('09/12/2007','DD/MM/YYYY'),3000,NULL,20); INSERT INTO Emp VALUES (7876,'ADAMS','CLERK',7788, to_date('12/01/2008','DD/MM/YYYY'),1100,NULL,20); CREATE INDEX idx_Emp_job ON Emp(job ASC); CREATE INDEX fk_Emp_mgr_Emp ON Emp(mgr); CREATE INDEX fk_Emp_deptno_Dept ON Emp(deptNo);