Tt
Click this widget to change the font size.
CC
Click this widget to change contrast.

Home Page CH 1 | CH 2 | CH 3 | CH 4 | CH 5 | CH 6 | CH 7 | CH 8 | CH 9 | CH 10 | CH 11 | CH 12 | CH 13 | Links | Search | Bio |

Guide to Oracle SQL (University at Buffalo Version)

Formatting Result Sets

Using SELECT statements can produce output that is needed by other people. Oracle SQL provides a robust commands that allow you to control the number of lines, the width of the page, column headings, column widths, and report title, just to name a few.

CommandDescription
BREAK ON columnDisplay data one time and then blanks in place of duplicate values.
COLUMN column CLEARClears a previously defined column format or heading.
COLUMN column FORMAT formatFormat the data based on the format parameter.
COLUMN column HEADING "text"Similar to using a column alias.
SET PAGESIZE numberNumber of lines before a page break.
SET LINESIZE numberWidth of a report line.
SET =Changes the underline from a hyphen (-) to an equal sign (=).
TTITLE "Report Title"Places a title before the report output.

Figure 11-1: SQL Report Formatting Commands

The COLUMN FORMAT format Command

The COLUMN FORMAT format command allows you to define a format a similar to TO_CHAR() and define the width of that column.

-- Before formatting
SELECT jimid, lastname, firstname FROM jimtable;

     JIMID LASTNAME                  FIRSTNAME
---------- ------------------------- -------------------------
         1 Gerland                   James
         2 Phailure                  Phil
         3 Smart                     Sally

-- After formatting
COLUMN jimid FORMAT 9
COLUMN lastname FORMAT A15
COLUMN firstname FORMAT A15
SELECT jimid, lastname, firstname FROM jimtable;

JIMID LASTNAME        FIRSTNAME
----- --------------- ---------------
    1 Gerland         James
    2 Phailure        Phil
    3 Smart           Sally
Figure 11-2: Using COLUMN FORMAT To Format Fields
-- Before formatting
SELECT jimid, lastname, firstname FROM jimtable;

     JIMID LASTNAME                  FIRSTNAME
---------- ------------------------- -------------------------
         1 Gerland                   James
         2 Phailure                  Phil
         3 Smart                     Sally

-- After formatting
COLUMN jimid FORMAT 9
COLUMN jimid HEADING "ID"
COLUMN lastname FORMAT A15
COLUMN lastname HEADING "Last Name"
COLUMN firstname FORMAT A15
COLUMN firstname HEADING "First Name"
SELECT jimid, lastname, firstname FROM jimtable;

ID Last Name       First Name
-- --------------- ---------------
 1 Gerland         James
 2 Phailure        Phil
 3 Smart           Sally
Figure 11-3: Using COLUMN HEADING To Format Column Headings

The TTITLE "text" Command

The TTITLE text command (yes, there are two (2) "T"s in TTITLE), allows you to define a page title for your report.

-- Before formatting
SELECT jimid, lastname, firstname FROM jimtable;

     JIMID LASTNAME                  FIRSTNAME
---------- ------------------------- -------------------------
         1 Gerland                   James
         2 Phailure                  Phil
         3 Smart                     Sally

-- After formatting
COLUMN jimid FORMAT 9
COLUMN jimid HEADING "ID"
COLUMN lastname FORMAT A15
COLUMN lastname HEADING "Last Name"
COLUMN firstname FORMAT A15
COLUMN firstname HEADING "First Name"
TTITLE "Employees In The jimTable"
SELECT jimid, lastname, firstname FROM jimtable;

Sat Jan 30                                                             page    1
                           Employees In The jimTable

ID Last Name       First Name
-- --------------- ---------------
 1 Gerland         James
 2 Phailure        Phil
 3 Smart           Sally
Figure 11-4: Using TTITLE "heading" To Format Fields

The BREAK ON field Command

The BREAK ON field command provides a way to list only the first occurance of that field and group the records on the field.

-- Before BREAK ON
SELECT SELECT department_name, last_name, first_name 
FROM departments d 
INNER JOIN employees e on d.department_id = e.department_id
ORDER BY department_name;

DEPARTMENT_NAME                LAST_NAME                 FIRST_NAME          
------------------------------ ------------------------- --------------------
Accounting                     Hall                      Darryl              
Accounting                     Felix                     Allison             
Executive                      Taylor                    James               
Executive                      Scott                     Steve               
Marketing                      Jones                     LoLo                
Purchasing                     Hall                      Sarah               
Purchasing                     Wertz                     Charlie             
Purchasing                     Taylor                    Livingston          
Purchasing                     Oates                     John                
Shipping                       Lindgren                  Gerry               

10 rows selected.

-- After BREAK ON
BREAK ON department_name
SELECT SELECT department_name, last_name, first_name 
FROM departments d 
INNER JOIN employees e on d.department_id = e.department_id
ORDER BY department_name;
                                            
DEPARTMENT_NAME                LAST_NAME                 FIRST_NAME          
------------------------------ ------------------------- --------------------
Accounting                     Hall                      Darryl              
                               Felix                     Allison             
Executive                      Taylor                    James               
                               Scott                     Steve               
Marketing                      Jones                     LoLo                
Purchasing                     Hall                      Sarah               
                               Wertz                     Charlie             
                               Taylor                    Livingston          
                               Oates                     John                
Shipping                       Lindgren                  Gerry               

10 rows selected.
Figure 11-5: Using BREAK ON field To Group Records

Format an Employees Report

Requirements

  1. Open SQL Developer
  2. Connect to your database instance
  3. Open a new SQL editor window
  4. Enter code to create a new query, "employees_report".
  5. Copy your "employees_join.sql" into this new query.
  6. Add appropriate formatting (COLUMN HEADING, COLUMN FORMAT, TTITLE, BREAK (department).
  7. Save your "employees_report" query.

When you have this Assignment ready for me to view and grade, you should click on this assignment and then choose "Add a File" and upload your employees_report.sql file, so I can download it and grade your work.

Help contribute to my OER Resources. Donate with PayPal button via my PayPal account.
Creative Commons License This work is licensed under a Creative Commons Attribution-NonCommercial-ShareAlike 4.0 International License. Copyright © 2016-2024 Jim Gerland