| |
 |
|
Oracle Concepts by Burleson
Consulting |
Use a CASE Statement in
Oracle SQL
This assignment will ask you to modify an
existing report to use the CASE statement. Assume that you have
created the following report of total sales for all bookstores:
STORE_NAME
BOOK_TITLE TOTAL_SALES
------------------------- -----------------------------------
-----------
hot wet and sexy books The zen of auto repair
$369,815
wee bee books
windows sucks
$307,560
books for dummies
windows sucks
$279,600
borders
the willow weeps no more
$263,560
quagmire books
managing stress
$203,745
hot wet and sexy books piano greats
$200,995
specialty bookstore piano greats
$181,225
borders
DOS for dummies
$177,555
blue ride booksellers managing stress
$135,830
hot wet and sexy books writers market
$126,225
eaton books
pay no taxes and go to jail
$109,500
ignoramus and dufus piano greats
$67,877
hot wet and sexy books pay no taxes and go to jail
$60,225
books for dummies the
fall of microsoft
$41,895
wee bee books
operations research theory
$35,960
barnes and Noble
windows sucks
$34,950
eaton books
piano greats
$33,609
borders
writers market
$32,130
eaton books
Oracle 10g sql tuning
$29,970
quagmire books
bears are people too
$27,960
borders
the fall of microsoft $27,930
hot wet and sexy books DOS for dummies
$27,930
books for dummies
piano greats
$26,360
barnes and Noble
reduce spending the republican way $25,155
wee bee books
reduce spending the republican way
$25,155
borders
managing stress
$23,970
wee bee books
the willow weeps no more
$23,960
blue ride booksellers UNIX for experts
$23,370
wee bee books
writers market
$22,950
hot wet and sexy books operations research theory
$22,475
hot wet and sexy books UNIX for experts
$19,475
ignoramus and dufus zero loss
finance
$18,658
hot wet and sexy books zero loss finance
$17,560
borders
bears are people too
$17,475
quagmire books
reduce spending the republican way
$16,770
blue ride booksellers the fall of microsoft
$15,960
quagmire books
DOS for dummies
$15,960
quagmire books
piano greats
$13,180
wee bee books
piano greats
$13,180
wee bee books
zero loss finance $13,170
books for dummies
bears are people too
$10,485
ignoramus and dufus windows sucks
$10,485
eaton books
The zen of auto repair
$9,995
wee bee books
The zen of auto repair
$9,995
ignoramus and dufus The zen of
auto repair
$9,995
books for dummies pay
no taxes and go to jail
$7,665
books for dummies
writers market
$6,885
books for dummies non
violins in the workplace
$5,975
hot wet and sexy books non violins in the workplace
$5,975
barnes and Noble
the willow weeps no more
$5,391
barnes and Noble
Oracle 10g sql tuning
$4,995
borders
Oracle 10g sql tuning
$4,995
books for dummies
Oracle 10g sql tuning
$4,995
wee bee books
Oracle 10g sql tuning
$4,995
specialty bookstore Oracle 10g sql
tuning
$4,995
eaton books
operations research theory
$4,495
ignoramus and dufus operations
research theory
$4,495
borders
reduce spending the republican way
$4,193
books for dummies
managing stress
$3,995
hot wet and sexy books the fall of microsoft
$3,990
ignoramus and dufus DOS for
dummies
$3,990
books for dummies UNIX
for experts $3,895
eaton books
UNIX for experts
$3,895
wee bee books
UNIX for experts
$3,895
specialty bookstore DOS for
dummies
$3,591
blue ride booksellers bears are people too
$3,495
quagmire books
windows sucks
$3,495
eaton books
bears are people too
$3,495
specialty bookstore the willow
weeps no more
$2,995
blue ride booksellers reduce spending the
republican way $2,795
specialty bookstore reduce
spending the republican way $2,795
blue ride booksellers writers market
$2,295
quagmire books
writers market
$2,295
blue ride booksellers DOS for dummies
$1,995
blue ride booksellers non violins in the
workplace
$1,195
wee bee books
non violins in the workplace
$1,195
eaton books
non violins in the workplace
$1,195
blue ride booksellers pay no taxes and go to
jail
$1,095
specialty bookstore pay no taxes
and go to jail
$1,095
borders
piano greats
$330
80 rows
selected.
Here is your original SQL:
col
store_name format a25
col book_title format a35
col total_sales format $999,999
select
store_name,
book_title,
sum(quantity)*book_retail_price total_sales
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
total_sales desc
;
Your assignment is to modify this report and
display text based upon the total dollar sales for each book.
For the total_sales column, change the display as follows:
* Books with total sales greater than
$100,000, display “Best Seller”
* Books with total sales between $10,000 and
$99,999 display “Average Seller”
* Books with sales less than $10,000 display
“Poor Seller”
Your report should look like this:
SALES
STORE_NAME
BOOK_TITLE
TOTAL_SALES
--------------- ------------------------- -------------------------
-----------
Best Seller hot wet and sexy books
The zen of auto repair $369,815
wee bee books
windows sucks
$307,560
books for dummies
windows sucks
$279,600
borders
the willow weeps no more $263,560
quagmire books
managing stress
$203,745
hot wet and sexy books piano greats
$200,995
specialty bookstore piano greats
$181,225
borders
DOS for dummies
$177,555
blue ride booksellers managing stress
$135,830
hot wet and sexy books writers market
$126,225
eaton books
pay no taxes and go to jail $109,500
Average
Seller ignoramus and dufus
piano greats
$67,877
hot wet and sexy books pay no taxes and go to jail
$60,225
books for dummies the
fall of microsoft $41,895
wee bee books
operations research theory $35,960
barnes and Noble
windows sucks
$34,950
eaton books
piano greats
$33,609
borders
writers market
$32,130
eaton books
Oracle 10g sql tuning
$29,970
quagmire books
bears are people too
$27,960
borders
the fall of microsoft
$27,930
hot wet and sexy books DOS for dummies
$27,930
books for dummies
piano greats
$26,360
barnes and Noble
reduce spending the
$25,155
republican way
wee bee books
reduce spending the
$25,155
republican way
borders
managing stress
$23,970
wee bee books
the willow weeps no more $23,960
blue ride booksellers UNIX for experts
$23,370
wee bee books
writers market
$22,950
hot wet and sexy books operations research theory
$22,475
hot wet and sexy books UNIX for experts
$19,475
ignoramus and dufus
zero loss finance
$18,658
hot wet and sexy books zero loss finance
$17,560
borders
bears are people too
$17,475
quagmire books
reduce spending the
$16,770
republican way
blue ride booksellers the fall of microsoft
$15,960
quagmire books
DOS for dummies
$15,960
quagmire books
piano greats
$13,180
wee bee books
piano greats
$13,180
wee bee books
zero loss finance
$13,170
books for dummies
bears are people too
$10,485
ignoramus and dufus windows sucks
$10,485
Poor Seller
eaton books
The zen of auto repair $9,995
wee bee books
The zen of auto repair
$9,995
ignoramus and dufus The zen of
auto repair $9,995
books for dummies pay
no taxes and go to jail $7,665
books for dummies
writers market
$6,885
books for dummies
non violins in the workplace $5,975
hot wet and sexy books non violins in the workplace
$5,975
barnes and Noble the
willow weeps no more $5,391
barnes and Noble
Oracle 10g sql tuning
$4,995
borders
Oracle 10g sql tuning
$4,995
books for dummies
Oracle 10g sql tuning
$4,995
wee bee books
Oracle 10g sql tuning
$4,995
specialty bookstore Oracle 10g sql
tuning $4,995
eaton books
operations research theory $4,495
ignoramus and dufus operations
research theory $4,495
borders
reduce spending the
$4,193
republican way
books for dummies
managing stress
$3,995
hot wet and sexy books the fall of microsoft
$3,990
ignoramus and dufus DOS for
dummies
$3,990
books for dummies UNIX
for experts
$3,895
eaton books
UNIX for experts
$3,895
wee bee books
UNIX for experts
$3,895
specialty bookstore DOS for
dummies
$3,591
blue ride booksellers bears are people too
$3,495
quagmire books
windows sucks
$3,495
eaton books
bears are people too
$3,495
specialty bookstore the willow
weeps no more $2,995
blue ride booksellers reduce spending the
$2,795
republican way
specialty bookstore reduce
spending the
$2,795
republican way
blue ride booksellers writers market
$2,295
quagmire books
writers market
$2,295
blue ride booksellers DOS for dummies
$1,995
blue ride booksellers non violins in the
workplace $1,195
wee bee books
non violins in the workplace $1,195
eaton books
non violins in the workplace $1,195
blue ride booksellers pay no taxes and go to
jail $1,095
specialty bookstore pay no taxes
and go to jail $1,095
borders
piano greats
$330
80 rows
selected.
ANSWER:
col
store_name format a25
col book_title format a25
col total_sales format $999,999
col sales format a15
break on
sales skip 2
select
(case
when sum(quantity)*book_retail_price
> 100000 then 'Best Seller'
when sum(quantity)*book_retail_price < 10000 then
'Poor Seller'
else 'Average Seller'
end ) sales,
store_name,
book_title,
sum(quantity)*book_retail_price total_sales
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
total_sales 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
|
|
