/*=====
    demos for Week 10 Asynchronous Material - Part 2

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

spool 325lect10-2-out.txt

prompt =====
prompt union of department numbers in Chicago, and of employees
prompt who are managers
prompt =====

(select dept_num
 from   dept
 where  dept_loc = 'Chicago')
union
(select dept_num
 from   empl
 where  job_title = 'Manager');

prompt =====
prompt hey let us view these in reverse order of dept_num
prompt =====

(select dept_num
 from   dept
 where  dept_loc = 'Chicago')
union
(select dept_num
 from   empl
 where  job_title = 'Manager')
order by dept_num desc;

prompt =====
prompt here is an example where union is REALLLLY useful:
prompt what if you want to see "total pay" for ALL employees,
prompt when only some have non-NULL commissions?
prompt union along with projecting a literal can make this quite reasonable!
prompt =====

prompt =====
prompt remember, this did NOT work: (will not do computations with null)
prompt =====

select empl_last_name, salary + commission
from   empl;

prompt =====
prompt BUT this can work: (note the outside ORDER BY only "sees"
prompt the final result! Use the column alias it can "SEE"!)
prompt =====

(select empl_last_name "Employee", salary "Total Pay"
 from   empl
 where  commission is null)
union
(select empl_last_name, salary + commission
 from   empl
 where  commission is NOT null)
order by "Total Pay";

/*---
    FUN FACT: SQL also gives you the option of a "non-true" UNION
    with TWO copies of the row if it is in both sub-selects ...!

    use UNION ALL to get that (!!)
---*/

prompt =====
prompt UNION ALL will let me have a non-truly-relational union
prompt including BOTH copies of a tuple from both sub-selects:
prompt =====

(select dept_num
 from   dept
 where  dept_loc = 'Chicago')
union ALL
(select dept_num
 from   empl
 where  job_title = 'Manager');

/*---
    trying out INTERSECT!
---*/

prompt =====
prompt the INTERSECTION of employees hired after 2013-01-01 intersected with
prompt employees located in Dallas (just project out the employee last name,
prompt dept_num, and hiredate)
prompt =====

(select empl_last_name, dept_num, hiredate
 from   empl
 where  hiredate < '01-Jan-2013')
intersect
(select empl_last_name, e.dept_num, hiredate
 from   empl e, dept d
 where  e.dept_num = d.dept_num
        and dept_loc = 'Dallas')
order by empl_last_name;

/*---
    trying out MINUS!
---*/

prompt =====
prompt the DIFFERENCE of employees hired after 2013-01-01 "minus"
prompt employees located in Dallas (just project out the employee last name,
prompt dept_num, and hiredate)
prompt =====

(select empl_last_name, dept_num, hiredate
 from   empl
 where  hiredate < '01-Jan-2013')
minus
(select empl_last_name, e.dept_num, hiredate
 from   empl e, dept d
 where  e.dept_num = d.dept_num
        and dept_loc = 'Dallas')
order by empl_last_name;

prompt =====
prompt order of the operands MATTERS with minus operation!
prompt
prompt the DIFFERENCE of employees located in Dallas "minus" employees
prompt hired after 2013-01-01 (just project out the employee last name,
prompt dept_num, and hiredate)
prompt =====

(select empl_last_name, e.dept_num, hiredate
 from   empl e, dept d
 where  e.dept_num = d.dept_num
        and dept_loc = 'Dallas')
minus
(select empl_last_name, dept_num, hiredate
 from   empl
 where  hiredate < '01-Jan-2013')
order by empl_last_name;

/*=====
    hey, we DO change rows in tables in databases in operational
    databases!

    we've inserted rows;
    we can also UPDATE the contents in existing rows,
    and we can also DELETE rows (but still have a table)

    SQL UPDATE command: lets you MODIFY one or more existing rows in a table
    SQL DELETE command: lets you REMOVE one or more existing rows from a table
 
    =====
    basic UPDATE syntax:

    UPDATE desired_tbl_name
    SET    desired_attrib = expr1
    WHERE  expr2;

    semantics: in desired_tbl_name, 
        for each row for which expr2 is true,
        set desired_attrib to the value of exp1

    *   the SET and WHERE clauses can be as complex as you'd like...!
---*/

prompt =====
prompt current employee last names, job titles, salaries, and hiredates:
prompt =====

select empl_last_name, job_title, salary, hiredate
from   empl;

prompt =====
prompt updating empl: for employees whose salary is LESS than the
prompt average salary of a Sales person, change their hiredate to the
prompt maximum hiredate of any employee:
prompt =====

update empl
set    hiredate = (select max(hiredate)
                   from   empl)
where  salary < (select avg(salary)
                 from   empl
                 where  job_title = 'Sales');

prompt =====
prompt afterwards, here are employee last names, job titles, salaries, and hiredates;
prompt see those who have a changed hiredate to latest hiredate?
prompt =====

select empl_last_name, job_title, salary, hiredate
from   empl;

prompt =====
prompt remember, you can UNDO a change such as an insert, update, or
prompt delete IF you have not yet committed that change (either with a
prompt commit command or by changing a table's structure or exiting sqlplus)
prompt by using the SQL rollback command.
prompt
prompt Now rolling back the above update:
prompt =====

rollback;

/*---
    basic DELETE syntax:

    DELETE FROM desired_tbl_name
    WHERE  expr1;

    semantics: for each row for which expr1 is true,
        delete that row!

    *   where clause is OPTIONAL -- but without it,
        ALL rows will be deleted (but the table remains!!!)

    *   and, that expr1 in the WHERE can be as big'n'fancy as you'd like!
---*/

prompt =====
prompt current employee last names, job titles, salaries, and hiredates:
prompt =====

select empl_last_name, job_title, salary, hiredate
from   empl;

prompt =====
prompt deleting rows of Clerks whose salary is more than the average
prompt salary for a Clerk:
prompt ===== 

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

prompt =====
prompt afterwards, here are employee last names, job titles, salaries, and hiredates;
prompt see how there are now 12 rows, and two fewer Clerks? (the 2 highest-paid?)
prompt =====

select empl_last_name, job_title, salary, hiredate
from   empl;

prompt =====
prompt I am now going to rollback that change... 8-)
prompt =====

rollback;

-- see more examples in the SQL Reading Packet 6!

-- also you can read a little about the ALTER command,
-- which you can use (sometimes) to CHANGE the structure of a table
-- after it is created

-- we will have to come back to sequences later...!

spool off