SQL/MP Reference Manual

HP NonStop SQL/MP Reference Manual—523352-013
C-163
Examples—CREATE TABLE

This example creates a table with column headings. The EMP table contains three
columns, EMPNUM, EMPNAME, and SALARY, that are assigned the headings
“Employee/Number,” “Employee Name,” and “Monthly Salary,” respectively. The
primary key is EMPNUM.
CREATE TABLE EMP (
EMPNUM DEC(5) NO DEFAULT NOT NULL
HEADING "Employee/Number",
EMPNAME CHAR(30) UPSHIFT NO DEFAULT
HEADING "Employee Name",
SALARY DEC(8,2) DEFAULT SYSTEM
HEADING "Monthly Salary", PRIMARY KEY EMPNUM );
One row of EMP, with column headings, might appear as
follows. The SALARY column has the default value, and the
EMPNAME column is upshifted.
Employee
Number Employee Name Monthly Salary
-------- -------------------------------- --------------
62389 ANNA JONES 3882.50

This example creates a table with a clustering key.
CREATE TABLE PROC.HISTORY (
SYSTEM_ID SMALLINT UNSIGNED,
CPU SMALLINT UNSIGNED,
PIN SMALLINT UNSIGNED,
PROGRAM_FILE_NAME VARCHAR (34)
)
CATALOG \SYS3.$VOL1.SYSCAT
ORGANIZATION KEY SEQUENCED
CLUSTERING KEY ( SYSTEM_ID, CPU, PIN );

This example creates a table with Format 2 enabled partitions. Suppose that table
ODETAIL contains about 5 million rows. Each row has 20 bytes: ORDERNUM, 6;
PARTNUM, 4; UNIT_PRICE, 6; and QTY_ORDERED, 4. The PARTITION
specification could describe 24 partitions:
CREATE TABLE \SYS1.$VOL1.SALES.ODETAIL (
ORDERNUM NUMERIC (6) UNSIGNED NO DEFAULT NOT NULL,
PARTNUM NUMERIC (4) UNSIGNED NO DEFAULT NOT NULL,
UNIT_PRICE NUMERIC (8,2) NO DEFAULT NOT NULL
QTY_ORDERED NUMERIC (5) UNSIGNED NO DEFAULT NOT NULL,
PRIMARY KEY ( ORDERNUM , PARTNUM )
)
CATALOG \SYS1.$VOL1.SALES
ORGANIZATION KEY SEQUENCED
PARTITION (
\SYS1.$VOL2.SALES.ODETAIL
CATALOG \SYS1.$VOL1.SALES
EXTENT (16368,64)
MAXEXTENTS 919
FORMAT 2
FIRST KEY 030000
,