View the Exhibit and examine the descriptions of ORDER_ITEMS and ORDERS tables.
You want to display the CUSTOMER_ID, PRODUCT_ID, and total (UNIT_PRICE multiplied by QUANTITY) for the order placed. You also want to display the subtotals for a CUSTOMER_ID as well as for a PRODUCT_ID for the last six months. Which SQL statement would you execute to get the desired output?
A.
SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) “Total” FROM order_items oi JOIN orders o
ON oi.order_id=o.order_id
GROUP BY ROLLUP (o.customer_id,oi.product_id)
WHERE MONTHS_BETWEEN(order_date, SYSDATE) <= 6;
B.
SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) “Total” FROM order_items oi JOIN orders o
ON oi.order_id=o.order_id
GROUP BY ROLLUP (o.customer_id,oi.product_id)
HAVING MONTHS_BETWEEN(order_date, SYSDATE) <= 6;
C.
SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) “Total” FROM order_items oi JOIN orders o
ON oi.order_id=o.order_id
GROUP BY ROLLUP (o.customer_id, oi.product_id)
WHERE MONTHS_BETWEEN(order_date, SYSDATE) >= 6;
D.
SELECT o.customer_id, oi.product_id, SUM(oi.unit_price*oi.quantity) “Total” FROM order_items oi JOIN orders o
ON oi.order_id=o.order_id
WHERE MONTHS_BETWEEN(order_date, SYSDATE) <= 6
GROUP BY ROLLUP (o.customer_id, oi.product_id) ;
If subtotals for PRODUCT_ID are needed independently from CUSTOMER_ID, I would use CUBE rather then ROLLUP. ROLLUP gives (CUSTOMER_ID,PRODUCT_ID), (CUSTOMER_ID), and total for all(), but not (PRODUCT_ID).
select count(*), job, deptno from emp where sal < 3000 group by rollup (job, deptno );
select count(*), job, deptno from emp where sal < 3000 group by cube (job, deptno );
select count(*), job, deptno from emp group by rollup (job, deptno) where sal < 3000 ;
Yes. CUBE would give PRODUCT_ID, CUSTOMER_ID each independent subtotal.
why D not B?
B- (having is wrong) “HAVING MONTHS_BETWEEN(order_date, SYSDATE) <= 6"