List SQL queries for below: Table: ORDERMASTERS Columns: ORDER_NBR (Varchar), OR

Question

List SQL queries for below:

Table: ORDERMASTERS

Columns: ORDER_NBR (Varchar), ORDER_LINE_NBR (Number), CUSTOMER_NBR(Varchar), PRODUCT_NBR(Varchar), ORDER_PLATFORM(Varchar), ORDER_DATE(Varchar), ORDER_TIME(Varchar), TOTAL_LINE_AMT (Number)

1. List the top 7 best-selling products based on the total sales amount in the data set. Show only the product number (PRODUCT_NBR) and the SUM of total dollar amount (TOTAL_LINE_AMT).

2. Write a query to filter your dataset by identifying the product number ending with 35, limit the sales channel only through “On Air” channel. And, show only TOTAL_LINE_AMT greater than 100. Sort the result by TOTAL_LINE_AMT).

3. ORDER_LINE_NBR is a column showing how many Line Items are included in a particular order. Write a query to find out how many order contains more than 6 (6 or more) line items. Group the count by ORDER_LINE_NBR. Rank the result ascendingly based on the count.

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

1.

SELECT rno.PRODUCT_NBR,rno.TOTAL_LINE_AMT
from
(SELECT rs.PRODUCT_NBR,rs.TOTAL_LINE_AMT, Row_number()
over (ORDER BY TOTAL_LINE_AMT DESC ) AS RN
FROM (
SELECT PRODUCT_NBR,sum(TOTAL_LINE_AMT) TOTAL_LINE_AMT
FROM ORDERMASTERS
group by PRODUCT_NBR
) rs

) rno

HERE rno.RN <= 7

2.SELECT *
FROM ORDERMASTERS
where PRODUCT_NBR like '%35'
and ORDER_PLATFORM = 'On Air'
and TOTAL_LINE_AMT > 100
order by TOTAL_LINE_AMT

3.Select ORDER_LINE_NBR,countn,RANK()
over (ORDER BY count) AS RN
(SELECT ORDER_LINE_NBR,count(*) countn
FROM ORDERMASTERS
where ORDER_LINE_NBR > 6
group by ORDER_LINE_NBR ) cn

4.
Select cn.Hour,cn.TOTAL_LINE_AMT,RANK()
over (ORDER BY cn.TOTAL_LINE_AMT) AS RN
(SELECT trunc(ORDER_TIME) as Hour,sum(TOTAL_LINE_AMT) TOTAL_LINE_AMT
FROM ORDERMASTERS
where ORDER_PLATFORM = 'QVC.com'
group by trunc(ORDER_TIME)) cn

5.SELECT extract(month from to_date(ORDER_DATE,'dd/mon/yyyy')) monthn,
count(*) order_count,
sum(TOTAL_LINE_AMT) TOTAL_LINE_AMT
FROM ORDERMASTERS
WHERE
extract(year from to_date(ORDER_DATE,'dd/mm/yyyy')) = 2015
group by extract(month from to_date(ORDER_DATE,'dd/mon/yyyy'))
ORDER BY order_count

Related Questions

Navigate

• Browse (All)
• Browse L
• Subjects

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