BNF for SQL Grammar

Reserved Keywords

Keyword Usage

ADD

add set child option, add set option, ADD column

ALL

standard aggregate function, function, query expression body, query term, grant type, select clause, quantified comparison predicate

ALTER

alter, alter column options, alterStatement, grant type

AND

between predicate, boolean term

ANY

standard aggregate function, with role, quantified comparison predicate

ARRAY

ARRAY expression constructor

ARRAY_AGG

ordered aggregate function

AS

alter, ALTER PROCEDURE, ALTER TABLE, ALTER TRIGGER, array table, create procedure, option namespace, create table, create trigger, derived column, dynamic data statement, function, loop statement, xml namespace element, object table, select derived column, table subquery, text table, table name, unescapedFunction, with list element, xml serialize, xml table

ASC

sort specification

ATOMIC

compound statement, for each row trigger action

AUTHENTICATED

with role

BEGIN

compound statement, for each row trigger action

BETWEEN

between predicate

BIGDECIMAL

simple data type

BIGINT

simple data type

BIGINTEGER

simple data type

BLOB

simple data type, xml serialize

BOOLEAN

simple data type

BOTH

function

BREAK

branching statement

BY

group by clause, order by clause, window specification

BYTE

simple data type

CALL

callable statement, call statement

CASE

case expression, searched case expression

CAST

function

CHAR

function, simple data type

CLOB

simple data type, xml serialize

COLUMN

ADD column, alter column options, DROP column, rename column options, Create GRANT, Revoke GRANT

COMMIT

create temporary table

CONSTRAINT

create table body, Create GRANT, Revoke GRANT

CONTINUE

branching statement

CONVERT

function

CREATE

create procedure, create data wrapper aka translator, create database, create foreign temp table, create role, create schema, create server, aka data source, create table, create temporary table, create trigger, procedure body definition

CROSS

cross join

DATE

non numeric literal, simple data type

DAY

function

DECIMAL

simple data type

DECLARE

declare statement

DEFAULT

table element, xml namespace element, object table column, procedure parameter, xml table column

DELETE

alter, ALTER TRIGGER, create trigger, delete statement, grant type

DESC

sort specification

DISTINCT

standard aggregate function, function, if statement, query expression body, query term, select clause

DOUBLE

simple data type

DROP

DROP column, drop option, Drop data wrapper aka translator, drop database, drop option, drop procedure, drop role, drop schema, drop server, aka data source, drop table, drop table, grant type

EACH

for each row trigger action

ELSE

case expression, if statement, searched case expression

END

case expression, compound statement, for each row trigger action, searched case expression

ERROR

raise error statement

ESCAPE

match predicate, text table

EXCEPT

Import foreign schema, query expression body

EXEC

dynamic data statement, call statement

EXECUTE

dynamic data statement, grant type, call statement

EXISTS

exists predicate

FALSE

non numeric literal

FETCH

fetch clause

FILTER

filter clause

FLOAT

simple data type

FOR

for each row trigger action, function, text aggreate function, text table column, xml table column

FOREIGN

alterStatement, create procedure, create data wrapper aka translator, create foreign temp table, create server, aka data source, create table, Drop data wrapper aka translator, drop procedure, drop table, foreign key, Import foreign schema

FROM

delete statement, from clause, function, if statement, Import foreign schema, Revoke GRANT

FULL

qualified table

FUNCTION

create procedure, drop procedure

GEOMETRY

simple data type

GLOBAL

create table, drop table

GRANT

Create GRANT, Revoke GRANT

GROUP

group by clause

HAVING

having clause

HOUR

function

IF

if statement

IMMEDIATE

dynamic data statement

IMPORT

Import another Database, Import foreign schema

IN

procedure parameter, in predicate

INNER

qualified table

INOUT

procedure parameter

INSERT

alter, ALTER TRIGGER, create trigger, function, insert statement, grant type

INTEGER

simple data type

INTERSECT

query term

INTO

dynamic data statement, Import foreign schema, insert statement, into clause

IS

if statement, is null predicate

JOIN

cross join, make dep options, qualified table

LANGUAGE

object table, grant type

LATERAL

table subquery

LEADING

function

LEAVE

branching statement

LEFT

function, qualified table

LIKE

match predicate

LIKE_REGEX

like regex predicate

LIMIT

Import foreign schema, limit clause

LOCAL

create foreign temp table, create temporary table

LONG

simple data type

LOOP

loop statement

MAKEDEP

option clause, table primary

MAKEIND

option clause, table primary

MAKENOTDEP

option clause, table primary

MERGE

insert statement

MINUTE

function

MONTH

function

NO

make dep options, xml namespace element, text aggreate function, text table column, text table

NOCACHE

option clause

NOT

alter column options, between predicate, compound statement, table element, if statement, is null predicate, match predicate, boolean factor, procedure parameter, procedure result column, like regex predicate, in predicate, temporary table element

NULL

alter column options, table element, is null predicate, non numeric literal, procedure parameter, procedure result column, temporary table element, xml query

OBJECT

simple data type

OF

alter, ALTER TRIGGER, create trigger

OFFSET

limit clause

ON

alter, ALTER TRIGGER, create foreign temp table, create temporary table, create trigger, Create GRANT, loop statement, qualified table, Revoke GRANT, xml query

ONLY

fetch clause

OPTION

option clause

OPTIONS

alter child options list, alter options list, options clause

OR

boolean value expression

ORDER

Create GRANT, order by clause, Revoke GRANT

OUT

procedure parameter

OUTER

qualified table

OVER

window specification

PARAMETER

alter column options, rename column options

PARTITION

window specification

PRIMARY

table element, create temporary table, primary key

PROCEDURE

alter, ALTER PROCEDURE, create procedure, drop procedure, Create GRANT, procedure body definition, Revoke GRANT

REAL

simple data type

REFERENCES

foreign key

RETURN

assignment statement, return statement, data statement

RETURNS

create procedure

REVOKE

Revoke GRANT

RIGHT

function, qualified table

ROLLUP

group by clause

ROW

fetch clause, for each row trigger action, limit clause, text table

ROWS

create temporary table, fetch clause, limit clause

SECOND

function

SELECT

grant type, select clause

