SQL

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.

The report layout printout:

Your assignments are:

1. To use TTITLE, COLUMN, COMPUTE and BREAK commands,

2. To query department name, employee names and their salaries, and

3. To have subtotal for each department.

Topics:

REPHEADER

REPFOOTER

COLUMN <col> HEADING <hdr> FORMAT a20

BREAK ON <col> SKIP n

COMPUTE SUM OF <col> ON <col>

SPOOL

HOST

CLEAR BUFFER

CLEAR COLUMNS

CLEAR COMPUTE

Connect to SQLPLUS as the oracle user.
SQL> CONNECT oracle/learning
 

Set the pagesize to 55 and the linesize to 80.
SQL> SET PAGESIZE 55
SQL> SET LINESIZE 80
 

Make a report header and footer the way it was stated in the case study.
SQL> REPHEADER '----------------Salary-----------------'
SQL> REPFOOTER '------------ confidential -------------'
 

Make your column heading and format as stated in the case study.
SQL> COLUMN dname HEADING 'Department' FORMAT a20
SQL> COLUMN sal HEADING 'Salary' FORMAT $99,999.99
SQL> COLUMN ename HEADING 'Employee' FORMAT a20
 

Build a break on the department number and compute a sub-total for each department.
Skip one line for each control break.
SQL> BREAK ON dname SKIP 1
SQL> COMPUTE SUM OF sal ON dname
 

Use the SPOOL command to populate a text file with any thing that you type in the SQLPLUS prompt or you query. You have to SPOOL off in order to be able to open the spool file. If you don�t spool off, you will see a zero byte written in the file and you will not be able to see any output data.

Spool it on a file called salary.out in your c: drive.
SQL> SPOOL c:.out
 

Query your report based on your case study description.
SQL> SELECT dname, ename, sal
FROM emp e, dept d
WHERE e.deptno = d.deptno AND sal > 3150
ORDER BY dname
SQL> /
 

Spool off, of the output.
SQL> SPOOL OFF
You can use the text editor to open your spool file.
 

Note that all the values in REPHEADER, REPFOOTER, BUFFER, COLUMNS, COMPUTE and etc are going to stay the same during your open session. In order to clean them, you should use the CLEAR command for BUFFER, COLUMNS, and COMPUTE. And input NULL to REPHEADER and REPFOOTER.

Clear the buffer, repheader, repfooter, and compute all of the columns from your session.
SQL> REPHEADER ''
SQL> REPFOOTER ''
SQL> CLEAR BUFFER
SQL> CLEAR COLUMNS
SQL> CLEAR COMPUTE
 

Q: What does the BREAK ON clause in SQLPLUS?

Q: What do the REPHEADER and REPFOOTER commands in SQLPLUS?

Q: What does the following commands?

COLUMN sal HEADING 'Salary' FORMAT $99,999.99

COLUMN ename HEADING 'Employee' FORMAT a20

BREAK ON dname SKIP 1

SPOOL c:.out

Q: What does the CLEAR command in SQLPLUS?