Examples

Example 1: Given administrative authority, create a table named 'ROSSITER.INVENTORY' with the following columns:

Part number
Small integer, must not be null
Description
Character of length 0 to 24, allows nulls
Quantity on hand,
Integer allows nulls

  CREATE TABLE ROSSITER.INVENTORY
    (PARTNO         SMALLINT     NOT NULL,
     DESCR          VARCHAR(24),
     QONHAND        INT)

Example 2: Create a table named DEPARTMENT with the following columns:

Department number
Character of length 3, must not be null
Department name
Character of length 0 through 36, must not be null
Manager number
Character of length 6
Administrative dept.
Character of length 3, must not be null
Location name
Character of length 16, allows nulls

The primary key is column DEPTNO.

  CREATE TABLE DEPARTMENT
    (DEPTNO    CHAR(3)     NOT NULL,
     DEPTNAME  VARCHAR(36) NOT NULL,
     MGRNO     CHAR(6),
     ADMRDEPT  CHAR(3)     NOT NULL,
     LOCATION  CHAR(16),
     PRIMARY KEY(DEPTNO))

Example 3: Create a table named REORG_PROJECTS which has the same column definitions as the columns in the view PRJ_LEADER.

  CREATE TABLE REORG_PROJECTS
    LIKE PRJ_LEADER

Example 4: Create an EMPLOYEE2 table with an identity column named EMP_NO. Define the identity column so that DB2(R) will always generate the values for the column. Use the default value, which is 1, for the first value that should be assigned and for the incremental difference between the subsequently generated consecutive numbers.

  CREATE TABLE EMPLOYEE2
    ( EMPNO INTEGER GENERATED ALWAYS AS IDENTITY,
      ID SMALLINT,
      NAME CHAR(30),
      SALARY DECIMAL(5,2),
      DEPTNO SMALLINT)

Example 5: Assume a very large transaction table named TRANS contains one row for each transaction processed by a company. The table is defined with many columns. Create a materialized query table for the TRANS table that contains daily summary data for the date and amount of a transaction.

  CREATE TABLE STRANS
    AS (SELECT  YEAR AS SYEAR, MONTH AS SMONTH, DAY AS SDAY, SUM(AMOUNT) AS SSUM
        FROM TRANS
        GROUP BY YEAR, MONTH, DAY )
    DATA INITIALLY DEFERRED
    REFRESH DEFERRED
    MAINTAINED BY USER