---

A simplified SQL grammar

---

---

---

 

Slideshow:

A simplified SQL grammar

Notes:

The database used to illustrate the SQL grammar
 
StarsIn     stores info on movies
MovieStar stores info on stars in the movies
Example of parsing an SQL query

SQL query:

Example of parsing an SQL query

SQL query:

Parsing the <Query> (syntactic category):

Example of parsing an SQL query

SQL query:

Re-writing the <SelList> (syntactic category):

Example of parsing an SQL query

SQL query:

Re-writing the <FromList> (syntactic category):

Example of parsing an SQL query

SQL query:

Finally, re-writing the <Condition> (syntactic category):

Another example of SQL parsing (sub-query)
SQL query:
 
Corresponding SQL parser tree:

❮ ❯



---

---

---

---

• A simplified SQL grammar
  • To illustrate the translation process from SQL query to physical query plan, we use a simplified SQL grammar:

    <Query> ::= SELECT <SelList> FROM <FromList> WHERE <Condition> <SelList> ::= <Attribute> | <Attribute> , <SelList> <FromList> ::= <Relation> | <Relation> , <FromList> <Condition> ::= <Condition> AND <Condition> | <Attribute> IN ( <Query> ) | <Attribute> = <Attribute> | <Attribute> LIKE <Pattern>

    ---

    ---

  • Note:

    This is the grammar used by the text book It is brief, but incomplete: The syntactic category <Condition> has not been defined (I.e.: a boolean expression)

  ---

  ---

  ---

  ---

• Example of parse trees
  • Relations used in the example:

    StarsIn( movieTitle, movieYear, starName ) The movie movieTitle made in movieYear features movie star starName MovieStar( name, address, gender, birthdate ) The movie star name has the specified address, gender and bdate

    ---

    ---

  • Example 1:

    SQL query: SELECT movieTitle FROM StarsIn, MovieStar WHERE starName = name AND birthdate LIKE '%1960' The parse tree: We re-write a Query using this rule: <Query> ::= SELECT <SelList> FROM <FromList> WHERE <Condition> The parse tree is now: Then we re-write SelList using: <SelList> ::= <Attribute> ::= movieTitle The parse tree is now: Then we re-write FromList using: <FromList> ::= <Relation> , <FromList> (<FromList> ::= <Relation>) ::= <Relation> , <Relation> ::= StarsIn , <Relation> ::= StarsIn , MovieStar The parse tree is now: Finally, we re-write Condition using: <Condition> ::= <Condition> AND <Condition> ::= <Attribute> = <Attribute> AND <Condition> ::= <Attribute> = <Attribute> AND <Attribute> LIKE <Pattern> ::= starName = name AND birthdate LIKE '%1960' The parse tree is:

    ---

    ---

  • Example 2:

    SQL query: SELECT movieTitle FROM StarsIn WHERE starName IN ( SELECT name FROM MovieStar WHERE birthdate LIKE '%1960' ) The parse tree: