/*======
  union of empl last names and job titles
  of people with job title of Manager,

  and empl last names and job titles
  of people who work 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');

/*=====
    notice that UNION does do a "true" union,
    in the sense that you get a "real" relation,
    with each row only appearing once even if
    it is in both sets;

    interestingly, you can use
    UNION ALL
    such that rows in BOTH sets will appear
    TWICE in the result...!
=====*/

(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');

/*=====
    so, the sub-selects MUST be union-compatible
=====*/

prompt will FAIL, not union compatible!
prompt (both sub-selects need SAME number of cols)

(select empl_last_name
 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');

prompt will FAIL, not union compatible!
prompt (the attributes need to have compatible
prompt domains)

(select empl_last_name, salary
 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');

prompt UNFORTUNATELY, the DBMS can't take
prompt domain compatibility as far as one
prompt might like...

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

/* demoing intersection */

(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');

/*===== demoing difference =====*/

(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');                     

/*=====
    it appears that (at least sometimes) you do not
    HAVE to have parentheses around the sub-selects...
=====*/

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

/*====
   notice that, even if it does not look like it
   in this UGLY version,  
   the order by is STILL ordering the RESULT of
   the minus 

   (it is NOT considered part of the
   second sub-select -- 
 
   note the use of the
   jt alias from the minus'd result)
=====*/

select empl_last_name, job_title jt                        
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'
order by jt;

/*=====
   notice that the result's column names
   are from the 1st sub-select

   an order by ONLY "sees" the resulting relation,
   you must write it accordingly
=====*/

(select empl_last_name
 from   empl
 where  job_title = 'Manager')
union
(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 FAILS, order by only sees attribute names from 1st
prompt sub-select

(select empl_last_name
 from   empl
 where  job_title = 'Manager')
union
(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, order by only sees attribute names from 1st
prompt sub-select

(select empl_last_name names
 from   empl
 where  job_title = 'Manager')
union
(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
(select job_title
 from   empl e join dept d
        on e.dept_num = d.dept_num
 where  dept_loc = 'Dallas')
order by names;

/*=====
    notice:

    you can often use a UNION in OR situations

    you can often use an INTERSECT in AND situations

    ...here's an example where you could carefully use
       MINUS instead of NOT EXISTS:
=====*/

prompt which departments have no employees? take 2

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

/*=====
   another handy use of UNION

   ...note that you cannot get a count of 0
   JUST with an equi-join...
=====*/

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

(select dept_name, count(*)
 from   empl e join dept d
        on e.dept_num = d.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 count(*);

(select dept_name, count(*) num_empls
 from   empl e join dept d
        on e.dept_num = d.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;

(select dept_name, count(*) "# of Employees"
 from   empl e join dept d
        on e.dept_num = d.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";