SQL Grammar

Statement 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 (alphabetical 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) wanted to define 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 a users 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, you provide the values for all the columns in the record in the order they are defined. You can, however, optionally set only values for a subset of columns, provided you do not omit any columns that are defined as being NOT NULL, since they must be defined when inserting a new record.

For duplicate records, Insert returns an error. To replace duplicates, use Replace.

INSERT INTO tablename insertvals

Replace

In BBj 12.0 and higher, this type inserts a new record into a table. By default, you provide the values for all the columns in the record in the order they are defined. You can, however, optionally set only values for a subset of columns, provided you do not omit any columns that are defined as being NOT NULL, since they must be defined when inserting a new record.

For duplicate records, Replace does replace the duplicates. To prevent replacement of duplicates, use Insert, which will return an error.

REPLACE 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.

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

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 REGEXP pattern | expression NOT REGEXP 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 (not available in PRO/5 or VPRO/5 SQL engine)

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

ojescape (not available in PRO/5 or VPRO/5 SQL engine)

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

ojshorthand (not available in PRO/5 or VPRO/5 SQL engine)

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

valuelist

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

viewname

viewname ::= identifier

where

where ::= | WHERE boolean