/*=====
    CS 325 - Week 10 Labs - 2016-10-26 - 11:00 am lab
=====*/

spool 325lab10-2-out.txt

/*=====
    order by

    *   allows you the specify the order that
        a result's rows will be displayed

    *   (selects never change your data!
        and so neither does its ORDER BY clause...!)

    *   really should only use with an
        *outermost* select...!
        (in terms of style, and in at least
        some SQL implementations, in terms of
        syntax...!)

    *   in its simplest form:
        *   follow ORDER BY with the name
            of the attribute whose value
            you wish the rows to be ordered
            by
            (by default, this will be in
            ASCending order)
=====*/

prompt see employee rows in order of salary

select   *
from     empl
order by salary;

prompt see employee rows in order of hiredate

select   *
from     empl
order by hiredate;

/*=====
    you can actually order by an attribute
    you are not projecting...!
=====*/

prompt can order these employee last names by salary without projecting
prompt    those salaries:

select   empl_last_name
from     empl
order by salary;

/*=====
    of course, you are only ordering what
    is projected from the actually-selected rows...
=====*/

prompt see the salaries and last names just of those with job title
prompt     of Manager, in order of their last names:

select   salary, empl_last_name
from     empl
where    job_title = 'Manager'
order by empl_last_name;

/*=====
    you can have a comma-separated list of
    attributes or appropriate expressions
    in an ORDER BY clause --

    you'll order FIRST by the first attribute given,
    and for rows with the SAME value of that
        first attribute,
        you'll order by the 2nd attribute,
        ...and so on
=====*/

prompt employee last name, job title, mgr, and hiredate
prompt     in order of job title:

select empl_last_name, job_title, mgr, hiredate
from   empl
order by job_title;

prompt employee last name, job title, mgr, and hiredate
prompt     in order of job title,
prompt     and for those with the same job title, in order
prompt     of mgr value:

select empl_last_name, job_title, mgr, hiredate
from   empl
order by job_title, mgr;

prompt employee last name, job title, mgr, and hiredate
prompt     in order of job title,
prompt     and for those with the same job title, in order
prompt     of mgr value,
prompt     and for those with the same job title and mgr value,
prompt     in order of hiredate:

select empl_last_name, job_title, mgr, hiredate
from   empl
order by job_title, mgr, hiredate;

/*=====
    it looks like, when ordering by something
    that happens to contain NULL, those rows
    tend to show up at the end...
=====*/

prompt notice what happens with NULL values when order by commission:

select empl_last_name, job_title, commission
from empl
order by commission;

prompt ...and when order by mgr value (note where employee King appears):

select *
from   empl
order by mgr;

/*=====
    you CAN specify that you want DESCending instead
    of ASCending

    you put that immediately after the column
    being ordered (before the comma if it is
    not the last column)
=====*/

prompt order in descending order of salary:

select *
from   empl
order by salary desc;

prompt order in descending order of job_title,
prompt     and for those with the same job title in 
prompt     ascending order of mgr number,
prompt     and for those with the same job title and mgr
prompt     in descending order of hiredate:

select *
from   empl
order by job_title desc, mgr, hiredate desc;

/*====
   ASC is the default, but you CAN specify
   it if you wish, also...
=====*/

prompt you get the same result when explicitly giving the default ASC for mgr:

select *
from   empl
order by job_title desc, mgr asc, hiredate desc;

prompt display in order of (increasing) salary,
prompt and for those with the same salary, in descending order of hiredate:

select *
from   empl
order by salary, hiredate desc;

/*====
   remember: order by should, by style if not by syntax,
   ONLY be used in an OUTERMOST select:

   ALSO COURSE STYLE:
   DO line up the outermost select's ORDER BY
   WITH that outermost select
====*/

prompt this FAILS, order by in a subselect does not
prompt really make sense

select *
from   empl
where  salary >
       (select min(salary)
        from   empl
        where job_title = 'Manager'
        order by salary);

prompt now properly placing the order by in the outer select,
prompt     to display these employees making more than the 
prompt     lowest-paid manager in order of their salaries

select *
from   empl
where  salary >
       (select min(salary)
        from   empl
        where job_title = 'Manager')