SERVER

ALTER SERVER, create schema, create server, aka data source, drop server, aka data source, Import foreign schema

SET

add set child option, add set option, option namespace, update statement, use schema

SHORT

simple data type

SIMILAR

match predicate

SMALLINT

simple data type

SOME

standard aggregate function, quantified comparison predicate

SQLEXCEPTION

sql exception

SQLSTATE

sql exception

SQLWARNING

raise statement

STRING

dynamic data statement, simple data type, xml serialize

TABLE

ALTER TABLE, create procedure, create foreign temp table, create table, create temporary table, drop table, drop table, Create GRANT, query primary, grant type, Revoke GRANT, table subquery

TEMPORARY

create foreign temp table, create table, create temporary table, drop table, grant type

THEN

case expression, searched case expression

TIME

non numeric literal, simple data type

TIMESTAMP

non numeric literal, simple data type

TINYINT

simple data type

TO

rename column options, Create GRANT, Import foreign schema, match predicate

TRAILING

function

TRANSLATE

function

TRIGGER

alter, ALTER TRIGGER, create trigger

TRUE

non numeric literal

UNION

cross join, query expression body

UNIQUE

other constraints, table element

UNKNOWN

non numeric literal

UPDATE

alter, ALTER TRIGGER, create trigger, dynamic data statement, grant type, update statement

USER

function

USING

dynamic data statement

VALUES

query primary

VARBINARY

simple data type, xml serialize

VARCHAR

simple data type, xml serialize

VIRTUAL

alterStatement, create procedure, create schema, create table, drop procedure, drop schema, drop table, procedure body definition

WHEN

case expression, searched case expression

WHERE

filter clause, where clause

WHILE

while statement

WITH

assignment statement, create role, Import another Database, query expression, with role, data statement

WITHOUT

assignment statement, data statement

WRAPPER

ALTER DATA WRAPPER, create data wrapper aka translator, create server, aka data source, Drop data wrapper aka translator

XML

simple data type

XMLAGG

ordered aggregate function

XMLATTRIBUTES

xml attributes

XMLCAST

unescapedFunction

XMLCOMMENT

function

XMLCONCAT

function

XMLELEMENT

xml element

XMLEXISTS

xml query

XMLFOREST

xml forest

XMLNAMESPACES

xml namespaces

XMLPARSE

xml parse

XMLPI

function

XMLQUERY

xml query

XMLSERIALIZE

xml serialize

XMLTABLE

xml table

XMLTEXT

function

YEAR

function

Non-Reserved Keywords

Name Usage

ACCESS

Import another Database, non-reserved identifier

ACCESSPATTERN

other constraints, non-reserved identifier

AFTER

alter, create trigger, non-reserved identifier

ARRAYTABLE

array table, non-reserved identifier

AUTO_INCREMENT

alter column options, table element, non-reserved identifier

AVG

standard aggregate function, non-reserved identifier

CHAIN

sql exception, non-reserved identifier

COLUMNS

array table, non-reserved identifier, object table, text table, xml table

CONDITION

Create GRANT, non-reserved identifier, Revoke GRANT

CONTENT

non-reserved identifier, xml parse, xml serialize

CONTROL

Import another Database, non-reserved identifier

COUNT

standard aggregate function, non-reserved identifier

DATA

ALTER DATA WRAPPER, create data wrapper aka translator, create server, aka data source, Drop data wrapper aka translator, non-reserved identifier

DATABASE

ALTER DATABASE, create database, drop database, Create GRANT, Import another Database, non-reserved identifier, Revoke GRANT, use database

DELIMITER

non-reserved identifier, text aggreate function, text table

DENSE_RANK

analytic aggregate function, non-reserved identifier

DISABLED

alter, ALTER TRIGGER, non-reserved identifier

DOCUMENT

non-reserved identifier, xml parse, xml serialize

EMPTY

non-reserved identifier, xml query

ENABLED

alter, ALTER TRIGGER, non-reserved identifier

ENCODING

non-reserved identifier, text aggreate function, xml serialize

EVERY

standard aggregate function, non-reserved identifier

EXCEPTION

compound statement, declare statement, non-reserved identifier

EXCLUDING

non-reserved identifier, xml serialize

EXTRACT

function, non-reserved identifier

FIRST

fetch clause, non-reserved identifier, sort specification

HEADER

non-reserved identifier, text aggreate function, text table column, text table

INCLUDING

non-reserved identifier, xml serialize

INDEX

other constraints, table element, non-reserved identifier

INSTEAD

alter, ALTER TRIGGER, create trigger, non-reserved identifier

JAAS

non-reserved identifier, with role

JSONARRAY_AGG

non-reserved identifier, ordered aggregate function

JSONOBJECT

json object, non-reserved identifier

KEY

table element, create temporary table, foreign key, non-reserved identifier, primary key

LAST

non-reserved identifier, sort specification

MASK

Create GRANT, non-reserved identifier, Revoke GRANT

MAX

standard aggregate function, make dep options, non-reserved identifier

MIN

standard aggregate function, non-reserved identifier

NAME

function, non-reserved identifier, xml element

NAMESPACE

option namespace, non-reserved identifier

NEXT

fetch clause, non-reserved identifier

NONE

non-reserved identifier

NULLS

non-reserved identifier, sort specification

OBJECTTABLE

non-reserved identifier, object table

ORDINALITY

non-reserved identifier, text table column, xml table column

PASSING

non-reserved identifier, object table, xml query, xml query, xml table

PATH

non-reserved identifier, xml table column

PRESERVE

create temporary table, non-reserved identifier

PRIVILEGES

non-reserved identifier, grant type

QUERYSTRING

non-reserved identifier, querystring function

QUOTE

non-reserved identifier, text aggreate function, text table

RAISE

non-reserved identifier, raise statement

RANK

analytic aggregate function, non-reserved identifier

RENAME

rename column options, non-reserved identifier

REPOSITORY

Import foreign schema, non-reserved identifier

RESULT

non-reserved identifier, procedure parameter

ROLE

create role, drop role, non-reserved identifier, with role

ROW_NUMBER

analytic aggregate function, non-reserved identifier

SCHEMA

