Teach Yourself SQL in 21 Days, Second Edition
  
- Day 20 -
SQL*Plus
Objectives
Today you will learn about SQL*Plus, the SQL interface for Oracle's RDBMS. By
the end of Day 20, you will understand the following elements of SQL*Plus:
- How to use the SQL*Plus buffer
- How to format reports attractively
- How to manipulate dates
- How to make interactive queries
- How to construct advanced reports
- How to use the powerful DECODE function
Introduction
We are presenting SQL*Plus today because of Oracle's dominance in the relational
database market and because of the power and flexibility SQL*Plus offers to the database
user. SQL*Plus resembles Transact-SQL (see Day 19, "Transact-SQL: An Introduction")
in many ways. Both implementations comply with the ANSI SQL standard for the most
part, which is still the skeleton of any implementation.
SQL*Plus commands can enhance an SQL session and improve the format of queries
from the database. SQL*Plus can also format reports, much like a dedicated report
writer. SQL*Plus supplements both standard SQL and PL/SQL and helps relational database
programmers gather data that is in a desirable format.
The SQL*Plus Buffer
The SQL*Plus buffer is an area that stores commands that are specific to your
particular SQL session. These commands include the most recently executed SQL statement
and commands that you have used to customize your SQL session, such as formatting
commands and variable assignments. This buffer is like a short-term memory. Here
are some of the most common SQL buffer commands:
- LIST line_number--Lists a line from the statement in the buffer and
designates it as the current line.
- CHANGE/old_value/new_value--Changes old_value to new_value
on the current line in the buffer.
- APPEND text--Appends text to the current line in the buffer.
- DEL-- Deletes the current line in the buffer.
- SAVE newfile--Saves the SQL statement in the buffer to a file.
- GET filename--Gets an SQL file and places it into the buffer.
- /--Executes the SQL statement in the buffer.
We begin with a simple SQL statement:
INPUT:
SQL> select *
2 from products
3 where unit_cost > 25;
OUTPUT:
PRO PRODUCT_NAME UNIT_COST
--- ------------------------------ ---------
P01 MICKEY MOUSE LAMP 29.95
P06 SQL COMMAND REFERENCE 29.99
P07 BLACK LEATHER BRIEFCASE 99.99
The LIST command lists the most recently executed SQL statement in the
buffer. The output will simply be the displayed statement.
SQL> list
1 select *
2 from products
3* where unit_cost > 25
ANALYSIS:
Notice that each line is numbered. Line numbers are important in the buffer; they
act as pointers that enable you to modify specific lines of your statement using
the SQL*PLUS buffer. The SQL*Plus buffer is not a full screen editor; after you hit
Enter, you cannot use the cursor to move up a line, as shown in the following example.
INPUT:
SQL> select *
2 from products
3 where unit_cost > 25
4 /
NOTE: As with SQL commands, you may issue
SQL*Plus commands in either uppercase or lowercase.
TIP: You can abbreviate most SQL*Plus
commands; for example, LIST can be abbreviated as l.
You can move to a specific line from the buffer by placing a line number after
the l:
INPUT:
SQL> l3
3* where unit_cost > 25
ANALYSIS:
Notice the asterisk after the line number 3. This asterisk denotes the current
line number. Pay close attention to the placement of the asterisk in today's examples.
Whenever a line is marked by the asterisk, you can make changes to that line.
Because you know that your current line is 3, you are free to make changes. The
syntax for the CHANGE command is as follows:
SYNTAX:
CHANGE/old_value/new_value
or
C/old_value/new_value
INPUT:
SQL> c/>/<
OUTPUT:
3* where unit_cost < 25
INPUT:
SQL> l
OUTPUT:
1 select *
2 from products
3* where unit_cost < 25
ANALYSIS:
The greater than sign (>) has been changed to less than (<)
on line 3. Notice after the change was made that the newly modified line was displayed.
If you issue the LIST command or l, you can see the full statement.
Now execute the statement:
INPUT:
SQL> /
OUTPUT:
PRO PRODUCT_NAME UNIT_COST
--- ------------------------------ ---------
P02 NO 2 PENCILS - 20 PACK 1.99
P03 COFFEE MUG 6.95
P04 FAR SIDE CALENDAR 10.5
P05 NATURE CALENDAR 12.99
ANALYSIS:
The forward slash at the SQL> prompt executes any statement that is
in the buffer.
INPUT:
SQL> l
OUTPUT:
1 select *
2 from products
3* where unit_cost < 25
Now, you can add a line to your statement by typing a new line number at the SQL>
prompt and entering text. After you make the addition, get a full statement listing.
Here's an example:
INPUT:
SQL> 4 order by unit_cost
SQL> 1
OUTPUT:
1 select *
2 from products
3 where unit_cost < 25
4* order by unit_cost
ANALYSIS:
Deleting a line is easier than adding a line. Simply type DEL 4 at the SQL>
prompt to delete line 4. Now get another statement listing to verify that the line
is gone.
INPUT:
SQL> DEL4
SQL> l
OUTPUT:
1 select *
2 from products
3* where unit_cost < 25
Another way to add one or more lines to your statement is to use the INPUT
command. As you can see in the preceding list, the current line number is 3. At the
prompt type input and then press Enter. Now you can begin typing text. Each time
you press Enter, another line will be created. If you press Enter twice, you will
obtain another SQL> prompt. Now if you display a statement listing, as
in the following example, you can see that line 4 has been added.
INPUT:
SQL> input
4i and product_id = 'P01'
5i
SQL> l
OUTPUT:
1 select *
2 from products
3 where unit_cost < 25
4 and product_id = 'P01'
5* order by unit_cost
To append text to the current line, issue the APPEND command followed
by the text. Compare the output in the preceding example--the current line number
is 5--to the following example.
INPUT:
SQL> append desc
OUTPUT:
5* order by unit_cost desc
Now get a full listing of your statement:
INPUT:
SQL> l
OUTPUT:
1 select *
2 from products
3 where unit_cost < 25
4 and product_id = 'P01'
5* order by unit_cost desc
Suppose you want to wipe the slate clean. You can clear the contents of the SQL*Plus
buffer by issuing the command CLEAR BUFFER. As you will see later,
you can also use the CLEAR command to clear specific settings from the buffer,
such as column formatting information and computes on a report.
INPUT:
SQL> clear buffer
OUTPUT:
buffer cleared
INPUT:
SQL> l
OUTPUT:
No lines in SQL buffer.
ANALYSIS:
Obviously, you won't be able to retrieve anything from an empty buffer. You aren't
a master yet, but you should be able to maneuver with ease by manipulating your commands
in the buffer.
The DESCRIBE Command
The handy DESCRIBE command enables you to view the structure of a table
quickly without having to create a query against the data dictionary.
SYNTAX:
DESC[RIBE] table_name
Take a look at the two tables you will be using throughout the day.
INPUT:
SQL> describe orders
OUTPUT:
Name Null? Type
------------------------------- -------- ----
ORDER_NUM NOT NULL NUMBER(2)
CUSTOMER NOT NULL VARCHAR2(30)
PRODUCT_ID NOT NULL CHAR(3)
PRODUCT_QTY NOT NULL NUMBER(5)
DELIVERY_DATE DATE
The following statement uses the abbreviation DESC instead of DESCRIBE:
INPUT:
SQL> desc products
OUTPUT:
Name Null? Type
------------------------------- -------- ----
PRODUCT_ID NOT NULL VARCHAR2(3)
PRODUCT_NAME NOT NULL VARCHAR2(30)
UNIT_COST NOT NULL NUMBER(8,2)
ANALYSIS:
DESC displays each column name, which columns must contain data (NULL/NOT
NULL), and the data type for each column. If you are writing many queries, you
will find that few days go by without using this command. Over a long time, this
command can save you many hours of programming time. Without DESCRIBE you
would have to search through project documentation or even database manuals containing
lists of data dictionary tables to get this information.
The SHOW Command
The SHOW command displays the session's current settings, from formatting
commands to who you are. SHOW ALL displays all settings. This discussion
covers some of the most common settings.
INPUT:
SQL> show all
OUTPUT:
appinfo is ON and set to "SQL*Plus"
arraysize 15
autocommit OFF
autoprint OFF
autotrace OFF
blockterminator "." (hex 2e)
btitle OFF and is the 1st few characters of the next SELECT statement
closecursor OFF
colsep " "
cmdsep OFF
compatibility version NATIVE
concat "." (hex 2e)
copycommit 0
copytypecheck is ON
crt ""
define "&" (hex 26)
echo OFF
editfile "afiedt.buf"
embedded OFF
escape OFF
feedback ON for 6 or more rows
flagger OFF
flush ON
heading ON
headsep "|" (hex 7c)
linesize 100
lno 6
long 80
longchunksize 80
maxdata 60000
newpage 1
null ""
numformat ""
numwidth 9
pagesize 24
pause is OFF
pno 1
recsep WRAP
recsepchar " " (hex 20)
release 703020200
repheader OFF and is NULL
repfooter OFF and is NULL
serveroutput OFF
showmode OFF
spool OFF
sqlcase MIXED
sqlcode 1007
sqlcontinue "> "
sqlnumber ON
sqlprefix "#" (hex 23)
sqlprompt "SQL> "
sqlterminator ";" (hex 3b)
suffix "SQL"
tab ON
termout ON
time OFF
timing OFF
trimout ON
trimspool OFF
ttitle OFF and is the 1st few characters of the next SELECT statement
underline "-" (hex 2d)
user is "RYAN"
verify ON
wrap : lines will be wrapped
The SHOW command displays a specific setting entered by the user. Suppose
you have access to multiple database user IDs and you want to see how you are logged
on. You can issue the following command:
INPUT:
SQL> show user
OUTPUT:
user is "RYAN"
To see the current line size of output, you would type:
INPUT:
SQL> show linesize
OUTPUT:
linesize 100
File Commands
Various commands enable you to manipulate files in SQL*Plus. These commands include
creating a file, editing the file using a full-screen editor as opposed to using
the SQL*Plus buffer, and redirecting output to a file. You also need to know how
to execute an SQL file after it is created.
The SAVE, GET, and EDIT Commands
The SAVE command saves the contents of the SQL statement in the buffer
to a file whose name you specify. For example:
INPUT:
SQL> select *
2 from products
3 where unit_cost < 25
SQL> save query1.sql
OUTPUT:
Created file query1.sql
ANALYSIS:
After a file has been saved, you can use the GET command to list the
file. GET is very similar to the LIST command. Just remember that
GET deals with statements that have been saved to files, whereas LIST
deals with the statement that is stored in the buffer.
INPUT:
SQL> get query1
OUTPUT:
1 select *
2 from products
3* where unit_cost < 25
You can use the EDIT command either to create a new file or to edit an
existing file. When issuing this command, you are taken into a full-screen editor,
more than likely Notepad in Windows. You will find that it is usually easier to modify
a file with EDIT than through the buffer, particularly if you are dealing
with a large or complex statement. Figure 20.1 shows an example of the EDIT
command.
INPUT:
SQL> edit query1.sql
Figure 20.1.
Editing a file in SQL*Plus.
Starting a File
Now that you know how to create and edit an SQL file, the command to execute it
is simple. It can take one of the following forms:
SYNTAX:
START filename
or
STA filename
or
@filename
TIP: Commands are not case sensitive.
INPUT:
SQL> start query1.sql
OUTPUT:
PRO PRODUCT_NAME UNIT_COST
--- ------------------------------ ---------
P02 NO 2 PENCILS - 20 PACK 1.99
P03 COFFEE MUG 6.95
P04 FAR SIDE CALENDAR 10.5
P05 NATURE CALENDAR 12.99
NOTE: You do not have to specify the file
extension .sql to start a file from SQL*Plus. The database assumes that
the file you are executing has this extension. Similarly, when you are creating a
file from the SQL> prompt or use SAVE, GET, or EDIT,
you do not have to include the extension if it is .sql.
INPUT:
SQL> @query1
OUTPUT:
PRO PRODUCT_NAME UNIT_COST
--- ------------------------------ ---------
P02 NO 2 PENCILS - 20 PACK 1.99
P03 COFFEE MUG 6.95
P04 FAR SIDE CALENDAR 10.5
P05 NATURE CALENDAR 12.99
INPUT:
SQL> run query1
OUTPUT:
1 select *
2 from products
3* where unit_cost < 25
PRO PRODUCT_NAME UNIT_COST
--- ------------------------------ ---------
P02 NO 2 PENCILS - 20 PACK 1.99
P03 COFFEE MUG 6.95
P04 FAR SIDE CALENDAR 10.5
P05 NATURE CALENDAR 12.99
Notice that when you use RUN to execute a query, the statement is echoed,
or displayed on the screen.
Spooling Query Output
Viewing the output of your query on the screen is very convenient, but what if
you want to save the results for future reference or you want to print the file?
The SPOOL command allows you to send your output to a specified file. If
the file does not exist, it will be created. If the file exists, it will be overwritten,
as shown in Figure 20.2.
INPUT:
SQL> spool prod.lst
SQL> select *
2 from products;
OUTPUT:
PRO PRODUCT_NAME UNIT_COST
--- ------------------------------ ---------
P01 MICKEY MOUSE LAMP 29.95
P02 NO 2 PENCILS - 20 PACK 1.99
P03 COFFEE MUG 6.95
P04 FAR SIDE CALENDAR 10.5
P05 NATURE CALENDAR 12.99
P06 SQL COMMAND REFERENCE 29.99
P07 BLACK LEATHER BRIEFCASE 99.99
7 rows selected.
INPUT:
SQL> spool off
SQL> edit prod.lst
ANALYSIS:
The output in Figure 20.2 is an SQL*Plus file. You must use the SPOOL OFF
command to stop spooling to a file. When you exit SQL*Plus, SPOOL OFF is
automatic. But if you do not exit and you continue to work in SQL*Plus, everything
you do will be spooled to your file until you issue the command SPOOL OFF.
Figure 20.2.
Spooling your output to a file.
SET Commands
SET commands in Oracle change SQL*Plus session settings. By using these
commands, you can customize your SQL working environment and invoke options to make
your output results more presentable. You can control many of the SET commands
by turning an option on or off.
To see how the SET commands work, perform a simple select:
INPUT:
SQL> select *
2 from products;
OUTPUT:
PRO PRODUCT_NAME UNIT_COST
--- ------------------------------ ---------
P01 MICKEY MOUSE LAMP 29.95
P02 NO 2 PENCILS - 20 PACK 1.99
P03 COFFEE MUG 6.95
P04 FAR SIDE CALENDAR 10.5
P05 NATURE CALENDAR 12.99
P06 SQL COMMAND REFERENCE 29.99
P07 BLACK LEATHER BRIEFCASE 99.99
7 rows selected.
ANALYSIS:
The last line of output
7 rows selected.
is called feedback, which is an SQL setting that can be modified. The settings
have defaults, and in this case the default for FEEDBACK is on.
If you wanted, you could type
SET FEEDBACK ON
before issuing your select statement. Now suppose that you do not want to see
the feedback, as happens to be the case with some reports, particularly summarized
reports with computations.
INPUT:
SQL> set feedback off
SQL> select *
2 from products;
OUTPUT:
PRO PRODUCT_NAME UNIT_COST
--- ------------------------------ ---------
P01 MICKEY MOUSE LAMP 29.95
P02 NO 2 PENCILS - 20 PACK 1.99
P03 COFFEE MUG 6.95
P04 FAR SIDE CALENDAR 10.5
P05 NATURE CALENDAR 12.99
P06 SQL COMMAND REFERENCE 29.99
P07 BLACK LEATHER BRIEFCASE 99.99
ANALYSIS:
SET FEEDBACK OFF turns off the feedback display.
In some cases you may want to suppress the column headings from being displayed
on a report. This setting is called HEADING, which can also be set ON
or OFF.
INPUT:
SQL> set heading off
SQL> /
OUTPUT:
P01 MICKEY MOUSE LAMP 29.95
P02 NO 2 PENCILS - 20 PACK 1.99
P03 COFFEE MUG 6.95
P04 FAR SIDE CALENDAR 10.5
P05 NATURE CALENDAR 12.99
P06 SQL COMMAND REFERENCE 29.99
P07 BLACK LEATHER BRIEFCASE 99.99
ANALYSIS:
The column headings have been eliminated from the output. Only the actual data
is displayed.
You can change a wide array of settings to manipulate how your output is displayed.
One option, LINESIZE, allows you to specify the length of each line of your
output. A small line size will more than likely cause your output to wrap; increasing
the line size may be necessary to suppress wrapping of a line that exceeds the default
80 characters. Unless you are using wide computer paper (11 x 14), you may want to
landscape print your report if you are using a line size greater than 80. The following
example shows the use of LINESIZE.
INPUT:
SQL> set linesize 40
SQL> /
OUTPUT:
P01 MICKEY MOUSE LAMP
29.95
P02 NO 2 PENCILS - 20 PACK
1.99
P03 COFFEE MUG
6.95
P04 FAR SIDE CALENDAR
10.5
P05 NATURE CALENDAR
12.99
P06 SQL COMMAND REFERENCE
29.99
P07 BLACK LEATHER BRIEFCASE
99.99
You can also adjust the size of each page of your output by using the setting
PAGESIZE. If you are simply viewing your output on screen, the best setting
for PAGESIZE is 23, which eliminates multiple page breaks per screen.
In the following example PAGESIZE is set to a low number to show you what
happens on each page break.
INPUT:
SQL> set linesize 80
SQL> set heading on
SQL> set pagesize 7
SQL> /
OUTPUT:
PRO PRODUCT_NAME UNIT_COST
-- ------------------------------ --------
P01 MICKEY MOUSE LAMP 29.95
P02 NO 2 PENCILS - 20 PACK 1.99
P03 COFFEE MUG 6.95
P04 FAR SIDE CALENDAR 10.5
PRO PRODUCT_NAME UNIT_COST
-- ------------------------------ --------
P05 NATURE CALENDAR 12.99
P06 SQL COMMAND REFERENCE 29.99
P07 BLACK LEATHER BRIEFCASE 99.99
ANALYSIS:
Using the setting of PAGESIZE 7, the maximum number of lines that may
appear on a single page is seven. New column headings will print automatically at
the start of each new page.
The TIME setting displays the current time as part of your SQL>
prompt.
INPUT:
SQL> set time on
OUTPUT:
08:52:02 SQL>
These were just a few of the SET options, but they are all manipulated
in basically the same way. As you saw from the vast list of SET commands
in the earlier output from the SHOW ALL statement, you have many options
when customizing your SQL*Plus session. Experiment with each option and see what
you like best. You will probably keep the default for many options, but you may find
yourself changing other options frequently based on different scenarios.
LOGIN.SQL File
When you log out of SQL*Plus, all of your session settings are cleared. When you
log back in, your settings will have to be reinitialized if they are not the defaults
unless you are using a login.sql file. This file is automatically executed
when you sign on to SQL*Plus. This initialization file is similar to the autoexec.bat
file on your PC or your .profile in a UNIX Korn Shell environment.
In Personal Oracle7 you can use the EDIT command to create your Login.sql
file, as shown in Figure 20.3.
Figure 20.3.
Your Login.sql file.
When you log on to SQL*Plus, here is what you will see:
SQL*Plus: Release 3.3.2.0.2 - Production on Sun May 11 20:37:58 1997
Copyright (c) Oracle Corporation 1979, 1994. All rights reserved.
Enter password: ****
Connected to:
Personal Oracle7 Release 7.3.2.2.0 - Production Release
With the distributed and replication options
PL/SQL Release 2.3.2.0.0 - Production
'HELLO!
-------
HELLO !
20:38:02 SQL>
CLEAR Command
In SQL*Plus, settings are cleared by logging off, or exiting SQL*Plus. Some of
your settings may also be cleared by using the CLEAR command, as shown in
the following examples.
INPUT:
SQL> clear col
OUTPUT:
columns cleared
INPUT:
SQL> clear break
OUTPUT:
breaks cleared
INPUT:
SQL> clear compute
OUTPUT:
computes cleared
Formatting Your Output
SQL*Plus also has commands that enable you to arrange your output in almost any
format. This section covers the basic formatting commands for report titles, column
headings and formats, and giving a column a "new value."
TTITLE and BTITLE
TTITLE and BTITLE enable you to create titles on your reports.
Previous days covered queries and output, but with SQL*Plus you can convert simple
output into presentable reports. The TTITLE command places a title at the
top of each page of your output or report. BTITLE places a title at the
bottom of each page of your report. Many options are available with each of these
commands, but today's presentation covers the essentials. Here is the basic syntax
of TTITLE and BTITLE:
SYNTAX:
TTITLE [center|left|right] 'text' [&variable] [skip n]
BTITLE [center|left|right] 'text' [&variable] [skip n]
INPUT:
SQL> ttitle 'A LIST OF PRODUCTS'
SQL> btitle 'THAT IS ALL'
SQL> set pagesize 15
SQL> /
OUTPUT:
Wed May 07
page 1
A LIST OF PRODUCTS
PRO PRODUCT_NAME UNIT_COST
-- ------------------------------ ---------
P01 MICKEY MOUSE LAMP 29.95
P02 NO 2 PENCILS - 20 PACK 1.99
P03 COFFEE MUG 6.95
P04 FAR SIDE CALENDAR 10.5
P05 NATURE CALENDAR 12.99
P06 SQL COMMAND REFERENCE 29.99
P07 BLACK LEATHER BRIEFCASE 99.99
THAT IS ALL
7 rows selected.
ANALYSIS:
The title appears at the top of the page and at the bottom. Many people use the
bottom title for signature blocks to verify or make changes to data on the report.
Also, in the top title the date and page number are part of the title.
Formatting Columns (COLUMN, HEADING, FORMAT)
Formatting columns refers to the columns that are to be displayed or the
columns that are listed after the SELECT in an SQL statement. The COLUMN,
HEADING, and FORMAT commands rename column headings and control
the way the data appears on the report.
The COL[UMN] command is usually used with either the HEADING
command or the FORMAT command. COLUMN defines the column that you
wish to format. The column that you are defining must appear exactly as it is typed
in the SELECT statement. You may use a column alias instead of the full
column name to identify a column with this command.
When using the HEADING command, you must use the COLUMN command
to identify the column on which to place the heading.
When using the FORMAT command, you must use the COLUMN command
to identify the column you wish to format.
The basic syntax for using all three commands follows. Note that the HEADING
and FORMAT commands are optional. In the FORMAT syntax, you must
use an a if the data has a character format or use 0s and 9s
to specify number data types. Decimals may also be used with numeric values. The
number to the right of the a is the total width that you wish to allow for
the specified column.
SYNTAX:
COL[UMN] column_name HEA[DING] "new_heading" FOR[MAT] [a1|99.99]
The simple SELECT statement that follows shows the formatting of a column.
The specified column is of NUMBER data type, and we want to display the
number in a decimal format with a dollar sign.
INPUT:
SQL> column unit_cost heading "PRICE" format $99.99
SQL> select product_name, unit_cost
2 from products;
OUTPUT:
PRODUCT_NAME PRICE
------------------------------ -------
MICKEY MOUSE LAMP $29.95
NO 2 PENCILS - 20 PACK $1.99
COFFEE MUG $6.95
FAR SIDE CALENDAR $10.50
NATURE CALENDAR $12.99
SQL COMMAND REFERENCE $29.99
BLACK LEATHER BRIEFCASE $99.99
7 rows selected.
ANALYSIS:
Because we used the format 99.99, the maximum number that will be displayed
is 99.99.
Now try abbreviating the commands. Here's something neat you can do with the HEADING
command:
INPUT:
SQL> col unit_cost hea "UNIT|COST" for $09.99
SQL> select product_name, unit_cost
2 from products;
OUTPUT:
PRODUCT_NAME UNIT COST
---------------------------- ---------
MICKEY MOUSE LAMP $29.95
NO 2 PENCILS - 20 PACK $01.99
COFFEE MUG $06.95
FAR SIDE CALENDAR $10.50
NATURE CALENDAR $12.99
SQL COMMAND REFERENCE $29.99
BLACK LEATHER BRIEFCASE $99.99
7 rows selected.
ANALYSIS:
The pipe sign (|) in the HEADING command forces the following
text of the column heading to be printed on the next line. You may use multiple pipe
signs. The technique is handy when the width of your report starts to push the limits
of the maximum available line size. The format of the unit cost column is now 09.99.
The maximum number displayed is still 99.99, but now a 0 will precede
all numbers less than 10. You may prefer this format because it makes the
dollar amounts appear uniform.
Report and Group Summaries
What would a report be without summaries and computations? Let's just say that
you would have one frustrated programmer. Certain commands in SQL*Plus allow you
to break up your report into one or more types of groups and perform summaries or
computations on each group. BREAK is a little different from SQL's standard
group functions, such as COUNT( ) and SUM( ). These functions are
used with report and group summaries to provide a more complete report.
BREAK ON
The BREAK ON command breaks returned rows of data from an SQL statement
into one or more groups. If you break on a customer's name, then by default the customer's
name will be printed only the first time it is returned and left blank with each
row of data with the corresponding name. Here is the very basic syntax of the BREAK
ON command:
SYNTAX:
BRE[AK] [ON column1 ON column2...][SKIP n|PAGE][DUP|NODUP]
You may also break on REPORT and ROW. Breaking on REPORT
performs computations on the report as a whole, whereas breaking on ROW
performs computations on each group of rows.
The SKIP option allows you to skip a number of lines or a page on each
group. DUP or NODUP suggests whether you want duplicates to be
printed in each group. The default is NODUP.
Here is an example:
INPUT:
SQL> col unit_cost head 'UNIT|COST' for $09.99
SQL> break on customer
SQL> select o.customer, p.product_name, p.unit_cost
2 from orders o,
3 products p
4 where o.product_id = p.product_id
5 order by customer;
OUTPUT:
CUSTOMER PRODUCT_NAME UNIT COST
------------------------------ ---------------------------- ---------
JONES and SONS MICKEY MOUSE LAMP $29.95
NO 2 PENCILS - 20 PACK $01.99
COFFEE MUG $06.95
PARAKEET CONSULTING GROUP MICKEY MOUSE LAMP $29.95
NO 2 PENCILS - 20 PACK $01.99
SQL COMMAND REFERENCE $29.99
BLACK LEATHER BRIEFCASE $99.99
FAR SIDE CALENDAR $10.50
PLEWSKY MOBILE CARWASH MICKEY MOUSE LAMP $29.95
BLACK LEATHER BRIEFCASE $99.99
BLACK LEATHER BRIEFCASE $99.99
NO 2 PENCILS - 20 PACK $01.99
NO 2 PENCILS - 20 PACK $01.99
13 rows selected.
Each unique customer is printed only once. This report is much easier to read
than one in which duplicate customer names are printed. You must order your results
in the same order as the column(s) on which you are breaking for the BREAK
command to work.
COMPUTE
The COMPUTE command is used with the BREAK ON command. COMPUTE
allows you to perform various computations on each group of data and/or on the entire
report.
SYNTAX:
COMP[UTE] function OF column_or_alias ON column_or_row_or_report
Some of the more popular functions are
- AVG--Computes the average value on each group.
- COUNT--Computes a count of values on each group.
- SUM--Computes a sum of values on each group.
Suppose you want to create a report that lists the information from the PRODUCTS
table and computes the average product cost on the report.
INPUT:
SQL> break on report
SQL> compute avg of unit_cost on report
SQL> select *
2 from products;
OUTPUT:
PRO PRODUCT_NAME UNIT_COST
--- ------------------------------ ---------
P01 MICKEY MOUSE LAMP 29.95
P02 NO 2 PENCILS - 20 PACK 1.99
P03 COFFEE MUG 6.95
P04 FAR SIDE CALENDAR 10.50
P05 NATURE CALENDAR 12.99
P06 SQL COMMAND REFERENCE 29.99
P07 BLACK LEATHER BRIEFCASE 99.99
---------
avg 27.48
ANALYSIS:
You can obtain the information you want by breaking on REPORT and then
computing the avg of the unit_cost on REPORT.
Remember the CLEAR command? Now clear the last compute from the buffer
and start again--but this time you want to compute the amount of money spent by each
customer. Because you do not want to see the average any longer, you should also
clear the computes.
INPUT:
SQL> clear compute
OUTPUT:
computes cleared
Now clear the last BREAK. (You don't really have to clear the BREAK
in this case because you still intend to break on report.)
INPUT:
SQL> clear break
OUTPUT:
breaks cleared
The next step is to reenter the breaks and computes the way you want them now.
You will also have to reformat the column unit_cost to accommodate a larger
number because you are computing a sum of the unit_cost on the report. You
need to allow room for the grand total that uses the same format as the column on
which it is being figured. So you need to add another place to the left of the decimal.
INPUT:
SQL> col unit_cost hea 'UNIT|COST' for $099.99
SQL> break on report on customer skip 1
SQL> compute sum of unit_cost on customer
SQL> compute sum of unit_cost on report
Now list the last SQL statement from the buffer.
INPUT:
SQL> l
OUTPUT:
1 select o.customer, p.product_name, p.unit_cost
2 from orders o,
3 products p
4 where o.product_id = p.product_id
5* order by customer
ANALYSIS:
Now that you have verified that this statement is the one you want, you can execute
it:
INPUT:
SQL> /
OUTPUT:
UNIT
CUSTOMER PRODUCT_NAME COST
------------------------------ ------------------------------ --------
JONES and SONS MICKEY MOUSE LAMP $029.95
NO 2 PENCILS - 20 PACK $001.99
COFFEE MUG $006.95
****************************** --------
sum $038.89
PARAKEET CONSULTING GROUP MICKEY MOUSE LAMP $029.95
NO 2 PENCILS - 20 PACK $001.99
SQL COMMAND REFERENCE $029.99
BLACK LEATHER BRIEFCASE $099.99
FAR SIDE CALENDAR $010.50
****************************** --------
sum $172.42
PLEWSKY MOBILE CARWASH MICKEY MOUSE LAMP $029.95
BLACK LEATHER BRIEFCASE $099.99
BLACK LEATHER BRIEFCASE $099.99
NO 2 PENCILS - 20 PACK $001.99
NO 2 PENCILS - 20 PACK $001.99
****************************** --------
UNIT
CUSTOMER PRODUCT_NAME COST
----------------------------- ------------------------------ --------
sum $233.91
--------
sum $445.22
13 rows selected.
ANALYSIS:
This example computed the total amount that each customer spent and also calculated
a grand total for all customers.
By now you should understand the basics of formatting columns, grouping data on
the report, and performing computations on each group.
Using Variables in SQL*Plus
Without actually getting into a procedural language, you can still define variables
in your SQL statement. You can use special options in SQL*Plus (covered in this section)
to accept input from the user to pass parameters into your SQL program.
Substitution Variables (&)
An ampersand (&) is the character that calls a value for a variable
within an SQL script. If the variable has not previously been defined, the user will
be prompted to enter a value.
INPUT:
SQL> select *
2 from &TBL
3 /
Enter value for tbl: products
The user entered the value "products."
OUTPUT:
old 2: from &TBL
new 2: from products
PRO PRODUCT_NAME UNIT_COST
--- ------------------------------ ---------
P01 MICKEY MOUSE LAMP 29.95
P02 NO 2 PENCILS - 20 PACK 1.99
P03 COFFEE MUG 6.95
P04 FAR SIDE CALENDAR 10.5
P05 NATURE CALENDAR 12.99
P06 SQL COMMAND REFERENCE 29.99
P07 BLACK LEATHER BRIEFCASE 99.99
7 rows selected.
ANALYSIS:
The value products was substituted in the place of &TBL
in this "interactive query."
DEFINE
You can use DEFINE to assign values to variables within an SQL script
file. If you define your variables within the script, users are not prompted to enter
a value for the variable at runtime, as they are if you use the &. The
next example issues the same SELECT statement as the preceding example,
but this time the value of TBL is defined within the script.
INPUT:
SQL> define TBL=products
SQL> select *
2 from &TBL;
OUTPUT:
old 2: from &TBL
new 2: from products
PRO PRODUCT_NAME UNIT_COST
--- ------------------------------ ---------
P01 MICKEY MOUSE LAMP 29.95
P02 NO 2 PENCILS - 20 PACK 1.99
P03 COFFEE MUG 6.95
P04 FAR SIDE CALENDAR 10.5
P05 NATURE CALENDAR 12.99
P06 SQL COMMAND REFERENCE 29.99
P07 BLACK LEATHER BRIEFCASE 99.99
7 rows selected.
ANALYSIS:
Both queries achieved the same result. The next section describes another way
to prompt users for script parameters.
ACCEPT
ACCEPT enables the user to enter a value to fill a variable at script
runtime. ACCEPT does the same thing as the & with no DEFINE
but is a little more controlled. ACCEPT also allows you to issue user-friendly
prompts.
The next example starts by clearing the buffer:
INPUT:
SQL> clear buffer
OUTPUT:
buffer cleared
Then it uses an INPUT command to enter the new SQL statement into the
buffer. If you started to type your statement without issuing the INPUT
command first, you would be prompted to enter the value for newtitle first.
Alternatively, you could go straight into a new file and write your statement.
INPUT:
SQL> input
1 accept newtitle prompt 'Enter Title for Report: '
2 ttitle center newtitle
3 select *
4 from products
5
SQL> save prod
OUTPUT:
File "prod.sql" already exists.
Use another name or "SAVE filename REPLACE".
ANALYSIS:
Whoops...the file prod.sql already exists. Let's say that you need the
old prod.sql and do not care to overwrite it. You will have to use the replace
option to save the statement in the buffer to prod.sql. Notice the use of
PROMPT in the preceding statement. PROMPT displays text to the
screen that tells the user exactly what to enter.
INPUT:
SQL> save prod replace
OUTPUT:
Wrote file prod
Now you can use the START command to execute the file.
INPUT:
SQL> start prod
Enter Title for Report: A LIST OF PRODUCTS
OUTPUT:
A LIST OF PRODUCTS
PRO PRODUCT_NAME UNIT_COST
--- ------------------------------ ---------
P01 MICKEY MOUSE LAMP 29.95
P02 NO 2 PENCILS - 20 PACK 1.99
P03 COFFEE MUG 6.95
P04 FAR SIDE CALENDAR 10.5
P05 NATURE CALENDAR 12.99
P06 SQL COMMAND REFERENCE 29.99
P07 BLACK LEATHER BRIEFCASE 99.99
7 rows selected.
ANALYSIS:
The text that you entered becomes the current title of the report.
The next example shows how you can use substitution variables anywhere in a statement:
INPUT:
SQL> input
1 accept prod_id prompt 'Enter PRODUCT ID to Search for: '
2 select *
3 from products
4 where product_id = '&prod_id'
5
SQL> save prod1
OUTPUT:
Created file prod1
INPUT:
SQL> start prod1
Enter PRODUCT ID to Search for: P01
OUTPUT:
old 3: where product_id = '&prod_id'
new 3: where product_id = 'P01'
A LIST OF PRODUCTS
PRO PRODUCT_NAME UNIT_COST
--- ------------------------------ ---------
P01 MICKEY MOUSE LAMP 29.95
ANALYSIS:
You can use variables to meet many needs--for example, to name the file to which
to spool your output or to specify an expression in the ORDER BY clause.
One of the ways to use substitution variables is to enter reporting dates in the
WHERE clause for transactional quality assurance reports. If your query
is designed to retrieve information on one particular individual at a time, you may
want to add a substitution variable to be compared with the SSN column of
a table.
NEW_VALUE
The NEW_VALUE command passes the value of a selected column into an undefined
variable of your choice. The syntax is as follows:
SYNTAX:
COL[UMN] column_name NEW_VALUE new_name
You call the values of variables by using the & character; for example:
&new_name
The COLUMN command must be used with NEW_VALUE.
Notice how the & and COLUMN command are used together in
the next SQL*Plus file. The GET command gets the file.
INPUT:
SQL> get prod1
OUTPUT:
line 5 truncated.
1 ttitle left 'Report for Product: &prod_title' skip 2
2 col product_name new_value prod_title
3 select product_name, unit_cost
4 from products
5* where product_name = 'COFFEE MUG'
INPUT:
SQL> @prod1
OUTPUT:
Report for Product: COFFEE MUG
PRODUCT_NAME UNIT_COST
------------------------------ ----------
COFFEE MUG 6.95
ANALYSIS:
The value for the column PRODUCT_NAME was passed into the variable prod_title
by means of new_value. The value of the variable prod_title was
then called in the TTITLE.
For more information on variables in SQL, see Day 18, "PL/SQL: An Introduction,"
and Day 19.
The DUAL Table
The DUAL table is a dummy table that exists in every Oracle database.
This table is composed of one column called DUMMY whose only row of data
is the value X. The DUAL table is available to all database users
and can be used for general purposes, such as performing arithmetic (where it can
serve as a calculator) or manipulating the format of the SYSDATE.
INPUT:
SQL> desc dual;
OUTPUT:
Name Null? Type
------------------------------- -------- ----
DUMMY VARCHAR2(1)
INPUT:
SQL> select *
2 from dual;
OUTPUT:
D
-
X
Take a look at a couple of examples using the DUAL table:
INPUT:
SQL> select sysdate
2 from dual;
OUTPUT:
SYSDATE
--------
08-MAY-97
INPUT:
SQL> select 2 * 2
2 from dual;
OUTPUT:
2*2
--------
4
Pretty simple. The first statement selected SYSDATE from the DUAL
table and got today's date. The second example shows how to multiply in the DUAL
table. Our answer for 2 * 2 is 4.
The DECODE Function
The DECODE function is one of the most powerful commands in SQL*Plus--and
perhaps the most powerful. The standard language of SQL lacks procedural functions
that are contained in languages such as COBOL and C.
The DECODE statement is similar to an IF...THEN statement in
a procedural programming language. Where flexibility is required for complex reporting
needs, DECODE is often able to fill the gap between SQL and the functions
of a procedural language.
SYNTAX:
DECODE(column1, value1, output1, value2, output2, output3)
The syntax example performs the DECODE function on column1.
If column1 has a value of value1, then display output1
instead of the column's current value. If column1 has a value of value2,
then display output2 instead of the column's current value. If column1
has a value of anything other than value1 or value2, then display
output3 instead of the column's current value.
How about some examples? First, perform a simple select on a new table:
INPUT:
SQL> select * from states;
OUTPUT:
ST
--
IN
FL
KY
IL
OH
CA
NY
7 rows selected.
Now use the DECODE command:
INPUT:
SQL> select decode(state,'IN','INDIANA','OTHER') state
2 from states;
OUTPUT:
STATE
------
INDIANA
OTHER
OTHER
OTHER
OTHER
OTHER
OTHER
7 rows selected.
ANALYSIS:
Only one row met the condition where the value of state was IN, so only
that one row was displayed as INDIANA. The other states took the default
and therefore were displayed as OTHER.
The next example provides output strings for each value in the table. Just in
case your table has states that are not in your DECODE list, you should
still enter a default value of 'OTHER'.
INPUT:
SQL> select decode(state,'IN','INDIANA',
2 'FL','FLORIDA',
3 'KY','KENTUCKY',
4 'IL','ILLINOIS',
5 'OH','OHIO',
6 'CA','CALIFORNIA',
7 'NY','NEW YORK','OTHER')
8 from states;
OUTPUT:
DECODE(STATE)
----------
INDIANA
FLORIDA
KENTUCKY
ILLINOIS
OHIO
CALIFORNIA
NEW YORK
7 rows selected.
That was too easy. The next example introduces the PAY table. This table
shows more of the power that is contained within DECODE.
INPUT:
SQL> col hour_rate hea "HOURLY|RATE" for 99.00
SQL> col date_last_raise hea "LAST|RAISE"
SQL> select name, hour_rate, date_last_raise
2 from pay;
OUTPUT:
HOURLY LAST
NAME RATE RAISE
-------------------- ------ --------
JOHN 12.60 01-JAN-96
JEFF 8.50 17-MAR-97
RON 9.35 01-OCT-96
RYAN 7.00 15-MAY-96
BRYAN 11.00 01-JUN-96
MARY 17.50 01-JAN-96
ELAINE 14.20 01-FEB-97
7 rows selected.
Are you ready? It is time to give every individual in the PAY table a
pay raise. If the year of an individual's last raise is 1996, calculate a 10 percent
raise. If the year of the individual's last raise is 1997, calculate a 20 percent
raise. In addition, display the percent raise for each individual in either situation.
INPUT:
SQL> col new_pay hea 'NEW PAY' for 99.00
SQL> col hour_rate hea 'HOURLY|RATE' for 99.00
SQL> col date_last_raise hea 'LAST|RAISE'
SQL> select name, hour_rate, date_last_raise,
2 decode(substr(date_last_raise,8,2),'96',hour_rate * 1.2,
3 '97',hour_rate * 1.1) new_pay,
4 decode(substr(date_last_raise,8,2),'96','20%',
5 '97','10%',null) increase
6 from pay;
OUTPUT:
HOURLY LAST
NAME RATE RAISE NEW PAY INC
-------------------- ------ --------- ------- ---
JOHN 12.60 01-JAN-96 15.12 20%
JEFF 8.50 17-MAR-97 9.35 10%
RON 9.35 01-OCT-96 11.22 20%
RYAN 7.00 15-MAY-96 8.40 20%
BRYAN 11.00 01-JUN-96 13.20 20%
MARY 17.50 01-JAN-96 21.00 20%
ELAINE 14.20 01-FEB-97 15.62 10%
7 rows selected.
ANALYSIS:
According to the output, everyone will be receiving a 20 percent pay increase
except Jeff and Elaine, who have already received one raise this year.
DATE Conversions
If you want to add a touch of class to the way dates are displayed, then you can
use the TO_CHAR function to change the "date picture." This example
starts by obtaining today's date:
INPUT:
SQL> select sysdate
2 from dual;
OUTPUT:
SYSDATE
--------
08-MAY-97
When converting a date to a character string, you use the TO_CHAR function
with the following syntax:
SYNTAX:
TO_CHAR(sysdate,'date picture')
date picture is how you want the date to look. Some of the most common
parts of the date picture are as follows: Month The current month spelled
out.
| Mon |
The current month abbreviated. |
| Day |
The current day of the week. |
| mm |
The number of the current month. |
| yy |
The last two numbers of the current year. |
| dd |
The current day of the month. |
| yyyy |
The current year. |
| ddd |
The current day of the year since January 1. |
| hh |
The current hour of the day. |
| mi |
The current minute of the hour. |
| ss |
The current seconds of the minute. |
| a.m. |
Displays a.m. or p.m. |
The date picture may also contain commas and literal strings as long as the string
is enclosed by double quotation marks "".
INPUT:
SQL> col today for a20
SQL> select to_char(sysdate,'Mon dd, yyyy') today
2 from dual;
OUTPUT:
TODAY
--------------------
May 08, 1997
ANALYSIS:
Notice how we used the COLUMN command on the alias today.
INPUT:
SQL> col today hea 'TODAYs JULIAN DATE' for a20
SQL> select to_char(sysdate,'ddd') today
2 from dual;
OUTPUT:
TODAYs JULIAN DATE
--------------------
128
ANALYSIS:
Some companies prefer to express the Julian date with the two-digit year preceding
the three-digit day. Your date picture could also look like this: 'yyddd'.
Assume that you wrote a little script and saved it as day. The next example
gets the file, looks at it, and executes it to retrieve various pieces of converted
date information.
INPUT:
SQL> get day
OUTPUT:
line 10 truncated.
1 set echo on
2 col day for a10
3 col today for a25
4 col year for a25
5 col time for a15
6 select to_char(sysdate,'Day') day,
7 to_char(sysdate,'Mon dd, yyyy') today,
8 to_char(sysdate,'Year') year,
9 to_char(sysdate,'hh:mi:ss a.m.') time
10* from dual
Now you can run the script:
INPUT:
SQL> @day
OUTPUT:
SQL> set echo on
SQL> col day for a10
SQL> col today for a25
SQL> col year for a25
SQL> col time for a15
SQL> select to_char(sysdate,'Day') day,
2 to_char(sysdate,'Mon dd, yyyy') today,
3 to_char(sysdate,'Year') year,
4 to_char(sysdate,'hh:mi:ss a.m.') time
5 from dual;
DAY TODAY YEAR TIME
---------- ------------------------ ----------------------- ------------
Thursday May 08, 1997 Nineteen Ninety-Seven 04:10:43 p.m.
ANALYSIS:
In this example the entire statement was shown before it ran because ECHO
was set to ON. In addition, sysdate was broken into four columns and the
date was converted into four formats.
The TO_DATE function enables you to convert text into a date format.
The syntax is basically the same as TO_CHAR.
SYNTAX:
TO_DATE(expression,'date_picture')
Try a couple of examples:
INPUT:
SQL> select to_date('19970501','yyyymmdd') "NEW DATE"
2 from dual;
OUTPUT:
NEW DATE
--------
01-MAY-97
INPUT:
SQL> select to_date('05/01/97','mm"/"dd"/"yy') "NEW DATE"
2 from dual;
OUTPUT:
NEW DATE
--------
01-MAY-97
ANALYSIS:
Notice the use of double quotation marks to represent a literal string.
Running a Series of SQL Files
An SQL script file can include anything that you can type into the SQL buffer
at the SQL> prompt, even commands that execute another SQL script. Yes,
you can start an SQL script from within another SQL script. Figure 20.4 shows a script
file that was created using the EDIT command. The file contains multiple
SQL statements as well as commands to run other SQL scripts.
INPUT:
SQL> edit main.sql
OUTPUT:
SQL> @main
ANALYSIS:
By starting main.sql, you will be executing each SQL command that is
contained within the script. Query1 through query5 will also be
executed, in that order, as shown in Figure 20.4.
Figure 20.4.
Running SQL scripts from within an SQL script.
Adding Comments to Your SQL Script
SQL*Plus gives you three ways to place comments in your file:
- -- places a comment on one line at a time.
- REMARK also places a comment on one line at a time.
- /* */ places a comment(s) on one or more lines.
Study the following example:
INPUT:
SQL> input
1 REMARK this is a comment
2 -- this is a comment too
3 REM
4 -- SET COMMANDS
5 set echo on
6 set feedback on
7 -- SQL STATEMENT
8 select *
9 from products
10
SQL>
To see how comments look in an SQL script file, type the following:
SQL> edit query10
Advanced Reports
Now let's have some fun. By taking the concepts that you have learned today, as
well as what you learned earlier, you can now create some fancy reports. Suppose
that you have a script named report1.sql. Start it, sit back, and observe.
INPUT:
SQL> @report1
OUTPUT:
SQL> set echo on
SQL> set pagesize 50
SQL> set feedback off
SQL> set newpage 0
SQL> col product_name hea 'PRODUCT|NAME' for a20 trunc
SQL> col unit_cost hea 'UNIT|COST' for $99.99
SQL> col product_qty hea 'QTY' for 999
SQL> col total for $99,999.99
SQL> spool report
SQL> compute sum of total on customer
SQL> compute sum of total on report
SQL> break on report on customer skip 1
SQL> select o.customer, p.product_name, p.unit_cost,
2 o.product_qty, (p.unit_cost * o.product_qty) total
3 from orders o,
4 products p
5 where o.product_id = p.product_id
6 order by customer
7 /
CUSTOMER PRODUCT UNIT QTY TOTAL
NAME COST
--------------------------- --------------------- ------ ----- ----------
JONES and SONS MICKEY MOUSE LAMP $29.95 50 $1,497.50
NO 2 PENCILS - 20 PA $1.99 10 $19.90
COFFEE MUG $6.95 10 $69.50
****************************** ----------
sum $1,586.90
PARAKEET CONSULTING GROUP MICKEY MOUSE LAMP $29.95 5 $149.75
NO 2 PENCILS - 20 PA $1.99 15 $29.85
SQL COMMAND REFERENC $29.99 10 $299.90
BLACK LEATHER BRIEFC $99.99 1 $99.99
FAR SIDE CALENDAR $10.50 22 $231.00
****************************** ----------
sum $810.49
PLEWSKY MOBILE CARWASH MICKEY MOUSE LAMP $29.95 1 $29.95
BLACK LEATHER BRIEFC $99.99 5 $499.95
BLACK LEATHER BRIEFC $99.99 1 $99.99
NO 2 PENCILS - 20 PA $1.99 10 $19.90
NO 2 PENCILS - 20 PA $1.99 10 $19.90
****************************** ----------
sum $669.69
----------
sum $3,067.08
SQL> Input truncated to 9 characters
spool off
ANALYSIS:
Several things are taking place in this script. If you look at the actual SQL
statement, you can see that it is selecting a data from two tables and performing
an arithmetic function as well. The statement joins the two tables in the WHERE
clause and is ordered by the customer's name. Those are the basics. In addition,
SQL*Plus commands format the data the way we want to see it. These commands break
the report into groups, making computations on each group and making a computation
on the report as a whole.
Summary
Day 20 explains Oracle's extension to the standard language of SQL. These commands
are only a fraction of what is available to you in SQL*Plus. If you use Oracle's
products, check your database documentation, take the knowledge that you have learned
here, and explore the endless possibilities that lie before you. You will find that
you can accomplish almost any reporting task using SQL*Plus rather than by resorting
to a procedural programming language. If you are not using Oracle products, use what
you have learned today to improve the ways you retrieve data in your implementation.
Most major implementations have extensions, or enhancements, to the accepted standard
language of SQL.
Q&A
- Q Why should I spend valuable time learning SQL*Plus when I can achieve the
same results using straight SQL?
A If your requirements for reports are simple, straight SQL is fine. But
you can reduce the time you spend on reports by using SQL*Plus. And you can be sure
that the person who needs your reports will always want more information.
Q How can I select SYSDATE from the DUAL table if it is not
a column?
A You can select SYSDATE from DUAL or any other valid
table because SYSDATE is a pseudocolumn.
Q When using the DECODE command, can I use a DECODE within
another DECODE?
A Yes, you can DECODE within a DECODE. In SQL you can
perform functions on other functions to achieve the desired results.
Workshop
The Workshop provides quiz questions to help solidify your understanding of the
material covered, as well as exercises to provide you with experience in using what
you have learned. Try to answer the quiz and exercise questions before checking the
answers in Appendix F, "Answers to Quizzes and Exercises."
Quiz
- 1. Which commands can modify your preferences for an SQL session?
2. Can your SQL script prompt a user for a parameter and execute the SQL
statement using the entered parameter?
3. If you are creating a summarized report on entries in a CUSTOMER
table, how would you group your data for your report?
4. Are there limitations to what you can have in your LOGIN.SQL
file?
5. True or False: The DECODE function is the equivalent of a loop
in a procedural programming language.
6. True or False: If you spool the output of your query to an existing
file, your output will be appended to that file.
Exercises
- 1. Using the PRODUCTS table at the beginning of Day 20, write
a query that will select all data and compute a count of the records returned on
the report without using the SET FEEDBACK ON command.
2. Suppose today is Monday, May 12, 1998. Write a query that will produce
the following output:
Today is Monday, May 12 1998
- 3. Use the following SQL statement for this Exercise:
1 select *
2 from orders
3 where customer_id = '001'
4* order by customer_id;
- Without retyping the statement in the SQL buffer, change the table in the FROM
clause to the CUSTOMER table.
Now append DESC to the ORDER BY clause.
© Copyright, Macmillan Computer Publishing. All
rights reserved.
| 
