Example 1: Given administrative authority, create a table named 'ROSSITER.INVENTORY' with the following columns:
CREATE TABLE ROSSITER.INVENTORY (PARTNO SMALLINT NOT NULL, DESCR VARCHAR(24), QONHAND INT)
Example 2: Create a table named DEPARTMENT with the following columns:
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
(C) Copyright IBM Corporation 1992, 2006. All Rights Reserved.