You want to display the CUSTOMER_ID, PRODUCT_ID, and total (UNIT_PRICE multiplied by QUANTITY) for the order p

Prev Question
Next Question

View the Exhibit and examine the descriptions of ORDER_ITEMS and ORDERS tables.

Exam 1Z0-047 - You want to display the CUSTOMER_ID, PRODUCT_ID, and total (UNIT_PRICE multiplied by QUANTITY) for the order p

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) ;

Prev Question
Next Question

Leave a Reply

Your email address will not be published. Required fields are marked *