SQL CUBE Usage - Examples

In the previous article, we discussed about the usage of ROLLUP statement in SQL with examples. We saw how ROLLUP modifies the output of a statement by running it against a test table. In this article, we will dwell on the usage of CUBE with examples.If you are jumping right into this article from the internet, I'd recommend you to read this article first, as it contains all the test tables that are required to run the examples presented in this post. 
Ok, we are ready to move on. We now have a test table SAM_STR, which is populated with numbers from 1000 to 9999 using DBMS_RANDOM.NUMBER method. Note that the results presented in this post and the previous one might differ, as I'm recreating the table for explanation purposes.

We then add two rows into this table, MAIN_CAT, [ categorizes the table based on a specified range of values ] and EVEN_ODD, which categorizes the rows based on 'E' for Even and 'O' for ODD. So, the table data looks like


SELECT MAIN_CAT,COUNT(*) FROM SAM_STR GROUP BY MAIN_CAT
A    299
B    261
C    256
D    184

At its usage, CUBE resembles the way ROLLUP is used, but the similarity ends here. In terms of the output, CUBE entirely brings a different set of results, if the number of columns used in GROUP BY clause is more than one. So, the statement

SELECT MAIN_CAT,COUNT(*) FROM SAM_STR GROUP BY CUBE(MAIN_CAT), yields the following output.

    1000 -> Total Row
A    299 -> Standard Output, Grouped by MAIN_CAT value of 'A'
B    261 ->Standard Output, Grouped by MAIN_CAT value of 'B'
C    256 ->Standard Output, Grouped by MAIN_CAT value of 'C'
D    184 -> Standard Output, Grouped by MAIN_CAT value of 'D'

Replacing CUBE with ROLLUP in the above SQL results in the same result set for me, but the order of the results are different.
Now to the twisting part. Consider the following SQL statement

SELECT MAIN_CAT, EVEN_ODD,COUNT(*) FROM SAM_STR GROUP BY CUBE(MAIN_CAT,EVEN_ODD)

The result would be divided into multiple sets as in the case of ROLLUP but we have more dimensions in the case of CUBE. The result sets are shown in the diagram below.

I have made my best attempts to group the rows which are shown in the result. We have a total of 15 rows in the result, which are grouped into four sets.

Green -> Which is equivalent to GROUP BY MAIN_CAT, EVEN_ODD. We have a total of 8 rows that matches this criteria. The equivalent SQL statement to get the 8 rows alone is our standard GROUP BY statement

SELECT MAIN_CAT, EVEN_ODD,COUNT(*) FROM SAM_STR GROUP BY MAIN_CAT,EVEN_ODD

Blue -> Which is the total number of rows at MAIN_CAT level.  Forgetting the EVEN_ODD column for a minute, the equivalent SQL statement for this would be [ 4 rows ]

SELECT MAIN_CAT, COUNT(*) FROM SAM_STR GROUP BY MAIN_CAT

Yellow -> Which is the total number of rows at EVEN_ODD level. Forgetting the MAIN_CAT column for a minute, the equivalent SQL statement for this would be [ 2 rows ]
SELECT EVEN_ODD, COUNT(*) FROM SAM_STR GROUP BY EVEN_ODD

We have accounted for 14 out of 15 rows in our result. The last row is the purple one.

