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 iself user.
--
pause

CONNECT iself/schooling
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 excercise 
-- 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