Write SQL to answer the following questions based on the Pine Valley Furniture C
ID: 3723533 • Letter: W
Question
Write SQL to answer the following questions based on the Pine Valley Furniture Company database. Note: the Pine Valley database is the one containing four tables (Customer, Order, OrderLine, and Product), which you can create by using CREATE_PV.txt and LOAD_PV.txt.
1. Modify the Product_T table by adding an attribute QtyOnHand that can be used to track the finished goods inventory. The field should be an integer field of five characters and should accept only positive numbers.
2. List the customers who live in California or Washington. Order them by zip code, from high to low.
3. List the number of customers living at each state that is included in the Customer_T table.
4. Display the product line ID and the average standard price for all products in each product line.
5. For every product that has been ordered, display the product ID and the total quantity ordered (label this result TotalOrdered). List the most popular product first and the least popular last.
6. For each customer, list the CustomerID and total number of orders placed.
7. Display the product ID and the number of orders placed for each product. Show the results in decreasing order by the number of times the product has been ordered and label this result column NumOrders.
8. For each customer who had more than two orders, list the CustomerID and the total number of orders placed.
Explanation / Answer
1.) ALTER TABLE Product_T ADD QtyOnHand integer(5);
ALTER TABLE Product_T ADD CHECK (QtyOnHand>0);
---------------------------------------------------------------------------------------------------------
2.) SELECT * FROM Customer_T WHERE Customer_City in ('California','Washington') ORDER BY Postal_Code;
----------------------------------------------------------------------------------------------------------
3.) SELECT Customer_State, count(*) as Customer_Count FROM Customer_T GROUP BY Customer_State;
------------------------------------------------------------------------------------------------------------
4.) SELECT Product_Line_ID, avg(Standard_Price) as SP_avg FROM Product_T GROUP BY Product_Line_ID;
------------------------------------------------------------------------------------------------------------
5.) SELECT Product_ID, sum(Ordered_Quantity) as TotalOrdered FROM OrderLine_T GROUP BY Product_ID ORDER BY TotalOrdered desc;
-------------------------------------------------------------------------------------------------------------
6.) SELECT Customer_ID, count(*) as OrderPlaced FROM Order_T GROUP BY Customer_ID;
-------------------------------------------------------------------------------------------------------------
7.)
--------------------------------------------------------------------------------------------------------------
8.) SELECT Customer_ID, count(*) as OrderPlaced FROM Order_T GROUP BY Customer_ID HAVING count(*)>2;
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.