CREATE TABLE

The CREATE TABLE statement defines a table. The definition must include its name and the names and attributes of its columns. The definition may include other attributes of the table such as primary key. You can specify one column as an identiy column.  You can also create a table LIKE or AS another table.

Examples:

To create a table:

Create a table named CORPDATA.DEPT 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 department: Character of length 3, must not be null
Number of departments: Integer, identity column

CREATE TABLE CORPDATA.DEPT
            (DEPTNO     CHAR(3)     NOT NULL,
            DEPTNAME    VARCHAR(36) NOT NULL,
            MGRNO     CHAR(6),
            ADMRDEPT CHAR(3)      NOT NULL
            NUMDEPT SMALLINT NOT NULL GENERATED ALWAYS AS IDENTITY
            (START WITH 1, INCREMENT BY 1))

ADD unique constraint/referential constraint/check constraint

Specifies a constraint on a column.  You can add a unique or primary key, a referential constraint, or a check constraint.

Create a table named INVENTORY, with the following columns:

ACTNO - Activity number
ACTKWD - Keyword for activity
ACTDESC - Description of activity
Make ACTNO the primary key

CREATE TABLE CORPDATA.ACT 
            (ACTNO SMALLINT NOT NULL, 
            ACTKWD CHAR(6) NOT NULL, 
            ACTDESC VARCHAR(20) NOT NULL, 
            PRIMARY KEY (ACTNO)) 

For more examples of adding constrants, see ALTER TABLE

CREATE TABLE LIKE

You can create a table like another table. That is, you can create a table that includes columns defined in an existing table. The definitions that are copied are:

If the LIKE clause immediately follows the table name and is not enclosed in parenthesis, the following attributes are also included:

If the specified table or view is a non-SQL created physical file or logical file, any non-SQL attributes are removed.

Example:

Create a table named EMPLOYEE2 that includes all of the columns defined in table EMPLOYEE.

CREATE TABLE EMPLOYEE2 LIKE EMPLOYEE

CREATE TABLE AS

CREATE TABLE AS creates a table from the result of a SELECT statement.  All of the expressions that can be used in SELECT can be used in a CREATE TABLE AS statement.  You can also include the data of the table or tables that you are selecting from.

Example:

Create a table named EMPLOYEE3 that includes all of the columns defined in the table EMPLOYEE where the DEPTNO = D11.

CREATE TABLE EMPLOYEE3 AS
    (SELECT PROJNO, PROJNAME, DEPTNO
      FROM CORPDATA.EMPLOYEE
      WHERE DEPTNO = 'D11') WITH NO DATA

For more information, see CREATE TABLE in the SQL Reference topic in the Information CenterLink to Information center.