ocp1z0-047: Extended GROUP by group ――rollup

We all know that group by can be used to group clauses, extended GROUP BY PLUS rollup clause, can return a small Ji Lu for each group, and return total records for all groups, as follows:

Gyj@ocm> with T as  
  2   (select ' A ' comp, ' x ' Cate, ' O ' oth,100 fee to dual UNION all  
  3    SELECT ' a ' comp, ' Y ' Cate, ' m ' oth,200 fee from dual UNION ALL  
  4   SELECT ' B ' comp, ' x ' Cate, ' O ' oth,300 fee from dual UNION ALL  
  5
  select ' B ' comp, ' y ' cate, ' m ' oth,100 fee from dual)  
  6   SELECT comp,cate,oth,sum (fee)  
  7 from   t  
  8   GROUP by Comp,rollup ((Cate,oth));  
      
C c o   SUM (FEE)--  
----------  
A x o a  
y m  
b x o        300
  b y m  
B  
      
6 rows selected.

Answer a is to say only find each line small, this should be grouping sets, the operation is as follows:

Gyj@ocm> with T as  
  2   (select ' A ' comp, ' x ' Cate, ' O ' oth,100 fee to dual UNION all  
  3    SELECT ' a ' comp, ' Y ' Cate, ' m ' oth,200 fee from dual UNION ALL  
  4   SELECT ' B ' comp, ' x ' Cate, ' O ' oth,300 fee from dual UNION ALL  
  5
  select ' B ' comp, ' y ' cate, ' m ' oth,100 fee from dual)  
  6   SELECT comp,cate,oth,sum (fee)  
  7 from   t  
  8   GROUP by Comp,grouping SETS ((Cate,oth));  
      
C c O   SUM (FEE)--  
----------  
a y m  
b x o  
b y m  
A x o        100

Answer b is to say that the total of each group, certainly wrong.

Answer C sees a right toleft (right-to-left), because Rollup (A,B,C) is in this order of the first group by A,b,c then A,b, then a, then not grouped, from right to left 4 results unionall.

The answer d is the combination of all possible, that should be the cube.

Gyj@ocm> with T as 2 (SELECT 1 a,2 b,3 c,4 d,5 e,6 f,7g,8 H 3 from dual) 4 SELECT 5 decode (GROUP ING (a), 1,0,1) A, 6 decode (GROUPING (b), 1,0,1) B, 7 decode (GROUPING (c), 1,0,1) C, 8 decode (GROUPING (d), 1,0,1) D, 9 decode (GROUPING (e), 1,0,1) E, decode (GROUPING (f), 1,0,1) F, Decode (GROUPING (g), 1,0,1) G, Deco  
      
         De (GROUPING (h), 1,0,1) h from T-GROUP by CUBE (A,B,C,D,E,F,G,H); A B C D E F G H--------------------------------------          ------------------------------------------0 0 0 0 0 0          0 0 0 0 0 0 0 0 0 1 0          0 0 0 0 0 1 0 0 0 0 0  
        0 0 1 1 0 0 0 0 0 1 0 0 0 0 0          0 0 1 0 1 0 0 0 0 0 1          1 0 0 0 0 0 0 1 1 1 0          0 0 0 1 0 0 0 0 0 0 0          1 0 0 1 0 0 0 0 1 0 1          0 0 0 0 0 1 0 1 1 0          0 0 0 1 1 0 0 0 0 0 0          1 1 0 1 0 0 0 0 1 1 1        0 0  0 0 0 1 1 1 1 0 0 0 1          0 0 0 0 0 0 0 1 0 0 0  
1 0 0 0 1 0 0 1 0 Omit results ...          1 1 1 1 1 1 0 0 1 1 1          1 1 1 0 1 1 1 1 1 1 1  1 0 1 1 1 1 1 1 1 1 256 rows Selected.

Detailed can buy the "sword broken Iceberg---oracle development" that book to see.

The correct answer is: C

See more highlights of this column: http://www.bianceng.cnhttp://www.bianceng.cn/database/Oracle/