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
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
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
7788 Scott Analyst 7566 09-NOV-91 3000 200
7844 Turner Sales 7698 08-SEP-91 1500 0 300
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7876 Adams Clerk 7788 23-SEP-91 1100 400
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
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
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
7788 Scott Analyst 7566 09-NOV-91 3000 200
7844 Turner Sales 7698 08-SEP-91 1500 0 300
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7876 Adams Clerk 7788 23-SEP-91 1100 400
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
========
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.
========
result of show feedback:
FEEDBACK ON for 3 or more rows
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 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
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
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
7788 Scott Analyst 7566 09-NOV-91 3000 200
7844 Turner Sales 7698 08-SEP-91 1500 0 300
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP
---- --------------- ---------- ---- --------- ---------- ---------- ---
7876 Adams Clerk 7788 23-SEP-91 1100 400
7900 James Clerk 7698 03-DEC-91 950 300
7934 Miller Clerk 7782 23-JAN-92 1300 100
14 rows selected.
========
redo previous SQL statement with pagesize of 30:
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
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
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
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
7900 James Clerk 7698 03-DEC-91 950 300
7934 Miller Clerk 7782 23-JAN-92 1300 100
14 rows selected.
========
setting pagesize back to default of 14
========
showing default linesize:
linesize 80
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 linesize 10:
DEP
---
DEPT_NAME
----------
DEPT_LOC
----------
100
Accounting
New York
200
Research
Dallas
DEP
---
DEPT_NAME
----------
DEPT_LOC
----------
300
Sales
Chicago
400
Operations
DEP
---
DEPT_NAME
----------
DEPT_LOC
----------
Boston
500
Management
New York
========
back to default linesize of 80
========
showing default newpage:
newpage 1
EMPL_LAST_NAME
---------------
King
Jones
Blake
Raimi
Ford
Smith
Michaels
Ward
Martin
Scott
Turner
EMPL_LAST_NAME
---------------
Adams
James
Miller
14 rows selected.
========
redoing previous SQL statement with a newpage value of 3:
EMPL_LAST_NAME
---------------
King
Jones
Blake
Raimi
Ford
Smith
Michaels
Ward
Martin
EMPL_LAST_NAME
---------------
Scott
Turner
Adams
James
Miller
14 rows selected.
========
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
...and for job_title 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
...and for salary column:
Empl
Last
Name Job Title Their 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 Their Salary
--------------- ---------- ------------
Scott Analyst 3000
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:
Employee
Last Name Job Title Their 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
Employee
Last Name Job Title Their Salary
----------------------------------- ---------- ------------
Turner Sales 1500
Adams Clerk 1100
James Clerk 950
Miller Clerk 1300
14 rows selected.
========
what if you forget the a in the format for a non-numeric column?
SP2-0246: Illegal FORMAT string "15"
========
what if format is narrower than your non-numeric column content?
Employee
Last Name Job Their Salary
----------------------------------- ---- ------------
King Pres 5000
iden
t
Jones Mana 2975
ger
Blake Mana 2850
ger
Employee
Last Name Job Their Salary
----------------------------------- ---- ------------
Raimi Mana 2450
ger
Ford Anal 3000
yst
Smith Cler 800
k
Michaels Sale 1600
Employee
Last Name Job Their Salary
----------------------------------- ---- ------------
s
Ward Sale 1250
s
Martin Sale 1250
s
Scott Anal 3000
yst
Employee
Last Name Job Their Salary
----------------------------------- ---- ------------
Turner Sale 1500
s
Adams Cler 1100
k
James Cler 950
k
Employee
Last Name Job Their Salary
----------------------------------- ---- ------------
Miller Cler 1300
k
14 rows selected.
========
note difference with TRUNCATED added to format:
Employee
Last Name Jo Their Salary
----------------------------------- -- ------------
King Pr 5000
Jones Ma 2975
Blake Ma 2850
Raimi Ma 2450
Ford An 3000
Smith Cl 800
Michaels Sa 1600
Ward Sa 1250
Martin Sa 1250
Scott An 3000
Employee
Last Name Jo Their Salary
----------------------------------- -- ------------
Turner Sa 1500
Adams Cl 1100
James Cl 950
Miller Cl 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 WOR (word wrapped) with format:
PTG_NUM PTR_NUM PTG_TIT
---------- ---------- -------
1 104 Mona
Lisa
2 104 Waterli
lies
3 106 Yet
four
more
========
explicitly using default (for non-numeric data) of WRAPPED:
PTG_NUM PTR_NUM PTG_TIT
---------- ---------- -------
1 104 Mona Li
sa
2 104 Waterli
lies
3 106 Yet fou
r more
Their Salary
------------
5000
2975
2850
2450
3000
800
1600
1250
1250
3000
1500
Their Salary
------------
1100
950
1300
14 rows selected.
========
using col command to give salary a heading:
sal
----------
5000
2975
2850
2450
3000
800
1600
1250
1250
3000
1500
sal
----------
1100
950
1300
14 rows selected.
========
using col command to give salary the format 999999999999:
sal
-------------
5000
2975
2850
2450
3000
800
1600
1250
1250
3000
1500
sal
-------------
1100
950
1300
14 rows selected.
========
using col command to give salary the format 99999:
sal
------
5000
2975
2850
2450
3000
800
1600
1250
1250
3000
1500
sal
------
1100
950
1300
14 rows selected.
========
using col command to give salary a TOO-SMALL format:
sal
---
##
##
##
##
##
##
##
##
##
##
##
sal
---
##
##
##
14 rows selected.
========
formatting to 3 fractional places:
sal
----------
5000.000
2975.000
2850.000
2450.000
3000.000
800.000
1600.000
1250.000
1250.000
3000.000
1500.000
sal
----------
1100.000
950.000
1300.000
14 rows selected.
========
adding a dollar sign, comma, and 2 fractional places
to a column that is a salary average computation:
MGR AVG(SALARY)
---- ------------
$5,000.00
7902 $800.00
7839 $2,758.33
7566 $3,000.00
7698 $1,310.00
7782 $1,300.00
7788 $1,100.00
7 rows selected.
========
(and, again, the effect if numeric format is too small):
MGR AVG(SALARY)
---- -----------
########
7902 $800.00
7839 ########
7566 ########
7698 ########
7782 ########
7788 ########
7 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):
sal
-------
005000
002975
002850
002450
003000
000800
001600
001250
001250
003000
001500
sal
-------
001100
000950
001300
14 rows selected.
========
a query with results ordered by dept_num:
Employee
DEP Last Name sal
--- --------------- ---------
100 Miller $1300
100 Raimi $2450
200 Scott $3000
200 Jones $2975
200 Ford $3000
200 Smith $800
300 Martin $1250
300 Ward $1250
300 Blake $2850
300 Michaels $1600
Employee
DEP Last Name sal
--- --------------- ---------
300 James $950
300 Turner $1500
400 Adams $1100
500 King $5000
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:
Employee
DEP Last Name sal
--- --------------- ---------
100 Miller $1300
Raimi $2450
200 Scott $3000
Jones $2975
Ford $3000
Smith $800
300 Martin $1250
Ward $1250
Blake $2850
Michaels $1600
Employee
DEP Last Name sal
--- --------------- ---------
300 James $950
Turner $1500
400 Adams $1100
500 King $5000
14 rows selected.
========
it is WEIRD to break on a column you
are not ordering by!
Employee
Last Name DEP
--------------- ---
King 500
Jones 200
Blake 300
Raimi 100
Ford 200
Smith
Michaels 300
Ward
Martin
Scott 200
Employee
Last Name DEP
--------------- ---
Turner 300
Adams 400
James 300
Miller 100
14 rows selected.
========
back to using break on dept_num with a query ordered by
dept_num:
Employee
DEP Last Name sal
--- --------------- ---------
100 Miller $1300
Raimi $2450
200 Scott $3000
Jones $2975
Ford $3000
Smith $800
300 Martin $1250
Ward $1250
Blake $2850
Michaels $1600
Employee
DEP Last Name sal
--- --------------- ---------
300 James $950
Turner $1500
400 Adams $1100
500 King $5000
14 rows selected.
========
to add a skipped line between breaks,
add skip to your break command
Employee
DEP Last Name sal
--- --------------- ---------
100 Miller $1300
Raimi $2450
200 Scott $3000
Jones $2975
Ford $3000
Smith $800
300 Martin $1250
Ward $1250
Employee
DEP Last Name sal
--- --------------- ---------
300 Blake $2850
Michaels $1600
James $950
Turner $1500
400 Adams $1100
500 King $5000
14 rows selected.
========
now ordering by dept_num, then mgr
Employee
DEP MGR Last Name sal
--- ---- --------------- ---------
100 7782 Miller $1300
7839 Raimi $2450
200 7566 Scott $3000
7566 Ford $3000
7839 Jones $2975
7902 Smith $800
300 7698 Michaels $1600
7698 James $950
Employee
DEP MGR Last Name sal
--- ---- --------------- ---------
300 7698 Turner $1500
7698 Ward $1250
7698 Martin $1250
7839 Blake $2850
400 7788 Adams $1100
500 King $5000
14 rows selected.
========
demo of saying you want to break on dept_num and mgr
in a SINGLE break command:
Employee
DEP MGR Last Name sal
--- ---- --------------- ---------
100 7782 Miller $1300
7839 Raimi $2450
200 7566 Scott $3000
Ford $3000
7839 Jones $2975
7902 Smith $800
300 7698 Michaels $1600
James $950
Turner $1500
Ward $1250
Employee
DEP MGR Last Name sal
--- ---- --------------- ---------
300 7698 Martin $1250
7839 Blake $2850
400 7788 Adams $1100
500 King $5000
14 rows selected.
========
...and can skip different amounts for the different
columns you are breaking on:
Employee
DEP MGR Last Name sal
--- ---- --------------- ---------
100 7782 Miller $1300
7839 Raimi $2450
200 7566 Scott $3000
Ford $3000
7839 Jones $2975
7902 Smith $800
300 7698 Michaels $1600
James $950
Employee
DEP MGR Last Name sal
--- ---- --------------- ---------
300 7698 Turner $1500
Ward $1250
Martin $1250
7839 Blake $2850
400 7788 Adams $1100
500 King $5000
14 rows selected.
========
example using compute:
Employee
DEP MGR Last Name sal
--- ---- --------------- ---------
100 7782 Miller $1300
7839 Raimi $2450
*** **** ---------
avg $1875
min $1300
max $2450
200 7566 Scott $3000
Ford $3000
7839 Jones $2975
Employee
DEP MGR Last Name sal
--- ---- --------------- ---------
200 7902 Smith $800
*** **** ---------
avg $2444
min $800
max $3000
300 7698 Michaels $1600
James $950
Turner $1500
Ward $1250
Employee
DEP MGR Last Name sal
--- ---- --------------- ---------
300 7698 Martin $1250
7839 Blake $2850
*** **** ---------
avg $1567
min $950
max $2850
400 7788 Adams $1100
*** **** ---------
avg $1100
Employee
DEP MGR Last Name sal
--- ---- --------------- ---------
min $1100
max $1100
500 King $5000
*** **** ---------
avg $5000
min $5000
max $5000
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 avg(salary) ON
FORMAT $999.99
COLUMN ptg_title ON
FORMAT a7
wrap
COLUMN salary ON
HEADING 'sal'
FORMAT $9999999
COLUMN job_title ON
HEADING 'Job Title'
FORMAT a2
truncate
COLUMN empl_last_name ON
HEADING 'Employee|Last Name' headsep '|'
FORMAT a15
========
ttitle to get a nice top title
========
btitle to get a nice bottom title
Thu Dec 01 page 1
Beautiful Top
Two-line Title
Employee
DEP MGR Last Name sal
--- ---- --------------- ---------
100 7782 Miller $1300
7839 Raimi $2450
*** **** ---------
avg $1875
min $1300
max $2450
200 7566 Scott $3000
Ford $3000
7839 Jones $2975
7902 Smith $800
*** **** ---------
avg $2444
min $800
max $3000
300 7698 Michaels $1600
James $950
Turner $1500
Three
Line
BottomTitle
Thu Dec 01 page 2
Beautiful Top
Two-line Title
Employee
DEP MGR Last Name sal
--- ---- --------------- ---------
300 7698 Ward $1250
Martin $1250
7839 Blake $2850
*** **** ---------
avg $1567
min $950
max $2850
400 7788 Adams $1100
*** **** ---------
avg $1100
min $1100
max $1100
500 King $5000
*** **** ---------
avg $5000
min $5000
max $5000
Three
Line
BottomTitle
14 rows selected.
========
to get rid of ttitles and btitles
Employee
DEP MGR Last Name sal
--- ---- --------------- ---------
100 7782 Miller $1300
7839 Raimi $2450
*** **** ---------
avg $1875
min $1300
max $2450
200 7566 Scott $3000
Ford $3000
7839 Jones $2975
7902 Smith $800
*** **** ---------
avg $2444
min $800
max $3000
300 7698 Michaels $1600
James $950
Turner $1500
Ward $1250
Martin $1250
7839 Blake $2850
*** **** ---------
avg $1567
min $950
max $2850
Employee
DEP MGR Last Name sal
--- ---- --------------- ---------
400 7788 Adams $1100
*** **** ---------
avg $1100
min $1100
max $1100
500 King $5000
*** **** ---------
avg $5000
min $5000
max $5000
14 rows selected.