/*=====
    CS 325 - Week 13 - Labs - 2016-11-16 - 3:00 lab
=====*/

spool 325lab13-2-out.txt

/*=====
   you know that sqlplus displays your SQL statement
   results with various default formats...

   Oracle SQL*Plus also provides a variety of commands
   for customizing/changing those defaults

   we are JUST changing how the results are being
   DISPLAYED -- we are NOT changing the table data!!!

   what we mean by report:
   especially-nicely formatted query,
   designed so it is understandable and readable
   "on its own", by anyone
=====*/

/*=====
    reminder

    /

    ...simply re-executes the previous SQL statement

    (not just the previous command done --
    the previous *SQL statement* done)
=====*/

select * 
from   empl;

prompt ========
prompt redoing previous SQL statement with /:

/

/*=====
    clear command

    when you muck with the default settings in sqlplus,
    they stay mucked with until you either
    change them again or you log out

    *   it it considered polite, then, to reset
        defaults back at the end of a SQL script
	 that mucks with those defaults;

    *   it can be considered smart to start your
        SQL report script making sure you start with
	 the defaults, anything else cleared
	 (so they can't affect you unexpectedly)

    An example SQL*Plus tool for this:

    clear command - to clear previous values
        for SOME of these settings

    clear columns
    clear breaks
    clear computes
    
    (and S. Griffin found that also:)

    clear columns breaks computes

=====*/

clear columns
clear breaks
clear computes

/*=====
    feedback

    the line sqlplus gives that says how many selected,
    for example

    show feedback
    ...gives the current value of feedback, when you
       will SEE feedback

    set feedback 3
    ...example changing feedback to only when 3 or more
       rows
=====*/

prompt ========
prompt using default feedback value:

select * 
from   dept;

prompt ========
prompt result of show feedback:

show feedback

set feedback 3

prompt ========
prompt now using a feedback of 3:
/

select *
from   dept
where  dept_loc = 'Dallas';

/*=====
    you can specify you do NOT want that feedback
    line with:

    set feedback off
=====*/

prompt ========
prompt ugly Cartesian product:

select e1.empl_last_name, e2.empl_last_name
from   empl e1, empl e2;

set feedback off

prompt ========
prompt ugly Cartesian product with feedback off:

show feedback
/

prompt ========
prompt setting feedback back to default

set feedback 6

/*=====
    pagesize - in SQL*Plus, this is the number of lines
        in a "page", the quantum that Oracle will
	 display before redisplaying column headings, etc.

    show pagesize

    set pagesize 30 

    (note that is appears that each query result
    is a new "page")
=====*/

prompt ========
prompt showing default pagesize:

show pagesize   

select empl_last_name
from   empl;

prompt ========
prompt redo previous SQL statement with pagesize of 30:

set pagesize 30
/

/*=====
   you can say you NEVER want page breaks with:

   set pagesize 0

   (can be nice for generating a "flat" file of data)
=====*/

/*===== back to the default pagesize =====*/

prompt ========
prompt setting pagesize back to default of 14

set pagesize 14

/*=====
    linesize - indicates how many characters are in
      a line (before line-wrapping must occur)

    show linesize

    set linesize 40
=====*/

prompt ========
prompt showing default linesize:

show linesize 

select *
from   empl;

prompt ========
prompt redoing previous SQL statement with linesize 40:

set linesize 40
/

/*===== linesize default =====*/

prompt ========
prompt back to default linesize of 80

set linesize 80

/*=====
    newpage is specifying how many blank lines should
    appear before column headings

    show newpage

    set newpage 5

    (setting this to 0 can be nice for creating
    a "flat" file of data, also)
=====*/

prompt ========
prompt showing default newpage:

show newpage

select *
from   dept;

prompt ========
prompt redoing previous SQL statement with a newpage value of 5:

set newpage 5
/

/*===== default for newpage =====*/

prompt ========
prompt resetting newpage back to default of 1

set newpage 1

/*=====
    COLUMN command

    (can be abbreviated as col)

    SQL*Plus column command is JUST about how a
    specified column is to be DISPLAYED at some
    point;

    JUST giving SQL*Plus display preferences!
    (and based on the columns from SELECT statements...)

    MANY options, we will go over a FEW...

    COLUMN col_to_format HEADING des_heading FORMAT des_fmt

    (remember that SQL*Plus commands are meant to
       be 1 line only -- you can "extend" that with
       a - at the end of that line...)

    let's play with the HEADING option:

    *   can put your desired heading here
        (as an alternative to column aliases
	 within the SELECT...)
 
    *   want a blank in your heading?
        put the heading in quotes (single OR double?!?)

    *   want a multi-line column heading?
        ...put a vertical bar/pipe, |, where you
	 want the line break
=====*/

select empl_last_name, job_title, salary
from   empl;

prompt ========
prompt redoing previous SQL statement, now affected by col command
prompt ...for empl_last_name column:

col empl_last_name heading Empl|Last|Name
/

prompt ========
prompt redoing previous SQL statement, now affected by col command
prompt ...again for empl_last_name column


col empl_last_name heading 'Empl|Last Name'
/

prompt ========
prompt redoing previous SQL statement, now affected by col command
prompt ...again for empl_last_name column

col empl_last_name heading "Empl Last|Name"
/

/*=====
    and for format --
    let's consider NON-numeric columns first...

    you use format a, followed by how wide you want
    that column
=====*/

prompt ========
prompt using col command to change format of empl_last_name column:

col empl_last_name format a10
/

