Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  Oracle Concepts by Burleson Consulting

Use the Oracle CUBE Function

Your exercise is to extend the following SQL statements to provide aggregations.  This exercise is not graded, but we highly recommend that you take the time to fully understand the powerful functionality of CUBE.  We start with a report that shows each publisher, the types of books they publish, the number of titles for each book type, and the sum of all sales for each publisher-type pair:

PUB_NAME         BOOK_TYPE       NBR_TITLES SUM_SOLD
---------------- --------------- ---------- --------                           
big city         fiction                  6    1,800                            
                 miscellaneous            5    1,300                           
bookhouse        computer                14   29,780                           
                 miscellaneous            7    8,400                           
                 music                   11   16,290                           
desk top         management               7    9,200                           
learning works   computer                 5    1,400                           
                 management               5    2,250                           
mammoth house    computer                 7    4,500                           
                 fiction                  6    2,750                           
mountain         fiction                  5    9,880                           
nitpick and sons computer                 6    1,100                           
                 management               5    4,000                           
star books       management               5    1,500                           
                 miscellaneous            6   16,400                           

15 rows selected.

Here is the original SQL query that produced this report:

col pub_name   format a16
col book_type  format a15
col nbr_titles format 999,999
col sum_sold   format 999,999

break on pub_name

SELECT
   pub_name,
   book_type,
   count(*)      nbr_titles,
   sum(quantity) sum_sold
FROM
   publisher
natural join
   book
natural join
   sales
GROUP BY
   (pub_name, book_type)
;

Your exercise is to add the CUBE operator to calculate the aggregates and reproduce this report.  Note the aggregates for each publisher, and the grand total aggregates at the end of the report.

PUB_NAME         BOOK_TYPE       NBR_TITLES SUM_SOLD                           
---------------- --------------- ---------- --------                           
big city         fiction                  6    1,800                           
                 miscellaneous            5    1,300                            
                                         11    3,100                           
bookhouse        computer                14   29,780                           
                 miscellaneous            7    8,400                            
                 music                   11   16,290                           
                                         32   54,470                           
desk top         management               7    9,200                           
                                          7    9,200        
                  
learning works   computer                 5    1,400                           
                 management               5    2,250                           
                                         10    3,650                           
mammoth house    computer                 7    4,500                           
                 fiction                  6    2,750                           
                                         13    7,250                           
mountain         fiction                  5    9,880                           
                                          5    9,880                           
nitpick and sons computer                 6    1,100                           
                 management               5    4,000                           
                                         11    5,100                           
star books       management               5    1,500
                 miscellaneous            6   16,400                           
                                         11   17,900
                 computer                32   36,780                            
                 fiction                 17   14,430                           
                 management              22   16,950                           
                 miscellaneous           18   26,100                           
                 music                   11   16,290                           
                                        100  110,550                           

29 rows selected.

ANSWER

col pub_name   format a16
col book_type  format a15
col nbr_titles format 999,999
col sum_sold   format 999,999

break on pub_name

SELECT
   pub_name,
   book_type,
   count(*)      nbr_titles,
   sum(quantity) sum_sold
FROM
   publisher
natural join
   book
natural join
   sales
GROUP BY
   CUBE(pub_name, book_type)
;



For more details, see the "Easy Oracle Series" a set of books especially designed by Oracle experts to get you started fast with Oracle database technology.


Download your Oracle scripts now:

www.oracle-script.com

The definitive Oracle Script collection for every Oracle professional DBA

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 

 

email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.