/*=====
    demos for Week 9 Asynchronous Material

    by: Sharon Tuttle
    last modified: 2021-10-19
=====*/

spool 325lect09-2-out.txt

/*=====
     select statement clause - ORDER BY

     another select statement optional clause!

     ORDER BY clause basically lets us specify the order we would
        like the resulting rows to be displayed
     IT DOES NOT CHANGE THE DATABASE TABLE(s) AT ALL.

     *   it MUST be the FINAL clause in a select statement!

     *   and (syntactically) it ONLY "belongs" to the OUTERMOST select!

     *   after order by, put the column whose values you want the
         rows ordered by
         ...by default, in ascending order
         (follow the column with DESC to order rows by DESCENDING order of
             that column
=====*/

prompt =====
prompt see empls ordered by salary (lowest to highest)
prompt =====

select   *
from     empl
order by salary;

prompt =====
prompt see empls ordered by salary (highest to lowest)
prompt =====

select   *
from     empl
order by salary desc;

/*=====
    what does it mean if I put a comma-separated list of
    column after order by?
    like in spreadsheet software, if two rows have the same
        value for the first order- by column,
        JUST those rows will be ordered by the next column, and if the
        first two are the same, JUST those are ordered by the next column
        and so on!
=====*/

prompt =====
prompt order empls by salary THEN by empl_last_name
prompt =====

select   *
from     empl
order by salary, empl_last_name;

/*=====
    need desc after EACH column you want to be descending...
=====*/

prompt =====
prompt in 1st-order by DESCending salary, in 2nd-order by ASCending empl_last_name
prompt =====

select   *
from     empl
order by salary desc, empl_last_name;

prompt =====
prompt in 1st-order by ASCending salary, in 2nd-order by DESCending empl_last_name
prompt =====

select   *
from     empl
order by salary, empl_last_name desc;

prompt =====
prompt in 1st-order by DESCending salary, in 2nd-order by DESCending empl_last_name
prompt =====

select   *
from     empl
order by salary desc, empl_last_name desc;

/*=====
    you can certainly have a where clause with this!
    just orders the selected rows!

    (yikes, though -- you CAN order by columns
    you are not projecting?!?)
=====*/

prompt =====
prompt showing last names of Sales employees, lowest to highest salary
prompt (yes, you can order by a column you are not projecting...!)
prompt =====

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

/*=====
    what happens if there are NULL values in a columns you
    are ordering by?
=====*/

prompt =====
prompt look what happens if you order by a column with null values in it:
prompt =====

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

/*=====
    GROUP BY

    use this when you want a multi-row result with
    AGGREGATE FUNCTION results for "grouping" of certain rows;

    CS 325 CLASS STYLE: do NOT use group by unless you
    you want to compute one or more aggregate functions on the
    grouped-by rows!
=====*/

prompt =====
prompt average salary of all employees:
prompt =====

select avg(salary)
from   empl;

prompt =====
prompt average salary of managers:
prompt =====

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

prompt =====
prompt average salary of analysts:
prompt =====

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

-- what if what I really want is the average salary for
--    EACH job_title?
-- group by lets me do that!
--
-- NOTE: with group by, you can project the grouped-by attribute!
--     and aggregate functions on the selected row per group!
--     

prompt =====
prompt with GROUP BY, can get average salary for employees based on
prompt on their job title, for ALL job titles:
prompt =====

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

prompt =====
prompt with GROUP BY, can get average salary for employees based on
prompt their dept_num, for ALL dept_nums:
prompt =====

select   dept_num, avg(salary)
from     empl
group by dept_num;
   
-- WHERE clause selects rows BEFORE group by groups them

prompt =====
prompt BUT note: WHERE clause selects rows BEFORE group-by groups them;
prompt this gives, JUST for employees hired AFTER January 1, 2015,
prompt the maximum salary and count by job title:
prompt =====

select job_title, max(salary), count(*)
from   empl
where  hiredate > '01-Jan-2015'
group by job_title;

-- if you group by more than one column, but you get a group for
--     each unique COMBO of values

prompt =====
prompt if your group by more than one column, there is a group for each
prompt different combination of those columns;
prompt =====
prompt here, get average salary and count for each different pair of
prompt job title and mgr columns:
prompt =====

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

-- remember that WHERE clause gets done BEFORE group by!!!
-- oh no! what if you don't want ALL the groups?
--    how can you choose JUST some groups?
--    THAT's why we have the HAVING clause! <-- lets you select
--       from GROUPS rather than rows
--
-- select ROWS using WHERE,
-- "select" GROUPS using HAVING (and group by)

prompt =====
prompt average salary for each dept_num (with the help of group by):
prompt =====

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

prompt =====
prompt having (used WITH group by) lets you specify WHICH groups you want included;
prompt here, want average salary for each dept_num,
prompt BUT only for dept_num groups whose average salary is MORE than 1500:
prompt =====

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

-- YES all these clauses can be in ONE select!

prompt =====
prompt JUST for empls hired BEFORE July 15, 2017,
prompt want average salary and count by dept_num
prompt BUT only for dept_num values whose average salary for these particular employees
prompt exceeds 1500, and order these rows from highest to lowest average salary...!
prompt =====

select dept_num, avg(salary), count(*)
from   empl
where  hiredate < '15-Jul-2017'
group by dept_num
having avg(salary) > 1500
order by avg(salary) desc;

spool off