create schema, drop schema, Create GRANT, Import foreign schema, non-reserved identifier, Revoke GRANT, use schema

SELECTOR

non-reserved identifier, text table column, text table

SERIAL

alter column options, table element, non-reserved identifier, temporary table element

SKIP

non-reserved identifier, text table

SQL_TSI_DAY

time interval, non-reserved identifier

SQL_TSI_FRAC_SECOND

time interval, non-reserved identifier

SQL_TSI_HOUR

time interval, non-reserved identifier

SQL_TSI_MINUTE

time interval, non-reserved identifier

SQL_TSI_MONTH

time interval, non-reserved identifier

SQL_TSI_QUARTER

time interval, non-reserved identifier

SQL_TSI_SECOND

time interval, non-reserved identifier

SQL_TSI_WEEK

time interval, non-reserved identifier

SQL_TSI_YEAR

time interval, non-reserved identifier

STDDEV_POP

standard aggregate function, non-reserved identifier

STDDEV_SAMP

standard aggregate function, non-reserved identifier

SUBSTRING

function, non-reserved identifier

SUM

standard aggregate function, non-reserved identifier

TEXTAGG

non-reserved identifier, text aggreate function

TEXTTABLE

non-reserved identifier, text table

TIMESTAMPADD

function, non-reserved identifier

TIMESTAMPDIFF

function, non-reserved identifier

TO_BYTES

function, non-reserved identifier

TO_CHARS

function, non-reserved identifier

TRANSLATOR

ALTER DATA WRAPPER, create data wrapper aka translator, create server, aka data source, Drop data wrapper aka translator, non-reserved identifier

TRIM

function, non-reserved identifier, text table column, text table

TYPE

alter column options, create data wrapper aka translator, create server, aka data source, non-reserved identifier

UPSERT

insert statement, non-reserved identifier

USE

non-reserved identifier, use database

VARIADIC

non-reserved identifier, procedure parameter

VAR_POP

standard aggregate function, non-reserved identifier

VAR_SAMP

standard aggregate function, non-reserved identifier

VERSION

create database, create server, aka data source, drop database, Import another Database, non-reserved identifier, use database, xml serialize

VIEW

alter, ALTER TABLE, create table, drop table, non-reserved identifier

WELLFORMED

non-reserved identifier, xml parse

WIDTH

non-reserved identifier, text table column

XMLDECLARATION

non-reserved identifier, xml serialize

Reserved Keywords For Future Use

ALLOCATE

ARE

ASENSITIVE

ASYMETRIC

AUTHORIZATION

BINARY

CALLED

CASCADED

CHARACTER

CHECK

CLOSE

COLLATE

CONNECT

CORRESPONDING

CRITERIA

CURRENT_DATE

CURRENT_TIME

CURRENT_TIMESTAMP

CURRENT_USER

CURSOR

CYCLE

DATALINK

DEALLOCATE

DEC

DEREF

DESCRIBE

DETERMINISTIC

DISCONNECT

DLNEWCOPY

DLPREVIOUSCOPY

DLURLCOMPLETE

DLURLCOMPLETEONLY

DLURLCOMPLETEWRITE

DLURLPATH

DLURLPATHONLY

DLURLPATHWRITE

DLURLSCHEME

DLURLSERVER

DLVALUE

DYNAMIC

ELEMENT

EXTERNAL

FREE

GET

HAS

HOLD

IDENTITY

INDICATOR

INPUT

INSENSITIVE

INT

INTERVAL

ISOLATION

LARGE

LOCALTIME

LOCALTIMESTAMP

MATCH

MEMBER

METHOD

MODIFIES

MODULE

MULTISET

NATIONAL

NATURAL

NCHAR

NCLOB

NEW

NUMERIC

OLD

OPEN

OUTPUT

OVERLAPS

PRECISION

PREPARE

RANGE

READS

RECURSIVE

REFERENCING

RELEASE

ROLLBACK

SAVEPOINT

SCROLL

SEARCH

SENSITIVE

SESSION_USER

SPECIFIC

SPECIFICTYPE

SQL

START

STATIC

SUBMULTILIST

SYMETRIC

SYSTEM

SYSTEM_USER

TIMEZONE_HOUR

TIMEZONE_MINUTE

TRANSLATION

TREAT

VALUE

VARYING

WHENEVER

WINDOW

WITHIN

XMLBINARY

XMLDOCUMENT

Tokens

Name Definition Usage

all in group identifier

<identifier> <period> <star>

all in group

binary string literal

"X" | "x" "\'" (<hexit> <hexit>)+ "\'"

non numeric literal

colon

":"

make dep options, statement

comma

","

alter child options list, alter options list, ARRAY expression constructor, column list, create procedure, typed element list, create table body, create temporary table, derived column list, sql exception, named parameter list, expression list, from clause, function, Create GRANT, limit clause, nested expression, object table, option clause, options clause, order by clause, simple data type, query expression, query primary, querystring function, identifier list, Revoke GRANT, select clause, set clause list, in predicate, text aggreate function, text table, xml attributes, xml element, xml query, xml forest, xml namespaces, xml query, xml table

concat_op

"||"

common value expression

decimal numeric literal

(<digit>)* <period> <unsigned integer literal>

unsigned numeric literal

digit

\["0"\-"9"\]

dollar

"$"

parameter reference

double_amp_op

"&&"

common value expression

eq

"="

assignment statement, callable statement, declare statement, named parameter list, comparison operator, set clause list

escaped function

"{" "fn"

unsigned value expression primary

escaped join

"{" "oj"

table reference

escaped type

"{" ("d" | "t" | "ts" | "b")

non numeric literal

approximate numeric literal

<digit> <period> <unsigned integer literal> \["e","E"\] (<plus> | <minus>)? <unsigned integer literal>

unsigned numeric literal

ge

">="

comparison operator

gt

">"

named parameter list, comparison operator

hexit

\["a"\-"f","A"\-"F"\] | <digit>

identifier

<quoted_id> (<period> <quoted_id>)*

identifier, unsigned value expression primary

id_part

("@" | "#" | <letter>) (<letter> | "_" | <digit>)*

lbrace

"{"

callable statement, match predicate

le

"⇐"

comparison operator

letter

