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

spool 325lab09-1-out.txt

/*=====
    gee, did you know that you can project
    literal values?
=====*/

prompt projecting the literal 'hi' for table dept 

select 'hi'
from   dept;

/*=====
    looks useless -- BUT it has its uses!

    for example, you can use that with concatenation
    to project some nice results...

    || (two vertical bar chars, NO space in between)
    can be used to concatenate two expression
    into a single expression
    (and you CAN project that 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 || ')'

   ...and YES you can build a CSV file from
      your database data using this!
*/

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

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

/*======
    the EXISTS predicate

    predicate: a function/operator that returns
        true or false

    (IN is a predicate, for example)

    IN has a left-hand-side and a right-hand side
*/

prompt names of departments located in Chicago or Dallas:

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

/*=====
    but, EXISTS *just* has a right-hand-side...!

    where EXISTS (correlated_subquery)

                  ^ this subquery has an interesting
                    relationship with the outer select!

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

        For EACH row in the outer select,
        IF that row results in EXISTS being true,
        that row IS selected;
        ...otherwise it ISN'T.

    *   how can this be different for each row,
        with EXISTS?
        because EXISTS *SHOULD* <-- !!!!!!!!!!!!!!!!!
           be used with a subselect containing
           a CORRELATION condition
           (making that subselect a correlated sub-select)

        *   a CORRELATION condition is one that refers
            to at least one attribute from a table
            NOT in that sub-select...!

            ^ can't run THESE sub-selects by themselves,
              they'll ONLY work when they are "within"
              a select including the table they don't have
=====*/

prompt adding a Computer Sci row to dept

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

prompt which departments currently have 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
        /*
            this is NOT a join condition, because
                 its FROM does not include dept!
            It IS a CORRELATION condition,
                 because it refers to a table, dept,
            NOT in ITS from clause, BUT in it
                 OUTER SELECT's from clause!  
        */
        where  empl.dept_num = dept.dept_num);

/*=====
    I've found NOT EXISTS even (arguably)
    more useful than EXISTS --

    this will be satisfied if there are NO
    rows in the correlated sub-select
    for a row in the outer select;
=====*/

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 use these WITH
        correlated subqueries!

    *   thou SHALT project a literal in
        those correlated subqueries!

    *   thou SHALT NOT use this to kluge
        avoiding writing join conditions
        in joins!
        (when you are joining N tables,
        you are EXPECTED to write/include
        N-1 join conditions!)
=====*/

/*=====
    so many ways to mess these up!

    what if you leave off the correlation condition?
=====*/

prompt OOOPS I left off the 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 error is to turn the correlation condition
    INTO a join condition...

    ...the correlation condition MUST reference a
    table "outside" of its sub-select to BE
    a correlation condition!
=====*/

prompt OOOPS I turned my correlation condition INTO
prompt a join condition!
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, dept
        where  empl.dept_num = dept.dept_num); 

/*=====
     beefier example:
     has any client (from HW 4 tables)
     rented BOTH Gone with the Wind AND Star Wars?
=====*/

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 "Starry Wind 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 has Edie Beta really rented both?

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-1.sql