Creating work tables for the EMP and DEPT sample tables

Before testing SQL statements that insert, update, and delete rows in the DSN8A10.EMP and DSN8A10.DEPT sample tables, you should create duplicates of these tables. Create duplicates so that the original sample tables remain intact. These duplicate tables are called work tables.

About this task

This topic shows how to create the department and employee work tables and how to fill a work table with the contents of another table:

Each of these topics assumes that you logged on by using your own authorization ID. The authorization ID qualifies the name of each object that you create. For example, if your authorization ID is SMITH, and you create table YDEPT, the name of the table is SMITH.YDEPT. If you want to access table DSN8A10.DEPT, you must refer to it by its complete name. If you want to access your own table YDEPT, you need only to refer to it as YDEPT.

Use the following statements to create a new department table called YDEPT, modeled after the existing table, DSN8A10.DEPT, and an index for YDEPT:

CREATE TABLE YDEPT
  LIKE DSN8A10.DEPT;

CREATE UNIQUE INDEX YDEPTX
  ON YDEPT (DEPTNO);

If you want DEPTNO to be a primary key, as in the sample table, explicitly define the key. Use an ALTER TABLE statement, as in the following example:

ALTER TABLE YDEPT
  PRIMARY KEY(DEPTNO);

You can use an INSERT statement to copy the rows of the result table of a fullselect from one table to another. The following statement copies all of the rows from DSN8A10.DEPT to your own YDEPT work table:

INSERT INTO YDEPT
  SELECT *
    FROM DSN8A10.DEPT;

For information about using the INSERT statement, see Inserting rows by using the INSERT statement.

You can use the following statements to create a new employee table called YEMP:

CREATE TABLE YEMP
      (EMPNO     CHAR(6)        PRIMARY KEY NOT NULL,
       FIRSTNME  VARCHAR(12)    NOT NULL,
       MIDINIT   CHAR(1)        NOT NULL,
       LASTNAME  VARCHAR(15)    NOT NULL,
       WORKDEPT  CHAR(3)        REFERENCES YDEPT
                                  ON DELETE SET NULL,
       PHONENO   CHAR(4)        UNIQUE NOT NULL,
       HIREDATE  DATE                   ,
       JOB       CHAR(8)                ,
       EDLEVEL   SMALLINT               ,
       SEX       CHAR(1)                ,
       BIRTHDATE DATE                   ,
       SALARY    DECIMAL(9, 2)          ,
       BONUS     DECIMAL(9, 2)          ,
       COMM      DECIMAL(9, 2)          );

This statement also creates a referential constraint between the foreign key in YEMP (WORKDEPT) and the primary key in YDEPT (DEPTNO). It also restricts all phone numbers to unique numbers.

If you want to change a table definition after you create it, use the ALTER TABLE statement with a RENAME clause. If you want to change a table name after you create it, use the RENAME statement.

You can change a table definition by using the ALTER TABLE statement only in certain ways. For example, you can add and drop constraints on columns in a table. You can also change the data type of a column within character data types, within numeric data types, and within graphic data types. You can add a column to a table. However, you cannot use the ALTER TABLE statement to drop a column from a table.

Parent topic: Creating tables

Related tasks:

Altering DB2® tables

Related reference:

ALTER TABLE

RENAME