Purple -> Which is simply the count(*) for the result. (note, we don't have any 'where' clause in our query and hence the result is simply the count(*) on our table. Or equivalently, [ 1 row ]

SELECT COUNT(*) FROM SAM_STR GROUP BY ' '

Now to the highlighting point. When we give CUBE, the table is scanned only once to get these 15 rows. The plan is provided below

--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|   1 |  SORT GROUP BY       |             |       |       |       |
|   2 |   GENERATE CUBE      |             |       |       |       |
|   3 |    SORT GROUP BY     |             |       |       |       |
|   4 |     TABLE ACCESS FULL| SAM_STR     |       |       |       |
--------------------------------------------------------------------


The same 15 row result can be achieved by the following SQL statement as well:-

SELECT MAIN_CAT, EVEN_ODD,COUNT(*) FROM SAM_STR GROUP BY MAIN_CAT,EVEN_ODD
UNION ALL
SELECT MAIN_CAT,'', COUNT(*) FROM SAM_STR GROUP BY MAIN_CAT
UNION ALL
SELECT ' ',EVEN_ODD, COUNT(*) FROM SAM_STR GROUP BY EVEN_ODD
UNION ALL
SELECT '','',COUNT(*) FROM SAM_STR GROUP BY ' '

But, this would scan the table four times.
--------------------------------------------------------------------
| Id  | Operation            |  Name       | Rows  | Bytes | Cost  |
--------------------------------------------------------------------
|   0 | SELECT STATEMENT     |             |       |       |       |
|   1 |  UNION-ALL           |             |       |       |       |
|   2 |   SORT GROUP BY      |             |       |       |       |
|   3 |    TABLE ACCESS FULL | SAM_STR     |       |       |       |
|   4 |   SORT GROUP BY      |             |       |       |       |
|   5 |    TABLE ACCESS FULL | SAM_STR     |       |       |       |
|   6 |   SORT GROUP BY      |             |       |       |       |
|   7 |    TABLE ACCESS FULL | SAM_STR     |       |       |       |
|   8 |   SORT GROUP BY      |             |       |       |       |
|   9 |    TABLE ACCESS FULL | SAM_STR     |       |       |       |

So, CUBE does this all for us in a single statement producing multiple super set of results. The general formula is:- A cube operation involving 'n' columns would result in 2^n result sets. Here, we had 2 columns and hence we got 4 result sets. (as provided by different colors)
Interchanging the columns in the query yields no difference for us in the result set. Thus, 

SELECT EVEN_ODD,MAIN_CAT, COUNT(*) FROM SAM_STR GROUP BY CUBE(EVEN_ODD,MAIN_CAT)

and

SELECT MAIN_CAT, EVEN_ODD,COUNT(*) FROM SAM_STR GROUP BY CUBE(MAIN_CAT,EVEN_ODD)
yield the same set of rows in the result. However, this is not the case with ROLLUP.

In the next article, we will discuss ROLLUP and CUBE in depth.



  


Oracle ROLLUP Usage- Example


For some time till now, ROLLUP and CUBE were always complicated terms to comprehend and use in standard SQL usage for me. Today I decided to give it a go and understand them with some examples. So far so good and the simulation for ROLLUP is provided below. Let us create a basic table and do some updates so that we can get going.

CREATE TABLE SAM_STR AS
SELECT TRUNC(DBMS_RANDOM.value(1000,9999)) AS VAL FROM ALL_OBJECTS WHERE ROWNUM < 1001

Here we create a basic table SAM_STR and fill it with 1000 values, randomly generated betweek 1000 and 9999 using DBMS_RANDOM package.

ALTER TABLE SAM_STR ADD
(MAIN_CAT VARCHAR2(20))

UPDATE SAM_STR
SET MAIN_CAT = (case WHEN VAL > 7500 THEN 'C' WHEN VAL > 5000 THEN 'B' WHEN VAL > 2500 THEN 'A' ELSE 'D' end)


We now add a new column to this table, MAIN_CAT, which is then updated based on a condition against the VAL column of the table. More precisely, the category is set as 'C' if the number is > 7500, 'B' if it is greater than 5000, A if it is greater than 2500 and D otherwise.

Just to provide another dimension, we add another column to this table. EVEN_ODD. As the name goes, this column takes the value of 'E' if the number is even, else it takes a value of 'O' if the number is ODD. So, we have

alter table sam_str add
(even_odd varchar2(2))

UPDATE SAM_STR SET EVEN_ODD=DECODE(MOD(VAL,2),0,'E','O')

So, we have two dimensions to our table. One that categorizes the numbers as even or odd in a separate column. The other one, divides the table into categories based on the value read. Let us start with the simplest of all, GROUP BY.

SELECT MAIN_CAT,COUNT(*) FROM SAM_STR GROUP BY MAIN_CAT
A    276
B    261
C    292
D    171

As it goes, we take a count of all the different categories in this SQL. The output is also provide along with the Query. When you run this Query, you may get a different output, as the numbers are randomly generated.Let us introduce ROLLUP now.

SELECT MAIN_CAT,COUNT(*) FROM SAM_STR GROUP BY ROLLUP(MAIN_CAT)
A    276
B    261
C    292
D    171

    1000 => Additional row that gives the complete total.


At this point, ROLLUP produces an additional row (result set). It is good to note that, if the GROUP BY contains one column, the result of ROLLUP will contain 2 sets. (n+1). So, ROLLUP performs something in addition to what GROUP BY does, which is a running total count in this case.Let us now add one more dimension to the SELECT statement and observe the output.

SELECT MAIN_CAT, EVEN_ODD,COUNT(*) FROM SAM_STR GROUP BY MAIN_CAT,EVEN_ODD

A    E    137
A    O    139
B    E    135
B    O    126
C    E    167
C    O    125
D    E    77
D    O    94

Here, we get the number of even /odd numbers against every category. When we include a ROLLUP here, the SQL changes to 

SELECT MAIN_CAT, EVEN_ODD,COUNT(*) FROM SAM_STR GROUP BY ROLLUP(MAIN_CAT,EVEN_ODD)

and the output is; [ I have used different colors to identify different sets]

A    E    137 => standard Output 1
A    O    139 => standard Output 1
A          276 => Output of ROLLUP. Even/ ODD ignored and the total at category
B    E    135 =>
standard Output 1

B    O    126 => standard Output 1
B          261=>Output of ROLLUP. Even/ ODD ignored and the total at category
C    E    167=> standard Output 1

C    O    125=> standard Output 1
C          292=>Output of ROLLUP. Even/ ODD ignored and the total at category
D    E    77=>standard Output 1

D    O    94=>standard Output 1
D          171=>Output of ROLLUP. Even/ ODD ignored and the total at category
        1000 =>Complete Total. Output of ROLLUP

So, we have use two columns in the ROLLUP and we have got 3 (n+1) sets of result here. Note that ROLLUP moves from LEFT to RIGHT in the column order, and interchanging them, gives a different result, as shown below

SELECT EVEN_ODD,MAIN_CAT, COUNT(*) FROM SAM_STR GROUP BY ROLLUP(EVEN_ODD,MAIN_CAT)

E    A    137
E    B    135
E    C    167
E    D    77
E          516
O    A    139
O    B    126
O    C    125
O    D    94
O          484
           1000
This can be interpreted on the same lines as of the previous example. Total count result set is still the same. However, the second set of results gives the total against total count of even and odd numbers only. In the previous example, it was against individual categories. In the subsequent posts, let us dwell on how to use CUBE with examples.
 

Oracle 1Z0-047 Certification - Book or Ebook



This certification exam [ 1Z0-047 - OCA Oracle Database, SQL Certified Expert Exam Guide ] is one of the most prestigious one and not many materials are available in the web for preparing for this examination. I recently purchased a book from Amazon for this certification and it covers the complete syllabus of the exam in a concise manner. 

This book is authored by Steve O'Hearn and I must say I'm really pleased with the way the content is organized and delivered in this book. Further, the price at amazon.com was very cheap and overall, it was a very satisfying purchase.This book also comes with a CD ROM which contains a sample examination and all chapters in electronic format.I was also able to get one additional sample examination when I registered on the web for this exam.More information on the book is available below


It is worth spending money on this book rather than attempting to download an electronic version of this book from the internet. The actual price of this book is around $59.99 USD but it is available at a very reasonable price from the listing provided here. The shipment was done at a ultra speed pace and I got this delivered in less than five days.

For those, who would like to give a serious go at this examination and do a dedicated preparation, this is a must have book. The syllabus covered in this book exactly matches with the syllabus prescribed for this examination by Oracle. Also, this book exactly matches the syllabus and prepares one for the exam in a complete way .The content covered in this book are tested on a 11g R1 version of Oracle, but they should be workable on 11gR2 version as well.

Low price reprint editions of this book are not available elsewhere. I also did a search in Dymocks and many online book stores the average price comes around 100 USD. From the listing above, it come to less than 70 USD including shipping, which makes this too good to be missed.

Just a final comment on the content organization in this book..It is really good and the book offers very nice explanation on complicated areas. I would recommend this book for anybody who would like to have a serious go at this examination. Don't try to grab a ebook version for this book..Get the printed one. It is a must have..Also, check out these books as well..

uCertify Guide for Oracle Exam 1Z0-047

Tamilnadu +2 maths important one mark question

                                  Application of matrices and determinants - Answers
1. (1)
2. (4)
3. (3)
4. (1)
5. (3)
6. (2)
7. (4)
8. (2)
9. (4)
10. (1)
11. (2)
12. (1)
13. (1)
14. (2)
15. (1)
16. (2)
17. (1)
18. (3)
19. (1)
20. (1)
21. (4)

Tamilnadu +2 maths important one mark question

                                  Application of matrices and determinants - Set 3
   15. Equivalent matrices have rank ………….
   (1)    Same                                 (2)    different   
   (3)     May or may not be same    (4)    zero
   16. By determinant method the value of  x for the equations 
   x-y = 2; 3y = 3x-7 is
(1)0              (2) 1         (3)    5              (4)    8
   17.  The solution of 7x+5y-13z+4 = 0, 9x+2y+11z = 37, 3x-y+z = 2 is
   (1) x=1,y=3,z=2  (2) x=2,y=1,z=3  (3) x=3,y=2,z=1  (4) none of these
   18.  If A is a square matrix of order n, then A(adj A)
   (1) [aij]T     (2)AI          (3) IAI In           (4) IAI
   19.  The rank  of a non-singular matrix of order n×n is
   (1)  n                (2)  n2               (3)  0                        (4)    1
   20. If the value  of  x is given , then the value of  x is 
   (1) ∆x/∆             (2) ∆/∆x     (3) ∆x = 0           (4) ∆×∆x
   21. The value of z, from the given equations:
   2x+y+z = 5; x+y+z = 4; x-y+2z = 1 is
   (1) 2                 (2) 6                 (3) 3                 (4) 1

Tamilnadu +2 maths important one mark question

                                       Application of matrices and determinants - Set 2


8. A-1 exists when A is …… matrix
(1) Singular    (2)  Non-singular         (3) Triangular     (4) zero
9.  Equivalent matrices are obtained by
     (1)Taking inverses              (2)  Taking transposes 
     (3)Taking adjoints   (4)Taking finite number of elementarytransformations
     10. Every homogeneous system
    (1) is always consistent       (2) has only unique solution
    (3) has many solution          (4) need not be consistent
   11. If ρ(A)=ρ[A  B] then the system is
   (1) consistent and has many solution            (2) consistent
   (3)consistent and has unique solution            (4)inconsistent
    12. Let  2x-3y+7z = 5, 3x+y-3z = 13 , 2x+19y-47z = 32 are the system of equations. Then
    ρ[A  B]=
    (1)    3              (2)    1              (3)    2              (4)    0
    13. The solution of the liner equation ax = b,(a≠0) is
     (1) b/a              (2) a/b              (3) –(a/b)          (4) –(b/a)
     14. According to reversal law for inverses
     (1)    (BA)-1 = B-1A-1                              (2)    (AB)-1 = B-1A-1             
      (3)    (BA)-1 = A-1B-1                              (4) (AB)-1 = A-1B-1

Tamilnadu +2 maths Important one mark question

                                 
                              Application of matrices and determinants

1.   If A is a matrix of order 3,then det (kA)
 (1) K3det(A)            (2) k2det(A)       (3) k det(A)       (4) det(A) 
2.   If I is a unit matrix of order n,where  k≠0 is a constant, then adj(kI)=
   (1)  Kn (adj I)           (2)  k (adj I)       (3)  k2 (adj (I))   (4) kn-1 (adj I) 
  3.If the equation  -2x+y+z = l; x-2y+z = m; x+y-2z = n such that l + m + n = 0 , then the system  has
(1) A non-zero unique solution                  (2) trivial solution
( 3)  Infinitely many solution                    (4) no solution
  4.   If A and B are any two matrices such that AB=0 and A is non-singular, then
(1)B=0   (2)B is singular   (3)B is non singular     (4) B=A
5. If A is a square matrix of order 3, then det(kA)
(1) k3 det(A)  (2) k2 det(A)      (3)  k det(A)      (4) det(A)
6.  Cramer's rule is applicable, when
(1) ∆ = 0                (2)  ∆ ≠ 0           (3)  ∆=1             (4)  ∆ ≠ 1
7. (AT)-1 is equal to
(1)  A-1           (2)  AT       (3)  A                (4)  (A-1)T

Tamilnadu +2 chemistry important one mark question

                                                               Atomic structure - Answers
1. (b)
2. (b)
3. (d)
4. (a)
5. (a)
6. (c)
7. (d)
8. (d)
9. (d)
10. (a)
11. (d)
12. (b)
13. (c)
14. (a)
15. (a)
16. (d)
17. (c)
18. (c)
19. (b)
20. (c)
21. (c)

Tamil nadu +2 chemistry – important one mark question

                                                          Atomic structure -  Set 3
15.       The hybridization in co32- ion is
(a)        sp2                    (b)        sp3                    (c)        sp         (d)        sp3d

16. Which of the following molecular orbital has the lowest energy
(a)        σ2pz                   (b)        π*2py    (c)        σ*2pz     (d)        σ*2s

17. Which one  of the following is paramagnetic in nature?
(a) H2                (b)He2               (c)NO                (d)N2

18. The energy value of an electron in hydrogen atom is -82 KJmol-1. The value of "n" is
(a)        2          (b)        3          (c)        4          (d)        5

19. The momentum of a particle which has de-Broglie wavelength of 10-10 m (h=6.626×10-34 kg m2 s-1) is
(a)        6.6×10-23 kg ms-1                        (b)        6.6×10-24 kg ms-1
(c)     6.6×10-34 kg ms-1               (d)        6.6×1034 kg ms-1

20. Which of the following  indicates that a molecule is stable
(a) Nb=Na=0        (b) Nb=Na    (c)  Nb>Na     (d)   Nb<Na

21. The  uncertainity in the momentum of an electron is 1.0×10-5 kg ms-1. The uncertainity in its position will be (h= 6.626×1034 Js)
(a) 1.05×10-28 m (b) 1.05×10-26 m (c) 5.27×10-30 m (d) 5.25×10-28 m

Tamil nadu +2 chemistry– important one mark question

                                               Atomic structure- Set 2
8.         The circumference of the circular orbit of an electron is an integral multiple of its
(a)        Frequency         (b)        momentum
(c)    Mass                      (d)        wavelength
9.         Inter molecular hydrogen bonding is present in
(a)        HF                    (b)        water    (c)        ethanol          (d) all of these
10.       Energy levels of molecular orbitals have been determined experimentally by
(a) Spectroscopic studies             (b)X-ray diffraction
(c) Crystallographic studies          (d)none of these
11.       In a molecule  Nb=8 and Na=4. Its bond order is
(a)        3                      (b)        4          (c)        2.5                   (d)        2
12.       The hybridization involved in XeF6  
(a)  sp3d3                       (b)sp3d2             (c)sp3d  (d)sp3
13.       The nature of hybridization in IF7 molecule is
(a)        sp3d2     (b)        sp3d4     (c)        sp3d3     (d)        sp2d4
14.       The bond order of N2 molecule is
(a)        3          (b)        1          (c)        2          (d)        2.5

Tamil nadu +2 chemistry – important one mark question

                                                                        Atomic structure
             1.    En=-313.6/n2 if the value of Ei=-34.84 to which value ‘n’ corresponds 

(a)           4          (b)   3        (c)   2   (d)   1

2. If the energy of an electron in the second Bohr orbit of H- atom is –E, what is the energy of the electron in the Bohr’s first orbit?
(a)           2E         (b)        -4E       (c)        -2E       (d)       

 3. The bond order of oxygen molecule is
(a)           2.5       (b)        1          (c)        3          (d)        2


4.    The intramolecular  hydrogen bonding is present in
(a)            O-nitrophenol              (b)        m-nitrophenol               
(c)        p-nitrophenol                 (d)        none

5.         de-Broglie wavelength   λ=
(a)        h/p                   (b)        p/h                   (c)        hmv      (d)        m/h

6.         Dual character of an electron was explained by 
(a)        Bohr                  (b)        Heisenberg        (c)        Broglie  (d)        Pauli

7.         Which of the following particles having same kinetic energy would have the maximum de-Broglie wavelength?
(a)        Proton   (b)        Neutron (c)        α-particle             (d)     β-particle

Tamil nadu +2 physics – important one mark question Answers

Electostatics - Answers to Set 1 , Set 2 and Set 3
1.(b)
2.(c)
3.(c)
4.(c)
5.(a)
6.(a)
7.(b)
8.(d)
9.(d)
10.(d)
11.(a)
12.(a)
13.(d)
14.(a)
15.(b)
16.(a)
17.(b)
18.(c)
19.(d)
20.(d)
21. (d)