Use a CASE statement in 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 oracle9i 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 oracle9i sql
tuning $4,995
borders oracle9i sql
tuning $4,995
books for dummies oracle9i sql
tuning $4,995
wee bee books oracle9i sql
tuning $4,995
specialty bookstore oracle9i 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 ja $109,500
il
Average Seller ignoramus and dufus
piano greats $67,877
hot wet and sexy
books pay no taxes and go to ja
$60,225
il
books for
dummies the fall of microsoft
$41,895
wee bee
books operations research
theor $35,960
y
barnes and
Noble windows
sucks $34,950
eaton
books piano
greats $33,609
borders writers
market $32,130
eaton
books oracle9i 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 repub
$25,155
lican
way
wee bee
books reduce spending the
repub $25,155
lican 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 theor
$22,475
y
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
repub $16,770
lican 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
ja $7,665
il
books for
dummies writers
market $6,885
books for
dummies non violins in the
workpl $5,975
ace
hot wet and sexy
books non violins in the workpl
$5,975
ace
barnes and Noble
the willow weeps no more
$5,391
barnes and
Noble oracle9i sql
tuning $4,995
borders oracle9i sql
tuning $4,995
books for
dummies oracle9i sql
tuning $4,995
wee bee
books oracle9i sql
tuning $4,995
specialty
bookstore oracle9i sql
tuning $4,995
eaton
books operations research
theor $4,495
y
ignoramus and
dufus operations research
theor $4,495
y
borders reduce
spending the repub $4,193
lican
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
repub $2,795
lican way
specialty
bookstore reduce spending the
repub $2,795
lican 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
workpl $1,195
ace
wee bee books
non violins in the workpl
$1,195
ace
eaton
books non violins in the
workpl $1,195
ace
blue ride
booksellers pay no taxes and go to
ja $1,095
il
specialty
bookstore pay no taxes and go to
ja $1,095
il
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
;
Note: These exercises may use the
pubsdb.sql script that can be
downloaded at this link.
|