order by salary;


/*=====
    GROUP BY

    GROUP BY provides a way to "group" 
    rows sharing common characteristics
    usually so you can perform aggregate
    function computations on each group;

    *   WHEN you are using GROUP BY,
        you are allowed to project ONLY
        aggregate computations on the group
        OR that attribute(s) being grouped by

    *   COURSE STYLE STANDARD:
        use GROUP BY when you WANT such
        aggregate computation(s) on groups
        of rows...
=====*/

prompt show the average salary for all employees

select avg(salary)
from   empl;

prompt show the average salary for those with job title of Manager:

select avg(salary)
from   empl
where  job_title = 'Manager';

/*=====
    what if you'd like the average salary for
    EACH value of job_title
    (even if you don't happen to know right
    now what those values are?)

    GROUP BY lets you do this more conveniently!
=====*/

prompt group the selected rows by job_title,
prompt    and project the avg salary of each
prompt    group

select   avg(salary)
from     empl
group by job_title;

/*=====
   group by is the only way to get MULTIPLE rows
   when projecting aggregate function calls!

   AND: when using group by, you may project
   ONLY such aggregate function computations
   OR the attribute(s) being grouped-by
=====*/

prompt group the selected rows by job_title,
prompt    and project the job title and the avg salary of each
prompt    group (OK since grouping by job_title!):

select   job_title, avg(salary)
from     empl
group by job_title;

prompt FAILS, with group by you CANNOT project
prompt    attributes you are not grouping by:

select   job_title, mgr, avg(salary)
from     empl
group by job_title;

/*====
    make sure it is clear to you:
    the FROM and WHERE clauses are done
    BEFORE the GROUP BY;
=====*/

prompt show the dept name and the average salary for those working
prompt     in that department:

select   dept_name, avg(salary)
from     empl e, dept d
where    e.dept_num = d.dept_num
group by dept_name
order by avg(salary);

prompt FAILS, with GROUP BY you CANNOT project
prompt    an attribute NOT being grouped-by

select   d.dept_num, dept_name, avg(salary)
from     empl e, dept d
where    e.dept_num = d.dept_num
group by dept_name
order by avg(salary);

/*=====
    you CAN have a comma-separated list
    of attributes after group by --
    BUT each group is each distinct
    collection of those attributes
=====*/

prompt now grouping by BOTH d.dept_num AND dept_name:

select   d.dept_num, dept_name, avg(salary)
from     empl e, dept d
where    e.dept_num = d.dept_num
group by dept_name, d.dept_num
order by avg(salary);

/*=====
    count(*) returns the number of rows
    selected/or in that group
=====*/

prompt note that grouping by job title AND mgr value
prompt    (so a group is each distinct PAIR of these values):

select job_title, mgr, avg(salary), count(*)
from   empl
group by job_title, mgr;

/*====
    you may certainly use group by in a
    nested select...
====*/

prompt can have a group by in a sub-select, also;
prompt here, project last names and salaries of
prompt     employees making >= the minimum of the
prompt     average salaries of those in each department:

select empl_last_name, salary
from   empl
where  salary >=
       (select   min(avg(salary))
        from     empl
        group by dept_num);

prompt you can do aggregate computations of the
prompt    grouped-by aggregate computations;
prompt what is the minimum of the average salaries of
prompt    each department?

select min(avg(salary))
from   empl
group by dept_num;

/*=====
    HAVING

    only makes sense combined with GROUP BY

    HAVING lets you specify that you only 
    want to see groups that HAVE some
    particular condition

    HAVING is to groups 
    as WHERE is to rows --

    HAVING lets you "select" which GROUPS you want
=====*/

prompt project the average salary for each department

select dept_num, avg(salary)
from   empl
group by dept_num;

prompt project the average salary only for departments whose
prompt     average salary is more than 1500:

select dept_num, avg(salary)
from   empl
group by dept_num
having   avg(salary) > 1600;
 
prompt this FAILS, you are dealing with ROWS
prompt   from the FROM clause in the WHERE clause:

select dept_num, avg(salary)
from   empl
where  avg(salary) > 1600
group by dept_num;

spool off

-- end of 325lab10-2.sql