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

spool 325lab10-1-out.txt

/*=====
   adding some more SELECT clauses!

   order by
   *   ALL this does is let the user specify
       the ORDER in which the select's resulting
       rows will be displayed!

   *   it does NOT CHANGE the DATABASE in
       ANY WAY
       (selects in general do not change the
       database!)

   *   this clause ONLY makes SENSE on an
       outermost select (don't use within
       a nested select!)
       *   definitely poor style to use in
           a nested select;
           MAY be a syntax error, depending
           on your SQL implementation

*   simplest form:
    you follow 

    ORDER BY

    with the name of the attribute you'd like
    the resulting rows to be ordered by

    *   it will be in increasing, "ASCending"
        order by default
=====*/

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 sort by columns 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;

/*=====
    and you will order the rows resulting
    from the selection, of course
=====*/

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;

/*=====
    if you put multiple comma-separated
    attributes in the order by clause,

    it means, order by the first attribute,
    for rows with the same value of first attribute,
        order by the second attribute,
        etc.
=====*/

prompt employee info in order of job title:

select   *
from     empl
order by job_title;

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

select   *
from     empl
order by job_title, mgr;

prompt employee info 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   *
from     empl
order by job_title, mgr, hiredate;

/*=====
    what happens with NULL columns and ORDER BY?

    ...these tend to get put 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   empl_last_name, job_title, mgr
from     empl
order by mgr;

/*=====
    you can also specify to order in DESCending
    order (not stuck with ASCending...)

    put DESC directly after the attribute
    you want ordered in descending order
    (before the comma, if there's another
    attribute after it)

    (ASCending order is the default,
    BUT if you feel like it, you CAN put
    ASC after an attribute to make it
    CLEAR it is in ascending order
=====*/

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;

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;

/*=====
  class style: ONLY use ORDER BY in the
  outermost select...

  ALSO class style:
  ...and line it up WITH the outermost select!
=====*/

prompt PROPERLY gives an error for badly-placed order by in a subselect:

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

   purpose: to give you a way to "group" rows
   sharing some common characteristic so
   you can perform aggregate function computations
   ON each group;

   COURSE STYLE: there needs to be a GOOD REASON for
   using GROUP BY -- such as 
   performing aggregate function computations
   on each resulting group...
=====*/

prompt show the average salary for all employees

select avg(salary)
from   empl;

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

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

/*=====
    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 this will fail, can only project
prompt    an aggregate function call or
prompt    grouped-by attribute:

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

/*=====
    note that GROUP BY is conceptually done
    AFTER the FROM clause
    and AFTER the WHERE clause...

    (and before the ORDER BY clause, kind of...)
=====*/

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

/*=====
    you CAN group by more than one attribute --
    these will be comma-separated,
    and the group is based on ALL these
    attributes being the same for that group
=====*/

prompt FAILS, cannot project dept_num unless group by it

select   d.dept_num, dept_name, min(salary), max(salary),
         min(hiredate), max(hiredate)
from     empl e, dept d
where    e.dept_num = d.dept_num
group by dept_name;

prompt now grouping by BOTH d.dept_num AND dept_name:

select   d.dept_num, dept_name, min(salary), max(salary),
         min(hiredate), max(hiredate)
from     empl e, dept d
where    e.dept_num = d.dept_num
group by d.dept_num, dept_name;

/*=====
    count(*) counts the number of rows selected
    (when  NO group by)
    OR in a group (when WITH group by)
=====*/

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 when using GROUP BY
   ...it is a way to LIMIT which groups are
      "selected"

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

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