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

Developing and Running SQL Queries

Querying a relational database can be quite simple, but there are many techniques that can improve your productivity when developing complex SQL queries.  As we know from the readings, the basic format of an SQL statement is as follows:

SELECT
   Column_name 1,
   Column name 2,
   Column name n
FROM
   Table_name 1,
   Table name 2,
   Table name n
WHERE
   Boolean filtering conditions
ORDER BY
   Column 1,
   Column 2,
   Column 3
;

All relational databases rely on SQL to allow access to information within tables.  If we look at a table as a two-dimensional box with rows and columns, we can easily see how SQL works.

All table columns specified after the SELECT clause will appear in the output of the query. In relational theory, this is called a project operation, and it is implemented in relational databases by allowing the user to specify those data columns that they wish to see in the result set.  This has the net effect of shrinking the width of the table as shown below.

FROM Clause

Table names are specified after the FROM clause for all data column and table joins that are required for the query.

WHERE Clause

The most basic SQL construct is the select operator.  The select operator can be modified to return specific rows by using the where clause.  By specifying constraints in the where clause, the user can reduce the number of rows returned by the query.

Join operations

As we know from the  class on database design, relational database contain primary keys and foreign keys to establish relationships between tables.  Whenever two tables have a relationship with each other, the primary key of the master table is reproduced in the subordinate table.  To view the relationship, we need only specify both tables in the where clause

To see how this works, let’s take a simple example from our sample database. Assume that we need to create a report that looks like this:

Sun May 26                                                                                 page 1

                                            Book Report
                                       Organized by Book Type


Book                             Book                           Retail                             
type            Publisher        Title                          Price                              
--------------- ---------------- ------------------------------ ------------------------------     
COMPUTER        Bookhouse        Dos For Dummies                19.95
                Learning Works   Unix For Experts               38.95                              
                Nitpick And Sons Oracle 10g Sql Tuning          49.95                              
                Mammoth House    The Fall Of Microsoft          19.95                              
                Bookhouse        Windows Sucks                  34.95                         
    
*************** ----------------
FICTION         Big City         Bears Are People Too           34.95                              
                Mountain         Cooking Light                  24.95                              
                Mammoth House    Reduce Spending The Republican 27.95                              
                                  Way
                Mountain         The Willow Weeps No More       29.95                              
*************** ----------------                                                                    MANAGEMENT      Nitpick And Sons The Zen Of Auto Repair         99.95                              
                Desk Top         Managing Stress                39.95                              
                Star Books       Operations Research Theory     44.95                              
                Learning Works   Zero Loss Finance              21.95                              
*************** ----------------                                                                    MISCELLANEOUS   Nitpick And Sons How To Housebreak Your Horse   29.95                              
                Book Press       Never Eat Boogers              10.95                              
                Big City         Non Violins In The Workplace   11.95
                Star Books       Pay No Taxes And Go To Jail    10.95
                Mammoth House    Was George Washington Gay?     24.95
                Bookhouse        Writers Market                 22.95                              
*************** ----------------                                                                   
MUSIC           Bookhouse        Piano Greats                   32.95                              
*************** ----------------                                                                                                         20 rows selected.

Creating SQL statements

The creation of any SQL statement involves several steps.  Let’s do this one step-by-step.  Pay special attention to the SQL*Plus formatting commands because you will be using these shortly in a later exercise.

Step 1 – Determine the data columns

The first step is to reference our roadmap figure below.  From this, we note that the columns we desire are inside the book table, and the publisher name column being pub_name in the publisher table.

Before we join the book and sales table together, let’s start by specifying the first three columns in the book table.  We can start our query by specifying the desired column names in the select clause and the desired table name in the from clause as follows:

select
  book_type,
  book_title,
  book_retail_price
from
   book
;

When we run this query we see the following output:

BOOK_TYPE 

BOOK_TITLE

BOOK_RETAIL_PRICE

computer 

