TABLE NAME = ORDERMASTERS COLUMNS ORDER_NBR ORDER_LINE_NBR CUSTOMER_NBR PRODUCT_

Question

TABLE NAME = ORDERMASTERS COLUMNS ORDER_NBR ORDER_LINE_NBR CUSTOMER_NBR PRODUCT_NBR ORDER_PLATFORM ORDER_DATE ORDER_TIME TOTAL_LINE_AMT Based on information above, please help answer the following two questions 4. Customers can order from QVC either through the QVC website (QVC.com) or by calling a phone number when am infomercial is aired (On Air). The column named ORDER_PLATFORM captures this information. Also, customers can place order anytime in a 24-hours day. The ORDER_TIME column captures when the order was placed. TOTAL_LINE_AMT shows the dollar amount for each order. Write a query to show the total dollar amount that has been ordered grouped by hours (0-23), ranked by total dollar amount when the order platform is QVC.com. This query can show online customer ordering behaviors. Use the SQL Character or Text (String Processing) function to handle the ORDER_TIME column. 5. It would be interesting as well to identify monthly trend in customer ordering. Write a query to show both total order count and total dollar amount for each month in 2015. Sort the results based on the order count. Use the Oracle DATE processing functions that you can find on the Internet to process the data information in this query.

Explanation / Answer

4. SELECT SUM(TOTAL_LINE_AMT), RANK() OVER (PARTITION BY ORDER_PLATFORM ORDER BY SUM(TOTAL_LINE_AMT)) FROM ORDERMASTERS WHERE ORDER_PLATFORM='QVC.COM' GROUP BY TO_CHAR(ORDER_DATE,'HH24' );

5. SELECT COUNT(ORDER_NBR), SUM(TOTAL_LINE_AMT) FROM ORDERMASTERS WHERE TO_CHAR(ORDER_DATE,'YYYY') = 2015 GROUP BY TO_CHAR(ORDER_DATE,'MM') ORDER BY COUNT(ORDER_NBR);

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

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