\["a"\-"z","A"\-"Z"\] | \["\u0153"\-"\ufffd"\]

lparen

"("

standard aggregate function, alter child options list, alter options list, analytic aggregate function, array table, callable statement, column list, other constraints, create procedure, create table body, create temporary table, filter clause, function, group by clause, if statement, Import foreign schema, json object, loop statement, make dep options, nested expression, object table, options clause, ordered aggregate function, simple data type, query primary, querystring function, in predicate, call statement, subquery, quantified comparison predicate, table subquery, table primary, text aggreate function, text table, unescapedFunction, while statement, window specification, with list element, xml attributes, xml element, xml query, xml forest, xml namespaces, xml parse, xml query, xml serialize, xml table

lsbrace

"["

ARRAY expression constructor, data type, value expression primary

lt

"<"

comparison operator

minus

"-"

plus or minus

ne

"<>"

comparison operator

ne2

"!="

comparison operator

period

"."

plus

"+"

plus or minus

qmark

"?"

callable statement, parameter reference

quoted_id

<id_part> | "\"" ("\"\"" | ~\["\""\])+ "\""

rbrace

"}"

callable statement, match predicate, non numeric literal, table reference, unsigned value expression primary

rparen

")"

standard aggregate function, alter child options list, alter options list, analytic aggregate function, array table, callable statement, column list, other constraints, create procedure, create table body, create temporary table, filter clause, function, group by clause, if statement, Import foreign schema, json object, loop statement, make dep options, nested expression, object table, options clause, ordered aggregate function, simple data type, query primary, querystring function, in predicate, call statement, subquery, quantified comparison predicate, table subquery, table primary, text aggreate function, text table, unescapedFunction, while statement, window specification, with list element, xml attributes, xml element, xml query, xml forest, xml namespaces, xml parse, xml query, xml serialize, xml table

rsbrace

"]"

ARRAY expression constructor, data type, value expression primary

semicolon

";"

delimited statement

slash

"/"

star or slash

star

"*"

standard aggregate function, dynamic data statement, select clause, star or slash

string literal

("N" | "E")? "\'" ("\'\'" | ~\["\'"\])* "\'"

string

unsigned integer literal

(<digit>)+

unsigned integer, unsigned numeric literal

Production Cross-Reference

Name Usage

add set child option

alter child options list

add set option

alter options list

standard aggregate function

unescapedFunction

all in group

select sublist

alter

directly executable statement

ADD column

ALTER TABLE

alter child option pair

add set child option

alter child options list

alter column options

alter column options

ALTER PROCEDURE, ALTER TABLE

ALTER DATABASE

alterStatement

DROP column

ALTER TABLE

alter option pair

add set option

alter options list

ALTER DATABASE, ALTER PROCEDURE, ALTER SERVER, ALTER TABLE, ALTER DATA WRAPPER

ALTER PROCEDURE

alterStatement

rename column options

ALTER PROCEDURE, ALTER TABLE

ALTER SERVER

alterStatement

alterStatement

ddl statement

ALTER TABLE

alterStatement

ALTER DATA WRAPPER

alterStatement

ALTER TRIGGER

alterStatement

analytic aggregate function

unescapedFunction

ARRAY expression constructor

unsigned value expression primary

array table

table primary

assignment statement

delimited statement

assignment statement operand

assignment statement, declare statement

between predicate

boolean primary

boolean primary

filter clause, boolean factor

branching statement

delimited statement

case expression

unsigned value expression primary

character

match predicate, text aggreate function, text table

column list

other constraints, create temporary table, foreign key, insert statement, primary key, with list element

common value expression

between predicate, boolean primary, comparison predicate, sql exception, match predicate, like regex predicate, in predicate, text table

comparison predicate

boolean primary

boolean term

boolean value expression

boolean value expression

condition

compound statement

statement, directly executable statement

other constraints

create table body

table element

ADD column, create table body

create procedure

ddl statement

create data wrapper aka translator

ddl statement

create database

ddl statement

typed element list

array table, dynamic data statement

create foreign temp table

directly executable statement

option namespace

ddl statement

create role

ddl statement

create schema

ddl statement

create server, aka data source

ddl statement

create table

ddl statement

create table body

create foreign temp table, create table

create temporary table

directly executable statement

create trigger

ddl statement, directly executable statement

condition

expression, having clause, if statement, qualified table, searched case expression, where clause, while statement

cross join

joined table

ddl statement

ddl statement

declare statement

delimited statement

delete statement

assignment statement operand, directly executable statement

delimited statement

statement

derived column

derived column list, object table, querystring function, text aggreate function, xml attributes, xml query, xml query, xml table

derived column list

json object, xml forest

drop option

alter child options list

Drop data wrapper aka translator

ddl statement

drop database

ddl statement

drop option

alter options list

drop procedure

ddl statement

drop role

ddl statement

drop schema

ddl statement

drop server, aka data source

ddl statement

drop table

directly executable statement

drop table

ddl statement

dynamic data statement

data statement

raise error statement

delimited statement

sql exception

assignment statement operand, exception reference

exception reference

sql exception, raise statement

named parameter list

callable statement, call statement

exists predicate

boolean primary

expression

standard aggregate function, ARRAY expression constructor, assignment statement operand, case expression, table element, derived column, dynamic data statement, raise error statement, named parameter list, expression list, function, nested expression, object table column, ordered aggregate function, procedure parameter, querystring function, return statement, searched case expression, select derived column, set clause list, sort key, quantified comparison predicate, unescapedFunction, xml table column, xml element, xml parse, xml serialize

expression list

callable statement, other constraints, function, group by clause, query primary, call statement, window specification

fetch clause

limit clause

filter clause

function, unescapedFunction

for each row trigger action

alter, ALTER TRIGGER, create trigger

foreign key

create table body

from clause

query

function

unescapedFunction, unsigned value expression primary

Create GRANT

ddl statement

group by clause

query

having clause

query

identifier

