Neoview SQL Reference Manual (R2.4)

• 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. For a system with 256 processors and 2 disks per processor,

the default number of pools is 2. Each pool contains 256 disks. T1 is created with 256 partitions

and assigned to a random disk pool.

CREATE TABLE t1 (a int not null, primary key(a));

• Example of DISK POOL usage. Example of DISK POOL usage. For a system with 256

processors and 2 disks per processor, the default number of pools is 2. Each pool contains

256 disksT2 is created with 256 partitions and allocated to the disk in disk pool 2.

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));

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’;

• The row is returned in this example. Both sides are case sensitive.

SELECT * FROM T WHERE a = ‘A’ (not casespecific);

• A row is not returned in this example. A case sensitive comparison is done since column ‘b’

is case sensitive.

SELECT * FROM T WHERE b = ‘A’;

• A row is not returned in this example. A case sensitive comparison is done since column ‘b’

is case sensitive.

SELECT * FROM T WHERE b = ‘A’ (not casespecific);

Examples of CREATE SET TABLE

This is an example of creating a SET table:

90 SQL Statements