Question

need a solution/explanation for this SQL problem.

7. Display a list of customer names with furniture series that purchased more than twice by this customer. Also, display the number of times of purchase.

8. Display a list of furniture series that are priced above the average of all other series.

9. Display the customer ID of the the customers, the series and price of the most expensive furniture they purchased. Sort the result by customer ID in ascending order

CUSTOMER (Cust_ID, Cust_First_name, Cust_Last_Name, Cust_Email, Cust_Street, Cust_Coty, Cust_State, Cust_Zip, Cust_Gender, Cust_CC)

PURORDER (Order_ID, Cust_ID, Order_Date, ORder_Delivery)

SUPPLIER (Supp_ID, Supp_Name, Supp_Street, Supp_City, Supp_State, Supp_Zip, Supp_license)

FURNITURE (Furn_ID, Supp_ID, FUrn_series, FUrn_material,

FUrn_Type, Furn_Color, Furn_price)

Answer 1

7.

select CUSTOMER.Cust_First_name, CUSTOMER.Cust_Last_Name, FURNITURE.FUrn_series, count(FURNITURE.FUrn_series)
from CUSTOMER, PURORDER, SUPPLIER, FURNITURE
where CUSTOMER.Cust_ID=PURORDER.Cust_ID
AND PURORDER.Order_ID=SUPPLIER.Order_ID
AND SUPPLIER.Supp_ID=FURNITURE.Supp_ID
AND sum(FURNITURE.FUrn_series)>2;

8.

select FURNITURE.FUrn_series
from CUSTOMER, PURORDER, SUPPLIER, FURNITURE
where CUSTOMER.Cust_ID=PURORDER.Cust_ID
AND PURORDER.Order_ID=SUPPLIER.Order_ID
AND SUPPLIER.Supp_ID=FURNITURE.Supp_ID
AND FURNITURE.Furn_price > avg(FURNITURE.Furn_price);

9.

select CUSTOMER.Cust_ID, FURNITURE.FUrn_series, max(FURNITURE.Furn_price)
from CUSTOMER, PURORDER, SUPPLIER, FURNITURE
where CUSTOMER.Cust_ID=PURORDER.Cust_ID
AND PURORDER.Order_ID=SUPPLIER.Order_ID
AND SUPPLIER.Supp_ID=FURNITURE.Supp_ID
ORDER BY CUSTOMER.Cust_ID;