Structured Query Language (SQL) and the BASIS ODBC Driver

ALL

AND

AS

ASC

AVG

BETWEEN

BYTE

CHAR

CHARACTER

CREATE

COUNT

DATE

DECIMAL

DELETE

DESC

DISTINCT

DOUBLE

DROP

EXISTS (New in Rev. 2.0 and above.)

FALSE

FLOAT

FOREIGN

FROM

GROUP BY

HAVING

IN

INDEX

INSERT

INTEGER

INTO

IS

KEY

LIKE

MAX

MIN

NOT

NULL

NUMERIC

ON

OR

ORDER BY

NULL

NUMERIC

TABLE

TEMPORARY

TRUE

UPDATE

UNION

UNIQUE

UNKNOWN

UNSIGNED

VALUES

VARCHAR

VARYING

VIEW

WHERE

Type

Description

Syntax

Create Index

Creates an index of a column or group of columns within a table. An index essentially functions as an ordered list (alphabet or numeric) that provides a fast and efficient way to look up records. If a UNIQUE index is specified, this indicates an index that does not allow duplicate values.

CREATE INDEX indexname ON tablename( indexcolumns )

Create Table

Creates a new table in a database by physically creating a data file. Specify the columns (and their data types) that will be defined as a record in the table.

CREATE TABLE tablename (createcols )

Create View

Creates a view of one or more tables. Views are typically used to restrict or allow user access to a database, and only to necessary information.

CREATE VIEW viewname

[(identifier[, identifier]…)]

AS

SELECT [ALL | DISTINCT] select-list

FROM table-reference-list

[WHERE search-condition]

Delete

Deletes one or more records in a table.

DELETE FROM tablename where

Drop Index

Removes an index from a table in a database. This does not alter the data in any way.

DROP INDEX indexname

Drop Table

Removes a table from a database.

DROP TABLE tablename

Drop View

Removes a view from a database.

DROP VIEW viewname

Insert

Inserts a new record into a table. By default, the values need to be provided for all columns in the record in the order they are defined. It is possible, however, to optionally set only values for a subset of columns, provided that none of the columns are omitted that are defined as being NOT NULL, since they must be defined when inserting a new record.

INSERT INTO tablename insertvals

Select

Selects information from one or more tables. This is the most fundamental building block in formulating queries on databases. It does not allow data to be changed, but combined with all of its options, it becomes a powerful query builder. See SELECT INTO.

SELECT selectcols FROM tablelist where groupby having

Update

Updates the values of one or more columns in one or more records in a table.

UPDATE tablename SET setlist where

UPDATE tablename SET setlist FROM table-reference-list where

Syntax

Description

aggterm

aggterm ::= COUNT ( * ) | AVG (expression) | MAX (expression) | MIN (expression) | SUM (expression)

aliasname

aliasname ::= identifier

and

and ::= not | not AND and

asc

asc ::= | ASC | DESC

boolean

boolean ::= and | and OR boolean

colref

colref ::= aliasname . columnname | columnname

columnlist

columnlist ::= columnname , columnlist | columnname

columnname

columnname ::= identifier

comparison

comparison ::= ( boolean ) | colref IS NULL | colref IS NOT NULL | expression LIKE pattern | expression NOT LIKE pattern | expression IN ( valuelist ) | expression NOT IN (valuelist) | expression op expression | EXISTS ( SELECT select ) | expression op selectop ( SELECT select) | expression IN ( SELECT select) | expression NOT IN ( SELECT select)

create

create ::= TABLE tablename ( createcols ) | INDEX indexname ON tablename( indexcolumns )

createcol

createcol ::= columnname datatype | columnname datatype( integer ) | columnname datatype(integer, integer)

createcols

createcols ::= createcol , createcols | createcol

datatype

datatype ::= identifier

date

date ::= dateescape | dateshorthand

dateescape

dateescape ::= --*(VENDOR(MICROSOFT),PRODUCT(ODBC) d dateval )*--

dateshorthand

dateshorthand ::= { d dateval }

dateval

dateval ::= a date in yyyy-mm-dd format in single quotes (for example, '1996-02-05')

