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″*)–