alter, alter child option pair, alter column options, ALTER DATABASE, DROP column, alter option pair, ALTER PROCEDURE, rename column options, ALTER SERVER, ALTER TABLE, ALTER DATA WRAPPER, ALTER TRIGGER, array table, assignment statement, branching statement, callable statement, column list, compound statement, table element, create procedure, create data wrapper aka translator, create database, typed element list, create foreign temp table, option namespace, create role, create schema, create server, aka data source, create table, create table body, create temporary table, create trigger, declare statement, delete statement, derived column, drop option, Drop data wrapper aka translator, drop database, drop option, drop procedure, drop role, drop schema, drop server, aka data source, drop table, drop table, dynamic data statement, exception reference, named parameter list, foreign key, function, Create GRANT, if statement, Import another Database, Import foreign schema, insert statement, into clause, loop statement, xml namespace element, object table column, object table, option clause, option pair, procedure parameter, procedure result column, query primary, identifier list, Revoke GRANT, select derived column, set clause list, statement, call statement, table subquery, temporary table element, text aggreate function, text table column, text table, table name, update statement, use database, use schema, with list element, xml table column, xml element, xml serialize, xml table

if statement

statement

Import another Database

ddl statement

Import foreign schema

ddl statement

insert statement

assignment statement operand, directly executable statement

integer parameter

fetch clause, limit clause

unsigned integer

dynamic data statement, Create GRANT, integer parameter, make dep options, parameter reference, simple data type, Revoke GRANT, text table column, text table

time interval

function

into clause

query

is null predicate

boolean primary

joined table

table primary, table reference

json object

function

limit clause

query expression body

loop statement

statement

make dep options

option clause, table primary

match predicate

boolean primary

xml namespace element

xml namespaces

nested expression

unsigned value expression primary

non numeric literal

alter child option pair, alter option pair, option pair, value expression primary

non-reserved identifier

identifier, unsigned value expression primary

boolean factor

boolean term

object table column

object table

object table

table primary

comparison operator

comparison predicate, quantified comparison predicate

option clause

callable statement, delete statement, insert statement, query expression body, call statement, update statement

option pair

options clause

options clause

table element, create procedure, create data wrapper aka translator, create database, create schema, create server, aka data source, create table, create table body, Import foreign schema, procedure parameter, procedure result column

order by clause

function, ordered aggregate function, query expression body, text aggreate function, window specification

ordered aggregate function

unescapedFunction

parameter reference

unsigned value expression primary

data type

alter column options, table element, create procedure, typed element list, declare statement, function, object table column, procedure parameter, procedure result column, temporary table element, text table column, unescapedFunction, xml table column

simple data type

data type

numeric value expression

common value expression, value expression primary

plus or minus

alter child option pair, alter option pair, option pair, numeric value expression, value expression primary

primary key

create table body

procedure parameter

create procedure

procedure result column

create procedure

qualified table

joined table

query

query primary

query expression

alter, ALTER TABLE, assignment statement operand, create table, insert statement, loop statement, subquery, table subquery, directly executable statement, with list element

query expression body

query expression, query primary

query primary

query term

querystring function

function

query term

query expression body

raise statement

delimited statement

identifier list

create schema, Import foreign schema, with role

grant type

Create GRANT, Revoke GRANT

with role

create role

like regex predicate

boolean primary

return statement

delimited statement

Revoke GRANT

ddl statement

searched case expression

unsigned value expression primary

select clause

query

select derived column

select sublist

select sublist

select clause

set clause list

dynamic data statement, update statement

in predicate

boolean primary

sort key

sort specification

sort specification

order by clause

data statement

delimited statement

statement

alter, ALTER PROCEDURE, compound statement, create procedure, for each row trigger action, if statement, loop statement, procedure body definition, while statement

call statement

assignment statement, subquery, table subquery, directly executable statement

string

character, create database, option namespace, create server, aka data source, drop database, function, Create GRANT, Import another Database, xml namespace element, non numeric literal, object table column, object table, Revoke GRANT, text table column, text table, use database, xml table column, xml query, xml query, xml serialize, xml table

subquery

exists predicate, in predicate, quantified comparison predicate, unsigned value expression primary

quantified comparison predicate

boolean primary

table subquery

table primary

temporary table element

create temporary table

table primary

cross join, joined table

table reference

from clause, qualified table

text aggreate function

unescapedFunction

text table column

text table

text table

table primary

term

numeric value expression

star or slash

term

table name

table primary

unescapedFunction

unsigned value expression primary

unsigned numeric literal

alter child option pair, alter option pair, option pair, value expression primary

unsigned value expression primary

integer parameter, value expression primary

update statement

assignment statement operand, directly executable statement

use database

ddl statement

use schema

ddl statement

directly executable statement

data statement

value expression primary

array table, term

where clause

delete statement, query, update statement

while statement

statement

window specification

unescapedFunction

with list element

query expression

xml attributes

xml element

xml table column

xml table

xml element

function

xml query

boolean primary

xml forest

function

xml namespaces

xml element, xml query, xml forest, xml query, xml table

xml parse

function

xml query

function

xml serialize

function

xml table

table primary

Productions

string ::=

A string literal value. Use '' to escape ' in the string.

Example:

'a string'
'it''s a string'

identifier ::=

Partial or full name of a single entity.

Example:

tbl.col
"tbl"."col"

create trigger ::=

Creates a trigger action on the given target.

Example:

CREATE TRIGGER ON vw INSTEAD OF INSERT AS FOR EACH ROW BEGIN ATOMIC ... END

alter ::=

Alter the given target.

Example:

ALTER VIEW vw AS SELECT col FROM tbl

for each row trigger action ::=

Defines an action to perform on each row.

Example:

FOR EACH ROW BEGIN ATOMIC ... END

drop table ::=

Drop the given table.

Example:

DROP TABLE #temp

create temporary table ::=

Creates a temporary table.

Example:

CREATE LOCAL TEMPORARY TABLE tmp (col integer)

temporary table element ::=

Defines a temporary table column.

Example:

col string NOT NULL

raise error statement ::=

Raises an error with the given message.

Example:

ERROR 'something went wrong'

raise statement ::=

Raises an error or warning with the given message.

Example:

RAISE SQLEXCEPTION 'something went wrong'

exception reference ::=

a reference to an exception

Example:

SQLEXCEPTION 'something went wrong' SQLSTATE '00X', 2

sql exception ::=

creates a sql exception or warning with the specified message, state, and code

