A.7. Productions string ::= < string literal > A string literal value. Use '' to escape ' in the string. Example: reserved identifier ::= INSTEAD VIEW ENABLED DISABLED KEY SERIAL TEXTAGG COUNT ROW_NUMBER RANK DENSE_RANK SUM AVG MIN MAX EVERY STDDEV_POP STDDEV_SAMP VAR_SAMP VAR_POP DOCUMENT CONTENT TRIM EMPTY ORDINALITY PATH FIRST LAST SUBSTRING EXTRACT TO_CHARS TO_BYTES TIMESTAMPADD TIMESTAMPDIFF QUERYSTRING NAMESPACE RESULT INDEX ACCESSPATTERN AUTO_INCREMENT WELLFORMED SQL_TSI_FRAC_SECOND SQL_TSI_SECOND SQL_TSI_MINUTE SQL_TSI_HOUR SQL_TSI_DAY SQL_TSI_WEEK SQL_TSI_MONTH SQL_TSI_QUARTER SQL_TSI_YEAR TEXTTABLE ARRAYTABLE SELECTOR SKIP WIDTH PASSING NAME ENCODING COLUMNS DELIMITER QUOTE HEADER NULLS OBJECTTABLE VERSION INCLUDING EXCLUDING XMLDECLARATION VARIADIC RAISE EXCEPTION CHAIN JSONARRAY_AGG JSONOBJECT Allows non-reserved keywords to be parsed as identifiers Example: SELECT COUNT FROM ... identifier ::= < identifier > < non-reserved identifier > Partial or full name of a single entity. Example: create trigger ::= CREATE TRIGGER ON < identifier > INSTEAD OF ( INSERT | UPDATE | DELETE ) AS < for each row trigger action > Creates a trigger action on the given target. Example: alter ::= ALTER ( ( VIEW < identifier > AS < query expression > ) | ( PROCEDURE < identifier > AS < statement > ) | ( TRIGGER ON < identifier > INSTEAD OF ( INSERT | UPDATE | DELETE ) ( ( AS < for each row trigger action > ) | ENABLED | DISABLED ) ) ) Alter the given target. Example: for each row trigger action ::= FOR EACH ROW ( ( BEGIN ( ATOMIC )? ( < statement > )* END ) | < statement > ) Defines an action to perform on each row. Example: directly executable statement ::= < query expression > < call statement > < insert statement > < update statement > < delete statement > < drop table > < create temporary table > < create foreign temp table > < alter > < create trigger > A statement that can be executed at runtime. Example: drop table ::= DROP TABLE < identifier > Creates a trigger action on the given target. Example: create temporary table ::= CREATE LOCAL TEMPORARY TABLE < identifier > < lparen > < temporary table element > ( < comma > < temporary table element > )* ( < comma > PRIMARY KEY < column list > )? < rparen > Creates a temporary table. Example: temporary table element ::= < identifier > ( < data type > | SERIAL ) ( NOT NULL )? Defines a temporary table column. Example: raise error statement ::= ERROR < expression > Raises an error with the given message. Example: raise statement ::= RAISE ( SQLWARNING )? < exception reference > Raises an error or warning with the given message. Example: exception reference ::= < identifier > < sql exception > a reference to an exception Example: sql exception ::= SQLEXCEPTION < common value expression > ( SQLSTATE < common value expression > ( < comma > < common value expression > )? )? ( CHAIN < exception reference > )? creates a sql exception or warning with the specified message, state, and code Example: statement ::= ( ( < identifier > < colon > )? ( < loop statement > | < while statement > | < compound statement > ) ) < if statement > | < delimited statement > A procedure statement. Example: delimited statement ::= ( < assignment statement > | < data statement > | < raise error statement > | < raise statement > | < declare statement > | < branching statement > | < return statement > ) < semicolon > A procedure statement terminated by ;. Example: compound statement ::= BEGIN ( ( NOT )? ATOMIC )? ( < statement > )* ( EXCEPTION < identifier > ( < statement > )* )? END A procedure statement block contained in BEGIN END. Example: branching statement ::= ( ( BREAK | CONTINUE ) ( < identifier > )? ) ( LEAVE < identifier > ) A procedure branching control statement, which typically specifies a label to return control to. Example: return statement ::= RETURN ( < expression > )? A return statement. Example: while statement ::= WHILE < lparen > < condition > < rparen > < statement > A procedure while statement that executes until its condition is false. Example: loop statement ::= LOOP ON < lparen > < query expression > < rparen > AS < identifier > < statement > A procedure loop statement that executes over the given cursor. Example: if statement ::= IF < lparen > < condition > < rparen > < statement > ( ELSE < statement > )? A procedure loop statement that executes over the given cursor. Example: declare statement ::= DECLARE ( < data type > | EXCEPTION ) < identifier > ( < eq > < assignment statement operand > )? A procedure declaration statement that creates a variable and optionally assigns a value. Example: assignment statement ::= < identifier > < eq > ( < assignment statement operand > | ( < call statement > ( ( WITH | WITHOUT ) RETURN )? ) ) Assigns a variable a value in a procedure. Example: assignment statement operand ::= < insert statement > < update statement > < delete statement > < expression > < query expression > < sql exception > A value or command that can be used in an assignment. Note All assignments except for expression are deprecated. data statement ::= ( < directly executable statement > | < dynamic data statement > ) ( ( WITH | WITHOUT ) RETURN )? A procedure statement that executes a SQL statement. An update statement can have its update count accessed via the ROWCOUNT variable. procedure body definition ::= ( CREATE ( VIRTUAL )? PROCEDURE )? < statement > Defines a procedure body on a Procedure metadata object. Example: dynamic data statement ::= ( EXECUTE | EXEC ) ( STRING | IMMEDIATE )? < expression > ( AS < typed element list > ( INTO < identifier > )? )? ( USING < set clause list > )? ( UPDATE ( < unsigned integer > | < star > ) )? A procedure statement that can execute arbitrary sql. Example: set clause list ::= < identifier > < eq > < expression > ( < comma > < identifier > < eq > < expression > )* A list of value assignments. Example: typed element list ::= < identifier > < data type > ( < comma > < identifier > < data type > )* A list of typed elements. Example: callable statement ::= < lbrace > ( < qmark > < eq > )? CALL < identifier > ( < lparen > ( < expression list > )? < rparen > )? < rbrace > ( < option clause > )? A callable statement defined using JDBC escape syntax. Example: call statement ::= ( ( EXEC | EXECUTE | CALL ) < identifier > < lparen > ( < named parameter list > | ( < expression list > )? ) < rparen > ) ( < option clause > )? Executes the procedure with the given parameters. Example: named parameter list ::= ( < identifier > < eq > ( < gt > )? < expression > ( < comma > < identifier > < eq > ( < gt > )? < expression > )* ) A list of named parameters. Example: insert statement ::= ( INSERT | MERGE ) INTO < identifier > ( < column list > )? ( ( VALUES < lparen > < expression list > < rparen > ) | < query expression > ) ( < option clause > )? Inserts values into the given target. Example: expression list ::= < expression > ( < comma > < expression > )* A list of expressions. Example: update statement ::= UPDATE < identifier > SET < set clause list > ( < where clause > )? ( < option clause > )? Update values in the given target. Example: delete statement ::= DELETE FROM < identifier > ( < where clause > )? ( < option clause > )? Delete rows from the given target. Example: query expression ::= ( WITH < with list element > ( < comma > < with list element > )* )? < query expression body > A declarative query for data. Example: with list element ::= < identifier > ( < column list > )? AS < lparen > < query expression > < rparen > A query expression for use in the enclosing query. Example: query expression body ::= < query term > ( ( UNION | EXCEPT ) ( ALL | DISTINCT )? < query term > )* ( < order by clause > )? ( < limit clause > )? ( < option clause > )? The body of a query expression, which can optionally be ordered and limited. Example: query term ::= < query primary > ( INTERSECT ( ALL | DISTINCT )? < query primary > )* Used to establish INTERSECT precedence. Example: query primary ::= < query > ( TABLE < identifier > ) ( < lparen > < query expression body > < rparen > ) A declarative source of rows. Example: query ::= < select clause > ( < into clause > )? ( < from clause > ( < where clause > )? ( < group by clause > )? ( < having clause > )? )? A SELECT query. Example: into clause ::= INTO < identifier > Used to direct the query into a table. Note This is deprecated. Use INSERT INTO with a query expression instead. Example: select clause ::= SELECT ( ALL | DISTINCT )? ( < star > | ( < select sublist > ( < comma > < select sublist > )* ) ) The columns returned by a query. Can optionally be distinct. Example: select sublist ::= < select derived column > < all in group > An element in the select clause Example: select derived column ::= ( < expression > ( ( AS )? < identifier > )? ) A select clause item that selects a single column. Note This is slightly different than a derived column in that the AS keyword is optional. Example: derived column ::= ( < expression > ( AS < identifier > )? ) An optionally named expression. Example: all in group ::= < all in group identifier > A select sublist that can select all columns from the given group. Example: ordered aggreate function ::= ( XMLAGG | ARRAY_AGG | JSONARRAY_AGG ) < lparen > < expression > ( < order by clause > )? < rparen > An aggregate function that can optionally be ordered. Example: text aggreate function ::= TEXTAGG < lparen > ( FOR )? < derived column > ( < comma > < derived column > )* ( DELIMITER < character > )? ( QUOTE < character > )? ( HEADER )? ( ENCODING < identifier > )? ( < order by clause > )? < rparen > An aggregate function for creating separated value clobs. Example: standard aggregate function ::= ( COUNT < lparen > < star > < rparen > ) ( ( COUNT | SUM | AVG | MIN | MAX | EVERY | STDDEV_POP | STDDEV_SAMP | VAR_SAMP | VAR_POP | SOME | ANY ) < lparen > ( DISTINCT | ALL )? < expression > < rparen > ) A standard aggregate function. Example: analytic aggregate function ::= ( ROW_NUMBER | RANK | DENSE_RANK ) < lparen > < rparen > An analytic aggregate function. Example: filter clause ::= FILTER < lparen > WHERE < boolean primary > < rparen > An aggregate filter clause applied prior to accumulating the value. Example: from clause ::= FROM ( < table reference > ( < comma > < table reference > )* ) A query from clause containing a list of table references. Example: table reference ::= ( < escaped join > < joined table > < rbrace > ) < joined table > An optionally escaped joined table. Example: joined table ::= < table primary > ( < cross join > | < qualified table > )* A table or join. Example: cross join ::= ( ( CROSS | UNION ) JOIN < table primary > ) A cross join. Example: qualified table ::= ( ( ( RIGHT ( OUTER )? ) | ( LEFT ( OUTER )? ) | ( FULL ( OUTER )? ) | INNER )? JOIN < table reference > ON < condition > ) An INNER or OUTER join. Example: table primary ::= ( < text table > | < array table > | < xml table > | < object table > | < table name > | < table subquery > | ( < lparen > < joined table > < rparen > ) ) ( MAKEDEP | MAKENOTDEP )? A single source of rows. Example: xml serialize ::= XMLSERIALIZE < lparen > ( DOCUMENT | CONTENT )? < expression > ( AS ( STRING | VARCHAR | CLOB | VARBINARY | BLOB ) )? ( ENCODING < identifier > )? ( VERSION < string > )? ( ( INCLUDING | EXCLUDING ) XMLDECLARATION )? < rparen > Serializes an XML value. Example: array table ::= ARRAYTABLE < lparen > < value expression primary > COLUMNS < typed element list > < rparen > ( AS )? < identifier > The ARRAYTABLE table function creates tabular results from arrays. It can be used as a nested table reference. Example: text table ::= TEXTTABLE < lparen > < common value expression > ( SELECTOR < string > )? COLUMNS < text table column > ( < comma > < text table column > )* ( NO ROW DELIMITER )? ( DELIMITER < character > )? ( ( ESCAPE < character > ) | ( QUOTE < character > ) )? ( HEADER ( < unsigned integer > )? )? ( SKIP < unsigned integer > )? < rparen > ( AS )? < identifier > The TEXTTABLE table function creates tabular results from text. It can be used as a nested table reference. Example: text table column ::= < identifier > < data type > ( WIDTH < unsigned integer > ( NO TRIM )? )? ( SELECTOR < string > < unsigned integer > )? A text table column. Example: xml query ::= XMLQUERY < lparen > ( < xml namespaces > < comma > )? < string > ( PASSING < derived column > ( < comma > < derived column > )* )? ( ( NULL | EMPTY ) ON EMPTY )? < rparen > Executes an XQuery to return an XML result. Example: object table ::= OBJECTTABLE < lparen > ( LANGUAGE < string > )? < string > ( PASSING < derived column > ( < comma > < derived column > )* )? COLUMNS < object table column > ( < comma > < object table column > )* < rparen > ( AS )? < identifier > Returns table results by processing a script. Example: object table column ::= < identifier > < data type > < string > ( DEFAULT < expression > )? object table column. Example: xml table ::= XMLTABLE < lparen > ( < xml namespaces > < comma > )? < string > ( PASSING < derived column > ( < comma > < derived column > )* )? ( COLUMNS < xml table column > ( < comma > < xml table column > )* )? < rparen > ( AS )? < identifier > Returns table results by processing an XQuery. Example: xml table column ::= < identifier > ( ( FOR ORDINALITY ) | ( < data type > ( DEFAULT < expression > )? ( PATH < string > )? ) ) XML table column. Example: unsigned integer ::= < unsigned integer literal > An unsigned interger value. Example: table subquery ::= ( TABLE | LATERAL )? < lparen > ( < query expression > | < call statement > ) < rparen > ( AS )? < identifier > A table defined by a subquery. Example: table name ::= ( < identifier > ( ( AS )? < identifier > )? ) A table named in the FROM clause. Example: where clause ::= WHERE < condition > Specifies a search condition Example: condition ::= < boolean value expression > A boolean expression. boolean value expression ::= < boolean term > ( OR < boolean term > )* An optionally ORed boolean expression. boolean term ::= < boolean factor > ( AND < boolean factor > )* An optional ANDed boolean factor. boolean factor ::= ( NOT )? < boolean primary > A boolean factor. Example: boolean primary ::= ( < common value expression > ( < between predicate > | < match predicate > | < like regex predicate > | < in predicate > | < is null predicate > | < quantified comparison predicate > | < comparison predicate > )? ) < exists predicate > A boolean predicate or simple expression. Example: comparison operator ::= < eq > < ne > < ne2 > < lt > < le > < gt > < ge > A comparison operator. Example: comparison predicate ::= < comparison operator > < common value expression > A value comparison. Example: subquery ::= < lparen > ( < query expression > | < call statement > ) < rparen > A subquery. Example: quantified comparison predicate ::= < comparison operator > ( ANY | SOME | ALL ) < subquery > A subquery comparison. Example: match predicate ::= ( NOT )? ( LIKE | ( SIMILAR TO ) ) < common value expression > ( ESCAPE < character > | ( < lbrace > ESCAPE < character > < rbrace > ) )? Matches based upon a pattern. Example: like regex predicate ::= ( NOT )? LIKE_REGEX < common value expression > A regular expression match. Example: character ::= < string > A single character. Example: between predicate ::= ( NOT )? BETWEEN < common value expression > AND < common value expression > A comparison between two values. Example: is null predicate ::= IS ( NOT )? NULL A null test. Example: in predicate ::= ( NOT )? IN ( < subquery > | ( < lparen > < common value expression > ( < comma > < common value expression > )* < rparen > ) ) A comparison with multiple values. Example: exists predicate ::= EXISTS < subquery > A test if rows exist. Example: group by clause ::= GROUP BY < expression list > Defines the grouping columns Example: having clause ::= HAVING < condition > Search condition applied after grouping. Example: order by clause ::= ORDER BY < sort specification > ( < comma > < sort specification > )* Specifies row ordering. Example: sort specification ::= < sort key > ( ASC | DESC )? ( NULLS ( FIRST | LAST ) )? Defines how to sort on a particular expression Example: sort key ::= < expression > A sort expression. Example: integer parameter ::= < unsigned integer > < qmark > A literal integer or parameter reference to an integer. Example: limit clause ::= ( LIMIT < integer parameter > ( < comma > < integer parameter > )? ) ( OFFSET < integer parameter > ( ROW | ROWS ) ( < fetch clause > )? ) < fetch clause > Limits and/or offsets the resultant rows. Example: fetch clause ::= FETCH ( FIRST | ) ( < integer parameter > )? ( ROW | ROWS ) ONLY ANSI limit. Example: option clause ::= OPTION ( MAKEDEP < identifier > ( < comma > < identifier > )* | MAKENOTDEP < identifier > ( < comma > < identifier > )* | NOCACHE ( < identifier > ( < comma > < identifier > )* )? )* Specifies query options. Example: expression ::= < condition > A value. Example: common value expression ::= ( < numeric value expression > ( < concat_op > < numeric value expression > )* ) Establishes the precedence of concat. Example: numeric value expression ::= ( < term > ( < plus or minus > < term > )* ) Example: plus or minus ::= < plus > < minus > The + or - operator. Example: term ::= ( < value expression primary > ( < star or slash > < value expression primary > )* ) A numeric term Example: star or slash ::= < star > < slash > The * or / operator. Example: value expression primary ::= < non numeric literal > ( < plus or minus > )? ( < unsigned numeric literal > | < unsigned value expression primary > ) A simple value expression. Example: unsigned value expression primary ::= < qmark > ( < dollar > < unsigned integer > ) ( < escaped function > < function > < rbrace > ) ( ( < text aggreate function > | < standard aggregate function > | < ordered aggreate function > ) ( < filter clause > )? ( < window specification > )? ) ( < analytic aggregate function > ( < filter clause > )? < window specification > ) ( < function > ( < window specification > )? ) ( ( < identifier > | < non-reserved identifier > ) ( < lsbrace > < common value expression > < rsbrace > )? ) < subquery > ( < lparen > < expression > < rparen > ( < lsbrace > < common value expression > < rsbrace > )? ) < searched case expression > < case expression > An unsigned simple value expression. Example: window specification ::= OVER < lparen > ( PARTITION BY < expression list > )? ( < order by clause > )? < rparen > The window specification for an analytical or windowed aggregate function. Example: case expression ::= CASE < expression > ( WHEN < expression > THEN < expression > )+ ( ELSE < expression > )? END If/then/else chain using a common search predicand. Example: searched case expression ::= CASE ( WHEN < condition > THEN < expression > )+ ( ELSE < expression > )? END If/then/else chain using multiple search conditions. Example: function ::= ( CONVERT < lparen > < expression > < comma > < data type > < rparen > ) ( CAST < lparen > < expression > AS < data type > < rparen > ) ( SUBSTRING < lparen > < expression > ( ( FROM < expression > ( FOR < expression > )? ) | ( < comma > < expression list > ) ) < rparen > ) ( EXTRACT < lparen > ( YEAR | MONTH | DAY | HOUR | MINUTE | SECOND ) FROM < expression > < rparen > ) ( TRIM < lparen > ( ( ( ( LEADING | TRAILING | BOTH ) ( < expression > )? ) | < expression > ) FROM )? < expression > < rparen > ) ( ( TO_CHARS | TO_BYTES ) < lparen > < expression > < comma > < string > < rparen > ) ( ( TIMESTAMPADD | TIMESTAMPDIFF ) < lparen > < time interval > < comma > < expression > < comma > < expression > < rparen > ) < querystring function > ( ( LEFT | RIGHT | CHAR | USER | YEAR | MONTH | HOUR | MINUTE | SECOND | XMLCONCAT | XMLCOMMENT ) < lparen > ( < expression list > )? < rparen > ) ( ( TRANSLATE | INSERT ) < lparen > ( < expression list > )? < rparen > ) < xml parse > < xml element > ( XMLPI < lparen > ( ( NAME )? < identifier > ) ( < comma > < expression > )? < rparen > ) < xml forest > < json object > < xml serialize > < xml query > ( < identifier > < lparen > ( ALL | DISTINCT )? ( < expression list > )? ( < order by clause > )? < rparen > ( < filter clause > )? ) Calls a scalar function. Example: xml parse ::= XMLPARSE < lparen > ( DOCUMENT | CONTENT ) < expression > ( WELLFORMED )? < rparen > Parses the given value as XML. Example: querystring function ::= QUERYSTRING < lparen > < expression > ( < comma > < derived column > )* < rparen > Produces a URL query string from the given arguments. Example: xml element ::= XMLELEMENT < lparen > ( ( NAME )? < identifier > ) ( < comma > < xml namespaces > )? ( < comma > < xml attributes > )? ( < comma > < expression > )* < rparen > Creates an XML element. Example: xml attributes ::= XMLATTRIBUTES < lparen > < derived column > ( < comma > < derived column > )* < rparen > Creates attributes for the containing element. Example: json object ::= JSONOBJECT < lparen > < derived column list > < rparen > Produces a JSON object containing name value pairs. Example: derived column list ::= < derived column > ( < comma > < derived column > )* a list of name value pairs Example: xml forest ::= XMLFOREST < lparen > ( < xml namespaces > < comma > )? < derived column list > < rparen > Produces an element for each derived column. Example: xml namespaces ::= XMLNAMESPACES < lparen > < xml namespace element > ( < comma > < xml namespace element > )* < rparen > Defines XML namespace URI/prefix combinations Example: xml namespace element ::= ( < string > AS < identifier > ) ( NO DEFAULT ) ( DEFAULT < string > ) An xml namespace Example: data type ::= ( STRING ( < lparen > < unsigned integer > < rparen > )? ) ( VARCHAR ( < lparen > < unsigned integer > < rparen > )? ) BOOLEAN BYTE TINYINT SHORT SMALLINT ( CHAR ( < lparen > < unsigned integer > < rparen > )? ) INTEGER LONG BIGINT ( BIGINTEGER ( < lparen > < unsigned integer > < rparen > )? ) FLOAT REAL DOUBLE ( BIGDECIMAL ( < lparen > < unsigned integer > ( < comma > < unsigned integer > )? < rparen > )? ) ( DECIMAL ( < lparen > < unsigned integer > ( < comma > < unsigned integer > )? < rparen > )? ) DATE TIME TIMESTAMP OBJECT ( BLOB ( < lparen > < unsigned integer > < rparen > )? ) ( CLOB ( < lparen > < unsigned integer > < rparen > )? ) ( VARBINARY ( < lparen > < unsigned integer > < rparen > )? ) XML A data type. Example: time interval ::= SQL_TSI_FRAC_SECOND SQL_TSI_SECOND SQL_TSI_MINUTE SQL_TSI_HOUR SQL_TSI_DAY SQL_TSI_WEEK SQL_TSI_MONTH SQL_TSI_QUARTER SQL_TSI_YEAR A time interval keyword. Example: non numeric literal ::= < string > < binary string literal > FALSE TRUE UNKNOWN NULL ( < escaped type > < string > < rbrace > ) An escaped or simple non numeric literal. Example: unsigned numeric literal ::= < unsigned integer literal > < approximate numeric literal > < decimal numeric literal > An unsigned numeric literal value. Example: ddl statement ::= ( < create table > | < create procedure > | < option namespace > | < alter options > | < create trigger > ) ( < semicolon > )? A data definition statement. Example: option namespace ::= SET NAMESPACE < string > AS < identifier > A namespace used to shorten the full name of an option key. Example: create procedure ::= CREATE ( VIRTUAL | FOREIGN )? ( PROCEDURE | FUNCTION ) ( < identifier > < lparen > ( < procedure parameter > ( < comma > < procedure parameter > )* )? < rparen > ( RETURNS ( ( ( TABLE )? < lparen > < procedure result column > ( < comma > < procedure result column > )* < rparen > ) | < data type > ) )? ( < options clause > )? ( AS < statement > )? ) Defines a procedure or function invocation. Example: procedure parameter ::= ( IN | OUT | INOUT | VARIADIC )? < identifier > < data type > ( NOT NULL )? ( RESULT )? ( DEFAULT < string > )? ( < options clause > )? A procedure or function parameter Example: procedure result column ::= < identifier > < data type > ( NOT NULL )? ( < options clause > )? A procedure result column. Example: create table ::= CREATE ( FOREIGN TABLE | ( VIRTUAL )? VIEW ) < identifier > ( < create table body > | ( < options clause > )? ) ( AS < query expression > )? Defines a table or view. Example: create foreign temp table ::= CREATE FOREIGN TEMPORARY TABLE < identifier > < create table body > ON < identifier > Defines a foreign temp table Example: create table body ::= ( < lparen > < table element > ( < comma > < table element > )* ( < comma > ( CONSTRAINT < identifier > )? ( < primary key > | < other constraints > | < foreign key > ) ( < options clause > )? )* < rparen > )? ( < options clause > )? Defines a table. Example: foreign key ::= FOREIGN KEY < column list > REFERENCES < identifier > ( < column list > )? Defines the foreign key referential constraint. Example: primary key ::= PRIMARY KEY < column list > Defines the primary key. Example: other constraints ::= ( ( UNIQUE | ACCESSPATTERN ) < column list > ) ( INDEX < lparen > < expression list > < rparen > ) Defines ACCESSPATTERN and UNIQUE constraints and INDEXes. Example: column list ::= < lparen > < identifier > ( < comma > < identifier > )* < rparen > A list of column names. Example: table element ::= < identifier > < data type > ( NOT NULL )? ( AUTO_INCREMENT )? ( ( PRIMARY KEY ) | ( ( UNIQUE )? ( INDEX )? ) ) ( DEFAULT < string > )? ( < options clause > )? Defines a table column. Example: options clause ::= OPTIONS < lparen > < option pair > ( < comma > < option pair > )* < rparen > A list of statement options. Example: option pair ::= < identifier > ( < non numeric literal > | ( < plus or minus > )? < unsigned numeric literal > ) An option key/value pair. Example: alter options ::= ALTER ( VIRTUAL | FOREIGN )? ( TABLE | VIEW | PROCEDURE ) < identifier > ( < alter options list > | < alter column options > ) alters options of tables/procedure Example: alter options list ::= OPTIONS < lparen > ( < add set option > | < drop option > ) ( < comma > ( < add set option > | < drop option > ) )* < rparen > a list of alterations to options Example: drop option ::= DROP < identifier > drop option Example: add set option ::= ( ADD | SET ) < option pair > add or set an option pair Example: alter column options ::= ALTER ( COLUMN | PARAMETER )? < identifier > < alter options list > alters a set of column options Example:

