BNF Grammar for ISO/IEC 9075:1999 - Database Language SQL (SQL-99)

Cross-Reference: rules
Cross-Reference: keywords

Derived from file sql-99.bnf version 2.8 dated 2004/07/26 18:00:06
Generated on 2005-07-13 18:32:52+00:00 by file bnf2html.pl version 3.7 dated 2005/07/13 18:32:35

Using Appendix G of "SQL:1999 Understanding Relational Language Components" by J Melton and A R Simon (Morgan Kaufmann, May 2001, ISBN 0-55860-456-1) as the primary source of the syntax, here is the BNF syntax for SQL-99. The Word 97 version of this document is available from: http://www.mkp.com/books_catalog/catalog.asp?ISBN=1-55860-456-1 .

Note that this version of this file includes the corrections from ISO 9075:1999/Cor.1:2000.

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

Key SQL Statements and Fragments

ALTER DOMAIN <alter domain statement>

ALTER TABLE <alter table statement>

CLOSE cursor <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 FUNCTION <schema function>

CREATE PROCEDURE <schema procedure>

CREATE SCHEMA <schema definition>

CREATE TABLE <table definition>

CREATE TRANSLATION <translation definition>

CREATE TRIGGER <trigger 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>

FROM clause <from clause>

GET DIAGNOSTICS <get diagnostics statement>

GRANT <grant statement>

GROUP BY clause <group by clause>

HAVING clause <having clause>

INSERT <insert statement>

Literals <literal>

OPEN cursor <open statement>

ORDER BY clause <order by clause>

PREPARE <prepare statement>

REVOKE <revoke statement>

ROLLBACK WORK <rollback statement>

SAVEPOINT <savepoint statement>

Search condition <search condition> <regular expression>

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 user identifier statement>

SET TIME ZONE <set local time zone statement>

SET TRANSACTION <set transaction statement>

SQL Client MODULE <SQL-client module definition>

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

Value expression <value expression>

WHERE clause <where clause>

Top

Identifying the version of SQL in use

This material (starting with <SQL object identifier>) is defined in section 6.3 "Object Identifier for Database Language SQL" of ISO/IEC 9075-1:1999 (SQL Framework). It is used to express the capabilities of an implementation. The package names are identifiers such as 'PKG001', equivalent to 'Enhanced datetime facilities', as defined in the informative Annex B to SQL Framework. Each such package identifies a number of features that are provided when the SQL object identifier claims to provide the package.

<arc2> ::= standard | 0 | standard <left paren> 0 <right paren>

<arc3> ::= 9075

<1987> ::= 0 | edition1987 <left paren> 0 <right paren>

<1989> ::= <1989 base> <1989 package>

<1989 base> ::= 1 | edition1989 <left paren> 1 <right paren>

<1989 package> ::= <integrity no> | <integrity yes>

<integrity no> ::= 0 | IntegrityNo <left paren> 0 <right paren>

<integrity yes> ::= 1 | IntegrityYes <left paren> 1 <right paren>

<1992> ::= 2 | edition1992 <left paren> 2 <right paren>

<1999> ::= 3 | edition1999 <left paren> 3 <right paren>

<intermediate> ::= 1 | Intermediate <left paren> 1 <right paren>

The parenthesized (i) and (n) are italic in the SQL standard. It is not clear exactly what this should look like, despite all the information. However, it is also not important; this is not really a part of the SQL language per se. Note that the package numbers are PKG001 to PKG009, for example. We still have to devise a mechanism to persuade bnf2yacc.pl to ignore this information.

<packages> ::= <Package PKG (i)> ...

<Part (n) yes> ::= !! (as specified in ISO/IEC 9075-(n))

<Part 3 conformance> ::= 3 | sqlcli1999 <left paren> 3 <right paren>

<Part 4 conformance> ::= 4 | sqlpsm1999 <left paren> 4 <right paren>

<Part 4 module yes> ::= 1 | moduleyes <left paren> 1 <right paren>

<Part 4 module no> ::= 0 | moduleno <left paren> 0 <right paren>

The original used sqlbindings199x, but the x should clearly be a 9.

<Part 5 conformance> ::= 5 | sqlbindings1999 <left paren> 5 <right paren>

<Part 5 direct yes> ::= 1 | directyes <left paren> 1 <right paren>

<Part 5 direct no> ::= 0 | directno <left paren> 0 <right paren>

<Part 5 embedded> ::= <Part 5 embedded no> | <Part 5 embedded languages> ...

<Part 5 embedded no> ::= 0 | embeddedno <left paren> 0 <right paren>

<Part 5 embedded Ada> ::= 1 | embeddedAda <left paren> 1 <right paren>

<Part 5 embedded C> ::= 2 | embeddedC <left paren> 2 <right paren>

