Q1.
a)
𝜋𝜋𝑎𝑎𝑎𝑎𝑎𝑎�(𝜋𝜋𝑐𝑐𝑐𝑐𝑐𝑐𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂 − 𝜋𝜋𝑐𝑐𝑐𝑐𝑐𝑐(�𝜎𝜎𝑦𝑦𝑎𝑎𝑎𝑎𝑦𝑦≤2000𝐵𝐵𝐵𝐵𝐵𝐵𝐵𝐵𝑂𝑂� ⋈ 𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂)� ⋈ 𝐶𝐶𝐶𝐶𝑂𝑂𝐶𝐶𝐵𝐵𝐶𝐶𝑂𝑂𝑂𝑂𝑂𝑂)
b)
𝜋𝜋𝑐𝑐𝑐𝑐𝑎𝑎𝑐𝑐𝑎𝑎�(𝜋𝜋𝑐𝑐𝑐𝑐𝑐𝑐𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂 ⋈ �𝜎𝜎𝑎𝑎𝑎𝑎𝑎𝑎ℎ𝑜𝑜𝑦𝑦=”𝐸𝐸𝑐𝑐𝑎𝑎𝑎𝑎𝑦𝑦𝐸𝐸𝑜𝑜𝑐𝑐𝑐𝑐”𝐵𝐵𝐵𝐵𝐵𝐵𝐵𝐵𝑂𝑂� − 𝜋𝜋𝑐𝑐𝑐𝑐𝑐𝑐(�𝜎𝜎𝑝𝑝𝑦𝑦𝑐𝑐𝑐𝑐𝑎𝑎>100𝐵𝐵𝐵𝐵𝐵𝐵𝐵𝐵𝑂𝑂� ⋈ 𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂)�
⋈ 𝐶𝐶𝐶𝐶𝑂𝑂𝐶𝐶𝐵𝐵𝐶𝐶𝑂𝑂𝑂𝑂𝑂𝑂)
c)
𝜋𝜋𝑧𝑧𝑐𝑐𝑝𝑝𝑐𝑐𝑜𝑜𝑐𝑐𝑎𝑎 �(𝜋𝜋𝑏𝑏𝑐𝑐𝑐𝑐�𝜎𝜎𝑎𝑎𝑎𝑎𝑎𝑎ℎ𝑜𝑜𝑦𝑦=”𝐸𝐸𝑐𝑐𝑎𝑎𝑎𝑎𝑦𝑦𝐸𝐸𝑜𝑜𝑐𝑐𝑐𝑐”^𝑏𝑏𝑐𝑐𝑎𝑎𝑐𝑐𝑎𝑎=”𝐷𝐷𝑎𝑎𝑎𝑎𝑎𝑎𝑏𝑏𝑎𝑎𝐷𝐷𝑎𝑎𝐷𝐷”𝐵𝐵𝐵𝐵𝐵𝐵𝐵𝐵𝑂𝑂�� ⋈ 𝜎𝜎𝑞𝑞𝑎𝑎𝑎𝑎𝑐𝑐𝑎𝑎𝑐𝑐𝑎𝑎𝑦𝑦≥20𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂
⋈ 𝐶𝐶𝐶𝐶𝑂𝑂𝐶𝐶𝐵𝐵𝐶𝐶𝑂𝑂𝑂𝑂𝑂𝑂)
d)
𝜌𝜌(𝐵𝐵1,𝐵𝐵𝐵𝐵𝐵𝐵𝐵𝐵𝑂𝑂 ⋈ 𝜋𝜋𝑏𝑏𝑐𝑐𝑐𝑐𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂 )
𝜌𝜌(𝐵𝐵2,𝐵𝐵1 )
𝜋𝜋𝑐𝑐𝑐𝑐𝑎𝑎𝑐𝑐𝑎𝑎.𝑧𝑧𝑐𝑐𝑝𝑝𝑐𝑐𝑜𝑜𝑐𝑐𝑎𝑎((𝜋𝜋𝑏𝑏𝑏𝑏𝑂𝑂𝐵𝐵1 − 𝜋𝜋𝐵𝐵1.𝑏𝑏𝑏𝑏𝑂𝑂(𝐵𝐵1 ⋈(𝐵𝐵1.𝑝𝑝𝑂𝑂𝑏𝑏𝑝𝑝𝑂𝑂 <𝐵𝐵2.𝑝𝑝𝑂𝑂𝑏𝑏𝑝𝑝𝑂𝑂) 𝐵𝐵2)) ⋈ 𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂 ⋈ 𝐶𝐶𝐶𝐶𝑂𝑂𝐶𝐶𝐵𝐵𝐶𝐶𝑂𝑂𝑂𝑂𝑂𝑂) e) 𝜌𝜌(𝐶𝐶1,𝐶𝐶𝐶𝐶𝑂𝑂𝐶𝐶𝐵𝐵𝐶𝐶𝑂𝑂𝑂𝑂𝑂𝑂 ) 𝜌𝜌(𝐶𝐶2,𝐶𝐶𝐶𝐶𝑂𝑂𝐶𝐶𝐵𝐵𝐶𝐶𝑂𝑂𝑂𝑂𝑂𝑂 ) 𝜌𝜌(𝐵𝐵1,𝜋𝜋𝑏𝑏𝑐𝑐𝑐𝑐(𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂𝑂 ⋈ �𝜋𝜋𝑝𝑝𝑏𝑏𝑂𝑂𝐶𝐶1 − 𝜋𝜋𝐶𝐶1.𝑝𝑝𝑏𝑏𝑂𝑂�𝐶𝐶1 ⋈(𝐶𝐶1.𝑎𝑎𝑎𝑎𝑂𝑂>𝐶𝐶2.𝑎𝑎𝑎𝑎𝑂𝑂) 𝐶𝐶2��) ⋈ 𝐵𝐵𝐵𝐵𝐵𝐵𝐵𝐵𝑂𝑂 )
𝜌𝜌(𝐵𝐵2,𝐵𝐵1 )
𝜋𝜋𝑏𝑏𝑎𝑎𝑐𝑐𝑎𝑎𝑏𝑏𝑎𝑎((𝜋𝜋𝑏𝑏𝑏𝑏𝑂𝑂𝐵𝐵1 − 𝜋𝜋𝐵𝐵1.𝑏𝑏𝑏𝑏𝑂𝑂(𝐵𝐵1 ⋈(𝐵𝐵1.𝑝𝑝𝑂𝑂𝑏𝑏𝑝𝑝𝑂𝑂 <𝐵𝐵2.𝑝𝑝𝑂𝑂𝑏𝑏𝑝𝑝𝑂𝑂) 𝐵𝐵2)) ⋈ 𝐵𝐵𝐵𝐵𝐵𝐵𝐵𝐵𝑂𝑂) Q2. a) SELECT DISTINCT C.zipcode FROM Customers C WHERE NOT EXISTS( SELECT B.year FROM Books B MINUS SELECT B1.year FROM Orders O, Books B1 WHERE O.bid=B1.bid AND O.cid = C.cid AND O.quantity >= 1
)
b)
SELECT C.cid, MAX(B.price)
FROM Customers C, Orders O, Books B
WHERE C.cid=O.cid AND O.bid=B.bid
GROUP BY C.cid
HAVING 10,000 <= SUM( O.quantity * B.price ) c) SELECT DISTINCT C.age FROM Customers C, Orders O WHERE C.cid = O.cid AND C.cid NOT IN ( SELECT O1.cid FROM Orders O1, Books B WHERE O1.bid=B.bid AND NOT B.title LIKE “%Databases%” ) d) SELECT C.age FROM Customers C, Orders O, Books B WHERE C.cid=O.cid AND O.bid=B.bid AND B.price = (SELECT MAX(B1.price) FROM Books B1, Orders O1 WHERE B1.bid=O1.bid) e) SELECT TMP.cname FROM (SELECT C.cname, SUM (O.quantity * B.price) as DollarAmount FROM Customers C, Orders O, Books B WHERE C.cid=O.cid and O.bid = B.bid GROUP BY C.cid, C.cname ) TMP WHERE TMP.DollarAmount = (SELECT MAX(DollarAmount) FROM TMP)