| |
 |
|
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
|
|
