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 SQL Query

After reading all of the reading assignments for this tutorial and completing exercises 1A and 1B, you are now ready to access your sample database and write some SQL queries. The process of creating and executing SQL queries requires that you take a step-by-step approach to the creation of the query.

In this assignment, your task will be to reproduce the result set below, and submit the SQL file to your instructor. Viewing the schema diagram for the sample database can identify the columns in this report.

The steps to create this query are:

1 – Write the basic query to join the tables and display the desired columns

2 – Add the order by clause

3 – Add SQL*Plus column statements for each column

4 – Insert break, compute, and title statements

5 – write the report to a file with the spool command

Below is a sample of the finished report.  Once you get your SQL to reproduce this report, submit the SQL to your instructor.


Mon May 27                                                             page    1

                                 Total Salaries
                                  by Publisher 

                  Employee
                  Last            Job                                           
Publisher         Name            Description        Salary                    
----------------- --------------- --------------- ---------                    
Big City          Tokheim         Editor            $63,000                    
*****************                                 ---------                    
sum                                                 $63,000                    

Bookhouse         King            Salesperson       $95,000                    
                  Korn            Marketer          $28,000                    
*****************                                 ---------                    
sum                                                $123,000                    

Learning Works    Brannigan       Manager           $66,666                    
*****************                                 ---------                    
sum                                                 $66,666                    

Mammoth House     Baker           Manager           $51,000                     
*****************                                 ---------                    
sum                                                 $51,000                    


Nitpick And Sons  Coleman         Manager           $73,000                    
                  Jackson         Salesperson       $35,000                    
                  Johnson         Editor            $31,000                    
                  Levender        Editor            $14,000                    
                  Linus           Marketer          $45,000                    
*****************                                 ---------                    
sum                                                $198,000                    

ANSWER

set pagesize 999; 

spool publisher_report.lst
ttitle 'Total Salaries|by Publisher' 

column c1 heading 'Publisher'          format a17
column c2 heading 'Employee|Last|Name' format a15
column c3 heading 'Job|Description'    format a15
column c4 heading 'Salary'             format $999,999 

break on c1 skip 2 

compute sum of c4 on c1

select
   initcap(pub_name)      c1,
   initcap(emp_last_name) c2,
   job_name               c3,
   emp_salary             c4
from
   publisher      p,
   emp            e,
   job            j
where
   p.pub_key = e.pub_key
and
   e.job_key = j.job_key
order by
   pub_name,
   emp_last_name
;
 

spool off;


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.