The syntax of SQL statements is presented in the form of diagrams, showing how the statements may be written. The diagrams are read from left to right.
Valid statements are constructed by following the lines in the diagrams and “picking up” elements of the syntax on the way.
It is not practical to give the full, exhaustive syntax of each SQL statement in a single diagram. Instead, many of the syntax diagrams for statements in SQL Statements refer to language elements, which are themselves expanded into syntax diagrams in Operators and Values.
For each syntax diagram, references are given to where in the manual the expansion of the language elements may be found.
A sample diagram illustrating most of the features of the syntax diagrams is given at the end of this chapter, together with some valid and some invalid formulations.
A word bounded by diagram lines must be separated from adjoining words by at least one separator.
A separator is represented by a white-space character.
Words separated from each other by at least one space in the syntax diagram must also be separated from each other by at least one separator in the real statement.
Where the descriptive names for identifiers used in the diagrams consist of more than one word, these are bound together by hyphens.
Branched lines indicate alternative constructions. Only one branch may be followed for any one passage along the line: in this example either option-1 or option-2 may be used, but not both.
This representation is used to show that a section of the syntax construction may be repeated. Any construction required between the repetitions is shown on the repeat line.
In this example, the statement must contain at least one instance of parameter. If several instances are given, they must be separated from each other by a comma. If a comma or other separator is specified in a list, white spaces need not be used between the elements of the list.
Arrows at the beginning and end of a statement show that the statement is complete.
Dots at the beginning or end of a line in a diagram show that the statement on the line is incomplete.
The continuation may be in the same diagram or relate to a separate diagram, as in the language elements, see Operators and Values. The dots are not part of the statement syntax.
KEYWORDS
Keywords are words that are defined in the SQL language. Keywords are written in UPPERCASE in the diagrams. They must always be written in the statement exactly as shown, except that the case of letters is not significant.
Examples of keywords are:
ALTER
CREATE
NULL
TABLE
Spaces between keywords are significant. Thus the keywords CREATE TABLE in this example must be separated by at least one white space character.
Parameters
Parameters are indicated by words in lowercase in the diagrams, and replaced by the appropriate identifiers or constructions when statements are written. Examples of parameters are:
column-name
expression
data-type
The blank spaces in the diagrams are significant. Words bound together by hyphens (e.g. column-name, data-type) represent single parameters.
Syntax Diagram Example
The following sample illustrates the use of the syntax diagrams.
Some valid formulations are:
KEYWORD-1 (parameter) option-1 KEYWORD-2 string
KEYWORD-1 (parameter, parameter) option-1 KEYWORD-2 string option-3
KEYWORD-1 (parameter, parameter, parameter) option-2 KEYWORD-2 string
The following formulations are not valid:
KEYWORD-1 (parameter) KEYWORD-2 string
option-1 or option-2 missing
KEYWORD-1 parameter option-1 KEYWORD-2 string
parentheses missing
KEYWORD-1 (parameter,) parameter option-2 KEYWORD-2 string
closing parentheses wrongly placed
KEYWORD-1 (parameter, parameter) option-1KEYWORD-2 stringoption-3
separating blanks missing
KEYWORD-1 (parameter parameter parameter) option-2 KEYWORD-2 string
no commas in parameter list
Reading Standard Compliance Tables
For each language element and statement, the standards compliance is noted in a table, e.g. for GRANT ACCESS PRIVILEGE:
|
Standard |
Compliance |
Comments |
|---|---|---|
|
SQL-2016 |
Core |
Fully compliant. |
|
SQL-2016 |
Feature outside core |
Feature F731, “INSERT column privileges” support for granting insert on individual columns. |
|
|
Mimer SQL extension |
The keyword PRIVILEGES is optional and not mandatory in Mimer SQL. |
The compliance of a certain statement is always compared to the current ANSI/ISO standard, which at the moment of writing is SQL-2016.
The table indicates how Mimer SQL complies to SQL-2016. Mimer SQL is fully compliant with all features in core SQL-2016, but Mimer SQL also supports a number of features outside core SQL-2016 which can be seen on the second row of the table.
Finally, extensions specific to Mimer SQL is described.
If portability over different database platforms is important, care should be taken to use standard SQL whenever possible. When you have to use Mimer SQL extensions these should be isolated so they can be exchanged when porting to another database. Even if you only use standard SQL there is no warranty that the code can be used with other database products as practically all vendors only implements a subset of the standard.