windows sucks

34.95

music

piano greats

32.95

computer

DOS for dummies

19.95

management

The zen of auto repair

99.95

management

zero loss finance

21.95

management 

operations research theory

44.95

miscellaneous

non violins in the workplace

11.95                

computer 

UNIX for experts

38.95

miscellaneous

pay no taxes and go to jail

10.95

computer 

the fall of Microsoft

19.95 

miscellaneous

writers market

22.95

management 

managing stress

39.95

fiction

bears are people too

34.95

fiction

reduce spending the republican way

27.95

fiction  

the willow weeps no more 

29.95

computer    

Oracle 10g sql tuning

49.95  

miscellaneous 

Was George Washington gay?

24.95

fiction

cooking light 

24.95 

miscellaneous   

never eat boogers  

10.95

miscellaneous

how to housebreak your horse

29.95

20 rows selected.

Step 2 – Change the case of the output

Now we note that the data is stored in the book table in lowercase, while the report shows book_type in uppercase and book_title in title case.  Hence, we use the upper and initcap functions to our query to change the case as shown below.  Also note that we created column aliases c1, c2, and c3 for the column headers..  This is to make it easier to specify the column names in the column heading line.

select
   upper(book_type)     c1,
   initcap(book_title)  c2,
   book_retail_price    c3
from
   book
;

BOOK_TYPE 

BOOK_TITLE

BOOK_RETAIL_PRICE

computer 

windows sucks

34.95

music

piano greats

32.95

computer

DOS for dummies

19.95

management

The zen of auto repair

99.95

management

zero loss finance

21.95

management 

operations research theory

44.95

miscellaneous

non violins in the workplace

11.95                

computer 

UNIX for experts

38.95

miscellaneous

pay no taxes and go to jail

10.95

computer 

the fall of Microsoft

19.95 

miscellaneous

writers market

22.95

management 

managing stress

39.95

fiction

bears are people too

34.95

fiction

reduce spending the republican way

27.95

fiction  

the willow weeps no more 

29.95

computer    

Oracle 10g sql tuning

49.95  

miscellaneous 

Was George Washington gay?

24.95

fiction

cooking light 

24.95 

miscellaneous   

never eat boogers  

10.95

miscellaneous

how to housebreak your horse

29.95


20 rows selected.

Step 3 – order the output

Next, we note that the book titles appear in alphabetical order by book type and alphabetical by book title.  To do this, we add the book_type and book_title columns to an ORDER BY clause at the end of the SQL query.  We can order the output from any SQL statement by several columns, and specify different sorting orders for the columns.  For example, we could use the following ORDER BY clause to display the output in ascending order of book type and descending order by retail price:

ORDER BY
   Book_title,
   Book_retail_price desc;

select
   upper(book_type)     c1,
   initcap(book_title)  c2,
   book_retail_price    c3
from
   book
order by
   book_type,
   book_title
;

C1

C2

C3

COMPUTER

Dos For Dummies

19.95

COMPUTER

Unix For Experts

38.95

COMPUTER

Oracle 10g Sql Tuning

49.95

COMPUTER    

The Fall Of Microsoft

19.95 

COMPUTER

Windows Sucks 

34.95

FICTION 

Bears Are People Too

34.95

FICTION  

Cooking Light 

24.95  

FICTION 

Reduce Spending The Republican Way 

27.95

FICTION

The Willow Weeps No More

29.95 

MANAGEMENT

The Zen Of Auto Repair 

99.95 

MANAGEMENT

Managing Stress

39.95

MANAGEMENT

Operations Research Theory

44.95

MANAGEMENT 

Zero Loss Finance  

21.95

MISCELLANEOUS 

How To Housebreak Your Horse 

29.95

MISCELLANEOUS

Never Eat Boogers

10.95

MISCELLANEOUS

Non Violins In The Workplace  

11.95

MISCELLANEOUS

Pay No Taxes And Go To Jail 

