Final Prep Answers V2 PDF

Preview

Summary

Final prep for exam, from 2020 includes the answers to everything...

Description

Final Prep Answers 1. Natural Inner Join T A 3 3 5 1

B 2 3 3 7

C 4 4 1 5

D 9 9 8 6

2. 𝑅 − 𝑆

The only correct answer is 𝑐. 𝑘 = 10, 𝑛 = 13, 𝑝 = 8.

For any two relations 𝑋 𝑎𝑛𝑑 𝑌, the relation 𝑋 − 𝑌 contains at least 𝑥 − 𝑦 tuples, where 𝑥 = the number of tuples in 𝑋 and 𝑦 = the number of tuples in 𝑌. This eliminates option D. Without any information on what the tuples in R and S are, the only thing other we can say for sure about 𝑅 − 𝑆 is that it must contain as many or fewer tuples than R. This eliminates options A and B. Option C fits both of our criteria; 𝑝 ≥ 𝑘 − 𝑛 and 𝑝 ≤ 𝑘. 3. Projection Query C 3 3 6 3 6

B 2 2 5 5 2

Please note the solutions provided for Questions 4 through 10 are not necessarily unique. Other solutions (perhaps even better solutions) may exist. 4. SQL Pizzerias 1 a. The price of the most expensive pizza at all pizzerias whose least expensive pizza is at least $7 SELECT pizzeria, MAX(price) FROM Serves GROUP BY pizzeria HAVING MIN(price) >= 7 b. The price of the most expensive pizza at all pizzerias whose names end in a “t” SELECT pizzeria, MAX(price) FROM Serves WHERE pizzeria LIKE “%t” GROUP BY pizzeria

5. Pizzerias 1 SQL: SELECT S.pizzeria FROM Serves S WHERE NOT EXISTS ((SELECT name FROM Person EXCEPT (SELECT name FROM Frequents F WHERE S.pizzeria=F.pizzeria)) RA:

𝜋𝑝𝑖𝑧𝑧𝑒𝑟𝑖𝑎,𝑛𝑎𝑚𝑒 (𝐹𝑟𝑒𝑞𝑢𝑒𝑛𝑡𝑠) ÷ 𝜋𝑛𝑎𝑚𝑒 (𝑃𝑒𝑟𝑠𝑜𝑛)

6. Pizzerias 2 SQL: SELECT E.PIZZA FROM PERSON P, EATS E WHERE P.Name = E.Name AND P.Age > 20 AND P.Gender = “Female” RA:

𝜋𝑝𝑖𝑧𝑧𝑎 (𝜎𝑎𝑔𝑒>20 ∧ 𝑔𝑒𝑛𝑑𝑒𝑟="𝑓𝑒𝑚𝑎𝑙𝑒" (𝐸𝑎𝑡𝑠 ⋈ 𝑃𝑒𝑟𝑠𝑜𝑛)) 7. Pizzerias 3 SQL: SELECT pizzeria FROM Serves WHERE price < 10 AND pizza IN (SELECT pizza FROM Eats WHERE name = “Amy” or name = “Bob”) RA:

𝜋𝑝𝑖𝑧𝑧𝑒𝑟𝑖𝑎 (

𝜎𝑝𝑟𝑖𝑐𝑒= 24) OR pizza NOT IN (SELECT pizza FROM Serves WHERE price >= 10) RA:

(𝜋𝑝𝑖𝑧𝑧𝑎 (𝑆𝑒𝑟𝑣𝑒𝑠) − 𝜋𝑝𝑖𝑧𝑧𝑎 (𝜎𝑎𝑔𝑒≥24 (𝐸𝑎𝑡𝑠 ⋈ 𝑃𝑒𝑟𝑠𝑜𝑛)))

∪ (𝜋𝑝𝑖𝑧𝑧𝑎 (𝑆𝑒𝑟𝑣𝑒𝑠) − 𝜋𝑝𝑖𝑧𝑧𝑎 (𝜎𝑝𝑟𝑖𝑐𝑒≥10 (𝑆𝑒𝑟𝑣𝑒𝑠)))

10. Pizzeria 6 SQL: SELECT MAX(age) FROM Person, Eats WHERE Person.name=Eats.name AND pizza = “mushroom”...