/*==========
   CS 325 - Week 9 Labs - 1:00 Lab - 2016-10-19
==========*/

spool 325lab09-2-out.txt

/*=====
    odd fact: you can project a literal value!
=====*/

prompt projecting the literal 'hi' for table dept

select 'hi'
from   dept;

/*=====
    looks useless, BUT can be used in other
    settings in a useful way...

    for example, there's a concatenation operator
    in SQL:

    || (two vertical bars with no spaces in between)

    and you can use this to concatenate and project
    the result!
=====*/

prompt dept_num concatenated with dept_name:

select dept_num || dept_name
from   dept;

prompt what if I concatenate a literal ' - ' between dept_num and dept_name?

select dept_num || ' - ' || dept_name
from   dept;

prompt how about adding a column alias:

select dept_num || ' - ' || dept_name "Departments"
from   dept;

/*=====
   imagine the possibilities!

   select last_name || ', ' || first_name

   select first_name || ' ' || last_name

   select city || ', ' || state || '  ' || zipcode

   select state || '(' || city || ')'

   YES you can also use this to
       get your database data in CSV format!
=====*/

prompt projecting department data in comma-separated/CSV format:

select dept_num || ', ' || dept_name || ', '
       || dept_loc
from   dept;

/*=====
   EXISTS predicate

   predicate - an operator that returns true or false

   IN is a predicate operator --
   it has a left-hand-side and a right-hand-side:

   job_title in ('Sales', 'Manager')
=====*/

prompt names of departments located in Chicago or Dallas:

select dept_name
from   dept
where  dept_loc in ('Chicago', 'Dallas');

/*=====
   ...EXISTS only has a right-hand-side!!!

   *   that RHS is a sub-select that has an interesting
       relationship with its outer select

   *   Sunderraman: "the EXISTS predicate is true
       if [its] sub-select presults in a non-empty
       set of [rows], and it is false otherwise"

       for EACH row in the outer select,
       it is checked to see if that row satisfies
       the EXISTS predicate --
       if, for that row, the sub-select has contents

    *   now -- why would the subselect have different
        results for one row in the outer select
        versus another?
        ...because EXISTS/NOT EXISTS SHOULD (!!!!!!)
        only be used with a so-called
        CORRELATED sub-select,
        a sub-select containing a CORRELATION CONDITION

        *   a correlation condition refers to 
            at least one attribute (or table?)
            from a table NOT in the FROM clause
            of that sub-select,
            but it IS in the FROM clause of the *outer*
            select

*   using EXISTS with a correlated sub-select,
    for each row in the outer select's FROM clause,
        the correlated sub-select is executed,
        and if ANY rows result,
        the EXISTS is true, and that row is 
        selected;
=====*/

prompt adding a Computer Sci row to dept

insert into dept
values
('600', 'Computer Sci', 'Arcata');

prompt project the locations and names of departments
prompt   with employees
prompt
prompt (for each department, do any employees
prompt EXIST for that department?)

select dept_loc, dept_name
from   dept
where  exists
       (select 'a'
        from   empl
        /*
            the below is NOT a join condition,
            because it refers to a table,
            dept, NOT in its FROM clause!
            It IS a correlation condition,
            because it refers to a table, dept,
            in its OUTER SELECT's from clause!
        */
        where  empl.dept_num = dept.dept_num);

/*=====
   I find (arguably) that NOT EXISTS is eve
   more useful than EXISTS...
=====*/

prompt which departments have NO employees?

select dept_loc, dept_name
from   dept
where  NOT exists
       (select 'a'
        from   empl
       where  empl.dept_num = dept.dept_num);

/*=====
    COURSE STYLE for exists, not exists
    *   thou SHALT only use these with
        correlated subqueries!

    *   thou shalt project literals in the
        correlated subqueries used with these

    *   thou shalt not abuse these
        to avoid writing (N-1) join conditions
	when joining N tables...!
=====*/

/*=====
    some COMMON errors involving EXISTS/NOT EXISTS

    ...leaving off the correlation condition!

    (you'll either get ALL rows or NO rows...)
=====*/

prompt OOPS, left off correlation condition with NOT EXISTS!
prompt (you might think it means all departments have employees,
prompt but Computer Sci department does not)
prompt
prompt (this will show no rows selected,
prompt even though Computer dept has no employees...)

select dept_loc, dept_name
from   dept
where  NOT exists
       (select 'a'
        from   empl);

prompt OOPS, left off correlation condition with EXISTS!
prompt (you might think it means all departments have employees,
prompt but Computer Sci department does not)
prompt
prompt (this will also select Computer Sci dept,
prompt even though Computer Sci dept has no employees...)

select dept_loc, dept_name
from   dept
where  exists
       (select 'a'
        from   empl);

/*=====
    another: do NOT use a join condition
    if a correlation condition is needed!
=====*/

prompt OOOPS, this will not work,
prompt inner select has a join condition
prompt NOT a correlation condition
prompt (might THINK it says all departments have employees,
prompt but Computer Sci department does not)

select dept_loc, dept_name
from   dept
where  NOT exists
       (select 'a'
        from   empl, dept
	where  empl.dept_num = dept.dept_num);

/*=====
    example showing more of the power of EXISTS...
=====*/

prompt using the HW 4 database,
prompt which client(s) have rented
prompt     BOTH 'Gone with the Wind' and 'Star Wars'?

select client_lname || ', ' || 
       client_fname "Windy Starry Client(s)"
from   client c
where  exists
       (select 'a'
        from   rental r
        where  c.client_num = r.client_num -- correl cond!
               and vid_id in 
                   (select vid_id
		    from   movie m, video v
                    where  m.movie_num = v.movie_num -- join cond!
		           and movie_title = 
                               'Gone with the Wind'))
       and exists
       (select 'a'
        from   rental r
        where  c.client_num = r.client_num -- correl cond! 
               and vid_id in
      	           (select vid_id
                    from   movie m, video v
                    where  m.movie_num = v.movie_num -- join cond!
                      	   and movie_title =
                                  'Star Wars'));

prompt double-check: what movies has Edie Beta rented?

select movie_title
from   movie m, video v, rental r
where  m.movie_num = v.movie_num
       and v.vid_id = r.vid_id
       and client_num IN
           (select client_num
            from   client
	    where  client_lname = 'Beta'
	           and client_fname = 'Edie');
			   	 	             
spool off

-- end of 325lab09-2.sql