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

spool 325lab08-1-out.txt

/*==========
    we're taking the WHERE clause
    (and the FROM clause)
    a LITTLE further this week;

    you CAN nest a select --
    a so-called sub-select --
    WITHIN a FROM clause,
    and WITHIN a WHERE clause

    (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: you need to surround the sub-select
    with parentheses!!
==========*/

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

prompt salary of the highest paid clerk(s)?

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

/*========
   what if you wanted the names of the
   Clerk(s) with that highest-clerk-salary?

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

/*========
   what if I want the manager id
   for clerks with the highest-clerk-salary?
========*/

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

/*========
    what if I want the name of this manager?

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

/*========
   nested selects certainly don't have to
   involve only 1 table...
========*/

prompt dept nums whose location is Dallas 

select dept_num
from   dept
where  dept_loc = 'Dallas';

/*========
   what if I'd like the last 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');

/*========
    COMMON ERROR:
    NOTE - aggregate functions cannot be
    called just anywhere...
========*/

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

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

/*========
   CAN use a nested select, instead 
========*/

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

select empl_num
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 ALSO WILL NOT WORK, MUST have parentheses
prompt around the sub-select
prompt

select empl_num
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_num, 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-1.sql