iSelfSchooling.com  Since 1999     References  |  Search more  | Oracle Syntax  | Free Online Oracle Problem Solver

    .Home      .Subscribe     .Login      .Start Learning      .Certification      .Cancel      .Password Reminder      .Password Change     .Sign out

 

. Online Accounting        .Copyright & User Agreement   |
    .Vision      .Biography     .Acknowledgement

.Contact Us      .Comments/Suggestions       .Email2aFriend    |

 

Online Oracle Training for beginners and advanced - The most comprehensive Oracle tutorial

The authors do not guarantee or take any responsibility for the accuracy, or completeness of the information.

Basics - SQL 

 

Lesson 01 | Lesson 02 | Lesson 03 | Lesson 04 | Lesson 05 | Lesson 06 | Lesson 07 | Lesson 08 | Lesson 09 | Lesson 10 | Lesson 11 | Lesson 12 | Lesson 13 | Lesson 14 | Lesson 15 | Lesson 16 | Lesson 17 | Lesson 18 |

 

Lesson 11

"In the End, we will remember not the words of our enemies, but the silence of our friends." - Martin Luther King Jr. (1929-1968)

 

Read first then play the video:

   SQL-VIDEO -The BREAK ON clause
 
 

The BREAK ON clause

Introduction

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:

----------------Salary-----------------

Department Employee Salary

-------------------- -------------------- -----------

ACCOUNTING KING       $5,250.00

******************** -----------

sum $5,250.00

 

SALES ALLEN           $3,758.57

BLAKE                 $3,464.20

******************** -----------

sum                   $7,222.77

 

------------ confidential -------------

 

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 iself user.
SQL> CONNECT iself/schooling


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


REPHEADER and REPFOOTER commands

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


COLUMN … HEADING … FORMAT …command

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


BREAK and COMPUTE command

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


SPOOL command

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.


CLEAR command

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

 

"Whether you think that you can, or that you can't, you are usually right." - Henry Ford (1863-1947)

 

Questions:

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

REPHEADER ''

BREAK ON dname SKIP 1

COMPUTE SUM OF sal ON dname

SPOOL c:.out

SPOOL OFF

REPFOOTER ''

CLEAR BUFFER

CLEAR COLUMNS

CLEAR COMPUTE

Q: What does the CLEAR command in SQLPLUS?