/*=====
    examples for Week 6 Asynchronous material
    (more detail: see SQL Reading Packet 3!!!!
         because did not quite get to SQL Reading Packet 4 material...!)

    NOTE: this script assumes that:
        set-up-ex-tbls.sql
        ... has already been run, so you have populated
            tables empl, dept, and customer   

    by: Sharon Tuttle
    last modified: 2021-09-28
=====*/

spool 325lect06-2-out.txt

/*=====
    a SQL select statement can do MORE than just relational operations...

    for example, a SELECT clause can project more than just columns.

    for example, you can specify that a computation on a column be
    projected, and then it will be done for each selected row from that
    query
    *   THIS DOES NOT CHANGE the DATA IN THAT TABLE!!!!! it just
        PROJECTS a result with this computation showing

=====*/

prompt ========
prompt project empl last names and salaries:

select empl_last_name, salary
from   empl;

prompt ========
prompt project empl last names and 2 * salaries:

select empl_last_name, salary * 2
from   empl;

-- empl salaries have NOT changed as a result!!!!!!!!!

prompt ========
prompt project empl last names and salaries
prompt (note that projecting a computed column does not change
prompt that column!)

select empl_last_name, salary
from   empl;

/*=======
    hey, guess what?
    you can ASK the select statement to use a DIFFERENT column
    heading for the columns in its result;

    ONE way to do this (using SQL and not other means)
    is with a COLUMN ALIAS

    IN a select clause,
    after a projected expression, you can put a blank and then
       the desired alternate title <-- that's the column alias

    *   if you don't surround it with DOUBLE (!!) quotes,
        it will be displayed in all-caps
    *   if you DO surround it with DOUBLE (!!) quotes,
        it will be displayed in the case shown within the quotes    
    *   (it CANNOT contain blanks unless it is in double quotes)

    *   THIS DOES NOT CHANGE the COLUMN NAMES IN THAT TABLE!!!!! 
        it just projects a DIFFERENT column heading for THAT
        select's results!
=====*/

prompt ========
prompt playing with column aliases!

select empl_last_name, salary * 2 if_raise
from empl;

select empl_last_name, salary *	2 "if Raise"
from empl;

select empl_last_name "Employee", salary * 1.1 "if 10% raise"
from   empl;

prompt ========
prompt WILL GET ERROR, column alias must be in NO quotes or DOUBLE quotes:

select empl_last_name, salary *	2 'if Raise'
from empl;

/*=====
     a caveat!

     computations on columns are NOT done for NULL values of that column!
=====*/

prompt ========
prompt computations will NOT be done on NULL values:

select empl_last_name, salary + commission "Total pay"
from  empl;

/*======
    TABLES can also have aliases!

    IN the from clause,
    you can follow a table name (or table EXPRESSEION) with a blank and
       a name (or something in double-quotes),
       and that becomes the alias for this tables THROUGHOUT that ONE
       SELECT statement!

    *   why??????
        *   it can make join conditions and references to
            columns with the same name shorter to type...
        *   it can allow joining a table to itself (!!)

    *   CLASS STYLE STANDARD: make the alias at least
        SOMEWHAT related to the table name (even if just the first
        letter of its name)
======*/

prompt ========
prompt empl last names and dept names, NO table aliases:

select empl_last_name, dept_name
from   empl, dept
where  empl.dept_num = dept.dept_num;

prompt ========
prompt empl last names and dept names, WITH table aliases
prompt (no difference in the output!):

select empl_last_name, dept_name
from   empl e, dept d
where  e.dept_num = d.dept_num;

prompt ========
prompt WILL GET ERROR; once you have a table alias,
prompt you must USE it THROUGHOUT that select,
prompt EVEN in its SELECT clause...!

select empl_last_name, dept.dept_num, dept_name
from   empl e, dept d
where  e.dept_num = d.dept_num;

prompt ========
prompt now using the needed table alias in select clause:

select empl_last_name, d.dept_num, dept_name
from   empl e, dept d
where  e.dept_num = d.dept_num;

/*======
     AGGREGATE functions
 
     ...these give you ONE computation for ALL of the selected
     rows!

     (until we add another SELECT clause later, these result
     in a SINGLE-row result!)

     avg
     min
     max
     sum
     count

     count(col_name) - counts the number of selected rows
        with a NON-NULL value of col_name
     count(*) - counts the number of selecte rows

     YES, these computations tend to SKIP null values...!
=====*/

prompt ========
prompt average salary of, and number of rows selected, 
prompt with job_title of Manager

select avg(salary), count(*)
from   empl
where  job_title = 'Manager';

prompt ========
prompt note difference between count(*) and count(commission),
prompt and again note that computations are NOT done with NULL values:

select count(*), count(commission), sum(commission)
from   empl;

/*========
    NESTED selects or SUB-selects!

    oops, we'll discuss these in the WEEK 7 asynchronous materials!
======*/

spool off