<Part 5 embedded COBOL> ::= 3 | embeddedCOBOL <left paren> 3 <right paren>

<Part 5 embedded Fortran> ::= 4 | embeddedFortran <left paren> 4 <right paren>

<Part 5 embedded MUMPS> ::= 5 | embeddedMUMPS <left paren> 5 <right paren>

<Part 5 embedded Pascal> ::= 6 | embeddedPascal <left paren> 6 <right paren>

<Part 5 embedded PL/I> ::= 7 | embeddedPLI <left paren> 7 <right paren>

Top

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

Most of this section would normally be handled within the lexical analyzer rather than in the grammar proper. Further, the original document does not quote the various single characters, which makes it hard to process automatically.

<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> ::= !! (See the Syntax Rules)

<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> ::= ?

<left bracket> ::= [

<right bracket> ::= ]

<circumflex> ::= ^

<underscore> ::= _

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

<left brace> ::= {

<right brace> ::= }

Top

Literal Numbers, Strings, Dates and Times

<SQL-client module definition> modified per ISO 9075:1999/Cor.1:2000(E)

<SQL-client module definition>    ::=
         <module name clause>
         <language clause>
         <module authorization clause>
         [ <module path specification> ]
         [ <module transform group specification> ]
         [ <temporary table declaration> ... ]
         <module contents> ...

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

<SQL-client module name> ::= <identifier>

<identifier body> modified per ISO 9075:1999/Cor.1:2000(E).

<identifier body> also rationalized by removing curly brackets around <identifier part> because they are unnecessary and inconsistent with other places where '...' modifies a single non-terminal.

<identifier body> ::= <identifier start> [ <identifier part> ... ]

<initial alphabetic character> ::= !! (See the Syntax Rules)

<ideographic character> ::= !! (See the Syntax Rules)

<alphabetic character> ::= !! (See the Syntax Rules)

<decimal digit character> ::= !! (See the Syntax Rules)

<identifier combining character> ::= !! (See the Syntax Rules)

<alternate underscore> ::= !! (See the Syntax Rules)

<extender character> ::= !! (See the Syntax Rules)

<identifier ignorable character> ::= !! (See the Syntax Rules)

<connector character> ::= !! (See the Syntax Rules)

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

<nondoublequote character> ::= !! (See the Syntax Rules)

Note that the two successive double quote characters must have no other character (such as a space) between them. The lexical analyzer would normally deal with this sort of issue.

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

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

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

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

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

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

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

<transform group specification> ::=
TRANSFORM GROUP { <single group specification> | <multiple group specification> }

<multiple group specification> ::= <group specification> [ { <comma> <group specification> }... ]

<user-defined type> ::= <user-defined type name>

<user-defined type name> ::= <schema qualified type name>

<temporary table declaration>    ::=
         DECLARE LOCAL TEMPORARY TABLE <table name>
         <table element list>
         [ ON COMMIT <table commit action> ROWS ]

<local or schema qualifier> ::= <schema name> | MODULE

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

Top

Data Types

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

<multiplier> ::= K | M | G

<binary large object string type>    ::=
         BINARY LARGE OBJECT [ <left paren> <large object length> <right paren> ]
     |     BLOB [ <left paren> <large object 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

<boolean type> ::= BOOLEAN

<datetime type>    ::=
         DATE
     |     TIME [ <left paren> <time precision> <right paren> ] [ <with or without time zone> ]
     |     TIMESTAMP [ <left paren> <timestamp precision> <right paren> ] [ <with or without time zone> ]

<with or without time zone> ::= WITH TIME ZONE | WITHOUT TIME ZONE

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

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

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

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

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

<field definition> ::= <field name> <data type> [ <reference scope check> ] [ <collate clause> ]

<reference scope check> ::=
REFERENCES ARE [ NOT ] CHECKED [ ON DELETE <reference scope check action> ]

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

<collate clause> ::= COLLATE <collation name>

<reference type> ::= REF <left paren> <referenced type> <right paren> [ <scope clause> ]

<scope clause> ::= SCOPE <table name>

<collection type constructor> ::= ARRAY

The trigraphs are strictly sequences of characters, not sequences of tokens. There may not be any spaces between the characters. Normally, the lexical analyzer would return the trigraphs as a simple symbol.

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

Top

Literals

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

<nonquote character> ::= !! (See the Syntax Rules.)

The <quote symbol> rule consists of two immediately adjacent <quote> marks with no spaces. As usual, this would be best handled in the lexical analyzer, not in the grammar.

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

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

<newline> ::= !! (See the Syntax Rules)

The <bracketed comment> rule included '!! (See the Syntax Rules)'. This probably says something about the <slash> <asterisk> and <asterisk> <slash> needing to be adjacent characters rather than adjacent tokens.