| |
 |
|
Oracle Concepts by Burleson
Consulting |
Write a Summary Query
Part 1:
Your task is to
write an SQL query against the sample database that display each book
store, a list of the titles sold at each book store, and the sum of
all copies sold for each title. The report will look like this:
Store
Book
Books
Name
Title
Sold
------------------------- ------------------------------ --------
Barnes And Noble
Windows Sucks
1,000
Reduce Spending The Republican
900
Way
The Willow Weeps No More
180
Oracle 10g Sql Tuning
100
Blue Ride Booksellers Managing Stress
3,400
The Fall Of Microsoft
800
Unix For Experts
600
Dos For Dummies
100
Non Violins In The Workplace
100
Pay No Taxes And Go To Jail
100
Writers Market
100
Reduce Spending The Republican 100
Way
Bears Are People Too
100
Books For Dummies
Windows Sucks
8,000
The Fall Of Microsoft
2,100
Piano
Greats
800
Pay No Taxes And Go To Jail
700
Non Violins In The Workplace
500
Bears Are People Too
300
Writers Market
300
Unix For Experts
100
Oracle 10g Sql Tuning
100
Managing Stress
100
Borders
Dos For Dummies
8,900
The Willow Weeps No More
8,800
The Fall Of Microsoft
1,400
Writers Market
1,400
Managing Stress
600
Bears Are People Too
500
Reduce Spending The Republican 150
Way
Oracle 10g Sql Tuning
100
Piano Greats
10
Eaton Books
Pay No Taxes And Go To Jail 10,000
Piano Greats
1,020
Oracle 10g Sql Tuning
600
The Zen Of Auto Repair
100
Unix For Experts
100
Bears Are People Too
100
Non Violins In The Workplace
100
Operations Research Theory
100
Hot Wet And Sexy Books Piano Greats
6,100
Pay No Taxes And Go To Jail 5,500
Writers Market
5,500
The Zen Of Auto Repair
3,700
Dos For Dummies
1,400
Zero Loss Finance
800
Unix For Experts
500
Non Violins In The Workplace
500
Operations Research Theory
500
The Fall Of Microsoft
200
Ignoramus And Dufus
Piano Greats
2,060
Zero Loss Finance
850
Windows Sucks
300
Dos For Dummies
200
The Zen Of Auto Repair
100
Operations Research Theory
100
Quagmire Books
Managing Stress
5,100
Dos For Dummies
800
Bears Are People Too
800
Reduce Spending The Republican 600
Way
Piano Greats
400
Windows Sucks
100
Writers Market
100
Specialty Bookstore
Piano Greats
5,500
Dos For Dummies
180
Oracle 10g Sql Tuning
100
The Willow Weeps No More
100
Reduce Spending The Republican 100
Way
Pay No Taxes And Go To Jail
100
Wee Bee Books
Windows Sucks
8,800
Writers Market
1,000
Reduce Spending The Republican 900
Way
Operations Research Theory
800
The Willow Weeps No More
800
Zero Loss Finance
600
Piano Greats
400
The Zen Of Auto Repair
100
Unix For Experts
100
Oracle 10g Sql Tuning
100
Non Violins In The Workplace
100
80 rows selected.
Hint: remember to
use the sum BIF to summarize the data and to GROUP BY all non-summed
columns.
Part 2
Modify your report
to also show the total dollar sales and sum the dollar sales when to
store name changes. Hint: Add a column to the select
statement to multiply the total units sold by the book retail price.
HintThe revised report will look
like this:
Store
Book
Books Dollar
Name Title
Sold Sales
------------------------- ------------------------------ --------
------------
Barnes And Noble
Windows Sucks
1,000 $34,950.00
Reduce Spending The Republican 900
$25,155.00
Way
The Willow Weeps No More
180 $5,391.00
Oracle 10g Sql Tuning
100 $4,995.00
*************************
------------
sum
$70,491.00
Blue Ride Booksellers Managing
Stress
3,400 $135,830.00
The Fall Of Microsoft
800 $15,960.00
Unix For Experts
600 $23,370.00
Dos For Dummies
100 $1,995.00
Non Violins In The Workplace
100 $1,195.00
Pay No Taxes And Go To Jail
100 $1,095.00
Writers Market
100 $2,295.00
Reduce Spending The Republican
100 $2,795.00
Way
Bears Are People Too
100 $3,495.00
*************************
------------
sum
$188,030.00
Books For Dummies
Windows Sucks
8,000 $279,600.00
The Fall Of Microsoft
2,100 $41,895.00
Piano Greats
800 $26,360.00
Pay No Taxes And Go To Jail
700 $7,665.00
Non Violins In The Workplace
500 $5,975.00
Bears Are People Too
300 $10,485.00
Writers Market
300 $6,885.00
Unix For Experts
100 $3,895.00
Oracle 10g Sql Tuning
100 $4,995.00
Managing Stress
100 $3,995.00
*************************
------------ sum
$391,750.00
Borders Dos For Dummies
8,900 $177,555.00
The Willow Weeps No More
8,800 $263,560.00
The Fall Of Microsoft
1,400 $27,930.00
Writers Market
1,400 $32,130.00
Managing Stress
600 $23,970.00
Bears Are People Too
500 $17,475.00
Reduce Spending The Republican 150
$4,192.50
Way
Oracle 10g Sql Tuning 100
$4,995.00
Piano Greats
10 $329.50
*************************
------------
sum
$552,137.00
Eaton Books
Pay No Taxes And Go To Jail 10,000
$109,500.00
Piano Greats
1,020 $33,609.00
Oracle 10g Sql Tuning
600 $29,970.00
The Zen Of Auto Repair
100 $9,995.00
Unix For Experts
100 $3,895.00
Bears Are People Too
100 $3,495.00
Non Violins In The Workplace
100 $1,195.00
Operations Research Theory
100 $4,495.00
*************************
------------ sum
$196,154.00
Hot Wet And Sexy Books Piano Greats
6,100 $200,995.00
Pay No Taxes And Go To Jail 5,500
$60,225.00
Writers Market
5,500 $126,225.00
The Zen Of Auto Repair
3,700 $369,815.00
Dos For Dummies
1,400 $27,930.00
Zero Loss Finance
800 $17,560.00
Unix For Experts
500 $19,475.00
Non Violins In The Workplace
500 $5,975.00
Operations Research Theory
500 $22,475.00
The Fall Of Microsoft
200 $3,990.00
*************************
------------
sum
$854,665.00
Ignoramus And Dufus
Piano Greats
2,060 $67,877.00
Zero Loss Finance
850 $18,657.50
Windows Sucks
300 $10,485.00
Dos For Dummies
200 $3,990.00
The Zen Of Auto Repair
100 $9,995.00
Operations Research Theory
100 $4,495.00
*************************
------------ sum
$115,499.50
Quagmire Books
Managing Stress
5,100 $203,745.00
Dos For Dummies
800 $15,960.00
Bears Are People Too
800 $27,960.00
Reduce Spending The Republican 600
$16,770.00
Way
Piano Greats
400 $13,180.00
Windows Sucks
100 $3,495.00
Writers Market
100 $2,295.00
*************************
------------
sum
$283,405.00
Specialty Bookstore
Piano Greats
5,500 $181,225.00
Dos For Dummies
180 $3,591.00
Oracle 10g Sql Tuning
100 $4,995.00
The Willow Weeps No More
100 $2,995.00
Reduce Spending The Republican 100
$2,795.00
Way
Pay No Taxes And Go To Jail
100 $1,095.00
*************************
------------
sum
$196,696.00
Wee Bee Books
Windows Sucks
8,800 $307,560.00
Writers Market
1,000 $22,950.00
Reduce Spending The Republican 900
$25,155.00
Way
Operations Research Theory
800 $35,960.00
The Willow Weeps No More
800 $23,960.00
Zero Loss Finance
600 $13,170.00
Piano Greats
400 $13,180.00
The Zen Of Auto Repair
100 $9,995.00
Unix For Experts
100 $3,895.00
Oracle 10g Sql Tuning
100 $4,995.00
Non Violins In The Workplace
100 $1,195.00
*************************
------------ sum
$462,015.00
80 rows selected.
ANSWERS
Part 1
set pages 999
col c1 heading 'Store|Name' format a25
col c2 heading 'Book|Title' format a30
col c3 heading 'Books|Sold' format 999,999
break on c1 skip 2
select
initcap(store_name)
c1,
initcap(book_title)
c2,
sum(quantity)
c3
from
store,
sales,
book
where
store.store_key = sales.store_key
and
sales.book_key = book.book_key
group by
store_name,
book_title,
book_retail_price
order by
store_name,
c3 desc
;
Part 2
set pages 999
col c1 heading 'Store|Name' format a25
col c2 heading 'Book|Title' format a30
col c3 heading 'Books|Sold' format 999,999
col c4 heading 'Total|Dollar|Sales' format $999,999.99
break on c1 skip 2
compute sum of c4 on c1
select
initcap(store_name)
c1,
initcap(book_title)
c2,
sum(quantity)
c3,
sum(quantity)*book_retail_price c4
from
store,
sales,
book
where
store.store_key = sales.store_key
and
sales.book_key = book.book_key
group by
store_name,
book_title,
book_retail_price
order by
store_name,
c3 desc
;
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
|
|
