Project 2: Developing a SQL*Plus Report Script
Objectives
After completing this project you should be able to
· create a .sql script file to select data from the database
· format the query ouput using SQL*Plus commands
· define an external editor (emacs, pico or vi) to use with SQL*Plus
Reference Material
The first part of this assignment is to repeat the examples in Chapter 5. The report is similar to the example shown in Chapter 6 of your text. While very similar, this exercise is different in subtle details. Exact copies of the script in chapter 6 will not be graded.
Due for Project 2
Turn in the SQL file saved after the step of this exercise, the output produced by this script, and Project02Ch05.txt.
Project Assignment Steps
1. If you are executing your assignment on firebird, set the editor for your SQL*Plus session by using one of the following:
define _editor = vi
or
define _editor = pico
or
define _editor = emacs
This will define the SQL*Plus system variable (_editor) the name of your favorite editor for your system—vi, pico, or emacs in the examples above. SQL*Plus will then use this editor every time you need to modify your SQL statements. Just type the keyword edit from the SQL*Plus command line to invoke this editor you just defined.
2.Open a spool file for Project02Ch5.txt. Go through Chapter 5 of the text and manually enter all SQL that appears in the chapter. Start with
SeLeCt feaTURE, section, PAGE FROM newsPaPer;
through
create or replace view RESTRICTED_NAMES as
create or replace view RESTRICTED_NAMES as
select * from NAME
where NAME = User;
Spool off;
It’s OK if you make errors during the spool. Just correct the error and continue. Do not start over every time you make an error.
3. The remainder of this project will be to develop a SQL*Plus report based upon a SQL query which you will write.
We start writing our query by looking at the table which you will work with--the ledger table. Describe the ledger table with the following command:
DESCRIBE LEDGER
or
DESC LEDGER
4. Now select all columns and all rows from the ledger table.
SELECT *
FROM LEDGER
/
5. Modify this query to only select the columns actiondate, person, item.
SELECT ACTIONDATE, PERSON, ITEM
FROM LEDGER
/
6. Restrict the query to only show rows for sales. Add a where clause similar to the one below:
WHERE ACTION = ’SOLD’
7. Add another where clause condition to only show items from the ledger between the actiondates July 1, 1901 and December 31, 1901.
SQL> ALTER SESSION SET NLS_DATE_FORMAT='DD-MON-YYYY';
Session altered.
8. Order the query output by Item and ActionDate.
9. In the select clause, also include the columns Quantity, QuantityType, Rate, and Quantity * Rate. Alias the Quantity * Rate calculation to the new name “Extended”.
10.Now save your query to a file. You can use any filename that makes sense to you. The syntax is:
SAVE filename [REPLACE]
Replace is optional. The filename uses .sql as a default extension.
11. Now edit your query file and add SQL*Plus heading format commands:
Top Title: Sales Report, Q3-Q4 1901
Bottom Title[1][1]: from yourname's Ledger
SQL*Plus commands like heading commands, column commands, etc. must appear before your SQL statement in your script. They can generally appear in any order, they just must be executed before your SQL does.
12. Add column formatting commands as per this table:
Item / Description / a18, truncated
ActionDate / Date / -
Person / Sold To / a18, word_wrapped
Quantity / Qty / 9990
QuantityType / Type / a8, truncated
Rate / Rate / 90.99
Extended[2][2] / Ext / 990.99
Re-run your report to see the progress you’ve made so far.
13. Return to editing your SQL*Plus report. Add the following commands, then re-run your report to review progress.
set linesize 79
set pagesize 50
set newpage 0
14. Now we will add subtotals. Add these lines:
break on item skip 2
compute sum of Extended on Item
The effect on your report output is amazing, run the report now and see!
15. Before your query, you will want to turn spooling on to save the report out to a file. Type this SQL*Plus just before your SQL statement:
spool yourfile.lst
Then just after your SQL statement, turn spooling off with this command:
spool off
16. Now run your report. Exit SQL*Plus. Upload your SQL file, your spooled report file, and Project02Ch05.txt to an Oncourse Drop Box by its due date. You’re done!
[1][1] The italicized yourname means your actual name. Everyone’s report in this class will all have a different bottom title! If you use “yourname” or “Talbot’s” (as appears in the book), well its counted wrong.
[2][2] This is subtle, so be careful. The computed column in the SQL query is aliased to EXTENDED. It is to appear on the report with the heading “Ext”.