Example:

SQLEXCEPTION 'something went wrong' SQLSTATE '00X', 2

statement ::=

A procedure statement.

Example:

IF (x = 5) BEGIN ... END

delimited statement ::=

A procedure statement terminated by ;.

Example:

SELECT * FROM tbl;

compound statement ::=

A procedure statement block contained in BEGIN END.

Example:

BEGIN NOT ATOMIC ... END

branching statement ::=

A procedure branching control statement, which typically specifies a label to return control to.

Example:

BREAK x

return statement ::=

A return statement.

Example:

RETURN 1

while statement ::=

A procedure while statement that executes until its condition is false.

Example:

WHILE (var) BEGIN ... END

loop statement ::=

A procedure loop statement that executes over the given cursor.

Example:

LOOP ON (SELECT * FROM tbl) AS x BEGIN ... END

if statement ::=

A procedure loop statement that executes over the given cursor.

Example:

IF (boolVal) BEGIN variables.x = 1 END ELSE BEGIN variables.x = 2 END

declare statement ::=

A procedure declaration statement that creates a variable and optionally assigns a value.

Example:

DECLARE STRING x = 'a'

assignment statement ::=

Assigns a variable a value in a procedure.

Example:

x = 'b'

assignment statement operand ::=

A value or command that can be used in an assignment. {note}All assigments except for expression are deprecated.{note}


data statement ::=

A procedure statement that executes a SQL statement. An update statement can have its update count accessed via the ROWCOUNT variable.


procedure body definition ::=

Defines a procedure body on a Procedure metadata object.

Example:

BEGIN ... END

dynamic data statement ::=

A procedure statement that can execute arbitrary sql.

Example:

EXECUTE IMMEDIATE 'SELECT * FROM tbl' AS x STRING INTO #temp

set clause list ::=

A list of value assignments.

Example:

col1 = 'x', col2 = 'y' ...

typed element list ::=

A list of typed elements.

Example:

col1 string, col2 integer ...

callable statement ::=

A callable statement defined using JDBC escape syntax.

Example:

{? = CALL proc}

call statement ::=

Executes the procedure with the given parameters.

Example:

CALL proc('a', 1)

named parameter list ::=

A list of named parameters.

Example:

param1 => 'x', param2 => 1

insert statement ::=

Inserts values into the given target.

Example:

INSERT INTO tbl (col1, col2) VALUES ('a', 1)

expression list ::=

A list of expressions.

Example:

col1, 'a', ...

update statement ::=

Update values in the given target.

Example:

UPDATE tbl SET (col1 = 'a') WHERE col2 = 1

delete statement ::=

Delete rows from the given target.

Example:

DELETE FROM tbl WHERE col2 = 1

query expression ::=

A declarative query for data.

Example:

SELECT * FROM tbl WHERE col2 = 1

with list element ::=

A query expression for use in the enclosing query.

Example:

X (Y, Z) AS (SELECT 1, 2)

query expression body ::=

The body of a query expression, which can optionally be ordered and limited.

Example:

SELECT * FROM tbl ORDER BY col1 LIMIT 1

query term ::=

Used to establish INTERSECT precedence.

Example:

SELECT * FROM tbl
SELECT * FROM tbl1 INTERSECT SELECT * FROM tbl2

query primary ::=

A declarative source of rows.

Example:

TABLE tbl
SELECT * FROM tbl1

query ::=

A SELECT query.

Example:

SELECT col1, max(col2) FROM tbl GROUP BY col1

into clause ::=

Used to direct the query into a table. {note}This is deprecated. Use INSERT INTO with a query expression instead.{note}

Example:

INTO tbl

select clause ::=

The columns returned by a query. Can optionally be distinct.

Example:

SELECT *
SELECT DISTINCT a, b, c

select sublist ::=

An element in the select clause

Example:

tbl.*
tbl.col AS x

select derived column ::=

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.{note}

Example:

tbl.col AS x

derived column ::=

An optionally named expression.

Example:

tbl.col AS x

all in group ::=

A select sublist that can select all columns from the given group.

Example:

tbl.*

ordered aggregate function ::=

An aggregate function that can optionally be ordered.

Example:

XMLAGG(col1) ORDER BY col2
ARRAY_AGG(col1)

text aggreate function ::=

An aggregate function for creating separated value clobs.

Example:

TEXTAGG (col1 as t1, col2 as t2 DELIMITER ',' HEADER)

standard aggregate function ::=

A standard aggregate function.

Example:

COUNT(*)

analytic aggregate function ::=

An analytic aggregate function.

Example:

ROW_NUMBER()

filter clause ::=

An aggregate filter clause applied prior to accumulating the value.

Example:

FILTER (WHERE col1='a')

from clause ::=

A query from clause containing a list of table references.

Example:

FROM a, b
FROM a right outer join b, c, d join e".</p>

table reference ::=

An optionally escaped joined table.

Example:

a
a inner join b

joined table ::=

A table or join.

Example:

a
a inner join b

cross join ::=

A cross join.

Example:

a CROSS JOIN b

qualified table ::=

An INNER or OUTER join.

Example:

a inner join b

table primary ::=

A single source of rows.

Example:

a

make dep options ::=

options for the make dep hint

Example:

(min:10000)

xml serialize ::=

Serializes an XML value.

Example:

XMLSERIALIZE(col1 AS CLOB)

array table ::=

The ARRAYTABLE table function creates tabular results from arrays. It can be used as a nested table reference.

Example:

ARRAYTABLE (col1 COLUMNS x STRING) AS y

text table ::=

The TEXTTABLE table function creates tabular results from text. It can be used as a nested table reference.

Example:

TEXTTABLE (file COLUMNS x STRING) AS y

text table column ::=

A text table column.

Example:

x INTEGER WIDTH 6

xml query ::=

Executes an XQuery to return an XML result.

Example:

XMLQUERY('<a>...</a>' PASSING doc)

xml query ::=

Executes an XQuery to return an XML result.

Example:

XMLQUERY('<a>...</a>' PASSING doc)

object table ::=

Returns table results by processing a script.

Example:

OBJECTTABLE('z' PASSING val AS z COLUMNS col OBJECT 'teiid_row') AS X

