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.
- Create the first table named INVENTORY_LIST:
- 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____ __ 2ITEM_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
- Type the table name INVENTORY_LIST and schema name SAMPLECOLL at the Table and Collection prompts, as shown.
- 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.
- 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 keysAnother 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.
- 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)
- Create a second table named SUPPLIERS. There are two methods you can use:
- Type the following command directly on the Enter SQL Statements display.
- 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