Find all pizzerias that serve every pizza eaten by people over 30

Encountered this Relational Algebra exercise in the Introduction to Databases free online course  offered by Stanford University in Fall, 2011.

Question 9
Find all pizzerias that serve every pizza eaten by people over 30.
(This query is very challenging; extra congratulations if you get it right.)

Person

name age gender
Amy 16 female
Ben 21 male
Cal 33 male
Dan 13 male
Eli 45 male
Fay 21 female
Gus 24 male
Hil 30 female
Ian 18 male

Frequents

name pizzeria
Amy Pizza Hut
Ben Chicago Pizza
Ben Pizza Hut
Cal New York Pizza
Cal Straw Hat
Dan New York Pizza
Dan Straw Hat
Eli Chicago Pizza
Eli Straw Hat
Fay Dominos
Fay Little Caesars
Gus Chicago Pizza
Gus Pizza Hut
Hil Dominos
Hil Pizza Hut
Hil Straw Hat
Ian Dominos
Ian New York Pizza
Ian Straw Hat

Eats

name pizza
Amy mushroom
Amy pepperoni
Ben cheese
Ben pepperoni
Cal supreme
Dan cheese
Dan mushroom
Dan pepperoni
Dan sausage
Dan supreme
Eli cheese
Eli supreme
Fay mushroom
Gus cheese
Gus mushroom
Gus supreme
Hil cheese
Hil supreme
Ian pepperoni
Ian supreme

Serves

pizzeria pizza price
Chicago Pizza cheese 7.75
Chicago Pizza supreme 8.5
Dominos cheese 9.75
Dominos mushroom 11
Little Caesars cheese 7
Little Caesars mushroom 9.25
Little Caesars pepperoni 9.75
Little Caesars sausage 9.5
New York Pizza cheese 7
New York Pizza pepperoni 8
New York Pizza supreme 8.5
Pizza Hut cheese 9
Pizza Hut pepperoni 12
Pizza Hut sausage 12
Pizza Hut supreme 12
Straw Hat cheese 9.25
Straw Hat pepperoni 8
Straw Hat sausage 9.75

These slides explain how to formulate relational algebra queries like the one presented above.


Leave a comment