SQL Chapter 7
Reports
Objectives
Use concatenation in a query
Create a view for a report
Create a query for a report
Change column headings and formats
in a report
Add a title to a report
Group data in a report
Include totals and subtotals in a
report
Send a report to a file that can
be printed
Concatenating Columns
Concatenation
l The process of combining two or more character columns into a single expression
To concatenate columns, type two
vertical lines (||) between the column names
Concatenating Columns
Problem:
l List the number and name of each sales rep. The name should be a concatenation of the FIRST_NAME and LAST_NAME columns.
Solution:
l
SELECT REP_NUM,
FIRST_NAME||LAST_NAME
FROM REP;
When the first name does not
include sufficient characters to fill the width of the column, SQL inserts
extra spaces
To remove these extra spaces, the
RTRIM (right trim) function can be used
Creating and Using Scripts
When entering report formatting
commands, it is a good idea to save the commands in script files for future use
Otherwise, the commands must be
reentered every time you want to produce the same report
In SQL*Plus, an editor can be used
to create script files by typing:
l The EDIT command
l The name of the file to be created
Oracle assigns the file the extension
.sql
To run the command(s) in the file
from SQL*Plus, type @ followed by the name of the file
In SQL*Plus Worksheet, a script
file can be created by:
l Typing the command(s) in the upper pane of the SQL*Plus Worksheet window
l Selecting the Save Input As command
l Specifying the name and location for the file
Oracle assigns the file the
extension .sql
In SQL*Plus Worksheet, to run the
command(s) in a file:
l Select the Open command
l Select the file
l Click the Open button
l Click the Execute button
Running the Query for the
Report
The data for a report can come
from either a table or a view
Using a view is preferable to
using a table, particularly if the report involves data from more than one
table
Running the Query for the
Report
Problem:
l Create a script file named SLSR_REPORT.sql that defines a view named SLSR_REPORT with five columns for the report
l Name first column SLSR: concatenation of the sales rep number, first name, and last name for each sales rep
l Insert a hyphen between sales rep number and name, separate first and last names with a single space, and trim the values
l Name second column CUST: concatenation of the customer number and the customer name
l Insert a hyphen between the customer number and name and trim the name
l Name the third column BAL: contains the balance
l Name the fourth column CRED: credit limit
Name the fifth column AVAIL:
available credit (CREDIT_LIMIT - BALANCE) for each customer
Run the script file to create the
view
Creating the Data for the
Report
To produce a report, a SELECT
command must be run to create the data to be used in the report
Problem:
l List all data in the SLSR_REPORT view
l Order rows by the SLSR and CUST columns
Solution:
SELECT *
FROM SLSR_REPORT
ORDER BY SLSR, CUST;
Changing Column Headings
To change a column heading:
l Type the COLUMN command followed by the name of the column heading to change
l Use the HEADING clause to assign a new heading
To display the heading on two
lines, separate the two portions of the heading with a single vertical line (|)
Changing Column Headings
Problem:
l Change the column headings in the report so they are more descriptive of the columns’ contents
Solution:
CLEAR COLUMNS
COLUMN SLSR HEADING 'Sales Rep|Number/Name‘
COLUMN CUST HEADING 'Customer|Number/Name‘
COLUMN BAL HEADING 'Current|Balance‘
COLUMN CRED HEADING 'Credit|Limit‘
COLUMN AVAIL HEADING 'Available|Credit‘
Changing Column Headings
CLEAR COLUMNS: clears any previous
column changes made to column headings or formats in the current work session
The COLUMN commands change the
column headings
The slash (/ ) on the last line
reruns the last query and displays the data with the new column headings
Changing Column Formats in
a Report
The COLUMN command is used to:
l Change column headings
l Change the width of a column
l Change the way entries appear in a column
Changing Column Formats in
a Report
Problem:
l Change the format of the columns to allow the SLSR and CUST columns to display 20 and 30 characters, respectively
l Display the data in the other columns with dollar signs and two decimal places
Solution:
CLEAR COLUMNS
COLUMN SLSR HEADING 'Sales Rep|Number/Name' FORMAT
A20
COLUMN CUST HEADING 'Customer|Number/Name' FORMAT A30
COLUMN BAL HEADING 'Current|Balance' FORMAT
$99,990.99
COLUMN CRED HEADING 'Credit|Limit' FORMAT $99,990.99
COLUMN AVAIL HEADING 'Available|Credit' FORMAT
$99,990.99
/
Adding a Title to a Report
Problem:
l Add a title that extends over two lines to the report
l The first line is “Customer Financial Report”
l The second line is “Organized by Sales Rep”
Solution:
SET LINESIZE 90
SET PAGESIZE 50
TTITLE 'Customer Financial Report|Organized by Sales Rep‘
/
Adding a Title to a Report
TTITLE: adds a title to the top of
the report
BTITLE: adds a title at the bottom
of the report
Enclose the title in single
quotation marks in the TTITLE and BTITLE commands
To display the title on two lines,
separate the lines with a vertical line
Line size is the maximum number of
characters each line can contain
SET LINESIZE: adjusts line size
Page size: maximum number of lines
per page
SET PAGESIZE: sets page size
Grouping Data in a Report
BREAK: identifies a column or
collection of columns on which to group data
Problem:
l Group rows in the report by SLSR column
l Remove message at the end of the report indicating number of rows selected
Grouping Data in a Report
Solution:
BREAK ON REPORT ON SLSR SKIP 1
SET FEEDBACK OFF
/
The 1 in the SKIP clause at the
end of the BREAK command inserts one blank line between groups
SET FEEDBACK OFF: turns off the
message indicating the number of rows selected by the query
Including Total and
Subtotals in a Report
Subtotal: A total that appears
after each group
To calculate a subtotal, use BREAK
to group the rows
Use COMPUTE to indicate the
computation for the subtotal
Including
Total and Subtotals in a Report
Problem:
l Include totals and subtotals in the report for the BAL and AVAIL columns
Solution:
COMPUTE SUM OF BAL ON SLSR
COMPUTE SUM OF AVAIL ON SLSR
COMPUTE SUM OF BAL ON REPORT
COMPUTE SUM OF AVAIL ON
REPORT
/
In the COMPUTE command:
l The OF clause includes the desired computations and the column names on which the computations are to occur
l The ON clause indicates the point at which the computation is to occur
Sending the Report to a
File
The exact manner in which a report
is printed depends on the DBMS
To print a report using Oracle:
l The output of the query is sent to a file by using the SPOOL command
l The contents of the file are printed
Problem:
l Send the report created in the previous examples to a file named SLSR_REPORT_OUTPUT.SQL
Solution:
SPOOL SLSR_REPORT_OUTPUT.SQL
/
SPOOL OFF
SPOOL SLSR_REPORT_OUTPUT.SQL sends
the output of subsequent commands to a file named SLSR_REPORT_OUTPUT.SQL
The SPOOL OFF command turns off
spooling and stops any further output from being sent to the
SLSR_REPORT_OUTPUT.SQL file
Completing the Script to
Produce the Report
CLEAR COLUMNS: clears any previous
column definitions
CLEAR COMPUTE: clears any
previously specified computations
CLEAR BREAK: clears any previous
breaks
TTITLE OFF: turns off any
previously specified title at the top of the report
Summary
To concatenate values in character
columns, separate the column names with two vertical lines
You can create script files to
create views and format reports
The data for a report can come
from a table or a view
Use COLUMN to change a column
heading
Use the HEADING clause to assign a
new heading name
Use COLUMN with a FORMAT clause to
change the format of column values
Use the TTITLE or BTITLE command
to add a title at the top or bottom of a report
Use BREAK and COMPUTE and an
appropriate statistical function to calculate data in a report
Use SPOOL to send a report to a
file for printing or editing
SQL Project Seven Completed
Good Luck
H. Zamanzadeh