EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE
---- --------------- ---------- ---- ---------
SALARY COMMISSION DEP
---------- ---------- ---
7839 King President 17-NOV-91
5000 500
7566 Jones Manager 7839 02-APR-91
2975 200
7698 Blake Manager 7839 01-MAY-91
2850 300
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE
---- --------------- ---------- ---- ---------
SALARY COMMISSION DEP
---------- ---------- ---
7782 Raimi Manager 7839 09-JUN-91
2450 100
7902 Ford Analyst 7566 03-DEC-91
3000 200
7369 Smith Clerk 7902 17-DEC-90
800 200
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE
---- --------------- ---------- ---- ---------
SALARY COMMISSION DEP
---------- ---------- ---
7499 Michaels Sales 7698 20-FEB-91
1600 300 300
7521 Ward Sales 7698 22-FEB-91
1250 500 300
7654 Martin Sales 7698 28-SEP-91
1250 1400 300
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE
---- --------------- ---------- ---- ---------
SALARY COMMISSION DEP
---------- ---------- ---
7788 Scott Analyst 7566 09-NOV-91
3000 200
7844 Turner Sales 7698 08-SEP-91
1500 0 300
7876 Adams Clerk 7788 23-SEP-91
1100 400
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE
---- --------------- ---------- ---- ---------
SALARY COMMISSION DEP
---------- ---------- ---
7900 James Clerk 7698 03-DEC-91
950 300
7934 Miller Clerk 7782 23-JAN-92
1300 100
14 rows selected.
========
redoing previous SQL statement with /:
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE
---- --------------- ---------- ---- ---------
SALARY COMMISSION DEP
---------- ---------- ---
7839 King President 17-NOV-91
5000 500
7566 Jones Manager 7839 02-APR-91
2975 200
7698 Blake Manager 7839 01-MAY-91
2850 300
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE
---- --------------- ---------- ---- ---------
SALARY COMMISSION DEP
---------- ---------- ---
7782 Raimi Manager 7839 09-JUN-91
2450 100
7902 Ford Analyst 7566 03-DEC-91
3000 200
7369 Smith Clerk 7902 17-DEC-90
800 200
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE
---- --------------- ---------- ---- ---------
SALARY COMMISSION DEP
---------- ---------- ---
7499 Michaels Sales 7698 20-FEB-91
1600 300 300
7521 Ward Sales 7698 22-FEB-91
1250 500 300
7654 Martin Sales 7698 28-SEP-91
1250 1400 300
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE
---- --------------- ---------- ---- ---------
SALARY COMMISSION DEP
---------- ---------- ---
7788 Scott Analyst 7566 09-NOV-91
3000 200
7844 Turner Sales 7698 08-SEP-91
1500 0 300
7876 Adams Clerk 7788 23-SEP-91
1100 400
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE
---- --------------- ---------- ---- ---------
SALARY COMMISSION DEP
---------- ---------- ---
7900 James Clerk 7698 03-DEC-91
950 300
7934 Miller Clerk 7782 23-JAN-92
1300 100
14 rows selected.
========
using default feedback value:
DEP DEPT_NAME DEPT_LOC
--- --------------- ---------------
100 Accounting New York
200 Research Dallas
300 Sales Chicago
400 Operations Boston
500 Management New York
========
result of show feedback:
FEEDBACK ON for 6 or more rows
========
now using a feedback of 3:
DEP DEPT_NAME DEPT_LOC
--- --------------- ---------------
100 Accounting New York
200 Research Dallas
300 Sales Chicago
400 Operations Boston
500 Management New York
5 rows selected.
DEP DEPT_NAME DEPT_LOC
--- --------------- ---------------
200 Research Dallas
========
ugly Cartesian product:
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
King King
King Jones
King Blake
King Raimi
King Ford
King Smith
King Michaels
King Ward
King Martin
King Scott
King Turner
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
King Adams
King James
King Miller
Jones King
Jones Jones
Jones Blake
Jones Raimi
Jones Ford
Jones Smith
Jones Michaels
Jones Ward
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Jones Martin
Jones Scott
Jones Turner
Jones Adams
Jones James
Jones Miller
Blake King
Blake Jones
Blake Blake
Blake Raimi
Blake Ford
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Blake Smith
Blake Michaels
Blake Ward
Blake Martin
Blake Scott
Blake Turner
Blake Adams
Blake James
Blake Miller
Raimi King
Raimi Jones
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Raimi Blake
Raimi Raimi
Raimi Ford
Raimi Smith
Raimi Michaels
Raimi Ward
Raimi Martin
Raimi Scott
Raimi Turner
Raimi Adams
Raimi James
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Raimi Miller
Ford King
Ford Jones
Ford Blake
Ford Raimi
Ford Ford
Ford Smith
Ford Michaels
Ford Ward
Ford Martin
Ford Scott
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Ford Turner
Ford Adams
Ford James
Ford Miller
Smith King
Smith Jones
Smith Blake
Smith Raimi
Smith Ford
Smith Smith
Smith Michaels
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Smith Ward
Smith Martin
Smith Scott
Smith Turner
Smith Adams
Smith James
Smith Miller
Michaels King
Michaels Jones
Michaels Blake
Michaels Raimi
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Michaels Ford
Michaels Smith
Michaels Michaels
Michaels Ward
Michaels Martin
Michaels Scott
Michaels Turner
Michaels Adams
Michaels James
Michaels Miller
Ward King
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Ward Jones
Ward Blake
Ward Raimi
Ward Ford
Ward Smith
Ward Michaels
Ward Ward
Ward Martin
Ward Scott
Ward Turner
Ward Adams
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Ward James
Ward Miller
Martin King
Martin Jones
Martin Blake
Martin Raimi
Martin Ford
Martin Smith
Martin Michaels
Martin Ward
Martin Martin
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Martin Scott
Martin Turner
Martin Adams
Martin James
Martin Miller
Scott King
Scott Jones
Scott Blake
Scott Raimi
Scott Ford
Scott Smith
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Scott Michaels
Scott Ward
Scott Martin
Scott Scott
Scott Turner
Scott Adams
Scott James
Scott Miller
Turner King
Turner Jones
Turner Blake
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Turner Raimi
Turner Ford
Turner Smith
Turner Michaels
Turner Ward
Turner Martin
Turner Scott
Turner Turner
Turner Adams
Turner James
Turner Miller
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Adams King
Adams Jones
Adams Blake
Adams Raimi
Adams Ford
Adams Smith
Adams Michaels
Adams Ward
Adams Martin
Adams Scott
Adams Turner
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Adams Adams
Adams James
Adams Miller
James King
James Jones
James Blake
James Raimi
James Ford
James Smith
James Michaels
James Ward
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
James Martin
James Scott
James Turner
James Adams
James James
James Miller
Miller King
Miller Jones
Miller Blake
Miller Raimi
Miller Ford
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Miller Smith
Miller Michaels
Miller Ward
Miller Martin
Miller Scott
Miller Turner
Miller Adams
Miller James
Miller Miller
196 rows selected.
========
ugly Cartesian product with feedback off:
feedback OFF
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
King King
King Jones
King Blake
King Raimi
King Ford
King Smith
King Michaels
King Ward
King Martin
King Scott
King Turner
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
King Adams
King James
King Miller
Jones King
Jones Jones
Jones Blake
Jones Raimi
Jones Ford
Jones Smith
Jones Michaels
Jones Ward
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Jones Martin
Jones Scott
Jones Turner
Jones Adams
Jones James
Jones Miller
Blake King
Blake Jones
Blake Blake
Blake Raimi
Blake Ford
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Blake Smith
Blake Michaels
Blake Ward
Blake Martin
Blake Scott
Blake Turner
Blake Adams
Blake James
Blake Miller
Raimi King
Raimi Jones
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Raimi Blake
Raimi Raimi
Raimi Ford
Raimi Smith
Raimi Michaels
Raimi Ward
Raimi Martin
Raimi Scott
Raimi Turner
Raimi Adams
Raimi James
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Raimi Miller
Ford King
Ford Jones
Ford Blake
Ford Raimi
Ford Ford
Ford Smith
Ford Michaels
Ford Ward
Ford Martin
Ford Scott
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Ford Turner
Ford Adams
Ford James
Ford Miller
Smith King
Smith Jones
Smith Blake
Smith Raimi
Smith Ford
Smith Smith
Smith Michaels
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Smith Ward
Smith Martin
Smith Scott
Smith Turner
Smith Adams
Smith James
Smith Miller
Michaels King
Michaels Jones
Michaels Blake
Michaels Raimi
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Michaels Ford
Michaels Smith
Michaels Michaels
Michaels Ward
Michaels Martin
Michaels Scott
Michaels Turner
Michaels Adams
Michaels James
Michaels Miller
Ward King
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Ward Jones
Ward Blake
Ward Raimi
Ward Ford
Ward Smith
Ward Michaels
Ward Ward
Ward Martin
Ward Scott
Ward Turner
Ward Adams
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Ward James
Ward Miller
Martin King
Martin Jones
Martin Blake
Martin Raimi
Martin Ford
Martin Smith
Martin Michaels
Martin Ward
Martin Martin
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Martin Scott
Martin Turner
Martin Adams
Martin James
Martin Miller
Scott King
Scott Jones
Scott Blake
Scott Raimi
Scott Ford
Scott Smith
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Scott Michaels
Scott Ward
Scott Martin
Scott Scott
Scott Turner
Scott Adams
Scott James
Scott Miller
Turner King
Turner Jones
Turner Blake
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Turner Raimi
Turner Ford
Turner Smith
Turner Michaels
Turner Ward
Turner Martin
Turner Scott
Turner Turner
Turner Adams
Turner James
Turner Miller
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Adams King
Adams Jones
Adams Blake
Adams Raimi
Adams Ford
Adams Smith
Adams Michaels
Adams Ward
Adams Martin
Adams Scott
Adams Turner
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Adams Adams
Adams James
Adams Miller
James King
James Jones
James Blake
James Raimi
James Ford
James Smith
James Michaels
James Ward
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
James Martin
James Scott
James Turner
James Adams
James James
James Miller
Miller King
Miller Jones
Miller Blake
Miller Raimi
Miller Ford
EMPL_LAST_NAME EMPL_LAST_NAME
--------------- ---------------
Miller Smith
Miller Michaels
Miller Ward
Miller Martin
Miller Scott
Miller Turner
Miller Adams
Miller James
Miller Miller
========
setting feedback back to default
========
showing default pagesize:
pagesize 14
EMPL_LAST_NAME
---------------
King
Jones
Blake
Raimi
Ford
Smith
Michaels
Ward
Martin
Scott
Turner
EMPL_LAST_NAME
---------------
Adams
James
Miller
14 rows selected.
========
redo previous SQL statement with pagesize of 30:
EMPL_LAST_NAME
---------------
King
Jones
Blake
Raimi
Ford
Smith
Michaels
Ward
Martin
Scott
Turner
Adams
James
Miller
14 rows selected.
========
setting pagesize back to default of 14
========
showing default linesize:
linesize 50
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE
---- --------------- ---------- ---- ---------
SALARY COMMISSION DEP
---------- ---------- ---
7839 King President 17-NOV-91
5000 500
7566 Jones Manager 7839 02-APR-91
2975 200
7698 Blake Manager 7839 01-MAY-91
2850 300
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE
---- --------------- ---------- ---- ---------
SALARY COMMISSION DEP
---------- ---------- ---
7782 Raimi Manager 7839 09-JUN-91
2450 100
7902 Ford Analyst 7566 03-DEC-91
3000 200
7369 Smith Clerk 7902 17-DEC-90
800 200
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE
---- --------------- ---------- ---- ---------
SALARY COMMISSION DEP
---------- ---------- ---
7499 Michaels Sales 7698 20-FEB-91
1600 300 300
7521 Ward Sales 7698 22-FEB-91
1250 500 300
7654 Martin Sales 7698 28-SEP-91
1250 1400 300
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE
---- --------------- ---------- ---- ---------
SALARY COMMISSION DEP
---------- ---------- ---
7788 Scott Analyst 7566 09-NOV-91
3000 200
7844 Turner Sales 7698 08-SEP-91
1500 0 300
7876 Adams Clerk 7788 23-SEP-91
1100 400
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE
---- --------------- ---------- ---- ---------
SALARY COMMISSION DEP
---------- ---------- ---
7900 James Clerk 7698 03-DEC-91
950 300
7934 Miller Clerk 7782 23-JAN-92
1300 100
14 rows selected.
========
redoing previous SQL statement with linesize 40:
EMPL EMPL_LAST_NAME JOB_TITLE MGR
---- --------------- ---------- ----
HIREDATE SALARY COMMISSION DEP
--------- ---------- ---------- ---
7839 King President
17-NOV-91 5000 500
7566 Jones Manager 7839
02-APR-91 2975 200
7698 Blake Manager 7839
01-MAY-91 2850 300
EMPL EMPL_LAST_NAME JOB_TITLE MGR
---- --------------- ---------- ----
HIREDATE SALARY COMMISSION DEP
--------- ---------- ---------- ---
7782 Raimi Manager 7839
09-JUN-91 2450 100
7902 Ford Analyst 7566
03-DEC-91 3000 200
7369 Smith Clerk 7902
17-DEC-90 800 200
EMPL EMPL_LAST_NAME JOB_TITLE MGR
---- --------------- ---------- ----
HIREDATE SALARY COMMISSION DEP
--------- ---------- ---------- ---
7499 Michaels Sales 7698
20-FEB-91 1600 300 300
7521 Ward Sales 7698
22-FEB-91 1250 500 300
7654 Martin Sales 7698
28-SEP-91 1250 1400 300
EMPL EMPL_LAST_NAME JOB_TITLE MGR
---- --------------- ---------- ----
HIREDATE SALARY COMMISSION DEP
--------- ---------- ---------- ---
7788 Scott Analyst 7566
09-NOV-91 3000 200
7844 Turner Sales 7698
08-SEP-91 1500 0 300
7876 Adams Clerk 7788
23-SEP-91 1100 400
EMPL EMPL_LAST_NAME JOB_TITLE MGR
---- --------------- ---------- ----
HIREDATE SALARY COMMISSION DEP
--------- ---------- ---------- ---
7900 James Clerk 7698
03-DEC-91 950 300
7934 Miller Clerk 7782
23-JAN-92 1300 100
14 rows selected.
========
back to default linesize of 80
========
showing default newpage:
newpage 1
DEP DEPT_NAME DEPT_LOC
--- --------------- ---------------
100 Accounting New York
200 Research Dallas
300 Sales Chicago
400 Operations Boston
500 Management New York
========
redoing previous SQL statement with a newpage value of 5:
DEP DEPT_NAME DEPT_LOC
--- --------------- ---------------
100 Accounting New York
200 Research Dallas
300 Sales Chicago
400 Operations Boston
500 Management New York
========
resetting newpage back to default of 1
EMPL_LAST_NAME JOB_TITLE SALARY
--------------- ---------- ----------
King President 5000
Jones Manager 2975
Blake Manager 2850
Raimi Manager 2450
Ford Analyst 3000
Smith Clerk 800
Michaels Sales 1600
Ward Sales 1250
Martin Sales 1250
Scott Analyst 3000
Turner Sales 1500
EMPL_LAST_NAME JOB_TITLE SALARY
--------------- ---------- ----------
Adams Clerk 1100
James Clerk 950
Miller Clerk 1300
14 rows selected.
========
redoing previous SQL statement, now affected by col command
...for empl_last_name column:
Empl
Last
Name JOB_TITLE SALARY
--------------- ---------- ----------
King President 5000
Jones Manager 2975
Blake Manager 2850
Raimi Manager 2450
Ford Analyst 3000
Smith Clerk 800
Michaels Sales 1600
Ward Sales 1250
Martin Sales 1250
Empl
Last
Name JOB_TITLE SALARY
--------------- ---------- ----------
Scott Analyst 3000
Turner Sales 1500
Adams Clerk 1100
James Clerk 950
Miller Clerk 1300
14 rows selected.
========
redoing previous SQL statement, now affected by col command
...again for empl_last_name column
Empl
Last Name JOB_TITLE SALARY
--------------- ---------- ----------
King President 5000
Jones Manager 2975
Blake Manager 2850
Raimi Manager 2450
Ford Analyst 3000
Smith Clerk 800
Michaels Sales 1600
Ward Sales 1250
Martin Sales 1250
Scott Analyst 3000
Empl
Last Name JOB_TITLE SALARY
--------------- ---------- ----------
Turner Sales 1500
Adams Clerk 1100
James Clerk 950
Miller Clerk 1300
14 rows selected.
========
redoing previous SQL statement, now affected by col command
...again for empl_last_name column
Empl Last
Name JOB_TITLE SALARY
--------------- ---------- ----------
King President 5000
Jones Manager 2975
Blake Manager 2850
Raimi Manager 2450
Ford Analyst 3000
Smith Clerk 800
Michaels Sales 1600
Ward Sales 1250
Martin Sales 1250
Scott Analyst 3000
Empl Last
Name JOB_TITLE SALARY
--------------- ---------- ----------
Turner Sales 1500
Adams Clerk 1100
James Clerk 950
Miller Clerk 1300
14 rows selected.
========
using col command to change format of empl_last_name column:
Empl Last
Name JOB_TITLE SALARY
---------- ---------- ----------
King President 5000
Jones Manager 2975
Blake Manager 2850
Raimi Manager 2450
Ford Analyst 3000
Smith Clerk 800
Michaels Sales 1600
Ward Sales 1250
Martin Sales 1250
Scott Analyst 3000
Empl Last
Name JOB_TITLE SALARY
---------- ---------- ----------
Turner Sales 1500
Adams Clerk 1100
James Clerk 950
Miller Clerk 1300
14 rows selected.
========
what if format is narrower than your non-unnumeric column content?
Emp
Nam JOB_TITLE SALARY
--- ---------- ----------
Kin President 5000
g
Jon Manager 2975
es
Bla Manager 2850
ke
Rai Manager 2450
Emp
Nam JOB_TITLE SALARY
--- ---------- ----------
mi
For Analyst 3000
d
Smi Clerk 800
th
Mic Sales 1600
hae
Emp
Nam JOB_TITLE SALARY
--- ---------- ----------
ls
War Sales 1250
d
Mar Sales 1250
tin
Sco Analyst 3000
tt
Emp
Nam JOB_TITLE SALARY
--- ---------- ----------
Tur Sales 1500
ner
Ada Clerk 1100
ms
Jam Clerk 950
es
Emp
Nam JOB_TITLE SALARY
--- ---------- ----------
Mil Clerk 1300
ler
14 rows selected.
========
note difference with TRUNCATED added to format:
Emp
Nam JOB_TITLE SALARY
--- ---------- ----------
Kin President 5000
Jon Manager 2975
Bla Manager 2850
Rai Manager 2450
For Analyst 3000
Smi Clerk 800
Mic Sales 1600
War Sales 1250
Mar Sales 1250
Sco Analyst 3000
Emp
Nam JOB_TITLE SALARY
--- ---------- ----------
Tur Sales 1500
Ada Clerk 1100
Jam Clerk 950
Mil Clerk 1300
14 rows selected.
3 rows deleted.
1 row created.
1 row created.
1 row created.
PTG_NUM PTR_NUM PTG_TITLE
---------- ---------- ------------------------------
1 104 Mona Lisa
2 104 Waterlilies
3 106 Yet four more
========
using format a7 for ptg_title:
PTG_NUM PTR_NUM PTG_TIT
---------- ---------- -------
1 104 Mona Li
sa
2 104 Waterli
lies
3 106 Yet fou
r more
========
using WOR (word wrapped) with format:
PTG_NUM PTR_NUM PTG_TIT
---------- ---------- -------
1 104 Mona
Lisa
2 104 Waterli
lies
3 106 Yet
four
more
========
(which is short for WORD_WRAPPED):
PTG_NUM PTR_NUM PTG_TIT
---------- ---------- -------
1 104 Mona
Lisa
2 104 Waterli
lies
3 106 Yet
four
more
========
using col command to give empl_last_name the format a20:
Empl Last
Name JOB_TITLE SALARY
-------------------- ---------- ----------
King President 5000
Jones Manager 2975
Blake Manager 2850
Raimi Manager 2450
Ford Analyst 3000
Smith Clerk 800
Michaels Sales 1600
Ward Sales 1250
Martin Sales 1250
Scott Analyst 3000
Empl Last
Name JOB_TITLE SALARY
-------------------- ---------- ----------
Turner Sales 1500
Adams Clerk 1100
James Clerk 950
Miller Clerk 1300
14 rows selected.
========
using col command to give salary the format 99999:
Empl Last
Name JOB_TITLE SALARY
-------------------- ---------- ------
King President 5000
Jones Manager 2975
Blake Manager 2850
Raimi Manager 2450
Ford Analyst 3000
Smith Clerk 800
Michaels Sales 1600
Ward Sales 1250
Martin Sales 1250
Scott Analyst 3000
Empl Last
Name JOB_TITLE SALARY
-------------------- ---------- ------
Turner Sales 1500
Adams Clerk 1100
James Clerk 950
Miller Clerk 1300
14 rows selected.
========
what if you format a numeric column too narrow?
Empl Last
Name JOB_TITLE SALARY
-------------------- ---------- ------
King President ####
Jones Manager ####
Blake Manager ####
Raimi Manager ####
Ford Analyst ####
Smith Clerk 800
Michaels Sales ####
Ward Sales ####
Martin Sales ####
Scott Analyst ####
Empl Last
Name JOB_TITLE SALARY
-------------------- ---------- ------
Turner Sales ####
Adams Clerk ####
James Clerk 950
Miller Clerk ####
14 rows selected.
========
now let us add a dollar sign
Empl Last
Name JOB_TITLE SALARY
-------------------- ---------- -------
King President $5000
Jones Manager $2975
Blake Manager $2850
Raimi Manager $2450
Ford Analyst $3000
Smith Clerk $800
Michaels Sales $1600
Ward Sales $1250
Martin Sales $1250
Scott Analyst $3000
Empl Last
Name JOB_TITLE SALARY
-------------------- ---------- -------
Turner Sales $1500
Adams Clerk $1100
James Clerk $950
Miller Clerk $1300
14 rows selected.
========
now let us add commas
Empl Last
Name JOB_TITLE SALARY
-------------------- ---------- --------
King President $5,000
Jones Manager $2,975
Blake Manager $2,850
Raimi Manager $2,450
Ford Analyst $3,000
Smith Clerk $800
Michaels Sales $1,600
Ward Sales $1,250
Martin Sales $1,250
Scott Analyst $3,000
Empl Last
Name JOB_TITLE SALARY
-------------------- ---------- --------
Turner Sales $1,500
Adams Clerk $1,100
James Clerk $950
Miller Clerk $1,300
14 rows selected.
========
yes, you can have a decimal point, also
(that is how you specify that ALL in a column
should be displayed to that precision)
Empl Last
Name JOB_TITLE SALARY
-------------------- ---------- -------------
King President $5,000.0000
Jones Manager $2,975.0000
Blake Manager $2,850.0000
Raimi Manager $2,450.0000
Ford Analyst $3,000.0000
Smith Clerk $800.0000
Michaels Sales $1,600.0000
Ward Sales $1,250.0000
Martin Sales $1,250.0000
Scott Analyst $3,000.0000
Empl Last
Name JOB_TITLE SALARY
-------------------- ---------- -------------
Turner Sales $1,500.0000
Adams Clerk $1,100.0000
James Clerk $950.0000
Miller Clerk $1,300.0000
14 rows selected.
========
answering the question: is a displayed numeric value rounded
or truncated?
Part 1: displaying the value 3.7 with default format
(with the help of the special table DUAL):
VAL
----------
3.7
========
Part 2: now displaying this value with the format 99:
VAL
---
4
========
quick demo of how you can get leading 0s if you WANT them
(with format string 099999):
SALARY
-------
005000
002975
002850
002450
003000
000800
001600
001250
001250
003000
001500
SALARY
-------
001100
000950
001300
14 rows selected.
========
a query with results ordered by dept_num:
Empl Last
DEP Name SALARY
--- -------------------- -------
100 Miller 001300
100 Raimi 002450
200 Scott 003000
200 Jones 002975
200 Ford 003000
200 Smith 000800
300 Martin 001250
300 Ward 001250
300 Blake 002850
300 Michaels 001600
Empl Last
DEP Name SALARY
--- -------------------- -------
300 James 000950
300 Turner 001500
400 Adams 001100
500 King 005000
14 rows selected.
========
break gives you a way to suppress repeated
display of a column when
ordered by that column
========
...and the same query, now with a break on dept_num:
Empl Last
DEP Name SALARY
--- -------------------- -------
100 Miller 001300
Raimi 002450
200 Scott 003000
Jones 002975
Ford 003000
Smith 000800
300 Martin 001250
Ward 001250
Blake 002850
Michaels 001600
Empl Last
DEP Name SALARY
--- -------------------- -------
300 James 000950
Turner 001500
400 Adams 001100
500 King 005000
14 rows selected.
========
let us add a skip of 1 line between
different values of dept_num
Empl Last
DEP Name SALARY
--- -------------------- -------
100 Miller 001300
Raimi 002450
200 Scott 003000
Jones 002975
Ford 003000
Smith 000800
300 Martin 001250
Ward 001250
Empl Last
DEP Name SALARY
--- -------------------- -------
300 Blake 002850
Michaels 001600
James 000950
Turner 001500
400 Adams 001100
500 King 005000
14 rows selected.
========
now ordering by dept_num, then mgr
Empl Last
DEP MGR Name SALARY
--- ---- -------------------- -------
100 7782 Miller 001300
7839 Raimi 002450
200 7566 Scott 003000
7566 Ford 003000
7839 Jones 002975
7902 Smith 000800
300 7698 Michaels 001600
7698 James 000950
Empl Last
DEP MGR Name SALARY
--- ---- -------------------- -------
300 7698 Turner 001500
7698 Ward 001250
7698 Martin 001250
7839 Blake 002850
400 7788 Adams 001100
500 King 005000
14 rows selected.
========
demo of saying you want to break on dept_num and mgr
in a SINGLE break command:
Empl Last
DEP MGR Name SALARY
--- ---- -------------------- -------
100 7782 Miller 001300
7839 Raimi 002450
200 7566 Scott 003000
Ford 003000
7839 Jones 002975
7902 Smith 000800
300 7698 Michaels 001600
James 000950
Turner 001500
Ward 001250
Empl Last
DEP MGR Name SALARY
--- ---- -------------------- -------
300 7698 Martin 001250
7839 Blake 002850
400 7788 Adams 001100
500 King 005000
14 rows selected.
========
...and can skip different amounts for the different
columns you are breaking on:
Empl Last
DEP MGR Name SALARY
--- ---- -------------------- -------
100 7782 Miller 001300
7839 Raimi 002450
200 7566 Scott 003000
Ford 003000
7839 Jones 002975
7902 Smith 000800
300 7698 Michaels 001600
James 000950
Empl Last
DEP MGR Name SALARY
--- ---- -------------------- -------
300 7698 Turner 001500
Ward 001250
Martin 001250
7839 Blake 002850
400 7788 Adams 001100
500 King 005000
14 rows selected.
========
SHOULD use break WITH a query using ORDER BY!!!
(note the WEIRD result here when you do not...)
Empl Last
EMPL Name JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- -------------------- ---------- ---- --------- ------- ---------- ---
7839 King President 17-NOV-91 005000 500
7566 Jones Manager 7839 02-APR-91 002975 200
7698 Blake Manager 7839 01-MAY-91 002850 300
7782 Raimi Manager 7839 09-JUN-91 002450 100
7902 Ford Analyst 7566 03-DEC-91 003000 200
7369 Smith Clerk 7902 17-DEC-90 000800
Empl Last
EMPL Name JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- -------------------- ---------- ---- --------- ------- ---------- ---
7499 Michaels Sales 7698 20-FEB-91 001600 300 300
7521 Ward Sales 22-FEB-91 001250 500
7654 Martin Sales 28-SEP-91 001250 1400
7788 Scott Analyst 7566 09-NOV-91 003000 200
7844 Turner Sales 7698 08-SEP-91 001500 0 300
7876 Adams Clerk 7788 23-SEP-91 001100 400
Empl Last
EMPL Name JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- -------------------- ---------- ---- --------- ------- ---------- ---
7900 James Clerk 7698 03-DEC-91 000950 300
7934 Miller Clerk 7782 23-JAN-92 001300 100
14 rows selected.
========
back to using a break with a query with appropriate ordering:
Empl Last
DEP MGR Name SALARY
--- ---- -------------------- -------
100 7782 Miller 001300
7839 Raimi 002450
200 7566 Scott 003000
Ford 003000
7839 Jones 002975
7902 Smith 000800
300 7698 Michaels 001600
James 000950
Empl Last
DEP MGR Name SALARY
--- ---- -------------------- -------
300 7698 Turner 001500
Ward 001250
Martin 001250
7839 Blake 002850
400 7788 Adams 001100
500 King 005000
14 rows selected.
========
example now using compute:
Empl Last
DEP MGR Name SALARY
--- ---- -------------------- -------
100 7782 Miller 001300
7839 Raimi 002450
*** **** -------
avg 001875
min 001300
max 002450
200 7566 Scott 003000
Ford 003000
7839 Jones 002975
Empl Last
DEP MGR Name SALARY
--- ---- -------------------- -------
200 7902 Smith 000800
*** **** -------
avg 002444
min 000800
max 003000
300 7698 Michaels 001600
James 000950
Turner 001500
Ward 001250
Empl Last
DEP MGR Name SALARY
--- ---- -------------------- -------
300 7698 Martin 001250
7839 Blake 002850
*** **** -------
avg 001567
min 000950
max 002850
400 7788 Adams 001100
*** **** -------
avg 001100
Empl Last
DEP MGR Name SALARY
--- ---- -------------------- -------
min 001100
max 001100
500 King 005000
*** **** -------
avg 005000
min 005000
max 005000
14 rows selected.
========
to see your current values for break, column, compute:
break on dept_num skip 1 nodup
on mgr nodup
COMPUTE avg LABEL 'avg' minimum LABEL 'minimum' maximum LABEL 'maximum' OF salary ON dept_num
COLUMN val ON
FORMAT 99
COLUMN salary ON
FORMAT 099999
COLUMN ptg_title ON
FORMAT a7
word_wrap
COLUMN empl_last_name ON
HEADING 'Empl Last|Name' headsep '|'
FORMAT a20
truncate
========
ttitle to get a nice top title
Thu Dec 01 page 1
a BEAUTIFUL
Three line
Title
Empl Last
DEP MGR Name SALARY
--- ---- -------------------- -------
100 7782 Miller 001300
7839 Raimi 002450
*** **** -------
avg 001875
min 001300
max 002450
200 7566 Scott 003000
Ford 003000
7839 Jones 002975
7902 Smith 000800
*** **** -------
avg 002444
min 000800
max 003000
300 7698 Michaels 001600
James 000950
Turner 001500
Ward 001250
Martin 001250
Thu Dec 01 page 2
a BEAUTIFUL
Three line
Title
Empl Last
DEP MGR Name SALARY
--- ---- -------------------- -------
300 7839 Blake 002850
*** **** -------
avg 001567
min 000950
max 002850
400 7788 Adams 001100
*** **** -------
avg 001100
min 001100
max 001100
500 King 005000
*** **** -------
avg 005000
min 005000
max 005000
14 rows selected.
========
btitle to get a nice bottom title
Thu Dec 01 page 1
a BEAUTIFUL
Three line
Title
Empl Last
DEP MGR Name SALARY
--- ---- -------------------- -------
100 7782 Miller 001300
7839 Raimi 002450
*** **** -------
avg 001875
min 001300
max 002450
200 7566 Scott 003000
Ford 003000
7839 Jones 002975
7902 Smith 000800
*** **** -------
avg 002444
min 000800
max 003000
300 7698 Michaels 001600
James 000950
Three
Line
BottomTitle
Thu Dec 01 page 2
a BEAUTIFUL
Three line
Title
Empl Last
DEP MGR Name SALARY
--- ---- -------------------- -------
300 7698 Turner 001500
Ward 001250
Martin 001250
7839 Blake 002850
*** **** -------
avg 001567
min 000950
max 002850
400 7788 Adams 001100
*** **** -------
avg 001100
min 001100
max 001100
500 King 005000
*** **** -------
avg 005000
Three
Line
BottomTitle
Thu Dec 01 page 3
a BEAUTIFUL
Three line
Title
Empl Last
DEP MGR Name SALARY
--- ---- -------------------- -------
min 005000
max 005000
Three
Line
BottomTitle
14 rows selected.
Thu Dec 01 page 1
a BEAUTIFUL
Three line
Title
Empl Last
DEP MGR Name SALARY
--- ---- -------------------- -------
100 7782 Miller 001300
7839 Raimi 002450
*** **** -------
avg 001875
min 001300
max 002450
200 7566 Scott 003000
Ford 003000
7839 Jones 002975
7902 Smith 000800
*** **** -------
avg 002444
min 000800
max 003000
300 7698 Michaels 001600
James 000950
Turner 001500
Two
Lines
Thu Dec 01 page 2
a BEAUTIFUL
Three line
Title
Empl Last
DEP MGR Name SALARY
--- ---- -------------------- -------
300 7698 Ward 001250
Martin 001250
7839 Blake 002850
*** **** -------
avg 001567
min 000950
max 002850
400 7788 Adams 001100
*** **** -------
avg 001100
min 001100
max 001100
500 King 005000
*** **** -------
avg 005000
min 005000
max 005000
Two
Lines
14 rows selected.
========
to get rid of ttitles and btitles
Empl Last
DEP MGR Name SALARY
--- ---- -------------------- -------
100 7782 Miller 001300
7839 Raimi 002450
*** **** -------
avg 001875
min 001300
max 002450
200 7566 Scott 003000
Ford 003000
7839 Jones 002975
7902 Smith 000800
*** **** -------
avg 002444
min 000800
max 003000
300 7698 Michaels 001600
James 000950
Turner 001500
Ward 001250
Martin 001250
7839 Blake 002850
*** **** -------
avg 001567
min 000950
max 002850
Empl Last
DEP MGR Name SALARY
--- ---- -------------------- -------
400 7788 Adams 001100
*** **** -------
avg 001100
min 001100
max 001100
500 King 005000
*** **** -------
avg 005000
min 005000
max 005000
14 rows selected.