/*=====
    CS 325 - Week 8 Asynchronous Materials

    last modified: 2021-10-13
=====*/

spool 325lect08-2-out.txt

prompt =====
prompt demo a SQL union operation!
prompt =====

prompt consider this relation:

select empl_last_name, job_title
from   empl
where  job_title = 'Manager';

prompt and consider this relation:

select empl_last_name, job_title
from   empl
where  commission is not null;

prompt and here is their union:

(select empl_last_name, job_title
 from   empl
 where  job_title = 'Manager')
UNION
(select empl_last_name, job_title
 from   empl
 where  commission is not null);

prompt =====
prompt another UNION example:
prompt dept_nums of employees in Sales and dept_nums of
prompt departments located in Dallas
prompt =====
 
prompt consider this (flaky) relation:

select dept_num
from   empl 
where  job_title = 'Sales';

prompt and consider this relation:

select dept_num
from   dept
where  dept_loc = 'Dallas';

prompt here is union of these two relations:

(select dept_num
 from   empl
 where  job_title = 'Sales')
UNION
(select dept_num
 from   dept
 where  dept_loc = 'Dallas');

prompt =====
prompt some COMMON ERRORS using the UNION operator:
prompt =====

prompt ERROR: you CANNOT use union with two sub-selects with different
prompt numbers of projected columns!

(select dept_num
 from   empl
 where  job_title = 'Sales')
UNION
(select dept_num, dept_name
 from   dept
 where  dept_loc = 'Dallas');

prompt ERROR: you CANNOT use union with two sub-selects whose respective
prompt respective columns have INCOMPATIBLE domains (at least not in a data-type
prompt sense)

(select salary
 from   empl
 where  job_title = 'Sales')
union
(select dept_name
 from   dept
 where  dept_loc = 'Dallas');

/*====
    BUT!! a DBMS can't/won't dig deeply into the domains of the
    respective projected columns here --
    you can get NONSENSE results if the domains are type-compatible
    but don't have the same MEANING!
=====*/

prompt BUT BE CAREFUL; if the projected columns are TYPE-compatible
prompt but not (truly) UNION-compatible -- if they don't have the
prompt same MEANING -- you can get NONSENSE results and NO syntax error:

(select dept_name
 from   dept)
union
(select empl_last_name
 from   empl);

/*=====
    this also shows another "fun fact" about SQL unions:
    the column heading of the first sub-select is used
    as the column heading of the result
=====*/

prompt =====
prompt a COOL use of union: when you WANT to better deal with NULL
prompt values in certain columns:
prompt =====

prompt for example, I want TOTAL recompense for all employees,
prompt salary + commission,
prompt even though some have null commissions

(select empl_last_name, salary + commission "total of salary + commission"
 from   empl
 where  commission is not null)
union
(select empl_last_name, salary
 from   empl
 where  commission is null);

prompt =====
prompt domain meanings are the point, not column names...
prompt =====

(select empl_rep
 from   customer)
union
(select empl_num
 from   empl
 where  job_title = 'Analyst');

/*=====

    quirky little SQL*Plus feature:
    using & for interactive input!

    IF you put & in front of a name in a query,
    or even in a prompt (!!), SQL*Plus will ask you to
    enter something when you run that script,
    using that name you gave after the & in the prompt,
    and use what you enter as that name's value in that query
=====*/

prompt =====
prompt demo of ampersand for interactive input into a SQL statement:
prompt (remember to type job_ttl IN single quotes!)
prompt =====

select salary
from   empl
where  job_title = &job_ttl;

/*=====
    Jay Jager, in a previous semester, noticed a cool tidbit here --
    if you put the & and name in single quotes,
    what you type is PUT in those single quotes!

    (that is, the user does not have to remember to type a string value 
    in single quotes for string input)
=====*/

prompt =====
prompt ANOTHER demo of ampersand,
prompt in single quotes, so DO NOT enter single quotes here:
prompt =====

select salary
from   empl
where  job_title = '&job_ttl';

spool off