Employee table (DSN8C10.EMP)
The sample employee table identifies all employees by an employee number and lists basic personnel information.
The employee table resides in the partitioned table space DSN8D12A.DSN8S12E. 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 DSN8C10.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 DSN8C10.DEPT
ON DELETE SET NULL )
EDITPROC DSN8EAE1
IN DSN8D12A.DSN8S12E
CCSID EBCDIC;
Content of the employee table
The following table shows the type of content of each of the columns in the employee table. The table has a check constraint, NUMBER, which checks that the four-digit phone number is in the numeric range 0000 to 9999.
Column | Column name | Description |
---|---|---|
1 | EMPNO | Employee number (the primary key) |
2 | FIRSTNME | First name of employee |
3 | MIDINIT | Middle initial of employee |
4 | LASTNAME | Last name of employee |
5 | WORKDEPT | ID of department in which the employee works |
6 | PHONENO | Employee telephone number |
7 | HIREDATE | Date of hire |
8 | JOB | Job held by the employee |
9 | EDLEVEL | Number of years of formal education |
10 | SEX | Sex of the employee (M or F) |
11 | BIRTHDATE | Date of birth |
12 | SALARY | Yearly salary in dollars |
13 | BONUS | Yearly bonus in dollars |
14 | COMM | Yearly commission in dollars |
The following table shows the indexes of the employee table.
Name | On column | Type of index |
---|---|---|
DSN8C10.XEMP1 | EMPNO | Primary, partitioned, ascending |
DSN8C10.XEMP2 | WORKDEPT | Ascending |
The following table shows the first half (left side) of the content of the employee table. (Table 4 shows the remaining content (right side) of the employee table.)
EMPNO | FIRSTNME | MIDINIT | LASTNAME | WORKDEPT | PHONENO | HIREDATE |
---|---|---|---|---|---|---|
000010 | CHRISTINE | I | HAAS | A00 | 3978 | 1965-01-01 |
000020 | MICHAEL | L | THOMPSON | B01 | 3476 | 1973-10-10 |
000030 | SALLY | A | KWAN | C01 | 4738 | 1975-04-05 |
000050 | JOHN | B | GEYER | E01 | 6789 | 1949-08-17 |
000060 | IRVING | F | STERN | D11 | 6423 | 1973-09-14 |
000070 | EVA | D | PULASKI | D21 | 7831 | 1980-09-30 |
000090 | EILEEN | W | HENDERSON | E11 | 5498 | 1970-08-15 |
000100 | THEODORE | Q | SPENSER | E21 | 0972 | 1980-06-19 |
000110 | VINCENZO | G | LUCCHESSI | A00 | 3490 | 1958-05-16 |
000120 | SEAN | O'CONNELL | A00 | 2167 | 1963-12-05 | |
000130 | DOLORES | M | QUINTANA | C01 | 4578 | 1971-07-28 |
000140 | HEATHER | A | NICHOLLS | C01 | 1793 | 1976-12-15 |
000150 | BRUCE | ADAMSON | D11 | 4510 | 1972-02-12 | |
000160 | ELIZABETH | R | PIANKA | D11 | 3782 | 1977-10-11 |
000170 | MASATOSHI | J | YOSHIMURA | D11 | 2890 | 1978-09-15 |
000180 | MARILYN | S | SCOUTTEN | D11 | 1682 | 1973-07-07 |
000190 | JAMES | H | WALKER | D11 | 2986 | 1974-07-26 |
000200 | DAVID | BROWN | D11 | 4501 | 1966-03-03 | |
000210 | WILLIAM | T | JONES | D11 | 0942 | 1979-04-11 |
000220 | JENNIFER | K | LUTZ | D11 | 0672 | 1968-08-29 |
000230 | JAMES | J | JEFFERSON | D21 | 2094 | 1966-11-21 |
000240 | SALVATORE | M | MARINO | D21 | 3780 | 1979-12-05 |
000250 | DANIEL | S | SMITH | D21 | 0961 | 1969-10-30 |
000260 | SYBIL | P | JOHNSON | D21 | 8953 | 1975-09-11 |
000270 | MARIA | L | PEREZ | D21 | 9001 | 1980-09-30 |
000280 | ETHEL | R | SCHNEIDER | E11 | 8997 | 1967-03-24 |
000290 | JOHN | R | PARKER | E11 | 4502 | 1980-05-30 |
000300 | PHILIP | X | SMITH | E11 | 2095 | 1972-06-19 |
000310 | MAUDE | F | SETRIGHT | E11 | 3332 | 1964-09-12 |
000320 | RAMLAL | V | MEHTA | E21 | 9990 | 1965-07-07 |
000330 | WING | LEE | E21 | 2103 | 1976-02-23 | |
000340 | JASON | R | GOUNOT | E21 | 5698 | 1947-05-05 |
200010 | DIAN | J | HEMMINGER | A00 | 3978 | 1965-01-01 |
200120 | GREG | ORLANDO | A00 | 2167 | 1972-05-05 | |
200140 | KIM | N | NATZ | C01 | 1793 | 1976-12-15 |
200170 | KIYOSHI | YAMAMOTO | D11 | 2890 | 1978-09-15 | |
200220 | REBA | K | JOHN | D11 | 0672 | 1968-08-29 |
200240 | ROBERT | M | MONTEVERDE | D21 | 3780 | 1979-12-05 |
200280 | EILEEN | R | SCHWARTZ | E11 | 8997 | 1967-03-24 |
200310 | MICHELLE | F | SPRINGER | E11 | 3332 | 1964-09-12 |
200330 | HELENA | WONG | E21 | 2103 | 1976-02-23 | |
200340 | ROY | R | ALONZO | E21 | 5698 | 1947-05-05 |
(Table 3 shows the first half (right side) of the content of employee table.)
(EMPNO) | JOB | EDLEVEL | SEX | BIRTHDATE | SALARY | BONUS | COMM |
---|---|---|---|---|---|---|---|
(000010) | PRES | 18 | F | 1933-08-14 | 52750.00 | 1000.00 | 4220.00 |
(000020) | MANAGER | 18 | M | 1948-02-02 | 41250.00 | 800.00 | 3300.00 |
(000030) | MANAGER | 20 | F | 1941-05-11 | 38250.00 | 800.00 | 3060.00 |
(000050) | MANAGER | 16 | M | 1925-09-15 | 40175.00 | 800.00 | 3214.00 |
(000060) | MANAGER | 16 | M | 1945-07-07 | 32250.00 | 600.00 | 2580.00 |
(000070) | MANAGER | 16 | F | 1953-05-26 | 36170.00 | 700.00 | 2893.00 |
(000090) | MANAGER | 16 | F | 1941-05-15 | 29750.00 | 600.00 | 2380.00 |
(000100) | MANAGER | 14 | M | 1956-12-18 | 26150.00 | 500.00 | 2092.00 |
(000110) | SALESREP | 19 | M | 1929-11-05 | 46500.00 | 900.00 | 3720.00 |
(000120) | CLERK | 14 | M | 1942-10-18 | 29250.00 | 600.00 | 2340.00 |
(000130) | ANALYST | 16 | F | 1925-09-15 | 23800.00 | 500.00 | 1904.00 |
(000140) | ANALYST | 18 | F | 1946-01-19 | 28420.00 | 600.00 | 2274.00 |
(000150) | DESIGNER | 16 | M | 1947-05-17 | 25280.00 | 500.00 | 2022.00 |
(000160) | DESIGNER | 17 | F | 1955-04-12 | 22250.00 | 400.00 | 1780.00 |
(000170) | DESIGNER | 16 | M | 1951-01-05 | 24680.00 | 500.00 | 1974.00 |
(000180) | DESIGNER | 17 | F | 1949-02-21 | 21340.00 | 500.00 | 1707.00 |
(000190) | DESIGNER | 16 | M | 1952-06-25 | 20450.00 | 400.00 | 1636.00 |
(000200) | DESIGNER | 16 | M | 1941-05-29 | 27740.00 | 600.00 | 2217.00 |
(000210) | DESIGNER | 17 | M | 1953-02-23 | 18270.00 | 400.00 | 1462.00 |
(000220) | DESIGNER | 18 | F | 1948-03-19 | 29840.00 | 600.00 | 2387.00 |
(000230) | CLERK | 14 | M | 1935-05-30 | 22180.00 | 400.00 | 1774.00 |
(000240) | CLERK | 17 | M | 1954-03-31 | 28760.00 | 600.00 | 2301.00 |
(000250) | CLERK | 15 | M | 1939-11-12 | 19180.00 | 400.00 | 1534.00 |
(000260) | CLERK | 16 | F | 1936-10-05 | 17250.00 | 300.00 | 1380.00 |
(000270) | CLERK | 15 | F | 1953-05-26 | 27380.00 | 500.00 | 2190.00 |
(000280) | OPERATOR | 17 | F | 1936-03-28 | 26250.00 | 500.00 | 2100.00 |
(000290) | OPERATOR | 12 | M | 1946-07-09 | 15340.00 | 300.00 | 1227.00 |
(000300) | OPERATOR | 14 | M | 1936-10-27 | 17750.00 | 400.00 | 1420.00 |
(000310) | OPERATOR | 12 | F | 1931-04-21 | 15900.00 | 300.00 | 1272.00 |
(000320) | FIELDREP | 16 | M | 1932-08-11 | 19950.00 | 400.00 | 1596.00 |
(000330) | FIELDREP | 14 | M | 1941-07-18 | 25370.00 | 500.00 | 2030.00 |
(000340) | FIELDREP | 16 | M | 1926-05-17 | 23840.00 | 500.00 | 1907.00 |
(200010) | SALESREP | 18 | F | 1933-08-14 | 46500.00 | 1000.00 | 4220.00 |
(200120) | CLERK | 14 | M | 1942-10-18 | 29250.00 | 600.00 | 2340.00 |
(200140) | ANALYST | 18 | F | 1946-01-19 | 28420.00 | 600.00 | 2274.00 |
(200170) | DESIGNER | 16 | M | 1951-01-05 | 24680.00 | 500.00 | 1974.00 |
(200220) | DESIGNER | 18 | F | 1948-03-19 | 29840.00 | 600.00 | 2387.00 |
(200240) | CLERK | 17 | M | 1954-03-31 | 28760.00 | 600.00 | 2301.00 |
(200280) | OPERATOR | 17 | F | 1936-03-28 | 26250.00 | 500.00 | 2100.00 |
(200310) | OPERATOR | 12 | F | 1931-04-21 | 15900.00 | 300.00 | 1272.00 |
(200330) | FIELDREP | 14 | F | 1941-07-18 | 25370.00 | 500.00 | 2030.00 |
(200340) | FIELDREP | 16 | M | 1926-05-17 | 23840.00 | 500.00 | 1907.00 |
Relationship to other tables
The employee table is a parent table of:
- The department table, through a foreign key on column MGRNO
- The project table, through a foreign key on column RESPEMP
The employee table is a dependent of the department table, through its foreign key on column WORKDEPT.