Difference between revisions of "Introduction to Databases with Stanford University : Relational Algebra Exercises"

From Ittichai Chammavanijakul's Wiki
Jump to navigation Jump to search
(Created page with " 1. Find all pizzas eaten by at least one female over the age of 20. <pre> \project_{pizza} ( \select_{gender='female' and age > 20} Person \join Eats) </pre>")
 
 
(4 intermediate revisions by the same user not shown)
Line 1: Line 1:
 +
* Introduction to Databases by Standford University http://db-class.org
 +
* Additional materials: http://openclassroom.stanford.edu/MainFolder/CoursePage.php?course=IntroToDatabases
 +
* Database data [[Pizza_data]]
  
 
1. Find all pizzas eaten by at least one female over the age of 20.
 
1. Find all pizzas eaten by at least one female over the age of 20.
Line 6: Line 9:
 
  \select_{gender='female' and age > 20} Person  
 
  \select_{gender='female' and age > 20} Person  
 
   \join Eats)  
 
   \join Eats)  
 +
</pre>
 +
 +
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.)
 +
 +
<pre>
 +
\project_{name} (
 +
  \select_{gender='female'} (
 +
      ( \select_{pizzeria='Straw Hat'} Serves
 +
        \join Eats
 +
      ) \join Person
 +
  )
 +
)
 +
</pre>
 +
 +
3. Find all pizzerias that serve at least one pizza for less than $10 that either Amy or Fay (or both) eat.
 +
 +
<pre>
 +
\project_{pizzeria} (
 +
  \select_{name='Amy' or name='Fay'} (
 +
      \select_{price < 10} Serves
 +
      \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>

Latest revision as of 18:28, 16 November 2011

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 
) 
)
Retrieved from "https://ittichaicham.com/wiki/index.php?title=Introduction_to_Databases_with_Stanford_University_:_Relational_Algebra_Exercises&oldid=613"