Oracle GROUP BY ROLLUP query structure

There are the following questions in the 047 questions

11.View the Exhibit and examine the descriptions of order_items and ORDERS tables.

You are want to display the customer_id, product_id, and Total (Unit_price multiplied by

QUANTITY) for the order placed. You are also want to display the subtotals for a customer_id as.

For a product_id to 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) has 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

In the above problem to choose the correct answer is very simple, that is, group by should be placed in the back of the where, but the problem appears in a knowledge point we need to pay attention to, is the use of Rollup.

Everyone is familiar with the group by basic syntax, for example, to count the highest salary of employees in each department, you can use

Select Max (SAL) from the EMP group by DEPTNO;

However, if there are more complex statistics, the group by basic usage is not satisfied with the requirements, but also with rollup to use, such as:

Need to install position, manager, department to count employee's total salary, and do a sub statistic for position and manager

Sql> Select Job,mgr,deptno,sum (SAL) from EMP where Deptno into (10,30) group by Rollup (JOB,MGR,DEPTNO);

JOB MGR DEPTNO SUM (SAL)

--------------------------- ---------- ---------- ----------

Clerk 7698 30 950

Clerk 7698 950

Clerk 7782 10 1300

Clerk 7782 1300

Clerk 2250

MANAGER 7839 10 2450

MANAGER 7839 30 2850

MANAGER 7839 5300

MANAGER 5300

Salesman 7698 30 5600

Salesman 7698 5600

JOB MGR DEPTNO SUM (SAL)

--------------------------- ---------- ---------- ----------

Salesman 5600

PRESIDENT 10 5000

PRESIDENT 5000

PRESIDENT 5000

18150

Rows selected.