4. Customers can order from QVC either through the QVC website (QVC.com) or by c

Question

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.

TABLE=ORDERMASTERS

Explanation / Answer

4.

select sum(TOTAL_LINE_AMT),TRUNC (ORDER_TIME, 'H24')
from ORDERMASTERS
where ORDER_PLATFORM='QVC.com'
group by TRUNC (ORDER_TIME, 'H24');

5.

select sum(TOTAL_LINE_AMT) TOTAL_AMOUNT, TRUNC (ORDER_TIME, 'MM') ORDER_MONTH,count(TOTAL_LINE_AMT) TOTAL_COUNT
from ORDERMASTERS
where TRUNC (ORDER_TIME, 'YYYY')=2015
group by TRUNC (ORDER_TIME, 'MM')
order by count(TOTAL_LINE_AMT);

Related Questions

Navigate

• Browse (All)
• Browse 4
• Subjects

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