SQL BASIC

DB2 SQL 102 DDL : CREATE TABLE


SQL Data Definition Language(DDL) works around the definition of Data. In simple word DDL is about create, delete, and alter SQL Tables.

Create Table

DB2 SQL provide 3 ways to use CREATE TABLE Statement.

    1. Basic CREATE TABLE


CREATE TABLE STUDENT    
   (STUDENTID NUMERIC NOT NULL,
    CLASS VARCHAR(36) NOT NULL,
    TEACHERID NUMERIC ,        
    LOCATION CHAR(16) ,        
    PRIMARY KEY(STUDENTID) )   

There are lot more options available. Check This link for more details

    1. Use LIKE To Duplicate a Table’s Schema

Use the LIKE clause to create a table with the same columns as another table or view. The following SQL creates a new table NEW_PROJECT using the PROJECT table as a template:


CREATE TABLE NEW_PROJECT LIKE PROJECT;
 

The LIKE clause is particularly useful in the following instances:
• When creating exception tables required by the CHECK utility
• When multiple instances of a similar table must be created
• When creating a PLAN_TABLE
• When creating the same table for multiple users

    1. Use a Fullselect to Define a Table’s Schema (Using AS clause)

You can also use the AS clause to create a table based on a SELECT statement. Use the AS clause to provide names for unnamed elements, such as functions or expressions.

For example, the following SQL creates a new table with columns based on the results of joining the EMP table and the DEPT table:


CREATE TABLE EMP_WITH_DEPT AS
   (SELECT EMPNO, FIRSTNME, LASTNAME, PHONENO,
            SALARY+COMM+BONUS AS TOTAL_COMP,   
     FROM  EMP  E,  DEPT D  WHERE E.WORKDEPT = D.DEPTNO) 
     WITH DATA/WITH NO DATA;

Short and Long Column name

When we create DDS for a PF we can create fields/columns with maximum 10 length name. Technically most of IBM i(like RPG or CL) work with “10 length” column names only. But SQL CREATE TABLE statement allow much bigger length for column names. To support this “10 length” standard SQL assign a system generated SHORT(10 length) name for every long column name. For example column CUSTOMER_NAME will be assigned a short name like CUS0000001.

Actually SQL CREATE TABLE statement gives option to define both Long name and Short name

CREATE TABLE TABLE_NAME( COLUMN_LONG_NAME For Column COLUMN_SHORT_NAME COLUMN_DATA_TYPE )
  • “FOR” or “For COLUMN” give option to define short name for column
CREATE TABLE CUSTOMER( Customer_Name For Column CusName CHAR(10) )

IBM i developer.

View Comments
There are currently no comments.