/* drop tables */
DROP TABLE Employee;
DROP TABLE Department;
/* create tables */
CREATE TABLE  Department  
  (deptno NUMBER(2)  CONSTRAINT pk_dept PRIMARY KEY, 
   dname   VARCHAR2(10)  CONSTRAINT nn_dname NOT NULL);
CREATE TABLE Employee 
  (empno     NUMBER        CONSTRAINT pk_emp PRIMARY KEY, 
   ename     VARCHAR2(10)  CONSTRAINT nn_ename NOT NULL 
                           CONSTRAINT upper_ename 
                            CHECK (ename = UPPER(ename)), 
   job       VARCHAR2(9), 
   mgr       NUMBER        CONSTRAINT fk_mgr 
                             REFERENCES Employee (empno), 
   hiredate  VARCHAR(10), 
   sal       NUMBER(10,2)  CONSTRAINT ck_sal CHECK (sal > 500), 
   comm      NUMBER(9,0)   DEFAULT NULL, 
   deptno    NUMBER(2)     CONSTRAINT nn_deptno NOT NULL 
                           CONSTRAINT fk_deptno 
                            REFERENCES Department (deptno) );
/* populate tables */
insert into Department  values (12,'CS');
insert into Department  values (20,'MAT');
insert into Department  values (30,'PSY');
insert into Employee    values
 (1,'MOHAMMADI','Prof',NULL,'09/01/1985',25000.0,NULL,12);
insert into Employee    values
 (2,'JONES','Prof',1,'09/01/2000',45000.0,10000,12);
insert into Employee    values
 (3,'MOORE','Tec',1,'09/01/2002',65000.0,10000,12);
insert into Employee    values
 (4,'SAINT','Prof',NULL,'09/01/1980',15000.0,NULL,20);
insert into Employee    values
 (7,'HUNT','Tec',4,'09/01/1985',25000.0,NULL,12);
