The SQL Command and Statements

The SQL command

SQL statements can be tested while logged on to Reality by using the SQL TCL command:

SQL sql_statement {(options}

Options

C Turns on the COUNT option (similar to English).

D8 Displays optimisation decisions.

O Turns all SQL optimisation off.

S Turns off sort optimisation.

T Turns off table access optimisation (item-id and index optimisation).

V Displays the version numbers of the SQL server and compiler.

W Turns off multi-table WHERE clause optimisation.

SQL Statements

The following statements are currently supported by Reality. Detailed descriptions of these statements are provided following the lists. The syntax of the SQL search condition element and SQL numeric expression element is described independently.

Creating, Deleting and Updating Tables

ALTER TABLE

Adds columns to an existing table.

CREATE TABLE

Creates an SQL table, defining columns and constraints.

DROP TABLE

Deletes an SQL table.

Creating, Deleting and Updating Indexes

CREATE INDEX

Creates an index on specified columns in an SQL table.

DROP INDEX

Deletes an index in an SQL table.

Retrieving Information From a Table

SELECT

Lists rows from a table.

Granting and Revoking Privileges

GRANT

Gives privileges for a particular table to users.

REVOKE

Revokes privileges for a particular table to users. Reverses the action of the GRANT command.

Inserting, Deleting and Updating Rows

INSERT

Adds new rows Into a table.

DELETE

Removes rows from a table.

UPDATE

Updates rows in a table.

Verifying a Table

VERIFY

Checks that table data matches the specified data type for each column.