This is the second part of the SQLxD series. For your convenience you can find other parts in the table of contents in Part 1 – XML Transformation
In order to implement SQLxD database, we need to know, what kind of queries we support. Here is subset of SQL-92 grammar:
ᐸscalar subquery> ::= ᐸsubquery>
ᐸsubquery> ::= ᐸleft paren> ᐸquery expression> ᐸright paren>
ᐸquery expression> ::= ᐸnon-join query expression> | ᐸjoined table>
ᐸ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>
ᐸsimple table> ::=
ᐸquery specification>
| ᐸtable value constructor>
| ᐸexplicit table>
ᐸquery specification> ::=
SELECT [ ᐸset quantifier> ] ᐸselect list> ᐸtable expression>
ᐸselect list> ::=
ᐸasterisk>
| ᐸselect sublist> [ { ᐸcomma> ᐸselect sublist> }... ]
ᐸselect sublist> ::= ᐸderived column> | ᐸqualifier> ᐸperiod> ᐸasterisk>
ᐸderived column> ::= ᐸvalue expression> [ ᐸas clause> ]
ᐸas clause> ::= [ AS ] ᐸcolumn name>
ᐸ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.
ᐸtable reference> ::=
ᐸtable name> [ ᐸcorrelation specification> ]
| ᐸderived table> ᐸcorrelation specification>
| ᐸjoined table>
ᐸcorrelation specification> ::=
[ AS ] ᐸcorrelation name> [ ᐸleft paren> ᐸderived column list> ᐸright paren> ]
ᐸderived column list> ::= ᐸcolumn name list>
ᐸderived table> ::= ᐸtable subquery>
ᐸtable subquery> ::= ᐸsubquery>
ᐸjoined table> ::=
ᐸcross join>
| ᐸqualified join>
| ᐸleft paren> ᐸjoined table> ᐸright paren>
ᐸcross join> ::=
ᐸtable reference> CROSS JOIN ᐸtable reference>
ᐸqualified join> ::=
ᐸtable reference> [ NATURAL ] [ ᐸjoin type> ] JOIN ᐸtable reference> [ ᐸjoin specification> ]
ᐸjoin type> ::=
INNER
| ᐸouter join type> [ OUTER ]
| UNION
ᐸouter join type> ::= LEFT | RIGHT | FULL
ᐸjoin specification> ::= ᐸjoin condition> | ᐸnamed columns join>
ᐸjoin condition> ::= ON ᐸsearch condition>
ᐸnamed columns join> ::= USING ᐸleft paren> ᐸjoin column list> ᐸright paren>
ᐸjoin column list> ::= ᐸcolumn name list>
ᐸwhere clause> ::= WHERE ᐸsearch condition>
ᐸgroup by clause> ::= GROUP BY ᐸgrouping column reference list>
ᐸgrouping column reference list> ::=
ᐸgrouping column reference> [ { ᐸcomma> ᐸgrouping column reference> }... ]
ᐸgrouping column reference> ::= ᐸcolumn reference> [ ᐸcollate clause> ]
ᐸcollate clause> ::= COLLATE ᐸcollation name>
ᐸcollation name> ::= ᐸqualified name>
ᐸhaving clause> ::= HAVING ᐸsearch condition>
ᐸtable value constructor> ::= VALUES ᐸtable value constructor list>
ᐸtable value constructor list> ::= ᐸrow value constructor> [ { ᐸcomma> ᐸrow value constructor> }... ]
ᐸexplicit table> ::= TABLE ᐸtable name>
ᐸquery term> ::= ᐸnon-join query term> | ᐸjoined table>
ᐸcorresponding spec> ::= CORRESPONDING [ BY ᐸleft paren> ᐸcorresponding column list> ᐸright paren> ]
ᐸcorresponding column list> ::= ᐸcolumn name list>
ᐸquery primary> ::= ᐸnon-join query primary> | ᐸjoined table>
ᐸcase expression> ::= ᐸcase abbreviation> | ᐸcase specification>
ᐸcase abbreviation> ::=
NULLIF ᐸleft paren> ᐸvalue expression> ᐸcomma> ᐸvalue expression> ᐸright paren>
| COALESCE ᐸleft paren> ᐸvalue expression> { ᐸcomma> ᐸvalue expression> }... ᐸright paren>
ᐸcase specification> ::= ᐸsimple case> | ᐸsearched case>
ᐸsimple case> ::=
CASE ᐸcase operand>
ᐸsimple when clause> ...
[ ᐸelse clause> ]
END
ᐸcase operand> ::= ᐸvalue expression>
ᐸsimple when clause> ::= WHEN ᐸwhen operand> THEN ᐸresult>
ᐸwhen operand> ::= ᐸvalue expression>
ᐸresult> ::= ᐸresult expression> | NULL
ᐸresult expression> ::= ᐸvalue expression>
ᐸelse clause> ::= ELSE ᐸresult>
ᐸsearched case> ::=
CASE
ᐸsearched when clause> ...
[ ᐸelse clause> ]
END
ᐸsearched when clause> ::= WHEN ᐸsearch condition> THEN ᐸresult>
ᐸcast specification> ::= CAST ᐸleft paren> ᐸcast operand> AS ᐸcast target> ᐸright paren>
ᐸcast operand> ::= ᐸvalue expression> | NULL
ᐸcast target> ::= ᐸdomain name> | ᐸdata type>
ᐸnumeric value function> ::= ᐸposition expression> | ᐸextract expression> | ᐸlength expression>
ᐸposition expression> ::=
POSITION ᐸleft paren> ᐸcharacter value expression> IN ᐸcharacter value expression> ᐸright paren>
ᐸcharacter value expression> ::= ᐸconcatenation> | ᐸcharacter factor>
ᐸconcatenation> ::= ᐸcharacter value expression> ᐸconcatenation operator> ᐸcharacter factor>
ᐸcharacter factor> ::= ᐸcharacter primary> [ ᐸcollate clause> ]
ᐸcharacter primary> ::= ᐸvalue expression primary> | ᐸstring value function>
ᐸstring value function> ::= ᐸcharacter value function> | ᐸbit value function>
ᐸcharacter value function> ::=
ᐸcharacter substring function>
| ᐸfold>
| ᐸform-of-use conversion>
| ᐸcharacter translation>
| ᐸtrim function>
ᐸcharacter substring function> ::=
SUBSTRING ᐸleft paren> ᐸcharacter value expression> FROM ᐸstart position> [ FOR ᐸstring length> ] ᐸright paren>
ᐸstart position> ::= ᐸnumeric value expression>
ᐸstring length> ::= ᐸnumeric value expression>
ᐸfold> ::= { UPPER | LOWER } ᐸleft paren> ᐸcharacter value expression> ᐸright paren>
ᐸform-of-use conversion> ::=
CONVERT ᐸleft paren> ᐸcharacter value expression> USING ᐸform-of-use conversion name> ᐸright paren>
ᐸform-of-use conversion name> ::= ᐸqualified name>
ᐸcharacter translation> ::=
TRANSLATE ᐸleft paren> ᐸcharacter value expression> USING ᐸtranslation name> ᐸright paren>
ᐸtranslation name> ::= ᐸqualified name>
ᐸtrim function> ::= TRIM ᐸleft paren> ᐸtrim operands> ᐸright paren>
ᐸtrim operands> ::= [ [ ᐸtrim specification> ] [ ᐸtrim character> ] FROM ] ᐸtrim source>
ᐸtrim specification> ::= LEADING | TRAILING | BOTH
ᐸtrim character> ::= ᐸcharacter value expression>
ᐸtrim source> ::= ᐸcharacter value expression>
ᐸbit value function> ::= ᐸbit substring function>
ᐸbit substring function> ::=
SUBSTRING ᐸleft paren> ᐸbit value expression> FROM ᐸstart position> [ FOR ᐸstring length> ] ᐸright paren>
ᐸbit value expression> ::= ᐸbit concatenation> | ᐸbit factor>
ᐸbit concatenation> ::= ᐸbit value expression> ᐸconcatenation operator> ᐸbit factor>
ᐸbit factor> ::= ᐸbit primary>
ᐸbit primary> ::= ᐸvalue expression primary> | ᐸstring value function>
ᐸextract expression> ::= EXTRACT ᐸleft paren> ᐸextract field> FROM ᐸextract source> ᐸright paren>
ᐸextract field> ::= ᐸdatetime field> | ᐸtime zone field>
ᐸdatetime field> ::= ᐸnon-second datetime field> | SECOND
ᐸtime zone field> ::= TIMEZONE_HOUR | TIMEZONE_MINUTE
ᐸextract source> ::= ᐸdatetime value expression> | ᐸinterval value expression>
ᐸdatetime value expression> ::=
ᐸdatetime term>
| ᐸinterval value expression> ᐸplus sign> ᐸdatetime term>
| ᐸdatetime value expression> ᐸplus sign> ᐸinterval term>
| ᐸdatetime value expression> ᐸminus sign> ᐸinterval term>
ᐸinterval term> ::=
ᐸinterval factor>
| ᐸinterval term 2> ᐸasterisk> ᐸfactor>
| ᐸinterval term 2> ᐸsolidus> ᐸfactor>
| ᐸterm> ᐸasterisk> ᐸinterval factor>
ᐸinterval factor> ::= [ ᐸsign> ] ᐸinterval primary>
ᐸinterval primary> ::= ᐸvalue expression primary> [ ᐸinterval qualifier> ]
ᐸinterval term 2> ::= ᐸinterval term>
ᐸinterval value expression> ::=
ᐸinterval term>
| ᐸinterval value expression 1> ᐸplus sign> ᐸinterval term 1>
| ᐸinterval value expression 1> ᐸminus sign> ᐸinterval term 1>
| ᐸleft paren> ᐸdatetime value expression> ᐸminus sign> ᐸdatetime term> ᐸright paren> ᐸinterval qualifier>
ᐸinterval value expression 1> ::= ᐸinterval value expression>
ᐸinterval term 1> ::= ᐸinterval term>
ᐸdatetime term> ::= ᐸdatetime factor>
ᐸdatetime factor> ::= ᐸdatetime primary> [ ᐸtime zone> ]
ᐸdatetime primary> ::= ᐸvalue expression primary> | ᐸdatetime value function>
ᐸtime zone> ::= AT ᐸtime zone specifier>
ᐸtime zone specifier> ::= LOCAL | TIME ZONE ᐸinterval value expression>
ᐸlength expression> ::= ᐸchar length expression> | ᐸoctet length expression> | ᐸbit length expression>
ᐸchar length expression> ::= { CHAR_LENGTH | CHARACTER_LENGTH } ᐸleft paren> ᐸstring value expression> ᐸright paren>
ᐸstring value expression> ::= ᐸcharacter value expression> | ᐸbit value expression>
ᐸoctet length expression> ::= OCTET_LENGTH ᐸleft paren> ᐸstring value expression> ᐸright paren>
ᐸbit length expression> ::= BIT_LENGTH ᐸleft paren> ᐸstring value expression> ᐸright paren>
ᐸnull specification> ::= NULL
ᐸdefault specification> ::= DEFAULT
ᐸrow value constructor list> ::= ᐸrow value constructor element> [ { ᐸcomma> ᐸrow value constructor element> } ... ]
ᐸrow subquery> ::= ᐸsubquery>
ᐸcomp op> ::=
ᐸequals operator>
| ᐸnot equals operator>
| ᐸless than operator>
| ᐸgreater than operator>
| ᐸless than or equals operator>
| ᐸgreater than or equals operator>
ᐸbetween predicate> ::=
ᐸrow value constructor> [ NOT ] BETWEEN ᐸrow value constructor> AND ᐸrow value constructor>
ᐸin predicate> ::= ᐸrow value constructor> [ NOT ] IN ᐸin predicate value>
ᐸin predicate value> ::= ᐸtable subquery> | ᐸleft paren> ᐸin value list> ᐸright paren>
ᐸin value list> ::= ᐸvalue expression> { ᐸcomma> ᐸvalue expression> } ...
ᐸlike predicate> ::= ᐸmatch value> [ NOT ] LIKE ᐸpattern> [ ESCAPE ᐸescape character> ]
ᐸmatch value> ::= ᐸcharacter value expression>
ᐸpattern> ::= ᐸcharacter value expression>
ᐸescape character> ::= ᐸcharacter value expression>
ᐸnull predicate> ::= IS [ NOT ] NULL
ᐸquantified comparison predicate> ::= ᐸrow value constructor> ᐸcomp op> ᐸquantifier> ᐸtable subquery>
ᐸquantifier> ::= ᐸall> | ᐸsome>
ᐸall> ::= ALL
ᐸsome> ::= SOME | ANY
ᐸexists predicate> ::= EXISTS ᐸtable subquery>
ᐸunique predicate> ::= UNIQUE ᐸtable subquery>
ᐸmatch predicate> ::= ᐸrow value constructor> MATCH [ UNIQUE ] [ PARTIAL | FULL ] ᐸtable subquery>
ᐸoverlaps predicate> ::= ᐸrow value constructor 1> OVERLAPS ᐸrow value constructor 2>
ᐸrow value constructor 1> ::= ᐸrow value constructor>
ᐸrow value constructor 2> ::= ᐸrow value constructor>
ᐸtruth value> ::= TRUE | FALSE | UNKNOWN
Here is grammar used by SQLxD, based on SQL-92:
ᐸquery specification> ::= SELECT [ ᐸset quantifier> ] ᐸselect list> ᐸtable expression>
ᐸset quantifier> ::= DISTINCT | ALL
ᐸselect list> ::= ᐸasterisk>
| ᐸselect sublist> [ { ᐸcomma> ᐸselect sublist> }... ]
ᐸselect sublist> ::= ᐸderived column>
ᐸderived column> ::= ᐸvalue expression> [ ᐸas clause> ]
ᐸvalue expression> ::= ᐸstring value expression>
ᐸstring value expression> ::= ᐸcharacter value expression>
ᐸcharacter value expression> ::= ᐸcharacter factor>
ᐸcharacter factor> ::= ᐸcharacter primary>
ᐸcharacter primary> ::= ᐸvalue expression primary>
ᐸvalue expression primary> ::=
ᐸunsigned value specification>
| ᐸcolumn reference>
| ᐸset function specification>
ᐸset function specification> ::=
COUNT ᐸleft paren> ᐸasterisk> ᐸright paren>
| ᐸgeneral set function>
ᐸgeneral set function> ::=
ᐸset function type> ᐸleft paren> ᐸvalue expression> ᐸright paren>
ᐸset function type> ::= AVG | MAX | MIN | SUM | COUNT
ᐸcolumn reference> ::= ᐸqualifier> ᐸperiod> ᐸcolumn name> [ ᐸperiod> ᐸcolumn name> ... ]
ᐸcolumn name> ::= ᐸidentifier>
ᐸas clause> ::= [ AS ] ᐸcolumn name>
ᐸunsigned value specification> ::= ᐸunsigned literal>
ᐸunsigned literal> ::= ᐸunsigned numeric literal> | ᐸgeneral literal>
ᐸunsigned numeric literal> ::=
ᐸexact numeric literal>
ᐸexact numeric literal> ::=
ᐸunsigned integer>
ᐸexact numeric literal> ::=
ᐸunsigned integer>
ᐸgeneral literal> ::=
ᐸcharacter string literal>
ᐸcharacter string literal> ::=
QUOTE [ ᐸcharacter representation> ... ] QUOTE
ᐸcharacter representation> ::= ᐸnonquote character>
ᐸidentifier> ::= ᐸactual identifier>
ᐸactual identifier> ::= ᐸregular identifier>
ᐸregular identifier> ::= ᐸidentifier body>
ᐸidentifier body> ::= ᐸidentifier start> [ { ᐸunderscore> | ᐸidentifier part> } ... ]
ᐸidentifier start> ::= ᐸletter_or_hash>
ᐸletter_or_hash> ::= ᐸletter> | ᐸhash>
ᐸ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’ | ‘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’
ᐸhash> ::= ‘#’
ᐸunderscore> ::= ‘_’
ᐸdigit> ::= ‘0’ | ‘1’ | ‘2’ | ‘3’ | ‘4’ | ‘5’ | ‘6’ | ‘7’ | ‘8’ | ‘9’
ᐸidentifier part> ::= ᐸidentifier start> | ᐸdigit>
ᐸtable expression> ::=
ᐸfrom clause>
[ ᐸwhere clause> ]
[ ᐸgroup by clause> ]
[ ᐸorder by clause]
ᐸfrom clause> ::= FROM ᐸtable reference>
ᐸtable reference> ::=
ᐸtable name> ᐸcorrelation specification>
| ᐸjoined table>
ᐸtable name> ::= ᐸqualified name>
ᐸqualified name> ::= ᐸqualified identifier> [ PERIOD ᐸqualified identifier> ...]
ᐸqualified identifier> ::= ᐸidentifier> | QUESTION_MARK | ASTERISK
ᐸcorrelation specification> ::= AS ᐸcorrelation name>
ᐸcorrelation name> ::= ᐸidentifier>
ᐸjoined table> ::=
ᐸcross join>
| ᐸqualified join>
ᐸcross join> ::= ᐸtable reference> CROSS JOIN ᐸtable reference>
ᐸqualified join> ::= ᐸtable reference> ᐸjoin type> JOIN ᐸtable reference> [ ᐸjoin specification> ]
ᐸjoin type> ::=
NATURAL
| INNER
| ᐸouter join type> [ OUTER ]
ᐸouter join type> ::= LEFT | RIGHT | FULL
ᐸjoin specification> ::= ᐸjoin condition>
ᐸjoin condition> ::= ON ᐸsearch condition>
ᐸsearch condition> ::=
ᐸboolean term>
| ᐸsearch condition> OR ᐸboolean term>
ᐸboolean term> ::=
ᐸboolean factor>
| ᐸboolean term> AND ᐸboolean factor>
ᐸboolean factor> ::= [ NOT ] ᐸboolean test>
ᐸboolean test> ::= ᐸboolean primary>
ᐸboolean primary> ::= ᐸpredicate> | ᐸleft paren> ᐸsearch condition> ᐸright paren>
ᐸpredicate> ::=
ᐸcomparison predicate>
| ᐸlike predicate>
| ᐸnull predicate>
ᐸcomparison predicate> ::= ᐸrow value constructor> ᐸcomp op> ᐸrow value constructor>
ᐸrow value constructor> ::=
ᐸrow value constructor element>
ᐸlike predicate> ::= ᐸmatch value> [ NOT ] LIKE ᐸpattern> [ ESCAPE ᐸescape character> ]
ᐸmatch value> ::= ᐸcharacter value expression>
ᐸqualifier> ::= ᐸtable name> | ᐸcorrelation name>
ᐸpattern> ::= ᐸcharacter value expression>
ᐸescape character> ::= ᐸcharacter value expression>
ᐸnull predicate> ::= IS [ NOT ] NULL
ᐸwhere clause> ::= WHERE ᐸsearch condition>
ᐸgroup by clause> ::= GROUP BY ᐸgrouping column reference list>
ᐸgrouping column reference list> ::=
ᐸgrouping column reference> [ { ᐸcomma> ᐸgrouping column reference> }... ]
ᐸgrouping column reference> ::= ᐸcolumn reference>
ᐸorder by clause> ::= ORDER BY ᐸordering column reference list> [ SKIP ᐸunsigned integer> ] [ FETCH ᐸunsigned integer> ]
ᐸordering column reference list> ::=
ᐸordering column reference> [ { ᐸcomma> ᐸordering column reference> }... ]
ᐸordering column reference> ::= ᐸcolumn reference> [ DESC ]
ᐸcomp op> ::=
ᐸequals operator>
| ᐸnot equals operator>
| ᐸless than operator>
| ᐸgreater than operator>
| ᐸless than or equals operator>
| ᐸgreater than or equals operator>
Most notable differences are:
- support for multipart identifiers
- SKIP FETCH instead of OFFSET FETCH
- smaller set of supported functions
