Difference between revisions of "Introduction to Databases with Stanford University : Relational Algebra Exercises"
Line 30: | Line 30: | ||
\join Eats | \join Eats | ||
) | ) | ||
+ | ) | ||
+ | </pre> | ||
+ | |||
+ | 4. Find all pizzerias that serve at least one pizza for less than $10 that both Amy and Fay eat. | ||
+ | |||
+ | <pre> | ||
+ | \project_{pizzeria} ( | ||
+ | \project_{pizzeria,pizza,price} ( | ||
+ | \select_{name='Fay'} ( | ||
+ | \select_{price < 10} Serves | ||
+ | \join Eats | ||
+ | ) | ||
+ | ) \join | ||
+ | \project_{pizzeria,pizza,price} ( | ||
+ | \select_{name='Amy'} ( | ||
+ | \select_{price < 10} Serves | ||
+ | \join Eats | ||
+ | ) | ||
+ | ) | ||
+ | ) | ||
+ | </pre> | ||
+ | |||
+ | 5. Find the names of all people who eat at least one pizza served by Dominos but who do not frequent Dominos. | ||
+ | |||
+ | <pre> | ||
+ | \project_{name} ( | ||
+ | \select_{pizzeria='Dominos'} Serves \ | ||
+ | \join Eats | ||
+ | ) | ||
+ | \diff | ||
+ | \project_{name} ( | ||
+ | \select_{pizzeria = 'Dominos'} Frequents | ||
+ | ) | ||
+ | </pre> | ||
+ | |||
+ | 6. Find all pizzas that are eaten only by people younger than 24, or that cost less than $10 everywhere they're served. | ||
+ | |||
+ | <pre> | ||
+ | ( | ||
+ | \project_{pizza} ( | ||
+ | \select_{age < 24} (Person \join Eats) | ||
+ | ) | ||
+ | \diff | ||
+ | \project_{pizza} ( | ||
+ | \select_{age >= 24} (Person \join Eats) | ||
+ | ) | ||
+ | ) | ||
+ | \union | ||
+ | ( | ||
+ | \project_{pizza} ( | ||
+ | \select_{price < 10} Serves | ||
+ | ) | ||
+ | \diff | ||
+ | \project_{pizza} ( | ||
+ | \select_{price >= 10} Serves | ||
+ | ) | ||
+ | ) | ||
+ | </pre> | ||
+ | |||
+ | 7. Find the age of the oldest person (or people) who eat mushroom pizza. | ||
+ | |||
+ | <pre> | ||
+ | \project_{age} (( | ||
+ | \select_{pizza = 'mushroom'} Eats | ||
+ | ) \join Person | ||
+ | ) | ||
+ | \diff | ||
+ | \project_{age1} ( | ||
+ | \select_{age2 > age1} ( | ||
+ | \rename_{age1} ( | ||
+ | \project_{age} (( | ||
+ | \select_{pizza = 'mushroom'} Eats | ||
+ | ) \join Person | ||
+ | ) | ||
+ | ) | ||
+ | \cross | ||
+ | \rename_{age2} ( | ||
+ | \project_{age} (( | ||
+ | \select_{pizza = 'mushroom'} Eats | ||
+ | ) \join Person | ||
+ | ) | ||
+ | ) | ||
+ | )) | ||
+ | </pre> | ||
+ | |||
+ | 8. Find all pizzerias that serve only pizzas eaten by people over 30. | ||
+ | |||
+ | <pre> | ||
+ | \project_{pizzeria} ( | ||
+ | Serves | ||
+ | ) | ||
+ | \diff | ||
+ | \project_{pizzeria} ( | ||
+ | ( | ||
+ | \project_{pizza} ( | ||
+ | Serves | ||
+ | ) | ||
+ | \diff | ||
+ | \project_{pizza} ( | ||
+ | \select_{age > 30} Person | ||
+ | \join Eats | ||
+ | )) \join Serves | ||
+ | ) | ||
+ | </pre> | ||
+ | |||
+ | 9. Find all pizzerias that serve every pizza eaten by people over 30. | ||
+ | |||
+ | <pre> | ||
+ | \project_{pizzeria} ( | ||
+ | Serves | ||
+ | ) | ||
+ | \diff | ||
+ | \project_{pizzeria} ( | ||
+ | \project_{pizzeria,pizza} (( | ||
+ | \select_{age > 30} Person | ||
+ | \join Eats | ||
+ | ) \cross Serves | ||
+ | ) | ||
+ | \diff | ||
+ | \project_{pizzeria,pizza} ( | ||
+ | Serves | ||
+ | ) | ||
) | ) | ||
</pre> | </pre> |
Revision as of 17:58, 16 November 2011
- Introduction to Databases by Standford University http://db-class.org
- Additional materials: http://openclassroom.stanford.edu/MainFolder/CoursePage.php?course=IntroToDatabases
1. Find all pizzas eaten by at least one female over the age of 20.
\project_{pizza} ( \select_{gender='female' and age > 20} Person \join Eats)
2. Find the names of all females who eat at least one pizza served by Straw Hat. (Note: The pizza need not be eaten at Straw Hat.)
\project_{name} ( \select_{gender='female'} ( ( \select_{pizzeria='Straw Hat'} Serves \join Eats ) \join Person ) )
3. Find all pizzerias that serve at least one pizza for less than $10 that either Amy or Fay (or both) eat.
\project_{pizzeria} ( \select_{name='Amy' or name='Fay'} ( \select_{price < 10} Serves \join Eats ) )
4. Find all pizzerias that serve at least one pizza for less than $10 that both Amy and Fay eat.
\project_{pizzeria} ( \project_{pizzeria,pizza,price} ( \select_{name='Fay'} ( \select_{price < 10} Serves \join Eats ) ) \join \project_{pizzeria,pizza,price} ( \select_{name='Amy'} ( \select_{price < 10} Serves \join Eats ) ) )
5. Find the names of all people who eat at least one pizza served by Dominos but who do not frequent Dominos.
\project_{name} ( \select_{pizzeria='Dominos'} Serves \ \join Eats ) \diff \project_{name} ( \select_{pizzeria = 'Dominos'} Frequents )
6. Find all pizzas that are eaten only by people younger than 24, or that cost less than $10 everywhere they're served.
( \project_{pizza} ( \select_{age < 24} (Person \join Eats) ) \diff \project_{pizza} ( \select_{age >= 24} (Person \join Eats) ) ) \union ( \project_{pizza} ( \select_{price < 10} Serves ) \diff \project_{pizza} ( \select_{price >= 10} Serves ) )
7. Find the age of the oldest person (or people) who eat mushroom pizza.
\project_{age} (( \select_{pizza = 'mushroom'} Eats ) \join Person ) \diff \project_{age1} ( \select_{age2 > age1} ( \rename_{age1} ( \project_{age} (( \select_{pizza = 'mushroom'} Eats ) \join Person ) ) \cross \rename_{age2} ( \project_{age} (( \select_{pizza = 'mushroom'} Eats ) \join Person ) ) ))
8. Find all pizzerias that serve only pizzas eaten by people over 30.
\project_{pizzeria} ( Serves ) \diff \project_{pizzeria} ( ( \project_{pizza} ( Serves ) \diff \project_{pizza} ( \select_{age > 30} Person \join Eats )) \join Serves )
9. Find all pizzerias that serve every pizza eaten by people over 30.
\project_{pizzeria} ( Serves ) \diff \project_{pizzeria} ( \project_{pizzeria,pizza} (( \select_{age > 30} Person \join Eats ) \cross Serves ) \diff \project_{pizzeria,pizza} ( Serves ) )