Which SQL statement would you execute to get the desired output?

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?

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 WHERE
MONTHS_BETWEEN(order date,SYSDATE)<=6 GROUP BY ROLLUP (o.customer_id,

oi.product_id);

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) WHERE 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) HAVING 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 GROUP BY ROLLUP
(o.customer_id, oi.product_id) WHERE MONTHS_BETWEEN(order_ date,SYSDATE)>=6;



Leave a Reply 2

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


KPD

KPD

Could be a problem in the original question, but the correct order should be MONTHS_BETWEEN(SYSDATE, order_date).
Otherwise, all returned values will be negative and, by this, per definitionem <= 6.

Ab Hin

Ab Hin

Answer is: A