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