|
Your organization now
wants to get a simple report from the following
report layout. Unfortunately, they don�t have the
�REPORTS builder� tool and you have to use sql*plus
in order to fulfill their user requirements.
The client asked you
to query all employee names and their departments
where their salaries are greater than $3,150.00,
sorted by department.
-- Hands-On 02(The
break on clause)
SET ECHO ON
CLEAR SCR
-- Connect to SQLPLUS as the oracle user.
--
pause
CONNECT oracle/learning
pause
CLEAR SCR
-- Set the pagesize to 55 and the linesize to 80.
--
pause
SET PAGESIZE 55
SET LINESIZE 80
pause
CLEAR SCR
-- Make a report header and footer the way it was
-- stated in the case study.
--
pause
REPHEADER '----------------Salary-----------------'
REPFOOTER '------------ confidential -------------'
pause
CLEAR SCR
-- Make your column heading and format as stated in
the
-- case study.
--
pause
COLUMN dname HEADING 'Department' FORMAT a20
COLUMN sal HEADING 'Salary' FORMAT $99,999.99
COLUMN ename HEADING 'Employee' FORMAT a20
pause
CLEAR SCR
-- Build a break on the department number and
-- compute a sub-total for each department.
-- Skip one line for each control break.
pause
BREAK ON dname SKIP 1
COMPUTE SUM OF sal ON dname
pause
CLEAR SCR
-- Spool it on a file called salary.out in your c:\
drive.
pause
SPOOL c:\salary.out
pause
CLEAR SCR
-- Query your report based on your case study
description.
--
pause
SELECT dname, ename, sal
FROM emp e, dept d
WHERE e.deptno = d.deptno
AND sal > 3150
ORDER BY dname
/
pause
CLEAR SCR
-- Spool off, of the output.
pause
SPOOL OFF
-- You can use the text editor to open your spool
file.
pause
CLEAR SCR
-- Clear the buffer, repheader, repfooter, and
-- compute all of the columns from your session.
--
pause
REPHEADER ''
REPFOOTER ''
CLEAR BUFFER
CLEAR COLUMNS
CLEAR COMPUTE
pause
CLEAR SCR
-- Now, you should practice this Hands-On exercise
-- over and over until you become a master at it.
-- For more information about the subject, you are
encouraged
-- to read from a wide selection of available books.
-- Good luck!
--
pause |
