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 |