/*=====
    what if too short?
    by default, you get WRAPPED:
=====*/

prompt ========
prompt what if format is narrower than your non-unnumeric column content?

col empl_last_name format a3
/

/*=====
    you can specify TRUNCATED or TRU to say,
    truncate too-long values in that column
    (JUST in the DISPLAY!!!)
=====*/

prompt ========
prompt note difference with TRUNCATED added to format:

col empl_last_name format a3 TRUNCATED
/

/*=====
   you can get attempte word-wrapping with
   WOR
====*/

delete from painting;

insert into painting
values
(1, 104, 'Mona Lisa');

insert into painting 
values
(2, 104, 'Waterlilies');

insert into painting
values
(3, 106, 'Yet four more');

select *
from   painting;

prompt ========
prompt using format a7 for ptg_title:

column ptg_title format a7
/

prompt ========
prompt using WOR (word wrapped) with format:

column ptg_title format a7 WOR
/

prompt ========
prompt (which is short for WORD_WRAPPED):

column ptg_title format a7 WORD_WRAPPED
/

/*=====
    what about numeric columns?

    you give a numeric format pattern AFTER the FORMAT
    keyword

    here are a FEW of the available options:

    *   to display a number as an integer in so
        many total columns, use that many 9's

	 999      <-- 3 wide, right-justified
	 999999   <-- 6 wide, right-justified

=====*/

col empl_last_name format a20

prompt ========
prompt using col command to give empl_last_name the format a20:

select empl_last_name, job_title, salary
from empl;

prompt ========
prompt using col command to give salary the format 99999:

col salary format 99999
/

prompt ========
prompt what if you format a numeric column too narrow?

col salary format 999
/

prompt ========
prompt now let us add a dollar sign

col salary format $99999
/

prompt ========
prompt now let us add commas

col salary format $99,999
/

prompt ========
prompt yes, you can have a decimal point, also
prompt (that is how you specify that ALL in a column
prompt should be displayed to that precision)

col salary format $99,999.9999
/

prompt ========
prompt answering the question: is a displayed numeric value rounded
prompt or truncated? 
prompt
prompt Part 1: displaying the value 3.7 with default format
prompt (with the help of the special table DUAL):

select 3.7 val
from dual;

prompt ========
prompt Part 2: now displaying this value with the format 99:

col val format 99

/

prompt ========
prompt quick demo of how you can get leading 0s if you WANT them
prompt (with format string 099999):

col salary format 099999

select salary
from   empl;

/*====
     another SQL*Plus option:

     break

     purpose: to make certain queries whose
         rows are ordered by some attribute
	   display more "nicely"

	    ...does NOT make much sense unless
	     used WITH a query using ORDER BY...!
=====*/

prompt ========
prompt a query with results ordered by dept_num:

select dept_num, empl_last_name, salary
from   empl
order by dept_num;

/*====
    break gives you a way to say,
    only show the FIRST value when a value
    in an ordered-by column REPEATS
====*/

prompt ========
prompt break gives you a way to suppress repeated
prompt    display of a column when
prompt    ordered by that column

prompt ========
prompt ...and the same query, now with a break on dept_num:

break on dept_num
/

prompt ========
prompt let us add a skip of 1 line between
prompt     different values of dept_num

break on dept_num skip 1
/

prompt ========
prompt now ordering by dept_num, then mgr

select dept_num, mgr, empl_last_name, salary
from   empl
order by dept_num, mgr;

/*=====
    can break on more than 1 column at a time,
    but there is only one break command in effect
    at a time (I believe), so combine into a SINGLE
    break command
=====*/

prompt ========
prompt demo of saying you want to break on dept_num and mgr
prompt in a SINGLE break command:

break on dept_num on mgr
/

prompt ========
prompt ...and can skip different amounts for the different
prompt columns you are breaking on:

break on dept_num skip 1 on mgr
/

prompt ========
prompt SHOULD use break WITH a query using ORDER BY!!!
prompt (note the WEIRD result here when you do not...)

select *
from   empl;

/*=====
    COMPUTE

    only makes sense used with BREAK!
    *   it lets you specify that you would like
        some computation to be done for rows
	 with the same value of someting you
	 are breaking on

=====*/

prompt ========
prompt back to using a break with a query with appropriate ordering:

select dept_num, mgr, empl_last_name, salary
from   empl
order by dept_num, mgr;

prompt ========
prompt example now using compute:

break on dept_num skip 1 on mgr
compute avg min max of salary on dept_num
/

/*====
    you can have multiple compute commands,
    but only 1 per column
=====*/

/*=====
    by the way -- you can SEE your current values
    of break, compute, and column
    by putting:

    break
    compute
    column
=====*/

prompt ========
prompt to see your current values for break, column, compute:

break 
compute
column

/*=====
    you can specify top and bottom titles, also

    (can use | for line breaks,
    and can use " " or '' if there blanks)
=====*/

set pagesize 30

prompt ========
prompt ttitle to get a nice top title

ttitle "a BEAUTIFUL|Three line|Title"
/

prompt ========
prompt btitle to get a nice bottom title

btitle Three|Line|BottomTitle

set linesize 50 
/

btitle Two|Lines 
/

prompt ========
prompt to get rid of ttitles and btitles

ttitle off
btitle off
/

/*=====
    I will be posting a nice little script
    from a previous CS 315 student
    with all the defaults, to make resetting them easier!
=====*/

set pagesize 14

clear columns
clear breaks
clear computes

spool off

-- end of 325lab13-2.sql