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

spool 325lab13-1-out.txt

/*=====
   reminder: if you give sqlplus a command of /

/

  ... sqlplus performs the latest SQL statement done

  (NOT SQL*Plus command -- *SQL* statement)
=====*/


select * 
from   empl;

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

/

/*=====
    note: when you change a SQL*Plus session's
    defaults, they stay changed until you
    change them again OR until you logout of that
    session

    the clear command is a SQL*Plus command that
    clears some of the potential things you might
    have set -- it can be good practice to
    clear these at the beginning and the end
    of a report script,

    (a SQL script setting up and creating
    especially-nicely-formatted query results)

    *   SOME of the things you can clear:
        columns
        breaks
        computes

=====*/

clear columns
clear breaks
clear computes

/*===== S. Griffin also found this works: =====*/

clear columns breaks computes

/*=====
    the "blah rows selected" type of message
    in SQL*Plus is called feedback

    you can show feedback's current value with:
show feedback

    one way you can change the feedback (when
    you see the feedback) is with:

set feedback 3

    ...saying only want to see feedback when
    there are at least 3 rows in a result

*   and you can say, NO feedback please, with:

    set feedback off

=====*/

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

select *
from   dept;

set feedback 3

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

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

show feedback

select *
from   dept
where  dept_loc = 'Dallas';

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
/

/*===== setting feedback back to its default... =====*/

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

set feedback 6

/*=====
    pagesize is the number of lines in a "page"
    (the quantum that Oracle SQL*Plus will display
    BEFORE redisplaying column headings, titles, etc

    show pagesize

    *   and you can set that:

    set pagesize 30

    *   and if you DON'T want "page breaks":
        (F16 - and not even "first" set of
	 column headers...?)    

    set pagesize 0

=====*/

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

show pagesize   

select *
from   empl;

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

set pagesize 30
/

/*===== and to set it back to the default: =====*/

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

set pagesize 14

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

    show linesize
    set linesize 50

    *   default is 80
    set linesize 80
=====*/

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

show linesize

select *
from   dept;

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

set linesize 10
/

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

set linesize 80

/*=====
    newpage

    *   the number of blank lines that appear before
        the column headings or top title (if there is
	 one) for each page

        (it also looks like each SQL query's result
	 appears on a new "page", in this sense)

    show newpage

    set newpage 3
=====*/

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

show newpage

select empl_last_name
from   empl;
    
prompt ========
prompt redoing previous SQL statement with a newpage value of 3:

set newpage 3
/

/*===== and newpage default is 1 =====*/

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

set newpage 1
 
/*=====
    column command

    THIS IS JUST for DISPLAY PREFERENCES --
    it does NOT change WHAT is in your tables
    or HOW it is stored in your tables!!!

    MANY options, 
    here are a FEW:

COLUMN col_to_format HEADING desired_heading FORMAT des_fmt

    *   (you can abbreviate COLUMN as COL)

    *   if your column command won't fit on 1 line,
        put a - at the end of the line to say you
	 are not done with the current "line" yet

    *   you can indicate a multiple-line column
        heading with a | in the heading...!

    *   if you want blanks in a column heading,
        you need to put that heading in quotes
	 (single OR double?!?)
   
=====*/

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:

column empl_last_name heading Empl|Last|Name
/

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

col job_title heading "Job Title"
/

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

col salary heading 'Their Salary'
/

/*======
    now let's talk about column FORMAT

    for a NON-numeric column,
    to say how WIDE you would like it formatted,
    use a followed by the desired width
=====*/

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

column empl_last_name heading 'Employee|Last Name' format a35
/
 
prompt ========
prompt what if you forget the a in the format for a non-numeric column?

column job_title format 15

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

column job_title format a4
/

/*=====
    if you put TRUNCATED or TRU after the format,
    it will NOT wrap too-long columns
=====*/

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

column job_title format	   a2 TRUNCATED
/ 

/*====
    if you put WORD WRAPPED or WOR for attempted
    line break at a word break if possible

    (default is WRAPPED...)
=====*/

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 WOR (word wrapped) with format:

col ptg_title format a7 WOR
/

prompt ========
prompt explicitly using default (for non-numeric data) of WRAPPED:

col ptg_title format a7 WRAPPED
/

/*=====
    how about NUMBERS?

    instead of a followed by a number,
    you give a numeric format PATTERN

    here are a FEW of the options for these:
 
    integer to a given width?

    99     <-- 2 digits wide
    99999  <-- 5 digits wide
=====*/

select salary
from   empl;

prompt ========
prompt using col command to give salary a heading:

col salary heading sal 
/

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

col salary format 999999999999
/

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

col salary format 99999
/

prompt ========
prompt using col command to give salary a TOO-SMALL format:

col salary format 9
/

/*====
    can specify how many fractional digits with
    a . in that format:
=====*/

prompt ========
prompt formatting to 3 fractional places:

col salary format 99999.999
/

prompt ========
prompt adding a dollar sign, comma, and 2 fractional places
prompt to a column that is a salary average computation:

col avg(salary) format $999,999.99

select mgr, avg(salary)
from   empl
group by mgr;

prompt ========
prompt (and, again, the effect if numeric format is too small):

col avg(salary) format $999.99
/

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;

/*=====
    break command

    can be used with a query using order by
    to result in "prettier" ordered-row
    displays
=====*/

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

col empl_last_name format a15
col salary format $9999999

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

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 it is WEIRD to break on a column you
prompt are not ordering by!

select empl_last_name, dept_num
from   empl;

prompt ========
prompt back to using break on dept_num with a query ordered by
prompt dept_num:

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

prompt ========
prompt to add a skipped line between breaks,
prompt    add skip to your break command

break on dept_num skip 1
/

/*=====
    only one break command can be in effect at a time...?
    SO sometimes you need to COMBINE them
=====*/

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

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

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
/

/*=====
  compute ONLY makes sense with BREAK;

  it gives you a sqlplus-level way to have a computation
  based on the rows just affected by a BREAK
=====*/

prompt ========
prompt example 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 one per column...
 
    want to see your current breaks, computes, and columns
=====*/

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 'Beautiful Top|Two-line Title' 

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

btitle Three|Line|BottomTitle
/

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

ttitle off
btitle off
/

/* and CLEAR all the goodies you have set
   back to a default at the end,
   I will be posting a script to make this easier...
*/

set pagesize 14

clear columns
clear breaks
clear computes

spool off

-- end of 325lab13-1.sql