object table column ::=

object table column.

Example:

y integer 'teiid_row_number'

xml table ::=

Returns table results by processing an XQuery.

Example:

XMLTABLE('/a/b' PASSING doc COLUMNS col XML PATH '.') AS X

xml table column ::=

XML table column.

Example:

y FOR ORDINALITY

unsigned integer ::=

An unsigned interger value.

Example:

12345

table subquery ::=

A table defined by a subquery.

Example:

(SELECT * FROM tbl) AS x

table name ::=

A table named in the FROM clause.

Example:

tbl AS x

where clause ::=

Specifies a search condition

Example:

WHERE x = 'a'

condition ::=

A boolean expression.


boolean value expression ::=

An optionally ORed boolean expression.


boolean term ::=

An optional ANDed boolean factor.


boolean factor ::=

A boolean factor.

Example:

NOT x = 'a'

comparison operator ::=

A comparison operator.

Example:

=

comparison predicate ::=

A value comparison.

Example:

= 'a'

subquery ::=

A subquery.

Example:

(SELECT * FROM tbl)

quantified comparison predicate ::=

A subquery comparison.

Example:

= ANY (SELECT col FROM tbl)

match predicate ::=

Matches based upon a pattern.

Example:

LIKE 'a_'

like regex predicate ::=

A regular expression match.

Example:

LIKE_REGEX 'a.*b'

character ::=

A single character.

Example:

'a'

between predicate ::=

A comparison between two values.

Example:

BETWEEN 1 AND 5

is null predicate ::=

A null test.

Example:

IS NOT NULL

in predicate ::=

A comparison with multiple values.

Example:

IN (1, 5)

exists predicate ::=

A test if rows exist.

Example:

EXISTS (SELECT col FROM tbl)

group by clause ::=

Defines the grouping columns

Example:

GROUP BY col1, col2

having clause ::=

Search condition applied after grouping.

Example:

HAVING max(col1) = 5

order by clause ::=

Specifices row ordering.

Example:

ORDER BY x, y DESC

sort specification ::=

Defines how to sort on a particular expression

Example:

col1 NULLS FIRST

sort key ::=

A sort expression.

Example:

col1

integer parameter ::=

A literal integer or parameter reference to an integer.

Example:

?

limit clause ::=

Limits and/or offsets the resultant rows.

Example:

LIMIT 2

fetch clause ::=

ANSI limit.

Example:

FETCH FIRST 1 ROWS ONLY

option clause ::=

Specifies query options.

Example:

OPTION MAKEDEP tbl

expression ::=

A value.

Example:

col1

common value expression ::=

Establishes the precedence of concat.

Example:

'a' || 'b'

numeric value expression ::=

Example:

1 + 2

plus or minus ::=

The + or - operator.

Example:

+

term ::=

A numeric term

Example:

1 * 2

star or slash ::=

The * or / operator.

Example:

/

parameter reference ::=

A parameter reference to be bound later.

Example:

?

nested expression ::=

An expression nested in parens

Example:

(1)

ARRAY expression constructor ::=

Creates and array of the given expressions.

Example:

----ARRAY[1,2]
----

window specification ::=

The window specification for an analytical or windowed aggregate function.

Example:

OVER (PARTION BY col1)

case expression ::=

If/then/else chain using a common search predicand.

Example:

CASE col1 WHEN 'a' THEN 1 ELSE 2

searched case expression ::=

If/then/else chain using multiple search conditions.

Example:

CASE WHEN x = 'a' THEN 1 WHEN y = 'b' THEN 2

xml parse ::=

Parses the given value as XML.

Example:

XMLPARSE(DOCUMENT doc WELLFORMED)

querystring function ::=

Produces a URL query string from the given arguments.

Example:

QUERYSTRING('path', col1 AS opt, col2 AS val)

xml element ::=

Creates an XML element.

Example:

XMLELEMENT(NAME "root", child)

xml attributes ::=

Creates attributes for the containing element.

Example:

XMLATTRIBUTES(col1 AS attr1, col2 AS attr2)

json object ::=

Produces a JSON object containing name value pairs.

Example:

JSONOBJECT(col1 AS val1, col2 AS val2)

derived column list ::=

a list of name value pairs

Example:

col1 AS val1, col2 AS val2

xml forest ::=

Produces an element for each derived column.

Example:

XMLFOREST(col1 AS ELEM1, col2 AS ELEM2)

xml namespaces ::=

Defines XML namespace URI/prefix combinations

Example:

XMLNAMESPACES('http://foo' AS foo)

xml namespace element ::=

An xml namespace

Example:

NO DEFAULT

data type ::=

A data type.

Example:

STRING[]

non numeric literal ::=

An escaped or simple non numeric literal.

Example:

'a'

option namespace ::=

A namespace used to shorten the full name of an option key.

Example:

SET NAMESPACE 'http://foo' AS foo

create database ::=

create a new database

Example:

CREATE DATABASE foo [VERSION 'version'] OPTIONS(...)

use database ::=

database into working context

Example:

USE DATABASE foo [VERSION 'version']

drop database ::=

drop database

Example:

DROP DATABASE foo [VERSION 'version']

create schema ::=

create a schema in database

Example:

CREATE [VIRTUAL] SCHEMA foo SERVER (s1,s2,s3) OPTIONS(...)

drop schema ::=

drop a schema in database

Example:

----DROP SCHEMA foo
----

use schema ::=

use schema for following database resources

Example:

USE SCHEMA foo

create data wrapper aka translator ::=

Defines a translator; use the options to override the translator properties.

Example:

CREATE FOREIGN (DATA WRAPPER|TRANSLATOR)  wrapper OPTIONS(properties)

Drop data wrapper aka translator ::=

Deletes a translator

Example:

DROP FOREIGN (DATA WRAPPER|TRANSLATOR) wrapper

create role ::=

Defines data role for the database

Example:

CREATE DATA ROLE <data-role>  [WITH JAAS ROLE <string>(,<string>)*]

drop role ::=

Removes data role for the database

Example:

DROP ROLE <data-role>

Create GRANT ::=

Defines GRANT for a role

Example:

