Neoview SQL Reference Manual (R2.5)

Examples of CREATE TABLE
• This example creates a table. The clustering key is the primary key.
CREATE TABLE 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) );
• This example creates a table like the JOB table with the same constraints:
CREATE TABLE PERSNL.JOB_CORPORATE
LIKE PERSNL.JOB WITH CONSTRAINTS;
• This example creates a volatile table:
CREATE VOLATILE TABLE vtable (a int);
• This is an example of one-part name usage:
CREATE VOLATILE TABLE vtable(a int);
INSERT INTO vtable values(1);
SELECT * from vtable;
CREATE VOLATILE INDEX vindex on vtable(a);
DROP VOLATILE INDEX vindex;
DROP VOLATILE TABLE vtable;
• This is an example of two-part name usage:
CREATE VOLATILE TABLE “mysch”.vtable(a int);
INSERT INTO mysch.vtable values(1);
SELECT * from mysch.vtable;
CREATE VOLATILE INDEX vindex on mysch.vtable(a);
DROP VOLATILE INDEX vindex;
DROP VOLATILE TABLE mysch.vtable;
• Volatile tables can be created as SET tables. For example:
CREATE SET VOLATILE TABLE T (a int not null, b int, primary key(a));
--- SQL operation complete
>>insert into T values(1,2)
--- 1 row(s) inserted.
>>insert into T values(1,2);
--- 0 row(s) inserted.
>>
• Example of DISK POOL usage. In this example, because the DISK POOL clause is not
specified, Neoview will assign the table to a random disk pool.
CREATE TABLE t1 (a int not null, primary key(a));
• Example of DISK POOL usage. If a DISK POOL clause is specified, Neoview will place the
table within the specified disk pool only if it is a valid specification. A valid specification is
between 1 and the maximum number of disk pools for the Neoview system. If an invalid
DISK POOL is specified, an error is returned.
CREATE TABLE t2 (a int not null, primary key(a)) DISK POOL 2;
• This is an example of NOT CASESPECIFIC usage:
CREATE TABLE T (a char(10) NOT CASESPECIFIC,
b char(10)) NO PARTITION;
INSERT INTO T values ('a', 'A');
• A row is not returned in this example. Constant ‘A’ is case sensitive, whereas column ‘a’ is
insensitive.
SELECT * FROM T WHERE a = 'A';
CREATE TABLE Statement 93