BNF Grammar for ISO/IEC 9075:1992 - Database Language SQL (SQL-92)

Cross-Reference: rules
Cross-Reference: keywords

Derived from file sql-92.bnf version 2.3 dated 2004/03/31 19:34:09
Generated on 2005-07-13 18:32:46+00:00 by file bnf2html.pl version 3.7 dated 2005/07/13 18:32:35

Using Appendix G of "Understanding the New SQL: A Complete Guide" by J Melton and A R Simon (Morgan Kaufmann, 1993, ISBN 0-55860-245-3) as the source of the syntax, here is (most of) the BNF syntax for SQL-92. The missing parts are the Cobol, Fortran, MUMPS, Pascal and PL/I variable definition rules.

The plain text version of this grammar is sql-92.bnf .

Key SQL Statements and Fragments

ALLOCATE CURSOR <allocate cursor statement>

ALTER DOMAIN <alter domain statement>

ALTER TABLE <alter table statement>

CLOSE cursor <close statement> <dynamic close statement>

Column definition <column definition>

COMMIT WORK <commit statement>

CONNECT <connect statement>

CREATE ASSERTION <assertion definition>

CREATE CHARACTER SET <character set definition>

CREATE COLLATION <collation definition>

CREATE DOMAIN <domain definition>

CREATE SCHEMA <schema definition>

CREATE TABLE <table definition>

CREATE TRANSLATION <translation definition>

CREATE VIEW <view definition>

Data type <data type>

DEALLOCATE PREPARE <deallocate prepared statement>

DECLARE cursor <declare cursor> <dynamic declare cursor>

DECLARE LOCAL TEMPORARY TABLE <temporary table declaration>

DELETE <delete statement: positioned> <delete statement: searched> <dynamic delete statement: positioned>

DESCRIBE <describe statement>

DESCRIPTOR statements <system descriptor statement>

DISCONNECT <disconnect statement>

EXECUTE <execute statement>

EXECUTE IMMEDIATE <execute immediate statement>

FETCH cursor <fetch statement> <dynamic fetch statement>

GET DIAGNOSTICS <get diagnostics statement>

GRANT <grant statement>

INSERT <insert statement>

Literal <literal>

MODULE <module>

OPEN cursor <open statement> <dynamic open statement>

PREPARE <prepare statement>

Preparable statement <preparable statement>

REVOKE <revoke statement>

ROLLBACK WORK <rollback statement>

Search condition <search condition>

SELECT <query specification>

SET CATALOG <set catalog statement>

SET CONNECTION <set connection statement>

SET CONSTRAINTS <set constraints mode statement>

SET NAMES <set names statement>

SET SCHEMA <set schema statement>

SET SESSION AUTHORIZATION <set session authorization identifier statement>

SET TIME ZONE <set local time zone statement>

SET TRANSACTION <set transaction statement>

UPDATE <update statement: positioned> <update statement: searched> <dynamic update statement: positioned>

Value expression <value expression>

Top

Basic Definitions of Characters Used, Tokens, Symbols, Etc.

<simple Latin upper case letter> ::=
A | B | C | D | E | F | G | H | I | J | K | L | M | N | O | P | Q | R | S | T | U | V | W | X | Y | Z

<simple Latin lower case letter> ::=
a | b | c | d | e | f | g | h | i | j | k | l | m | n | o | p | q | r | s | t | u | v | w | x | y | z

<digit> ::=
0 | 1 | 2 | 3 | 4 | 5 | 6 | 7 | 8 | 9

<space> ::= !! space character in character set in use

<double quote> ::= "

<percent> ::= %

<ampersand> ::= &

<quote> ::= '

<left paren> ::= (

<right paren> ::= )

<asterisk> ::= *

<plus sign> ::= +

<comma> ::= ,

<minus sign> ::= -

<period> ::= .

<solidus> ::= /

<colon> ::= :

<semicolon> ::= ;

<less than operator> ::= <

<equals operator> ::= =

<question mark> ::= ?

<underscore> ::= _

<vertical bar> ::= /* Nothing */ |

<left bracket> ::= [

<right bracket> ::= ]

<identifier body> ::= <identifier start> [ { <underscore> | <identifier part> } ... ]

<identifier start> ::= !! See the Syntax rules

Top

Literal Numbers, Strings, Dates and Times

<unsigned integer> ::= <digit> ...

<national character string literal> ::=
N <quote> [ <character representation> ... ] <quote> [ { <separator> ... <quote> [ <character representation> ... ] <quote> }... ]

