Consider the relations: Customer(CID, Name, City, State), Order(OID, CID, Date),
ID: 3878064 • Letter: C
Question
Consider the relations: Customer(CID, Name, City, State), Order(OID, CID, Date), and Product(PID, ProductName, Price) LineItem(LID, OID,PID, Number, TotalPrice), where CID is a customer id and is a key for Customer, OID is an order id and is a key for Order, and LID is a line item id and is a key for LineItem. In addition the attribute CID of Order is a foreign key referring to the CID of Customer, that is, for each CID c of Order there is exactly one tuple of Customer whose CID attribute is c. The OID of LineItem is a foreign key referring to the OID of Order. There are several line items for the same order, a line item refers to a product and contains the quantity ordered for the product.
Express the following queries Algebra:
1. List the customers who ordered computers
2. List the other products bought by customers who bought computers
3. List the customers who did not place any order since January 1 2014
4. List the products bought by at least one customer of Newark
5. List the products bought by all the customers of Newark
6. List the products ordered only by the customers of Newark
7. List the products never ordered by customers of New Jersey
Explanation / Answer
1. List the customers who ordered computers
SELECT C.Name FROM Customer C INNER JOIN LineItem L ON C.CID=L.CID
INNER JOIN Product P ON L.PID=p.PID
WHERE ProductName='computers';
2. List the other products bought by customers who bought computers
SELECT P.ProductName FROM Product P INNER JOIN LineItem L ON P.PID=L.PID
WHERE L.OID IN (SELECT C.CID FROM order O INNER JOIN LineItem L ON O.OID=L.OID
INNER JOIN Product P ON L.PID=p.PID
INNER JOIN Customer C.CID=O.CID
WHERE ProductName='computers')
3. List the customers who did not place any order since January 1 2014
SELECT C.Name FROM Customer C INNER JOIN Order O ON C.CID=O.CID
WHERE Date<="YYYY-MM-DD"--Please provide database date format
4. List the products bought by at least one customer of Newark
SELECT P.ProductName FROM Product P INNER JOIN LineItem L ON P.PID=L.PID
INNER JOIN Order O L.OID=O.OID
INNER JOIN Customer C ON O.CID=C.CID
WHERE C.CID =ANY (SELECT OID FROM Order where Name='Newark')
5. List the products bought by all the customers of Newark
SELECT P.ProductName FROM Product P INNER JOIN LineItem L ON P.PID=L.PID
INNER JOIN Order O L.OID=O.OID
INNER JOIN Customer C ON O.CID=C.CID
WHERE C.CID =ALL (SELECT OID FROM Order where Name='Newark')
6. List the products ordered only by the customers of Newark
SELECT P.ProductName FROM order O INNER JOIN LineItem L ON O.OID=L.OID
INNER JOIN Product P ON L.PID=p.PID
INNER JOIN Customer C.CID=O.CID
WHERE C.Name='Newark';
7. List the products never ordered by customers of New Jersey
SELECT P.ProductName FROM order O INNER JOIN LineItem L ON O.OID=L.OID
INNER JOIN Product P ON L.PID=p.PID
INNER JOIN Customer C.CID=O.CID
WHERE C.Name<>'New Jersey';