Creating and using a table

 

You can use the CREATE TABLE statement to create a table, to define the physical attributes of the columns in a table, and to define constraints to restrict the values that are allowed in a table.

When creating a table, understand the concepts of null value and default value. A null value indicates the absence of a column value for a row. It is not the same as a value of zero or all blanks. It means unknown. A null value is not equal to any value, not even to other null values. If a column does not allow the null value, a value must be assigned to the column, either a default value or a user-supplied value.

A default value is assigned to a column when a row is added to a table and no value is specified for that column. If a specific default value was not defined for a column, the system default value is used.

You are going to create a table to maintain information about the current inventory of a business. The table contains information about the items kept in the inventory, their cost, quantity currently on hand, the last order date, and the number last ordered. The item number is a required value. It cannot be null. The item name, quantity on hand, and order quantity have user-supplied default values. The last order date and quantity ordered allow null values.

You also need to create a second table. This table contains information about suppliers of your inventory items, which items they supply, and the cost of the item from that supplier.

  1. Create the first table named INVENTORY_LIST:

    1. On the Enter SQL Statements display, type CREATE TABLE and press F4 (Prompt). The following display is shown (with the input areas not yet filled in). 
                              Specify CREATE TABLE Statement  
Type information, press Enter.
 
Table  . . . . . . . . .   INVENTORY_LIST______     Name   Collection . . . . . .     SAMPLECOLL__           Name, F4 for list  
Nulls:  1=NULL, 2=NOT NULL, 3=NOT NULL WITH DEFAULT  
Column                FOR Column    Type              Length  Scale  Nulls ITEM_NUMBER_______    ____________  CHAR___________   6____   __    2


      ITEM_NAME_________    ____________  VARCHAR________   20___   __      3


      UNIT_COST_________    ____________  DECIMAL________   8____   2_      3


      QUANTITY_ON_HAND__    ____________  SMALLINT_______   _____   __      1


      LAST_ORDER_DATE___    ____________  DATE___________   _____   __      1


      ORDER_QUANTITY____    ____________  SMALLINT_______   _____   __      1
__________________    ____________  _______________   _____   __      3
                                                                        Bottom   Table CONSTRAINT . . . . . . . . . . . . .   N      Y=Yes, N=No   Distributed Table  . . . . . . . . . . . .   N      Y=Yes, N=No  
F3=Exit   F4=Prompt           F5=Refresh   F6=Insert line    F10=Copy line F11=Display more attributes   F12=Cancel   F14=Delete line   F24=More keys

    2. Type the table name INVENTORY_LIST and schema name SAMPLECOLL at the Table and Collection prompts, as shown.

    3. Each column you want to define for the table is represented by an entry in the list on the lower part of the display. For each column, type the name of the column, the data type of the column, its length and scale, and the null attribute.

    4. Press F11 (Display more attributes) to see more attributes that can be specified for the columns. This is where a default value can be specified. 
                              Specify CREATE TABLE Statement  
Type information, press Enter.
 
Table  . . . . . . . . .   INVENTORY_LIST______     Name   Collection . . . . . .     SAMPLECOLL__           Name, F4 for list  
Data:  1=BIT, 2=SBCS, 3=MIXED, 4=CCSID  
Column                Data  Allocate  CCSID  CONSTRAINT  Default ITEM NUMBER_______     _     _____    _____      N       __________________
ITEM NAME_________     _     _____    _____      N       '***UNKNOWN***'___
UNIT_COST_________     _     _____    _____      N       __________________
QUANTITY_ON_HAND__     _     _____    _____      N       NULL______________
LAST_ORDER_DATE___     _     _____    _____      N       __________________
ORDER_QUANTITY____     _     _____    _____      N       20________________
__________________     _     _____    _____      _       __________________
                                                                        Bottom   Table CONSTRAINT . . . . . . . . . . . . .   N      Y=Yes, N=No   Distributed Table  . . . . . . . . . . . .   N      Y=Yes, N=No  
F3=Exit   F4=Prompt           F5=Refresh   F6=Insert line    F10=Copy line F11=Display more attributes   F12=Cancel   F14=Delete line   F24=More keys

      Another way of entering column definitions is to press F4 (Prompt) with your cursor on one of the column entries in the list. A display that shows all of the attributes for defining a single column appears.

    5. When all the values have been entered, press Enter to create the table. The Enter SQL Statements display is shown again with a message indicating that the table has been created.

    You can type this CREATE TABLE statement on the Enter SQL Statements display as follows: 

    CREATE TABLE SAMPLECOLL.INVENTORY_LIST 
(ITEM_NUMBER CHAR(6) NOT NULL, 
 ITEM_NAME VARCHAR(20) NOT NULL WITH DEFAULT ’***UNKNOWN***’, 
 UNIT_COST DECIMAL(8,2) NOT NULL WITH DEFAULT, 
 QUANTITY_ON_HAND SMALLINT DEFAULT NULL, 
 LAST_ORDER_DATE DATE, 
 ORDER_QUANTITY SMALLINT DEFAULT 20)

  2. Create a second table named SUPPLIERS. There are two methods you can use:

    1. Type the following command directly on the Enter SQL Statements display.

    2. Press F4 (Prompt) to use the interactive SQL displays to create the definition. 
    CREATE TABLE SAMPLECOLL.SUPPLIERS      (SUPPLIER_NUMBER CHAR(4)NOT NULL,
     ITEM_NUMBER CHAR(6) NOT NULL,
     SUPPLIER_COST DECIMAL(8,2))

 

Parent topic:

Getting started with SQL

 

Related reference


INSERT