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

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


Free Oracle App Server Tips


Oracle support

Oracle training

Oracle tuning


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 

                  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                    


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

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

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:

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.