/*=====
    CS 325 - Week 11 Asynchronous Material 

    last modified: 2021-11-03
=====*/

prompt =====
prompt start with a "clean" set up empl, dept, customer tables
prompt =====

start set-up-ex-tbls.sql

prompt =====
prompt (but not starting spooling until NOW... 8-) )
prompt =====

spool 325lect11-2-out.txt

prompt =====
prompt here is one way to join empl and dept and project empl names
prompt and dept names:
prompt =====

select empl_last_name, dept_name
from   empl, dept
where  empl.dept_num = dept.dept_num;

prompt =====
prompt here is another way of writing the same operations,
prompt using ANSI join notation:
prompt =====

select empl_last_name, dept_name
from   empl JOIN dept ON (empl.dept_num = dept.dept_num);

prompt =====
prompt OH, the parentheses are NOT required around the join condition
prompt for Oracle implementation of ANSI join syntax:
prompt =====

select empl_last_name, dept_name
from   empl JOIN dept ON empl.dept_num = dept.dept_num;

prompt =====
prompt YES you can have table aliases using ANSI join syntax, also
prompt =====

select empl_last_name, dept_name
from   empl e JOIN dept d ON e.dept_num = d.dept_num;

prompt =====
prompt what if you want:
prompt the name of a customer,
prompt the name of the employee who represents them,
prompt and the name of the department that employee works for?
prompt =====

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

prompt =====
prompt using ANSI join syntax:
prompt =====

select cust_lname, empl_last_name, dept_name
from   customer c
         JOIN empl e ON c.empl_rep = e.empl_num
         JOIN dept d on e.dept_num = d.dept_num;

prompt =====
prompt either form can have a where clause, group by, order by, etc.
prompt version 1:
prompt =====

select cust_lname, empl_last_name, dept_name
from   customer c, empl e, dept d
where  c.empl_rep = e.empl_num
       and e.dept_num = d.dept_num
       and empl_last_name = 'Michaels'
order by cust_lname;

prompt =====
prompt version 2:
prompt =====

select cust_lname, empl_last_name, dept_name
from   customer c
         JOIN empl e ON c.empl_rep = e.empl_num
         JOIN dept d on e.dept_num = d.dept_num
where  empl_last_name = 'Michaels'
order by cust_lname;

/*====
    SQL views - "derived" tables, generated as referenced!

    basic syntax:

    create view view_name as
        desired_select_statement;

    ...and you drop a view from the DBMS using

    drop view view_name;

=====*/

prompt =====
prompt dropping and creating view short_empl:
prompt =====

drop view short_empl;

create view short_empl as
    select empl_last_name, job_title
    from   empl;

prompt =====
prompt using view short_empl:
prompt =====

select *
from   short_empl;

prompt =====
prompt committing before a temporary change:
prompt =====

commit;

prompt =====
prompt deleting all clerks:
prompt =====

delete from empl
where  job_title = 'Clerk';

prompt =====
prompt BUT see? view short_empl, re-generated each time used,
prompt properly reflects the changed contents:
prompt =====

select *
from   short_empl;

prompt =====
prompt rolling back that delete:
prompt =====

rollback;

prompt =====
prompt dropping and creating view short_empl2, defining
prompt column aliases in the select:
prompt =====

drop view short_empl2;

create view short_empl2 as
    select empl_last_name last_name, job_title job
    from   empl;

prompt =====
prompt querying short_empl2:
prompt =====

select *
from   short_empl2;

prompt =====
prompt projecting just job, last_name from short_empl2,
prompt ordering results by job:
prompt =====

select job, last_name
from   short_empl2
order by job;

prompt =====
prompt a case where a column in the view MUST be renamed,
prompt when projecting an aggregate function result or a computation:
prompt =====

drop view salary_avgs;

create view salary_avgs	as
    select job_title, avg(salary) job_avg_salary
    from   empl
    group by job_title;

prompt =====
prompt now querying view salary_avgs:
prompt =====

select *
from   salary_avgs;

prompt =====
prompt using other syntax for naming columns, as drop and
prompt create view salary_avgs2:
prompt =====

drop view salary_avgs2;

create view salary_avgs2(job_title, job_avg_salary) as
    select job_title, avg(salary)
    from   empl
    group by job_title;

prompt =====
prompt querying salary_avgs2:
prompt =====

select *
from   salary_avgs2;

spool off