/*========
    CS 325 - Week 14 Labs - 2016-12-01 -3:00 lab
========*/

spool 325lab14-1-out.txt

prompt ========
prompt quick reminder: of how useful concatenation can be,
prompt and that Oracle provides useful functions such as sysdate

select empl_last_name || ' - ' || job_title roles
from empl;

select sysdate 
from empl;

select '$' || salary "Salary"
from  empl;

/*======
    to_char 

    expects a date or a number
    and a format string,

    and returns a character-string version of
    the given date or number formatted
    as specified
=====*/

prompt ========
prompt examples using to_char function to project hiredate
prompt in different formats:

select empl_last_name, 
       to_char(hiredate, 'MONTH') month_hired
from   empl;

select empl_last_name, 
       to_char(hiredate, 'Month') month_hired
from   empl;

prompt ========
prompt quick demo of projecting sysdate using some of the 
prompt formatting options for to_char:

select to_char(sysdate, 'YYYY') year,
       to_char(sysdate, 'Mon YYYY') mon_year,
       to_char(sysdate,	'MM-DD-YY') num_version
from empl;

select to_char(sysdate,	'Day, Month DD, YYYY')
                        long_version,
       to_char(sysdate,	'DY - Mon DD - YY') briefer_vsn
from empl;

prompt ========
prompt (and for 3 days before today, to perhaps demo spacing
prompt differences...):

select to_char(sysdate-3, 'YYYY') year,
       to_char(sysdate-3, 'Mon YYYY') mon_year,
       to_char(sysdate-3, 'MM-DD-YY') num_version,
       to_char(sysdate-3, 'Day, Month DD, YYYY')
                        long_version,
       to_char(sysdate-3, 'DY - Mon DD - YY') briefer_vsn
from empl;

HERE PLEASE

/*=====
    there is a time component to Oracle's date type

    you can use to_char plus formats such as the
    following to pull those out:

    'HH12' - hour of day in 12-hour format (1-12)
    'HH24' - hour of day in 24-hour format (0-23)
    'MI' - minutes of the hour
    'SS' - seconds of the minute
    'AM' - display AM or PM depending on the time
=====*/

select to_char(sysdate, 
               'D DD DDD Day, Mon YYYY - HH12 HH24 MI SS AM')
	       ugly
from dual;

/*=====
    to_date -
    expects a date-string, and returns the corresponding
    date value
     
=====*/

select sysdate + 1
from   dual;

select hiredate, hiredate + 3
from   empl;

prompt this fails:

select '30-Oct-17' + 3
from   dual;

select to_date('30-Oct-17') + 3
from   dual;

/*=====
    next_day 
    expects a date and a string representing the
        day of the week, 
    and it returns the date of the next date after the
        given date that is on that given day of the
	week
=====*/

select next_day(sysdate, 'TUESDAY') nxt_tues,
       next_day(sysdate, 'WEDNESDAY') nxt_wed
from   dual;

/*=====
    add_months - expects a date and a number of months,
        and returns the date that is that many months
	after the given date

    months_between - expects two dates, and returns the
        number of months betwen those two dates
        (positive if 1st date is later than the 2nd,
	 negative otherwise)
=====*/

select add_months(sysdate, 1) one_month_later,
       months_between(to_date('15-Nov-16'), 
                      to_date('15-Feb-17')) diff1,
       months_between(to_date('1-Nov-16'), 
                      to_date('15-Feb-17')) diff2
from dual;

/*=====
    a few string-related functions

    initcap - expects a string, returns that string
        with an initial uppercase letter

    lower - expects a string, returns that string
         in all-lowercase

    upper - expects a string, returns that string
         in all-uppercase
=====*/

select initcap('SILLY') looky
from   dual;

select lower(empl_last_name) low, 
       upper(empl_last_name) upp
from   empl;

/*=====
    lpad - "left pad" - expects a string, a desired length,
        and a padding character,
        and it returns a string of that length
        containing the given string padded on the left
	with the given character

    rpad - "right pad" - expects a string, a desired length,
        and a padding character,
        and it returns a string of that length
        containing the given string padded on the right
	with the given character
=====*/

select lpad(empl_last_name, 12, '.') dots
from   empl;

select rpad(empl_last_name, 15, '?') huh
from   empl;

select lpad(empl_last_name, 12, ' ') right_justifd
from   empl;

/*=====
    sure, you can nest/compose/use these together
    as desired...
=====*/

select lpad( to_char(hiredate, 'Day'), 14, ' ' ) ||
       to_char(hiredate, '- Month YY') "Hiredate"
from empl;

/*=====
    ltrim - expects a string, returns that string
       with leading blanks removed

    rtrim - expects a string, returns that string
       with trailing blanks removed

    length - expects a string, and returns the
        number of characters in that string

    substr - expects a string, the position to start
        in that string (where the first character's
	position is 1), and how long a substring is
	desired,
	and it returns the substring of that length
        starting at that position

	(and the 3rd argument is optional,
	and if omitted, you get the rest of the string)
=====*/

col the_rest format a15

select empl_last_name,
       length(empl_last_name) length,
       substr(empl_last_name, 1, 3) abbrev,
       substr(empl_last_name, 4) the_rest
from empl;

spool off

-- end of 325lab14-2.sql