Use the 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)
;
Note: These exercises may use the
pubsdb.sql script that can be
downloaded at this link.
|
