Employee table (EMPLOYEE)

 

The employee table identifies every employee by an employee number and lists basic personnel information.

The employee table is created with the following CREATE TABLE and ALTER TABLE statements: 

CREATE TABLE EMPLOYEE       (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)                 ,
       HIREDATE    DATE                    ,
       JOB         CHAR(8)                 ,
       EDLEVEL     SMALLINT        NOT NULL,
       SEX         CHAR(1)                 ,
       BIRTHDATE   DATE                    ,
       SALARY      DECIMAL(9,2)            ,
       BONUS       DECIMAL(9,2)            ,
       COMM        DECIMAL(9,2)         
       PRIMARY KEY (EMPNO))



ALTER TABLE EMPLOYEE 
      ADD FOREIGN KEY RED (WORKDEPT) 
      REFERENCES DEPARTMENT 
      ON DELETE SET NULL



ALTER TABLE EMPLOYEE 
      ADD CONSTRAINT NUMBER 
      CHECK (PHONENO >= '0000' AND PHONENO <= '9999')

The following indexes are created: 

CREATE UNIQUE INDEX XEMP1 
       ON EMPLOYEE (EMPNO)



CREATE INDEX XEMP2 
       ON EMPLOYEE (WORKDEPT)

The following alias is created for the table: 

CREATE ALIAS EMP FOR EMPLOYEE

The table below shows the content of the columns.

Column name Description
EMPNO Employee number
FIRSTNME First name of employee
MIDINIT Middle initial of employee
LASTNAME Family name of employee
WORKDEPT ID of department in which the employee works
PHONENO Employee telephone number
HIREDATE Date of hire
JOB Job held by the employee
EDLEVEL Number of years of formal education
SEX Sex of the employee (M or F)
BIRTHDATE Date of birth
SALARY Yearly salary in dollars
BONUS Yearly bonus in dollars
COMM Yearly commission in dollars

 

Parent topic:

DB2 Universal Database for iSeries sample tables