Commands:
'a string'
'it''s a string'
tbl.col
"tbl"."col"
CREATE TRIGGER ON vw INSTEAD OF INSERT AS FOR EACH ROW BEGIN ATOMIC ... END
ALTER VIEW vw AS SELECT col FROM tbl
FOR EACH ROW BEGIN ATOMIC ... END
SELECT * FROM tbl
CREATE TRIGGER ON vw INSTEAD OF INSERT AS FOR EACH ROW BEGIN ATOMIC ... END
CREATE LOCAL TEMPORARY TABLE tmp (col integer)
col string NOT NULL
ERROR 'something went wrong'
RAISE SQLEXCEPTION 'something went wrong'
SQLEXCEPTION 'something went wrong' SQLSTATE '00X', 2
SQLEXCEPTION 'something went wrong' SQLSTATE '00X', 2
IF (x = 5) BEGIN ... END
SELECT * FROM tbl;
BEGIN NOT ATOMIC ... END
BREAK x
RETURN 1
WHILE (var) BEGIN ... END
IF (boolVal) BEGIN variables.x = 1 END ELSE BEGIN variables.x = 2 END
LOOP ON (SELECT * FROM tbl) AS x BEGIN ... END
DECLARE STRING x = 'a'
x = 'b'
CREATE VIRTUAL PROCEDURE BEGIN ... END
EXECUTE IMMEDIATE 'SELECT * FROM tbl' AS x STRING INTO #temp
col1 = 'x', col2 = 'y'
col1 string, col2 integer
{? = CALL proc}
CALL proc('a', 1)
param1 => 'x', param2 => 1
INSERT INTO tbl (col1, col2) VALUES ('a', 1)
col1, 'a',
UPDATE tbl SET (col1 = 'a') WHERE col2 = 1
DELETE FROM tbl WHERE col2 = 1
SELECT * FROM tbl WHERE col2 = 1
X (Y, Z) AS (SELECT 1, 2)
SELECT * FROM tbl ORDER BY col1 LIMIT 1
SELECT * FROM tbl
SELECT * FROM tbl1 INTERSECT SELECT * FROM tbl2
TABLE tbl
SELECT * FROM tbl1
SELECT col1, max(col2) FROM tbl GROUP BY col1
INTO tbl
SELECT *
SELECT DISTINCT a, b, c
tbl.*
tbl.col AS x
tbl.col AS x
tbl.col AS x
tbl.*
XMLAGG(col1) ORDER BY col2
ARRAY_AGG(col1)
TEXTAGG (col1 as t1, col2 as t2 DELIMITER ',' HEADER)
COUNT(*)
ROW_NUMBER()
FILTER (WHERE col1='a')
FROM a, b
FROM a right outer join b, c, d join e".</p>
a
a inner join b
a
a inner join b
a CROSS JOIN b
a inner join b
a
XMLSERIALIZE(col1 AS CLOB)
ARRAYTABLE (col1 COLUMNS x STRING) AS y
TEXTTABLE (file COLUMNS x STRING) AS y
x INTEGER WIDTH 6
XMLQUERY('<a>...</a>' PASSING doc)
OBJECTTABLE('z' PASSING val AS z COLUMNS col OBJECT 'teiid_row') AS X
y integer 'teiid_row_number'
XMLTABLE('/a/b' PASSING doc COLUMNS col XML PATH '.') AS X
y FOR ORDINALITY
12345
(SELECT * FROM tbl) AS x
tbl AS x
WHERE x = 'a'
NOT x = 'a'
col LIKE 'a%'
=
= 'a'
(SELECT * FROM tbl)
= ANY (SELECT col FROM tbl)
LIKE 'a_'
LIKE_REGEX 'a.*b'
'a'
BETWEEN 1 AND 5
IS NOT NULL
IN (1, 5)
EXISTS (SELECT col FROM tbl)
GROUP BY col1, col2
HAVING max(col1) = 5
ORDER BY x, y DESC
col1 NULLS FIRST
col1
?
LIMIT 2
FETCH FIRST 1 ROWS ONLY
OPTION MAKEDEP tbl
col1
'a' || 'b'
1 + 2
+
1 * 2
/
+col1
col1
OVER (PARTION BY col1)
CASE col1 WHEN 'a' THEN 1 ELSE 2
CASE WHEN x = 'a' THEN 1 WHEN y = 'b' THEN 2
func('1', col1)
XMLPARSE(DOCUMENT doc WELLFORMED)
QUERYSTRING(col1 AS opt, col2 AS val)
XMLELEMENT(NAME "root", child)
XMLATTRIBUTES(col1 AS attr1, col2 AS attr2)
JSONOBJECT(col1 AS val1, col2 AS val2)
col1 AS val1, col2 AS val2
XMLFOREST(col1 AS ELEM1, col2 AS ELEM2)
XMLNAMESPACES('http://foo' AS foo)
NO DEFAULT
STRING
SQL_TSI_HOUR
'a'
1.234
CREATE FOREIGN TABLE X (Y STRING)
SET NAMESPACE 'http://foo' AS foo
CREATE FOREIGN PROCEDURE proc (param STRING) RETURNS STRING
OUT x INTEGER
x INTEGER
CREATE VIEW vw AS SELECT 1
CREATE FOREIGN TEMPORARY TABLE t (x string) ON z
(x string) OPTIONS (CARDINALITY 100)
FOREIGN KEY (a, b) REFERENCES tbl (x, y)
PRIMARY KEY (a, b)
UNIQUE (a)
(a, b)
x INTEGER NOT NULL
OPTIONS ('x' 'y', 'a' 'b')
'key' 'value'
ALTER FOREIGN TABLE foo OPTIONS (ADD cardinality 100)
OPTIONS (ADD updatable true)
DROP updatable
ADD updatable true
ALTER COLUMN bar OPTIONS (ADD updatable true)