/*=====
    CS 325 - Week 7 Asynchronous Materials

    last modified: 2021-10-05
=====*/

spool 325lect07-2-out.txt

-- consider:

prompt =====
prompt the salary of the highest-paid clerk:
prompt =====

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

-- WHAT IF: I actually want the last name(s) and salary(ies) of
--     the Clerk who has the highest salary?
-- I can ask for rows where job_title is 'Clerk'
--     and salary is the result of a sub-query,
--     asking for the maximum salary of a clerk:

prompt =====
prompt the name, salary, and mgr number of the highest-paid clerk:
prompt =====

select empl_last_name, salary, mgr
from   empl
where  job_title = 'Clerk'
       and salary = (select max(salary)
                     from   empl
                     where  job_title = 'Clerk');

-- If I'd like that clerk's manager? 

prompt =====
prompt the mgr number of the highest-paid clerk:
prompt =====

select mgr
from   empl
where  job_title = 'Clerk'
       and salary = (select max(salary)
                     from   empl
                     where  job_title = 'Clerk');

-- Oh, I'd like the NAME of that manager? of the highest-paid clerk?
-- I can use another sub-select:

prompt =====
prompt the last name and mgr number of the highest-paid clerk:
prompt =====

select empl_last_name, empl_num
from   empl
where  empl_num IN (select mgr
                    from   empl
                    where  job_title = 'Clerk'
                           and salary = (select max(salary)
                                         from   empl
                                         where  job_title = 'Clerk'));

/*=====
    sub-selects do NOT have to involve all the same table;
    BUT their *results* need to make sense in the place where you
    put the sub-select;
=====*/

prompt =====
prompt I want to know which employees work in Dallas.
prompt =====

prompt =====
prompt I *could* start by asking: what departments are in Dallas?
prompt =====

select dept_num
from   dept
where  dept_loc = 'Dallas';

prompt =====
prompt then I could use this select as a sub-select in another
prompt query, to find out the last names of employees who work
prompt in Dallas:
prompt =====

select empl_last_name
from   empl
where  dept_num IN (select dept_num
                    from   dept
                    where  dept_loc = 'Dallas');

/*=====
    examples of some common errors...
=====*/

prompt =====
prompt you have to be careful where you use aggregate function calls;
prompt THIS causes an error:
prompt =====

select empl_last_name
from   empl
where  job_title = 'Clerk'
       and salary = max(salary);

prompt =====
prompt what if you want to know the names of Clerks making
prompt more than the salary of the average-paid Clerk?
prompt =====

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

prompt =====
prompt be careful; you need both instances of job_title = 'Clerk' here!
prompt =====

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

prompt =====
prompt be careful; you need both instances of job_title = 'Clerk' here!
prompt =====

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

/*=====
    BE CAREFUL:
    = < > <= >= != <>
    ...can only have ONE value for EACH of their operands!

    (why it sometimes matters whether you use = or IN for a sub-select!
    IN can have a set of values for its right-hand-side operand,
    BUT = can only have ONE value for its right-hand-side operand)
=====*/

prompt =====
prompt error you will see if you use a subquery that returns more than
prompt one row with the = operator:
prompt =====

select empl_last_name
from   empl
where  empl_num = (select mgr
                   from   empl
                   where  job_title = 'Sales'
                          and salary = (select min(salary)
                                        from   empl
                                        where  job_title = 'Sales'));

prompt =====
prompt but if I use IN instead of = here, this query works:
prompt =====

select empl_last_name
from   empl
where  empl_num IN (select mgr
                    from   empl
                    where  job_title = 'Sales'
                           and salary = (select min(salary)
                                         from   empl
                                         where  job_title = 'Sales'));

/*======
   another place you can put a sub-select:
   ...in a FROM clause!

   you can put names of tables, or EXPRESSIONS whose values are tabular,
   in a FROM clause! SO, a select results in tabular data,
   so a sub-select CAN be in a FROM clause
=====*/

prompt =====
prompt rather silly example of a sub-select in a FROM clause:
prompt =====

select empl_last_name, dept_name
from   (select *
        from   empl e, dept d
        where  e.dept_num = d.dept_num)
where  dept_name = 'Operations';

/*=====
   note, though: the outer select only SEES the columns as they
   are projected from the subquery in the FROM clause;
=====*/

prompt =====
prompt another rather silly example of a sub-select in a FROM clause:
prompt =====

select ename
from   (select empl_last_name ename, dept_name dname
        from   empl e, dept d
        where  e.dept_num = d.dept_num)
where   dname = 'Operations';

prompt =====
prompt this causes an error; outer select only knows what is projected
prompt from subselect in its FROM clause:
prompt =====

select empl_last_name
from   (select empl_last_name ename, dept_name dname
        from   empl e, dept d
        where  e.dept_num = d.dept_num)
where   dname = 'Operations';

prompt =====
prompt this causes an error; outer select only knows what is projected
prompt from subselect in its FROM clause:
prompt =====

