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