Example pastries database: PASTRY table Pastry Calories muffin 200 doughnut 400

Question

Example pastries database:
PASTRY table
Pastry Calories
muffin 200
doughnut 400
croissant 100
danish 300

BAKERY table

Baker Pastry Cost

1 muffin 0.50
1 croissant 0.75
1 danish 0.75
2 muffin 0.40
2 croissant 0.90
3 doughnut 0.30


Write code to answer the following questions about the pastries database:

9) Using SELECT, PROJECT and JOIN, write code to find the bakers who make a
pastry with 100 calories






10) Using SELECT, PROJECT and JOIN, write code to find the pastries made by baker
1, with calories of each pastry






11) Using SELECT, PROJECT and JOIN, write code to find which bakers make
croissants






12) Using SELECT, PROJECT and JOIN, write code to find the bakers who make both
muffins and croissants

Explanation / Answer

1) sql query:

select P.pastry from pastry P , bakery B where ((P.pastry = B.pastry) and (P.calorie=100));

in relational algebra:

project baker ( select ( P.pastry=B.pastry) and (P.calorie=100) (P join B) )

2)

sql query:

select P.pastry,B.calorie from pastry P , bakery B where (B.baker = 1 and B.pastry=P.pastry);

in relational algebra:

  project B.pastry,P.calorie ( select  (B.baker = 1 and B.pastry=P.pastry) (P join B) )

3) sql query:

select baker from bakery where pastry='criossant';

in relational algebra:

project baker (select (pastry='criossant') ( bakery) )

4)

sql query:

select distinct baker from bakery where (pastry='muffin' and pastry='criossant');

in relational algebra:

project (baker) (select (pastry='muffin' and pastry='criossant') (bakery))

Related Questions

Navigate

• Browse (All)
• Browse E
• Subjects

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