Employee table (DSN8A10.EMP)

The employee table resides in the partitioned table space DSN8D10A.DSN8S10E. Because this table has a foreign key that references DEPT, that table and the index on its primary key must be created first. Then EMP is created with the following statement:

CREATE TABLE DSN8A10.EMP
      (EMPNO     CHAR(6)                          NOT NULL,
       FIRSTNME  VARCHAR(12)                      NOT NULL,
       MIDINIT   CHAR(1)                          NOT NULL,
       LASTNAME  VARCHAR(15)                      NOT NULL,
       WORKDEPT  CHAR(3)                                  ,
       PHONENO   CHAR(4)         CONSTRAINT NUMBER CHECK
                 (PHONENO >= '0000' AND
                  PHONENO <= '9999')                      ,
       HIREDATE  DATE                                     ,
       JOB       CHAR(8)                                  ,
       EDLEVEL   SMALLINT                                 ,
       SEX       CHAR(1)                                  ,
       BIRTHDATE DATE                                     ,
       SALARY    DECIMAL(9,2)                             ,
       BONUS     DECIMAL(9,2)                             ,
       COMM      DECIMAL(9,2)                             ,
       PRIMARY KEY (EMPNO)                                ,
       FOREIGN KEY RED (WORKDEPT) REFERENCES DSN8A10.DEPT
                 ON DELETE SET NULL                       )
  EDITPROC  DSN8EAE1
  IN DSN8D10A.DSN8S10E
  CCSID EBCDIC;