Scenario 3: The marketing department of PVFC is designing a campaign to promote

Question

Scenario 3: The marketing department of PVFC is designing a campaign to promote its new line of products. The department needs to find out how the PVFC products have been ordered by customers in the past. As a data analyst in the Bl department, you are given the following three sets of requirements and asked to generate three result sets from the PVFD DB for the marketing department. ui Display customer ID, order ID, order date, product description and product finish if the ordered quantity of an order line is greater than 3 Sort the result set by order ID in ascending order SELECT Customer_ID, o.Order ID FROM Order_T AS o, Order_Line_T AS ol, Product T AS p WHERE o.Order ID-ol.OrderID AND AND ORDER BY Page 3 of 6 OIM360, Spring 2018 Isenberg School of Management, UMass Amherst ui Display customer ID, customer name, postal code, order ID, order date, product ID, ordered quantity and product description, if the customer is living in CA, FL or NJ and has ordered at least one product made with Cherry or Natural Ash Sort the result set first by customer id in ascending order and then by ordered quantity in descending order . SELECT c.Customer_ID,_ FROM Customer-TAS c, Order-TAS o,- WHERE c.Customer ID-o.Customer ID AND- AND Customer State AND Product Finish ORDER BY

Explanation / Answer

We will be writing the complete SQL query and against each line, explanation for each line would be given.

3.1

Select customer_ID, o.Order_ID, o.Order_date, p.product_description, p.product_finish // select statement choses whatever we want to display

From order_T AS o, Order_Line_T AS ol, Product_T AS p // these are the 3 table names

Where o.Order_ID = ol.Order_ID //join on common fields to link 2 tables

AND ol.Order_ID = p.Product_T_ID // join on common fields to link 2 tables

AND ol.Order_Quantity > 3 // this check ensures that quantity is greater than 3

ORDER BY o.Order_ID // sorting is done as per the order ID

3.2

Select c.Customer_ID, c.Customer_Name, o.Postal_Code, o.Order_ID, o.Order_date, p.Product_ID, o.Ordered_Quantity, p.Product_Description // select statement has all what we want to print as output

From Customer_T AS c, Order_T AS o, Product_T AS p // name of the tables

WHERE c.Customer_ID = o.Customer_ID // to join 2 tables

And o.Customer_ID = p.Customer_ID

AND

AND Customer_State in (CA, FL, NJ) // IN statement works like and OR statement

AND Product_Finish in (Cherry, Natural_Ash) // IN statement works like an OR statement and it says that product finish should be either of the given options.

Order by o.Ordered_Quantity desc // sort in descending order

Related Questions

Navigate

• Browse (All)
• Browse S
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.