prompt show last names and job titles
prompt of employees who are Managers or work
prompt    in Dallas

(select empl_last_name, job_title
 from   empl
 where  job_title = 'Manager')
 union
(select empl_last_name, job_title
 from   empl e join dept d
        on e.dept_num = d.dept_num
 where  dept_loc = 'Dallas');

/*=====
    note that UNION does result in a "true"
    set-theoretic union --
    a true relation;

    rows/set elements ONLY appear once;

    IF you put

    UNION ALL

    ... you will get the not-quite-really-a-true
    relation result, seeing rows that are in
    BOTH original sets TWICE

=====*/

prompt now doing it with UNION ALL (note the duplicates!)

(select empl_last_name, job_title
 from   empl
 where  job_title = 'Manager')
 union all
(select empl_last_name, job_title
 from   empl e join dept d
        on e.dept_num = d.dept_num
 where  dept_loc = 'Dallas');

/*=====
    remember, for union, intersect, and minus,

    the sub-selects MUST be UNION-COMPATIBLE
=====*/

prompt this will fail, union with sub-selects
prompt    that are NOT union-compatible:

(select empl_last_name
 from   empl
 where  job_title = 'Manager')
 union all
(select empl_last_name, job_title
 from   empl e join dept d
        on e.dept_num = d.dept_num
 where  dept_loc = 'Dallas');

prompt will also fail:

(select empl_last_name, salary
 from   empl
 where  job_title = 'Manager')
 union all
(select empl_last_name, job_title
 from   empl e join dept d
        on e.dept_num = d.dept_num
 where  dept_loc = 'Dallas');

/*=====
    BUT -- while IDEALLY you only union
    sets whose attributes have relatively
    the same domain in a MEANING/semantic sense,

    your DBMS is not that perceptive,
    and it will "accept" any attribute domains
    that are relatively type-compatible...!

=====*/

prompt this result does not make semantic sense!

(select empl_last_name
 from   empl
 where  job_title = 'Manager')
 union all
(select job_title
 from   empl e join dept d
        on e.dept_num = d.dept_num
 where  dept_loc = 'Dallas');    

/*=====
     NOTE that it looks like the sub-selects for
     these do NOT have to be in parentheses...

     (but less readable that way, sigh!!!)
=====*/

select empl_last_name
from   empl
where  job_title = 'Manager'
union all
select job_title
from   empl e join dept d
       on e.dept_num = d.dept_num
where  dept_loc = 'Dallas';    

/*=====
    NOTE that you can order-by the RESULT,
    BUT that order-by SHOULD go at the end
    OUTSIDE the 2nd sub-select,
    because you ordering the union'd or
    intersect'd or minus'd RESULT

    AND you need to order by something
    based on the attribute names in the
    FIRST sub-select
=====*/

(select empl_last_name
 from   empl
 where  job_title = 'Manager')
 union all
(select job_title
 from   empl e join dept d
        on e.dept_num = d.dept_num
 where  dept_loc = 'Dallas')
order by empl_last_name;

prompt this should fail, outer-result's order
prompt   by ONLY knows empl_last_name:

(select empl_last_name
 from   empl
 where  job_title = 'Manager')
 union all
(select job_title
 from   empl e join dept d
        on e.dept_num = d.dept_num
 where  dept_loc = 'Dallas')
order by job_title;

prompt FAILS - if 1st sub-select uses a column alias,
prompt then so must the order by ordering that
prompt union'd (or intersect'd or minus'd)
prompt result

(select empl_last_name names
 from   empl
 where  job_title = 'Manager')
 union all
(select job_title
 from   empl e join dept d
        on e.dept_num = d.dept_num
 where  dept_loc = 'Dallas')
order by empl_last_name;

(select empl_last_name names
 from   empl
 where  job_title = 'Manager')
 union all
(select job_title
 from   empl e join dept d
        on e.dept_num = d.dept_num
 where  dept_loc = 'Dallas')
order by names;

/*=====
    INTERSECT - of A and B,
    all the rows in A AND in B

    (still must be union-compatible!)
=====*/

(select empl_last_name, job_title
 from   empl
 where  job_title = 'Manager')
 intersect
(select empl_last_name, job_title
 from   empl e join dept d
        on e.dept_num = d.dept_num
 where  dept_loc = 'Dallas');

/*=====
    MINUS - of A and B,

    those rows in A that are NOT also in B
=====*/

(select empl_last_name, job_title
 from   empl
 where  job_title = 'Manager')
 minus
(select empl_last_name, job_title
 from   empl e join dept d
        on e.dept_num = d.dept_num
 where  dept_loc = 'Dallas');

(select empl_last_name, job_title
 from   empl e join dept d
        on e.dept_num = d.dept_num
 where  dept_loc = 'Dallas')
minus
(select empl_last_name, job_title
 from   empl
 where  job_title = 'Manager');

/*=====
    union CAN be an alternative to OR

    intersect CAN be an alternative AND

    ...I think minus CAN be carefully
    used as an alternative to NOT EXISTS...
=====*/

(select dept_name
 from   dept)
minus
(select dept_name
 from   dept d join empl e
        on d.dept_num = e.dept_num);

/*=====
    union can also be used for getting a count
    of 0 in certain situations...
=====*/

prompt you CANNOT get counts of 0 with JUST a join...

select dept_name, count(*)
from   dept d join empl e 
       on d.dept_num = e.dept_num
group by dept_name;

prompt BUT, I COULD use union to supplement that join

(select dept_name, count(*)
 from   dept d join empl e
        on d.dept_num = e.dept_num
 group by dept_name)
union
(select dept_name, 0
 from   dept
 where  NOT EXISTS
        (select 'a'
	 from empl
         where empl.dept_num = dept.dept_num));


(select dept_name, count(*) num_empls
 from   dept d join empl e
        on d.dept_num = e.dept_num
 group by dept_name)
union
(select dept_name, 0
 from   dept
 where  NOT EXISTS
        (select 'a'
	 from empl
         where empl.dept_num = dept.dept_num))
order by num_empls desc;

(select dept_name, count(*) "# of Employees"
 from   dept d join empl e
        on d.dept_num = e.dept_num
 group by dept_name)
union
(select dept_name, 0
 from   dept
 where  NOT EXISTS
        (select 'a'
	 from empl
         where empl.dept_num = dept.dept_num))
order by "# of Employees" desc;