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