Consider the relations: Customer(BID, Name, City, State), Order(RID, BID, Date),
ID: 3904795 • Letter: C
Question
Consider the relations: Customer(BID, Name, City, State), Order(RID, BID, Date), and Product(AID, ProductName, Price) LineItem(IID, RID, AID, Number, TotalPrice), where BID is a customer id and is a key for Customer, RID is an order id and is a key for Order, and AID is a line item id and is a key for LineItem. In addition the attribute BID of Order is a foreign key referring to the BID of Customer, that is, for each BID c of Order there is exactly one tuple of Customer whose BID attribute is c. The RID of LineItem is a foreign key referring to the RID 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 queries below in SQL. Create a database using Oracle or MySQL with the tables in the assignment, populate it so that you get at least 1 tuple as answer for every query.
1. List the products bought by all the customers of Houston
2. List the products ordered only by the customers of Houston
3. List the products bought by at least one customer of Houston
4. List the products never ordered by customers of Texas
5. List the customers who ordered computers
6. List the other products bought by customers who bought computers
7. List the customers who did not place any order since November 23 201
Views with SQL Queries
1. Make a view SaleAmount (SID, ProductName, Month, CustomerNumber SalesSold, Revenue) that gives for each the product and each month, the number of customers who bought the product, the quantity sold, and amount of money the sale corresponds to for the month.
2. Which product has the highest number of customers? How can this query be answered with the view? Without the view?
Explanation / Answer
If you have any doubts, please give me comment...
Customer(BID, Name, City, State)
Order(RID, BID, Date)
Product(AID, ProductName, Price)
LineItem(IID, RID, AID, Number, TotalPrice)
-- 1. List the products bought by all the customers of Houston
SELECT P.AID, ProductName, Price
FROM Product P
WHERE NOT EXISTS(
SELECT C.BID
FROM Customer C
WHERE C.City ='Houston' AND NOT EXISTS(
SELECT O.RID
FROM Order O, LineItem L
WHERE O.RID = L.RID AND P.AID = L.AID AND C.BID = O.BID
)
);
-- 2. List the products ordered only by the customers of Houston
SELECT P.AID, ProductName, Price
FROM Product P, LineItem L, Order O, Customer C
WHERE P.AID = L.AID AND L.RID = O.RID AND O.BID = C.BID AND City = 'Houston' AND P.AID NOT IN(
SELECT P1.AID
FROM Product P1, LineItem L1, Order O1, Customer C1
WHERE P1.AID = L1.AID AND L1.RID = O1.RID AND O1.BID = C1.BID AND City <> 'Houston'
);
-- 3. List the products bought by at least one customer of Houston
SELECT P.AID, ProductName, Price
FROM Product P, LineItem L, Order O, Customer C
WHERE P.AID = L.AID AND L.RID = O.RID AND O.BID = C.BID AND City = 'Houston';
-- 4. List the products never ordered by customers of Texas
SELECT P.AID, ProductName, Price
FROM Product P, LineItem L, Order O
WHERE P.AID = L.AID AND L.RID = O.RID AND O.BID NOT IN(
SELECT BID
FROM Customer
WHERE City ='Texas'
);
-- 5. List the customers who ordered computers
SELECT C.BID, C.Name
FROM Customer C, Order O, LineItem L, Product P
WHERE C.BID = O.BID AND O.RID = L.RID AND L.AID = P.AID AND P.ProductName = 'Computers';
-- 6. List the other products bought by customers who bought computers
SELECT P.AID, ProductName, Price
FROM Product P, LineItem L, Order O
WHERE P.AID = L.AID AND L.RID = O.RID AND O.BID NOT IN(
SELECT C1.BID
FROM Customer C1, Order O1, LineItem L1, Product P1
WHERE C1.BID = O1.BID AND O1.RID = L1.RID AND L1.AID = P1.AID AND P1.ProductName = 'Computers'
);
-- 7. List the customers who did not place any order since November 23 2011
SELECT BID, Name
FROM Customer
WHERE C.BID NOT IN(
SELECT C.BID
FROM ORDER
WHERE Date > '2011-11-23'
);