1. Consider a database with the following schema: Person (name, age, gender) nam

Question

1. Consider a database with the following schema: Person (name, age, gender) name is a key Frequents (name, iceCreamShop) (name, ice cream shop) is a key Eats (name, flavor) (name, flavor) is a key Sells (iceCreamShop, flavor, price) (iceCreamShop, flavor) is a key Using relational algebra:

e) For each person, find all ice cream flavors the person eats that are not served by any ice cream shop the person frequents. Return the name of the person and the ice cream flavor in pairs.

f) Find an ice cream shop that is frequented by people older than 30 but younger than 40 years old.

g) Find all the names of females that frequent every ice cream shop serving at least one flavor they eat. h) Find the ice cream shop with the most expensive scoop of vanilla ice cream, if there’s more than one return all that apply.

i) Find the names of all people who frequent only ice cream shops serving at least one flavor they like

Explanation / Answer

e) For each person, find all ice cream flavors the person eats that are not served by any ice cream shop the person frequents. Return the name of the person and the ice cream flavor in pairs.

select Name,Flavor from Eats where Flavor Not In (select Flavor from Sells where Icecreamshops not in (select Icecreamshops from Frequents))

f) Find an ice cream shop that is frequented by people older than 30 but younger than 40 years old.

select Icecreamshops from Frequents where Name IN(select Name from Person where Age>30 And Age<40)

g) Find all the names of females that frequent every ice cream shop serving at least one flavor they eat.

select Name from Person p where Gender='F' and Name IN (select Icecreamshops from Frequents f where Icecreamshops IN(select Icecreamshops from Sells s where Flavor IN (select Flavor from Eats)))

h) Find the ice cream shop with the most expensive scoop of vanilla ice cream, if there’s more than one return all that apply.

select Icecreamshops from Sells where Flavor='venila' order by Price desc limit 1

i) Find the names of all people who frequent only ice cream shops serving at least one flavor they like

select Name from Frequents where Icecreamshops IN(select Icecreamshops from Sells)

Related Questions

Navigate

• Browse (All)
• Browse 1
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.