/*========
    CS 325 - Week 14 Labs - 2016-12-01 - 11: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 it returns a character-string version
    of the given date or number
    based on that given format string
=====*/

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;

col month_hired format a11 tru
/

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 dual;

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

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

select to_char(sysdate-2, 'Mon YYYY') mon_year,
       to_char(sysdate-2, 'MM-DD-YY') num_version,
       to_char(sysdate-2, 'Day, Month DD, YYYY') long_version,
       to_char(sysdate-2, 'DY - Mon DD - YY') brief_version
from dual;

HERE PLEASE

/*=====
    the Oracle date type also has time within it...!

    'HH12' - hour of the day in 12-hour time (1-12)
    'HH24' - hour of the day in 24-hour time (0-23)
    'MI' - minutes of the hour
    'SS' - seconds of the minute
    'AM' - displays 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;

/*=====
    we know you can add to a date, and get the date
    that many days away
=====*/

select sysdate + 1
from dual;

select hiredate, hiredate + 1
from   empl;

/*=====
    you DO have to add to an actual date item
    (something of type date
=====*/

prompt this fails (a string is not a date):

select '31-DEC-16' + 1
from   dual;

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

select to_date('31-DEC-16') + 1
from   dual;

/*=====
    next_day -
    expects an expression of type date
    and a day of the week (as a string),
    
    and it returns the date of the next date after
    the given date that is on that day of the week
=====*/

select next_day(sysdate, 'TUESDAY') next_tues_date
from   dual;

select next_day(sysdate, 'WEDNESDAY') next_wed_date
from   dual;

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

    months_between - expects two dates, and returns the
    number of months between those two dates
=====*/

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

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

    initcap - expects a string, and returns a string
       with an initial uppercase letter
 
    lower - expect a string, and returns that string in
        all-lowercase

    upper - expect a string, and returns that string in
        all-uppercase
    

=====*/

select initcap('SILLY')
from   dual;

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

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

    rpad - right pad - expects a string, a desired length,
        and a padding character,
	and return a string of that length containing
	the given string and 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;

prompt you can combine these in fun and interesting
prompt ways...

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

/*======
    ltrim - expects a string, and returns that string
         with any leading blanks (blanks starting the
	 string) removed 

    rtrim - expects a string, and returns that string
         with any trailing blanks (blanks ending the
	 string) removed 

=====*/

select ltrim('   Hi    ') leftchop,
       rtrim('   Hi    ') rightchop,
       rtrim( to_char(sysdate, 'Day')) || ', ' 
           || rtrim( to_char(sysdate, 'Month')) || ' '
           || to_char(sysdate, 'DD, YYYY') nicer
from dual;
          
/*=====
    length - expects a string, returns the number
         of characters in that string

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

        (3rd argument optional, if omitted you get
	the rest of the string starting at the given
	position)
=====*/

col rest_of_name format a10

select empl_last_name,
       length(empl_last_name) length,
       substr(empl_last_name, 1, 3) name_abbrev,
       substr(empl_last_name, 4) rest_of_name
from   empl;

spool off

-- end of 325lab14-1.sql