delete

delete ::= FROM tablename where

drop

drop ::= TABLE tablename | INDEX indexname | VIEW viewname

expression

expression ::= expression + times | expression - times | times

fn

fn ::= functionname ( valuelist ) | functionname ( )

functionname

functionname ::= identifier

groupby

groupby ::= | GROUP BY groupbyterms

groupbyterms

groupbyterms ::= colref | colref, groupbyterms

having

having ::= | HAVING boolean

identifier

identifier ::= an identifier (identifiers containing spaces must be enclosed in double quotes)

indexcolumn

indexcolumn ::= columnname asc

indexcolumns

indexcolumns ::= indexcolumn | indexcolumn, indexcolumns

indexname

indexname ::= identifier

insert

insert ::= INTO tablename insertvals

insertvals

insertvals ::= ( columnlist ) VALUES ( valuelist ) | VALUES (valuelist) | (columnlist) VALUES ( SELECT select ) | VALUES ( SELECT select)

integer

integer ::= a non-negative integer

neg

neg ::= term | + term | - term

not

not ::= comparison | NOT comparison

oj

oj ::= tableref LEFT OUTER JOIN tableref ON boolean | tableref LEFT OUTER JOIN oj ON boolean

ojescape

ojescape ::= --*(VENDOR(MICROSOFT),PRODUCT(ODBC) OJ oj )*--

ojshorthand

ojshorthand ::= { OJ oj }

op

op ::= > | >= | < | <= | = | <>

orderby

orderby ::= | ORDER BY orderbyterms

orderbyterm

orderbyterm ::= colref asc | integer asc

orderbyterms

orderbyterms ::= orderbyterm | orderbyterm, orderbyterms

outerjoin

outerjoin ::= ojescape | ojshorthand

pattern

pattern ::= string | ? | USER

realnumber

realnumber ::= a non-negative real number (including E notation)

scalar

scalar ::= scalarescape | scalarshorthand

scalarescape

scalarescape ::= --*(VENDOR(MICROSOFT),PRODUCT(ODBC) FN fn )*--

scalarshorthand

scalarshorthand ::= { FN fn }

select

select ::= selectcols FROM tablelist where groupby having

selectallcols

selectallcols ::= | ALL | DISTINCT

selectcols

selectcols ::= selectallcols * | selectallcols selectlist

selectlist

selectlist ::= selectlistitem , selectlist | selectlistitem

selectlistitem

selectlistitem ::= expression | expression aliasname | expression AS aliasname

selectop

selectop ::= | ALL | ANY

set

set ::= columnname = NULL | columnname = expression

setlist

setlist ::= set | setlist , set

simpleterm

simpleterm ::= string | realnumber | ? | USER | date | time | timestamp

string

string ::= a string (enclosed in single quotes)

tablelist

tablelist ::= tablelistitem , tablelist | tablelistitem

tablelistitem

tablelistitem ::= tableref | outerjoin

tablename

tablename ::= identifier

tableref

tableref ::= tablename | tablename aliasname

term

term ::= ( expression ) | colref | simpleterm | aggterm | scalar

time

time :: = timeescape | timeshorthand (a time in hh:mm:ss format in single quotes, for example, '10:19:48')

timeescape

timeescape ::= --*(VENDOR(MICROSOFT),PRODUCT(ODBC) ttimeval )*--

times

times ::= times * neg | times / neg | neg

timeshorthand

timeshorthand ::= { t timeval }

timestamp

timestamp ::= timestampescape | timestampshorthand

timestampescape

timestampescape ::= --*(VENDOR(MICROSOFT),PRODUCT(ODBC) ts timestampval )*--

timestampshorthand

timestampshorthand ::= { ts timestampval }

timestampval

timestampval ::= a timestamp in yyyy-mm-dd hh:mm:ss[.ffffff] format in single quotes (for example, '1996-02-05 10:19:48.529')

update

update ::= tablename SET setlist where

update ::= tablename SET setlist FROM table where

valuelist

valuelist ::= NULL , valuelist | expression , valuelist | expression | NULL

viewname

viewname ::= identifier

where

where ::= | WHERE boolean