GRANT ( SELECT | INSERT |  UPDATE | DELETE | EXECUTE | LANGUAGE | ALTER | DROP | ALL PRIVILEGES | TEMPORARY TABLES )

'''


=== [[revokeGrantOption]]_<<usage_revokeGrantOption, Revoke GRANT>>_ ::=

* <<token_REVOKE,REVOKE>> <<token_GRANT,GRANT>> <<<readGrantTypes,grant type>>> ( <<<token_COMMA,comma>>> <<<readGrantTypes,grant type>>> )* <<token_ON,ON>> ( <<token_DATABASE,DATABASE>> ( <<<id,identifier>>> )? | <<token_TABLE,TABLE>> <<<id,identifier>>> ( <<token_CONDITION,CONDITION>> ( <<token_CONSTRAINT,CONSTRAINT>> )? <<<stringVal,string>>> )? | <<token_PROCEDURE,PROCEDURE>> <<<id,identifier>>> ( <<token_CONDITION,CONDITION>> ( <<token_CONSTRAINT,CONSTRAINT>> )? <<<stringVal,string>>> )? | <<token_SCHEMA,SCHEMA>> <<<id,identifier>>> | <<token_COLUMN,COLUMN>> <<<id,identifier>>> ( <<token_MASK,MASK>> ( <<token_ORDER,ORDER>> <<<intVal,unsigned integer>>> )? <<<stringVal,string>>> )? ( <<token_CONDITION,CONDITION>> ( <<token_CONSTRAINT,CONSTRAINT>> )? <<<stringVal,string>>> )? ) <<token_FROM,FROM>> <<<id,identifier>>>


Revokes GRANT for a role


Example:
[source,sql]

REVOKE GRANT ( SELECT | INSERT | UPDATE | DELETE | EXECUTE | LANGUAGE | ALTER | DROP | ALL PRIVILEGES | TEMPORARY TABLES )


create server, aka data source ::=

Defines connection to foreign source

Example:

CREATE SERVER server_name [ TYPE 'server_type' ] [ VERSION 'server_version' ] FOREIGN (<DATA> <WRAPPER>|<TRANSLATOR>)  fdw_name [ OPTIONS ( option 'value' [, ... ] ) ]

drop server, aka data source ::=

Defines dropping connection to foreign source

Example:

----DROP SERVER server_name
----

create procedure ::=

Defines a procedure or function invocation.

Example:

CREATE FOREIGN PROCEDURE proc (param STRING) RETURNS STRING

drop procedure ::=

Drops a table or view.

Example:

DROP [FOREIGN (TABLE|VIEW) table-name

procedure parameter ::=

A procedure or function parameter

Example:

OUT x INTEGER

procedure result column ::=

A procedure result column.

Example:

x INTEGER

create table ::=

Defines a table or view.

Example:

CREATE VIEW vw AS SELECT 1

drop table ::=

Drops a table or view.

Example:

DROP (FOREIGN TABLE | [VIRTUAL] VIEW) table-name

create foreign temp table ::=

Defines a foreign temp table

Example:

CREATE FOREIGN TEMPORARY TABLE t (x string) ON z

create table body ::=

Defines a table.

Example:

(x string) OPTIONS (CARDINALITY 100)

foreign key ::=

Defines the foreign key referential constraint.

Example:

FOREIGN KEY (a, b) REFERENCES tbl (x, y)

primary key ::=

Defines the primary key.

Example:

PRIMARY KEY (a, b)

other constraints ::=

Defines ACCESSPATTERN and UNIQUE constraints and INDEXes.

Example:

UNIQUE (a)

column list ::=

A list of column names.

Example:

(a, b)

table element ::=

Defines a table column.

Example:

x INTEGER NOT NULL

options clause ::=

A list of statement options.

Example:

OPTIONS ('x' 'y', 'a' 'b')

option pair ::=

An option key/value pair.

Example:

'key' 'value'

alter option pair ::=

Alter An option key/value pair.

Example:

'key' 'value'

ALTER TABLE ::=

alters options of database

Example:

ALTER TABLE foo (ADD|DROP|ALTER) COLUMN <name> <type> OPTIONS ( (ADD|SET|DROP) x y)

ADD column ::=

alters table and adds a column

Example:

ADD COLUMN bar type OPTIONS (ADD updatable true)

DROP column ::=

alters table and adds a column

Example:

----DROP COLUMN bar
----

alter column options ::=

alters a set of column options

Example:

ALTER COLUMN bar OPTIONS (ADD updatable true)

rename column options ::=

renames either a table column or procedure’s parameter name

Example:

RENAME COLUMN bar TO foo

ALTER PROCEDURE ::=

alters options of database

Example:

ALTER PROCEDURE foo [AS <stmt>] OPTIONS (ADD x y)

ALTER TRIGGER ::=

alters options of table triggers

Example:

ALTER TRIGGER ON <id> INSTEAD OF (INSERT|UPDATE|DELETE) AS [ENABLED|DISABLED]

ALTER SERVER ::=

alters options of database

Example:

ALTER SERVER foo OPTIONS (ADD x y)

ALTER DATA WRAPPER ::=

alters options of data wrapper

Example:

ALTER [DATA WRAPPER|TRANSLATOR] foo OPTIONS (ADD x y)

ALTER DATABASE ::=

alters options of database

Example:

ALTER DATABASE foo OPTIONS (ADD x y)

alter options list ::=

a list of alterations to options

Example:

OPTIONS (ADD updatable true)

drop option ::=

drop option

Example:

DROP updatable

add set option ::=

add or set an option pair

Example:

ADD updatable true

alter child options list ::=

a list of alterations to options

Example:

OPTIONS (ADD updatable true)

drop option ::=

drop option

Example:

DROP updatable

add set child option ::=

add or set an option pair

Example:

ADD updatable true

alter child option pair ::=

Alter An option key/value pair.

Example:

'key' 'value'

Import foreign schema ::=

imports schema metadata from server

Example:

IMPORT FOREIGN SCHEMA foo [LIMIT TO (x,y,z)|EXCEPT (x,y,z)] FROM SERVER bar

Import another Database ::=

imports another database into current database

Example:

IMPORT DATABASE <id> VERSION <string-val> [WITH ACCESS CONTROL]

results matching ""

    No results matching ""