10.95

MISCELLANEOUS

Was George Washington Gay? 

24.95

MISCELLANEOUS

Writers Market

22.95

MUSIC  

Piano Greats

32.95

20 rows selected.

Step 4– add column aliases and column formats

At this point, we have completed the SQL query and we are now ready to use SQL*Plus formatting commands to present the output.  In this section you will learn the SQL*Plus column command.  This command is used to format column width and headings for columns.  Note that the column command has two sub-arguments heading and format.  The heading command specifies the text heading for the data column and the vertical bar key “|” is used to stack column headers.  The format argument is used to format output as alphanumeric, or numeric, and the format specifies the display characteristics for the columns.

In our example below, note that we have specified column aliases of c1, c2 and c3.  This is a common technique in SQL because it simplifies the queries and reduces the amount of typing.  Note that the c1 in the select section matches the c1 in the SQL*Plus column format.

column c1 heading 'Book|type'         format a15
column c2 heading 'Book|Title'        format a30
column c3 heading 'Book|Retail|Price' format $99.99

select
   upper(book_type)     c1,
   initcap(book_title)  c2,
   book_retail_price    c3
from
   book
order by
   book_type
;

The result of the above query is the report below:

C1

C2

C3

COMPUTER

Dos For Dummies

19.95

COMPUTER

Unix For Experts

38.95

COMPUTER

Oracle 10g Sql Tuning

49.95

COMPUTER    

The Fall Of Microsoft

19.95 

COMPUTER

Windows Sucks 

34.95

FICTION 

Bears Are People Too

34.95

FICTION  

Cooking Light 

24.95  

FICTION 

Reduce Spending The Republican Way 

27.95

FICTION

The Willow Weeps No More

29.95 

MANAGEMENT

The Zen Of Auto Repair 

99.95 

MANAGEMENT

Managing Stress

39.95

MANAGEMENT

Operations Research Theory

44.95

MANAGEMENT 

Zero Loss Finance  

21.95

MISCELLANEOUS 

How To Housebreak Your Horse 

29.95

MISCELLANEOUS

Never Eat Boogers

10.95

MISCELLANEOUS

Non Violins In The Workplace  

11.95

MISCELLANEOUS

Pay No Taxes And Go To Jail 

10.95

MISCELLANEOUS

Was George Washington Gay? 

24.95

MISCELLANEOUS

Writers Market

22.95

MUSIC  

Piano Greats

32.95

20 rows selected.

Step 6 – add breaks, averages and title

Next, we are ready to add breaks.  The break command is used inside SQL*Plus to eliminate the replication of data columns for repeating groups.

Note that after we add the breaks, the COMPUTE column is repeated for every row in that book type:

column c1 heading 'Book|type'         format a15
column c2 heading 'Book|Title'        format a30
column c3 heading 'Book|Retail|Price' format $99.99

title ‘Book Report|Organized by Book Type’

break on c1 skip 2

compute avg of c3 on c1

select
   upper(book_type)     c1,
   initcap(book_title)  c2,
   book_retail_price    c3
from
   book
order by
   book_type,
   book_title
;

The result of the above query is the report below:

 

                                                                Book
Book                             Book                           Retail                             
type            Publisher        Title                          Price                              
--------------- ---------------- ------------------------------ ------------------------------     
COMPUTER        Bookhouse        Dos For Dummies                19.95                              
                Learning Works   Unix For Experts               38.95                               
                Nitpick And Sons Oracle 10g Sql Tuning          49.95                              
                Mammoth House    The Fall Of Microsoft          19.95                              
                Bookhouse        Windows Sucks                  34.95                              
*************** ----------------                                                                  
avg                                                            $32.75
                            

FICTION         Big City         Bears Are People Too           34.95
                Mountain         Cooking Light                  24.95                              
                Mammoth House    Reduce Spending The Republican 27.95                              
                                  Way
                Mountain         The Willow Weeps No More       29.95                               
