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:
- 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)
- The syntactic category
<Condition> has
not been defined
- This is the grammar used by the
text book
- 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> ::= movieTitleThe parse tree is now:
- Then we re-write
FromList using:
<FromList> ::= <Relation> , <FromList> (<FromList> ::= <Relation>) ::= <Relation> , <Relation> ::= StarsIn , <Relation> ::= StarsIn , MovieStarThe 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:
- We re-write
a Query using this
rule:
- SQL query:
- Example 2:
- SQL query:
SELECT movieTitle FROM StarsIn WHERE starName IN ( SELECT name FROM MovieStar WHERE birthdate LIKE '%1960' ) - The parse tree:
- SQL query: