3.8.3 Creating tables

After creating a schema, you will create a table with the name SAMPLE for storing the sample data.

Note: The authorization identifier (SAMPLE) for the sample application program ($ADBDIR/sample) and the authorization identifier explained in this section (ADBUSER02) are different. If using the sample application program, be sure to change the authorization identifier first.

Organization of this subsection

(1) Executing an SQL statement for creating a table

To create a table, use the CREATE TABLE definition SQL statement. Enter the following SQL statement and press Enter:

CREATE TABLE "SAMPLE" ("STATECODE" SMALLINT, "STATENAME" VARCHAR(15),

NEXT ? will then be displayed. Continue by entering the following SQL statement and pressing Enter:

"ZIPCODE" CHAR(15), "ADDRESS" VARCHAR(100), "AREA" DECIMAL(19))

NEXT ? will then be displayed. Continue by entering the following SQL statement and pressing Enter:

IN ADBUTBL01;

The following table shows the details of the CREATE TABLE statement entered.

Table 3‒6: Details of the CREATE TABLE statement specified
No.	Input content	Description
1	`"SAMPLE"`	Specifies the name of the table to be created.
2	`"STATECODE" SMALLINT`	Specifies column names and data types for the table.	Specifies `STATECODE` (state code) as the column name. Since a number between 1 and 50 is to be stored in the `STATECODE` column, `SMALLINT` is specified as the data type.
3	`"STATENAME" VARCHAR(15)`		Specifies `STATENAME` (state name) as the column name. Since a variable-length character string of from 1 to 15 bytes is to be stored in the `STATENAME` column, `VARCHAR` is specified as the data type.
4	`"ZIPCODE" CHAR(15)`		Specifies `ZIPCODE` (postal zip code) as the column name. Since a 15-byte, fixed-length character string is to be stored in the `ZIPCODE` column, `CHAR` is specified as the data type.
5	`"ADDRESS" VARCHAR(100)`		Specifies `ADDRESS` (address of state capitol building) as the column name. Since a variable-length character string of from 1 to 100 bytes is to be stored in the `ADDRESS` column, `VARCHAR` is specified as the data type.
6	`"AREA" DECIMAL(19)`		Specifies `AREA` (area size) as the column name. Since a 19-digit number is to be stored in the `AREA` column, `DECIMAL` is specified as the data type.
7	`IN ADBUTBL01`	Specifies the name of the DB area for storing the table.

Note: You can also create a table using an SQL statement stored in a file. If the length of the SQL statement (CREATE TABLE statement) makes it difficult to enter the adbsql command without making a mistake, follow the procedure in (3) Ending the connection to the HADB server, and then see 3.12.2 I cannot create a table.

A message is output when the table SAMPLE is created. The following shows an example of the output message.

■ Example of the output message

KFAA96403-I SQL processing completed.

When you have confirmed that the table exists, table creation is complete.

Once you have created the table, terminate the adbsql command. Enter the following adbsql subcommand and press Enter:

Make sure that the last character you enter is a semicolon (;), or the SQL statement will not execute.

#EXIT;

The adbsql command is terminated and another command can now be entered. The following shows an example of the execution result.

■ Execution result example

[adbmanager@localhost ~]$

CREATE TABLE (define a table) in Definition SQL in the manual HADB SQL Reference