*************** ----------------                                                                   
avg                                                            $29.45

MANAGEMENT      Nitpick And Sons The Zen Of Auto Repair         99.95                              
                Desk Top         Managing Stress                39.95                              
                Star Books       Operations Research Theory     44.95                              
                Learning Works   Zero Loss Finance              21.95                               
*************** ----------------                                                                   
avg                                                            $51.70

MISCELLANEOUS   Nitpick And Sons How To Housebreak Your Horse   29.95
                Book Press       Never Eat Boogers              10.95                              
                Big City         Non Violins In The Workplace   11.95                              
                Star Books       Pay No Taxes And Go To Jail    10.95                              
                Mammoth House    Was George Washington Gay?     24.95                              
                Bookhouse        Writers Market                 22.95                              
*************** ----------------                                                                   
avg                                                            $18.62    

MUSIC           Bookhouse        Piano Greats                   32.95                              
*************** ----------------                                                                    
avg                                                            $32.95

20 rows selected.

Step 7 – Add a title and spool the output file

The final step is to use the SQL*Plus spool command to direct our report into a file.  Note in the example below that we have spooled the report to a file called book_report.lst, and that we use the “spool off” command at the end of the code to stop the spooling.

spool t.lst

title 'Book Report|Organized by Book Type'

column c1 heading 'Book|type'         format a15
column c4 heading 'Publisher'         format a16
column c2 heading 'Book|Title'        format a30
column c3 heading 'Retail|Price'      format $99.99

break on c1 skip 2

compute avg of c4 on c1

select
  upper(book_type)    c1,
  initcap(pub_name)   c4,
  initcap(book_title) c2,
  book_retail_price   c3
from
   book      b,
   publisher p
where
   b.pub_key = p.pub_key
order by
   book_type,
   book_title
;

spool off;

The result of the above query is the report below:

 

Mon May 27                                                                                 page    1

                                            Book Report
                                       Organized by Book Type


Book                             Book                            Retail                             
type            Publisher        Title                            Price                            
--------------- ---------------- ------------------------------ -------                            
COMPUTER        Bookhouse        Dos For Dummies                 $19.95                            
                Learning Works   Unix For Experts                $38.95                            
                Nitpick And Sons Oracle 10g Sql Tuning           $49.95                             
                Mammoth House    The Fall Of Microsoft           $19.95                            
                Bookhouse        Windows Sucks                   $34.95                            
*************** ----------------                                -------                            
avg                                                              $32.75                            

FICTION         Big City         Bears Are People Too            $34.95                            
                Mountain         Cooking Light                   $24.95                            
                Mammoth House    Reduce Spending The Republican  $27.95                            
                                  Way
                Mountain         The Willow Weeps No More        $29.95                            
*************** ----------------                                -------                            
avg                                                              $29.45                            


MANAGEMENT      Nitpick And Sons The Zen Of Auto Repair          $99.95                            
                Desk Top         Managing Stress                 $39.95                            
                Star Books       Operations Research Theory      $44.95                            
                Learning Works   Zero Loss Finance               $21.95                            
*************** ----------------                                -------                            
avg                                                              $51.70                            

MISCELLANEOUS   Nitpick And Sons How To Housebreak Your Horse    $29.95                            
                Book Press       Never Eat Boogers               $10.95                            
                Big City         Non Violins In The Workplace    $11.95                            
                Star Books       Pay No Taxes And Go To Jail     $10.95                            
                Mammoth House    Was George Washington Gay?      $24.95                            
                Bookhouse        Writers Market                  $22.95                            
*************** ----------------                                -------                            
avg                                                              $18.62                            

MUSIC           Bookhouse        Piano Greats                    $32.95                            
*************** ----------------                                -------                            
avg                                                              $32.95                            

20 rows selected.

And that is all there is to it.  Using a step-by step approach, you can develop a complex query in a progression of simple steps.

 

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.