Supported SQL Grammar
Note: In Zim, constant strings can be represented either surrounded in single quotes (‘) or double quotes (“). In SQL, constant strings are surrounded by single quotes (‘) whereas identifiers are surrounded by double quotes (“).
statement ::= SELECT select | INSERT insert | DELETE delete | UPDATE update | passthroughSQL
passthroughSQL ::= any statement supported by the back end
tablename ::= identifier
columnname ::= identifier
select ::= selectcols FROM tablelist where groupby having orderby
delete ::= FROM table where
insert ::= INTO table insertvals
update ::= table SET setlist where
setlist ::= set | setlist , set
set ::= column = NULL | column = expression
insertvals ::= ( columnlist ) VALUES ( valuelist ) | VALUES ( valuelist )
columnlist ::= column , columnlist | column
column ::= columnname
valuelist ::= NULL , valuelist | expression , valuelist | expression | NULL
selectcols ::= selectallcols * | selectallcols selectlist
selectallcols ::= | ALL | DISTINCT
selectlist ::= expression , selectlist | expression
where ::= | WHERE boolean
having ::= | HAVING boolean
boolean ::= and | and OR boolean
and ::= not | not AND and
not ::= comparison | NOT 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
op ::= > | >= | < | <= | = | <>
pattern ::= string | ? | USER
expression ::= expression + times | expression – times | times
times ::= times * neg | times / neg | neg
neg ::= term | + term | – term
term ::= ( expression ) | colref | simpleterm | aggterm
aggterm ::= COUNT ( * ) | AVG ( expression ) | MAX ( expression ) | MIN ( expression ) | SUM ( expression )
simpleterm ::= string | realnumber | ? | USER | date | time | timestamp
groupby ::= | GROUP BY groupbyterms
groupbyterms ::= colref | colref , groupbyterms
orderby ::= | ORDER BY orderbyterms
orderbyterms ::= orderbyterm | orderbyterm , orderbyterms
orderbyterm ::= colref asc | integer asc
asc ::= | ASC | DESC
colref ::= aliasname.columnname | columnname
aliasname ::= identifier
tablelist ::= tableref, tablelist | tableref
tableref ::= table | table aliasname
table ::= tablename
identifier ::= an identifier (identifiers containing spaces must be enclosed in double quotation marks)
string ::= a string (enclosed in single quotation marks)
realnumber ::= a non-negative real number
integer ::= a non-negative integer
date ::= a date in ODBC escape clause format (for example, {d’1996-02-05′} or
–(*vendor(Microsoft),product(ODBC) d’1996-02-05’*)–
time ::= a time in ODBC escape clause format (for example, {t’10:19:48′} or
–(*vendor(Microsoft),product(ODBC) t ’10:19:48’*)–
timestamp ::= a timestamp in ODBC escape clause format (for example,
{ts’1996-02-05 10:19:48.529′} or
–(*vendor(Microsoft),product(ODBC) ts ‘1996-02-05 10:19:48.529″*)–