OCP-047 group by rollup and where clause

225. 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) ;
Answer: D

题目解释：  WHERE需在Group by前面

正解：