CIS 770: Database System Implementation Notes 7-2: Query Parsing

1
CIS 770 Database System ImplementationNotes
7-2 Query Parsing

• Douglas S. Kerr

2
A Simple Subset of SQL
Keywords SELECT, FROM WHERE, AND, IN, LIKE, , lt
3
Example Database and Query
StarsIn(title, year, starName) MovieStar(name,
address, gender, birthdate)
SELECT title FROM StarsIn WHERE starName IN
( SELECT name FROM MovieStar WHERE gender
F AND birthdate LIKE 1960 )
4
A Grammar for Simple SQL
Queries ltQuerygt ltSFWgt ltQuerygt ( ltQuerygt
) Select-From-Where ltSFWgt SELECT ltSelListgt
FROM ltFromListgt WHERE ltConditiongt Select-Lists ltSe
lListgt ltAttributegt, ltSelListgt ltSelListgt
ltAttributegt Conditions ltConditiongt
ltConditiongt AND ltConditiongt ltConditiongt
ltTuplegt IN ltQuerygt ltConditiongt ltAttributegt
ltAttributegt ltConditiongt ltAttributegt
ltValuegt ltConditiongt ltAttributegt LIKE ltPatterngt
From-Lists ltFromListgt ltRelationgt,
ltFromListgt ltFromListgt ltRelationgt
ltTuplegt ltAttributegt
5
A Grammar for Simple SQL, contd.
Special Syntactic Categories ltRelationgt name of
any table in database ltAttributegt name of any
attribute in a table ltValuegt number or character
string in quotes ltPatterngt quoted string of legal
SQL pattern, e.g., a-z, A-Z, 0-9, ,
6
A Simple Query and Parse Tree
SELECT title FROM StarsIn, MovieStar WHERE starNa
me name AND gender F
ltquerygt ltSFWgt SELECT ltSelListgt FROM
ltFromListgt WHERE ltConditiongt ltAttributegt
ltRelNamegt , ltFromListgt title
StarsIn ltRelNamegt MovieStar
ltConditiongt AND ltConditiongt ltAttributegt
ltAttributegt ltAttributegt ltValuegt starName
name gender F
7
Conversion to Relational Algebra
Query of the form ltquerygt ltSFWgt SELECT
ltSelListgt FROM ltFromListgt WHERE
ltConditiongt where ltConditiongt has no subqueries
p ltSelListgt s ltConditiongt X ltFromListgt
8
Simple Query
SELECT title FROM StarsIn, MovieStar WHERE starNa
me name AND gender F
9
Removing Subqueries From Conditions
SELECT title FROM StarsIn WHERE starName IN
( SELECT name FROM MovieStar WHERE gender
F AND birthdate LIKE 1960)
Find the titles of movies with female movie stars
born in 1960.
Uncorrelated subquery
SELECT DISTINCT m1.title, m1.year FROM StarsIn
m1 WHERE m1.year 40 lt ( SELECT AVG(birthdate)
FROM StarsIn m2, MovieStar s WHERE m2.starName
s.name AND m1.title m2.title
AND m1.year m2.year)
Find the movies where the average age of the
stars was at most 40 when the movie was made.
Correlated subquery
10
Removing Subqueries From Conditions
SELECT title FROM StarsIn WHERE starName IN
( SELECT name FROM MovieStar WHERE gender
F AND birthdate LIKE 1960 )
Apply select-from-where rule to subquery.
11
SELECT title FROM StarsIn WHERE starName IN
( SELECT name FROM MovieStar WHERE gender
F AND birthdate LIKE 1960)
Completing the parse
12
Removing Subqueries From Conditions Step 1
Introduce two-argument select (s) operator
13
Removing Subqueries From Conditions Step 2
Convert two-argument select (s) operator to
Cartesian product
14
Step 1 Introduce two-argument select (s) operator
Example
15
Step2 Convert two-argument select (s) operator
to Cartesian product
16
Convert s,X to join.
17
Correlated subquery
SELECT DISTINCT m1.title, m1.year FROM StarsIn
m1 WHERE m1.year 40 lt ( SELECT AVG(birthdate)
FROM StarsIn m2, MovieStar s WHERE m2.starName
s.name AND m1.title m2.title
AND m1.year m2.year)
Find the movies where the average age of the
stars was at most 40 when the movie was made.
Translate the subquery with extra attributes to
be compared with attributes of outer query,
namely title and year.
18
Adding Extra Attributes in Subquery
SELECT AVG(birthdate) FROM StarsIn m2, MovieStar
s WHERE m2.starName s.name AND m1.title
m2.title AND m1.year m2.year
SELECT m2.title, m2. year, AVG(birthdate) FROM St
arsIn m2, MovieStar s WHERE m2.starName
s.name GROUP BY m2. title, m2. year
19
Correlated subquery
SELECT DISTINCT m1.title, m1.year FROM StarsIn
m1 WHERE m1.year 40 lt ( SELECT AVG(birthdate)
FROM StarsIn m2, MovieStar s WHERE m2.starName
s.name AND m1.title m2.title
AND m1.year m2.year)
20
Improving the Logical Query Plan

• Equating title and year means join may be
  eliminated.

21
Improving the Logical Query Plan
Eliminating the 1st join.
22
Grouping Associative/Commutative Operators

• Recall the natural join, intersection and union
  are associative/commutative operators.
• Allows us to pick most efficient order of
  evaluation.

R ?(S ? T)
(R ?S) ? T
R ?S ? T
?
23
(No Transcript)
24

• ?
• pM2.title, m2.year
• sm2.year lt 40
• m1.title m2.title AND m1.year m2.year
• starName name
• StarsIn

p name s gender F AND birthdate LIKE
1960 MovieStar