<nonquote character> ::= !! See the Syntax rules

<separator> ::= { <comment> | <space> | <newline> }...

<comment introducer> ::= <minus sign> <minus sign> [<minus sign >...]

<newline> ::= !! implementation defined end of line indicator

<bit string literal> ::=
B <quote> [ <bit> ... ] <quote> [ { <separator> ... <quote> [ <bit> ... ] <quote> }... ]

<bit> ::= 0 | 1

<hex string literal> ::=
X <quote> [ <hexit> ... ] <quote> [ { <separator> ... <quote> [ <hexit> ... ] <quote> }... ]

<hexit> ::= <digit> | A | B | C | D | E | F | a | b | c | d | e | f

<character string literal> ::=
[ <introducer> <character set specification> ] <quote> [ <character representation> ... ] <quote> [ { <separator> ... <quote> [ <character representation> ... ] <quote> }... ]

<delimited identifier body> ::= <delimited identifier part> ...

<nondoublequote character> ::= !! See the syntax rules

<SQL language identifier> ::=
<SQL language identifier start> [ { <underscore> | <SQL language identifier part> }... ]

<implementation-defined character repertoire name> ::= <character set name>

<user-defined character repertoire name> ::= <character set name>

<implementation-defined universal character form-of-use name> ::= <character set name>

<seconds value> ::= <seconds integer value> [ <period> [ <seconds fraction> ] ]

<year-month literal>    ::=
         <years value>
     |     [ <years value> <minus sign> ] <months value>

<day-time literal> ::= <day-time interval> | <time interval>

<day-time interval> ::=
<days value> [ <space> <hours value> [ <colon> <minutes value> [ <colon> <seconds value> ] ] ]

<time-interval>    ::=
         <hours value> [ <colon> <minutes value> [ <colon> <seconds value> ] ]
     |     <minutes value> [ <colon> <seconds value> ]
     |     <seconds value>

<greater than or equals operator> ::= >=

<less than or equals operator> ::= <=

<concatenation operator> ::= ||

<double period> ::= ..

Top

SQL Module

<module>    ::=
         <module name clause> <language clause> <module authorization clause>
         [ <temporary table declaration> ... ]
         <module contents> ...

<module name clause> ::=
MODULE [ <module name> ] [ <module character set specification> ]

<module character set specification> ::= NAMES ARE <character set specification>

<language clause> ::= LANGUAGE <language name>

<module authorization clause>    ::=
         SCHEMA <schema name>
     |     AUTHORIZATION <module authorization identifier>
     |     SCHEMA <schema name> AUTHORIZATION <module authorization identifier>

<temporary table declaration> ::=
DECLARE LOCAL TEMPORARY TABLE <qualified local table name> <table element list> [ ON COMMIT { PRESERVE | DELETE } ROWS ]

<qualified local table name> ::= MODULE <period> <local table name>

<column definition> ::=
<column name> { <data type> | <domain name> } [ <default clause> ] [ <column constraint definition> ... ] [ <collate clause> ]

Top

Data Types

<character string type>    ::=
         CHARACTER [ <left paren> <length> <right paren> ]
     |     CHAR [ <left paren> <length> <right paren> ]
     |     CHARACTER VARYING [ <left paren> <length> <right paren> ]
     |     CHAR VARYING [ <left paren> <length> <right paren> ]
     |     VARCHAR [ <left paren> <length> <right paren> ]

<national character string type>    ::=
         NATIONAL CHARACTER [ <left paren> <length> <right paren> ]
     |     NATIONAL CHAR [ <left paren> <length> <right paren> ]
     |     NCHAR [ <left paren> <length> <right paren> ]
     |     NATIONAL CHARACTER VARYING [ <left paren> <length> <right paren> ]
     |     NATIONAL CHAR VARYING [ <left paren> <length> <right paren> ]
     |     NCHAR VARYING [ <left paren> <length> <right paren> ]

<bit string type>    ::=
         BIT [ <left paren> <length> <right paren> ]
     |     BIT VARYING [ <left paren> <length> <right paren> ]

<exact numeric type>    ::=
         NUMERIC [ <left paren> <precision> [ <comma> <scale> ] <right paren> ]
     |     DECIMAL [ <left paren> <precision> [ <comma> <scale> ] <right paren> ]
     |     DEC [ <left paren> <precision> [ <comma> <scale> ] <right paren> ]
     |     INTEGER
     |     INT
     |     SMALLINT

<approximate numeric type>    ::=
         FLOAT [ <left paren> <precision <right paren> ]
     |     REAL
     |     DOUBLE PRECISION

<datetime type>    ::=
         DATE
     | TIME [ <left paren> <time precision> <right paren> ] [ WITH TIME ZONE ]
     | TIMESTAMP [ <left paren> <timestamp precision> <right paren> ] [ WITH TIME ZONE ]

<interval type> ::= INTERVAL <interval qualifier>

<start field> ::=
<non-second datetime field> [ <left paren> <interval leading field precision> <right paren> ]

<non-second datetime field> ::= YEAR | MONTH | DAY | HOUR | MINUTE

<end field>    ::=
         <non-second datetime field>
     | SECOND [ <left paren> <interval fractional seconds precision> <right paren> ]

<single datetime field>    ::=
         <non-second datetime field> [ <left paren> <interval leading field precision> <right paren> ]
     | SECOND [ <left paren> <interval leading field precision> [ <comma> <left paren> <interval fractional seconds precision> ] <right paren> ]

<default clause> ::= DEFAULT <default option>

Top

Literals

<timestamp literal> ::= TIMESTAMP <timestamp string>

<interval literal> ::= INTERVAL [ <sign> ] <interval string> <interval qualifier>

<current date value function> ::= CURRENT_DATE

<current time value function> ::= CURRENT_TIME [ <left paren> <time precision> <right paren> ]

<current timestamp value function> ::= CURRENT_TIMESTAMP [ <left paren> <timestamp precision> <right paren> ]

Top

Constraints

<column constraint definition> ::=
[ <constraint name definition> ] <column constraint> [ <constraint attributes> ]

<constraint name definition> ::= CONSTRAINT <constraint name>

<unique specification> ::= UNIQUE | PRIMARY KEY

<references specification> ::=
REFERENCES <referenced table and columns> [ MATCH <match type> ] [ <referential triggered action> ]

<referenced table and columns> ::= <table name> [ <left paren> <reference column list> <right paren> ]

<column name list> ::= <column name> [ { <comma> <column name> }... ]

<match type> ::= FULL | PARTIAL

<referential triggered action>    ::=
         <update rule> [ <delete rule> ]
     |     <delete rule> [ <update rule> ]

<update rule> ::= ON UPDATE <referential action>

<referential action> ::= CASCADE | SET NULL | SET DEFAULT | NO ACTION

<delete rule> ::= ON DELETE <referential action>

<check constraint definition> ::= CHECK <left paren> <search condition> <right paren>

Top

Search Condition

<boolean test> ::= <boolean primary> [ IS [ NOT ] <truth value> ]

<parameter specification> ::= <parameter name> [ <indicator parameter> ]

<indicator parameter> ::= [ INDICATOR ] <parameter name>

<variable specification> ::= <embedded variable name> [ <indicator variable> ]

<Ada host identifier> ::= !! See syntax rules

<C host identifier> ::= !! See syntax rules

<Cobol host identifier> ::= !! See syntax rules

<Fortran host identifier> ::= !! See syntax rules

<MUMPS host identifier> ::= !! See syntax rules

<Pascal host identifier> ::= !! See syntax rules

<PL/I host identifier> ::= !! See syntax rules

<indicator variable> ::= [ INDICATOR ] <embedded variable name>

<set function specification>    ::=
         COUNT <left paren> <asterisk> <right paren>
     | <general set function>

<set function type> ::= AVG | MAX | MIN | SUM | COUNT

<set quantifier> ::= DISTINCT | ALL

Top

Queries

<non-join query expression>    ::=
         <non-join query term>
     |     <query expression> UNION [ ALL ] [ <corresponding spec> ] <query term>
     |     <query expression> EXCEPT [ ALL ] [ <corresponding spec> ] <query term>

<non-join query term>    ::=
         <non-join query primary>
     |     <query term> INTERSECT [ ALL ] [ <corresponding spec> ] <query primary>

<non-join query primary> ::= <simple table> | <left paren> <non-join query expression> <right paren>

<query specification> ::=
SELECT [ <set quantifier> ] <select list> <table expression>

<select list>    ::=
         <asterisk>
     |     <select sublist> [ { <comma> <select sublist> }... ]

<derived column> ::= <value expression> [ <as clause> ]

<table expression>    ::=
         <from clause>
         [ <where clause> ]
         [ <group by clause> ]
         [ <having clause> ]

<from clause> ::= FROM <table reference> [ { <comma> <table reference> }... ] Note that <correlation specification> does not appear in the ISO/IEC grammar. The notation is written out longhand several times, instead.