/*==========
   CS 325 - Week 8 Labs - 3:00 Lab - 2016-10-12 

   (NOTE: ADDED working versions of several queries
   discussed on the whiteboard while waiting for
   nrs-projects to come back up...!)
==========*/

spool 325lab08-2-out.txt

/*==========
    it turns out that you can use a SELECT
    inside of a SELECT to project values
    that you want;

    when you have a select within a select,
    that's a sub-select or a NESTED select;

    (and we'll sometimes talk about OUTER
    select and an INNER select
    where the OUTER select is the select
    that contains another select,
    which is the inner select,
             the sub-select,
	           the nested select...!)
    *   although you can also nest selects as deeply
        as you'd like...!

    *   NOTE that a sub-select does
        need to be surrounded by parentheses!
==========*/

/*========
   can be helpful to build these from the
   "inside" out;
========*/

prompt what is the salary of the highest-paid clerk(s)? 

select max(salary)
from   empl
where  job_title = 'Clerk';

/*========
   what if I would like the names of these clerks? 

   turns out, you can USE a select to
   project a value USED as part of an expression!
========*/

prompt names of clerk(s) with the highest clerk salary

select empl_last_name
from   empl
where  job_title = 'Clerk'
       and salary = (select max(salary)
                     from   empl
		     where job_title = 'Clerk');

/*========
    and don't forget the parentheses around a nested select...
========*/

prompt WILL NOT WORK, subselect NEEEEEDS parentheses
prompt around it:
prompt

select empl_last_name
from   empl
where  job_title = 'Clerk'
       and salary =  select max(salary)
                     from   empl
                     where job_title = 'Clerk';

/*========
    I decide I'd like to know who manages
    these clerks
========*/

prompt the id of the manager who manages the highest-paid clerk(s)

select mgr
from   empl
where  job_title = 'Clerk'
       and salary =  (select max(salary)
                      from   empl
                      where job_title = 'Clerk');

/*========
   and what if I would like that manager's name? 

    ...well, I could use the above in the
    WHERE clause of another select...
========*/

prompt name of the manager(s) of the highest paid clerk(s)

select empl_last_name "Clerk Manager"
from   empl
where  empl_num IN 
       (select mgr
        from   empl
        where  job_title = 'Clerk'
               and salary =  
                   (select max(salary)
                    from   empl
                    where job_title = 'Clerk'));

/*========
   these can of course involve multiple tables 
========*/

prompt what are the numbers of departments located in Dallas? 

select dept_num
from   dept
where  dept_loc = 'Dallas';

/*========
     and what are the names and salaries of
     employees who work in Dallas? 
========*/

prompt last names and salaries of employees who work in Dallas

select empl_last_name, salary
from   empl
where  dept_num in (select dept_num
                    from   dept
		    where  dept_loc = 'Dallas');

/*========
     some common errors...
========*/

/*========
   first: you can't just use aggregate functions
   willy-nilly in a query...
========*/

prompt WILL NOT WORK, must called aggregate/group
prompt    function max appropriately; for example, in a select clause!
prompt

select empl_last_name
from   empl
where  job_title = 'Clerk'
       and salary = max(salary);

prompt WILL work: put the call to max in a nested select

select empl_last_name
from   empl
where  job_title = 'Clerk'
       and salary = (select max(salary)
                     from   empl
		     where  job_title = 'Clerk');

/*========
    ANOTHER common error: if projecting an aggregate
        function call, cannot also project an attribute...!

        (aggregate function is a SINGLE value for the selected rows,
	 attribute might have a value for EACH selected row...!)
========*/

prompt ALSO WILL NOT WORK, cannot project
prompt "plain" attributes along with aggregate
prompt function calls
prompt

select empl_last_name, max(salary)
from   empl
where  job_title = 'Clerk';

/*========  
    another thing to watch out for:

    how many results might your sub-select project?

    NOTE that 
    =  > < >= <= != <>

    ONLY work with a SINGLE value on their
    right-hand-side!
========*/

prompt WILL NOT WORK, = cannot have >1 value on RHS
prompt

select empl_last_name
from   empl
where  empl_num = (select mgr
                   from   empl
		   where  job_title = 'Clerk');

prompt when a sub-select might project more than
prompt    one value, IN works where = does not:

select empl_last_name
from   empl
where  empl_num IN (select mgr
                    from   empl
		    where  job_title = 'Clerk');

/*========
   SO: if in doubt, use IN instead of = ... 8-) 
========*/

/*========
   since relational operations return relations,
   and since you can put expressions-representing-relations
       in the FROM clause,
   that means yes, you CAN put a nested select
       in the FROM clause!
========*/

prompt selecting employee last names and dept names with
prompt the help of a nested select in a FROM clause:

select empl_last_name, dept_name
from   (select *
        from   empl e, dept d
	where  e.dept_num = d.dept_num)
where  dept_name = 'Sales';

/*========
   of course, the outer select DEPENDS on the
   relation(s) in the FROM clause...

   (column aliases in the nested select in the FROM?
   gotta use those in the outer select...)
========*/

prompt demo: if sub-select in FROM uses a column alias,
prompt that alias MUST also be used in the outer select!

select ename, dname
from   (select empl_last_name ename, dept_name dname
        from   empl e, dept d
	where  e.dept_num = d.dept_num)
where  dname = 'Sales';

/*========
   a few more nested examples 
========*/

/*========
   ultimate question:
   select the rows for employees who are clerks
   making more than the lowest-paid sales person
========*/

prompt minimum salary for anyone in Sales

select min(salary)
from   empl
where  job_title = 'Sales';

prompt info for clerks making more than the lowest-paid Sales person

select *
from   empl
where  job_title = 'Clerk'
       and salary > (select min(salary)
                     from   empl
		     where  job_title = 'Sales');

prompt  who is/are the manager(s) of such clerks? 

select empl_last_name
from   empl
where  empl_num IN (select mgr
                    from   empl
                    where  job_title = 'Clerk'
                           and salary > (select min(salary)
             		                 from   empl
                                         where  job_title = 'Sales'));

/*========
     by the way,
     you CAN have an equi-join of
     more than 2 tables,
     BUT!!!!!
     for joining N tables,
     ...NEEEEEED (N-1) join conditions!!!!!!!!!!!!!!!!!!
========*/

prompt example joining THREE tables using TWO join conditions,
prompt customer last names, the employee who reps them,
prompt and that employee's department:

select cust_lname, empl_last_name, dept_name
from   customer, empl, dept
where  empl.dept_num = dept.dept_num
       and customer.empl_rep = empl.empl_num;

spool off

-- end of 325lab08-2.sql