Call for Oracle support & training (800) 766-1884
Free Oracle Tips

Home
Corporate Oracle Training
Custom Oracle Training
Oracle New Features Training
Advanced Oracle DBA Classes
Oracle Tuning Courses
Oracle Tips & Tricks
Oracle Training Links
Oracle Training Links
Oracle Training Links

We are top for USA Oracle Training Clients

 

Free Oracle Tips


 
HTML Text AOL

Free Oracle App Server Tips


 
HTML Text

Oracle support

Oracle training

Oracle tuning

Rednecks!

Remote Oracle

Custom Oracle Training

 

   
  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

 
 
 

Oracle performance tuning book

 

 

Oracle performance tuning software

 
Oracle performance tuning software
 
Oracle performance Tuning 10g reference poster
 
Oracle training in Linux commands
 
Oracle training Excel
 
 
 
 

 

email BC:


Copyright © 1996 -  2014 by Burleson Inc. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.