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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 |
ᐸ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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 |
ᐸ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