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

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

 
 
 

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.