select ename
from   (select empl_last_name ename, dept_name dname
        from   empl e, dept d
        where  e.dept_num = d.dept_num)
where   dept_name = 'Operations';

prompt =====
prompt this causes an error; outer select only knows what is projected
prompt from subselect in its FROM clause:
prompt =====

select ename, salary
from   (select empl_last_name ename, dept_name dname
        from   empl e, dept d
        where  e.dept_num = d.dept_num)
where   dname = 'Operations';

/*======
    BIZARRE ASIDE: more projecting options:
    projecting literals,
    and concatenation

    IF you project a literal -- say a number or a string --
    then you'll project that for each row selected by that
    select statement:
======*/

prompt =====
prompt rather silly example of projecting a literal!
prompt =====

select 'moo'
from   empl;

prompt =====
prompt another rather silly example of projecting a literal!
prompt =====

select 35
from   empl
where  job_title = 'Manager';

/*=====
    USEFUL use for this: is with CONCATENATION

    ||   <-- that's TWO vertical bars, no space in between

    ...is SQL's concatenation operator
=====*/

prompt =====
prompt concatenating empl_last_name and salary:
prompt =====

select empl_last_name || salary
from   empl;

prompt =====
prompt concatenating empl_last_name, a dash, and salary:
prompt =====

select empl_last_name || '-' || salary
from   empl;

prompt =====
prompt concatenating empl_last_name, a dash and $, and salary:
prompt =====

select empl_last_name || '-$' || salary
from   empl;

prompt =====
prompt concatenating empl_last_name, a dash and $, and salary,
prompt now with a column alias, also:
prompt =====

select empl_last_name || '-$' || salary "Empl-Salary"
from   empl;

prompt =====
prompt quick example of using concatenation to generate "CSV",
prompt comma-separated-values, output from a database:
prompt =====

select empl_last_name || ',' || job_title || ',' || salary "csv output"
from   empl;

/*=====
    the EXISTS predicate - another predicate useful with nested selects!

    EXISTS has no left-hand-side operand, just a right-hand-side;
    and (quoting Sunderraman) when EXISTS is used in a WHERE clause,
        the EXISTS predicate, for each row of the outer select,
        "the EXISTS predicate is true if [its] sub-select results
        in a NON-EMPTY set of values, and it is false otherwise"

    this NEEEEEEDS to be used with a correlation condition

    (and NOT EXISTS is true if the sub-select has NO values in it!)
=====*/

prompt =====
prompt temporarily inserting a new department 600, a Computer department
prompt in Arcata (new, so has not employees yet):
prompt =====

insert into dept
values
('600', 'Computer', 'Arcata');

prompt =====
prompt the names and locations of departments WITH employees
prompt =====

select dept_loc, dept_name
from   dept
where  exists
       (select 'a'
        from   empl
        where  empl.dept_num = dept.dept_num);

prompt =====
prompt the names and locations of departments WITHOUT employees
prompt =====

select dept_loc, dept_name
from   dept
where  NOT exists
       (select 'a'
        from   empl
        where  empl.dept_num = dept.dept_num);

/*=====
    COURSE STYLE STANDARD: we'll project a literal in the sub-queries
    used with EXISTS or NOT EXISTS,
    since these predicates don't care WHAT is in the resulting set,
    JUST if it is empty or not;
=====*/

/*=====
    COMMON ERROR 1:
    leaving out the correlation condition!
=====*/

prompt =====
prompt ERROR: does NOT correctly return
prompt the names and locations of departments WITHOUT employees
prompt (it neeeeeeds a correlation condition!)
prompt =====

select dept_loc, dept_name
from   dept
where  NOT exists
       (select 'a'
        from   empl);

prompt =====
prompt ERROR: does NOT correctly return
prompt the names and locations of departments WITHOUT employees
prompt (it has a join condition instead of a correlation condition!)
prompt =====

select dept_loc, dept_name
from   dept 
where  NOT exists
       (select 'a'
        from   empl, dept
        where  empl.dept_num = dept.dept_num);

prompt =====
prompt can use table aliases to make sure you have a correlation
prompt condition when a table is in the outer and an inner subquery:
prompt =====

select dept_loc, dept_name
from   dept outer_dept
where  NOT exists
       (select 'a'
        from   empl, dept
        where  empl.dept_num = dept.dept_num
               and empl.dept_num = outer_dept.dept_num);

/*=====
    we'll be discussing this later --
    BUT if I have changed table CONTENTS during a sqlplus session,
    and I want to UNDO those changes,
    I can ROLL BACK the database to its state at the time of the
        previous commit or auto-commit 
        (either the last table creation or the end
        of my last sqlplus session)
        with the SQL rollback statement:
=====*/

prompt =====
prompt (I am UNDOING, rolling-back, the insertion of the Computer department
prompt into the dept table)
prompt =====

rollback;

spool off

-- end of 325lect07-2.sql