/*=====
    examples for Week 5 Asynchronous material
    (more detail: see SQL Reading Packet 3!!!!)

    NOTE: this script assumes that:
        set-up-ex-tbls.sql
        ... has already been run, so you have populated
            tables empl, dept, and customer   

    by: Sharon Tuttle
    last modified: 2021-09-22
=====*/

spool 325lect05-2-out.txt

/*=====
    SQL "GOTCHA" - how to select rows for which an attribute's value
    is null

    YOU CANNOT DO THIS WITH = !!!!!!!!!

    YOU MUST USE THE OPERATOR:   is null

    (likewise you can't use !=, you must use:   is not null
=====*/

-- I want last names of employees whose commission attribute is null

prompt
prompt =====
prompt THIS DOES NOT WORK, there ARE empls whose commission is null
prompt =====

select empl_last_name
from   empl
where  commission = null;

prompt =====
prompt use IS NULL to get empls whose commission is null
prompt =====

select empl_last_name
from   empl
where  commission is null;

prompt =====
prompt THIS DOES NOT WORK, there ARE empls whose commission is NOT null
prompt =====

select empl_last_name
from   empl
where  commission != null;

prompt =====
prompt use IS NOT NULL to get empls whose commission is NOT null
prompt =====

select empl_last_name, commission
from   empl
where  commission is not null;

/*=====
the IN predicate
 
    attrib IN (comma-sep'd list of values)

    this will be true for rows whose value of attrib IS
    one of those within (comma-sep'd list of values)
======*/

prompt =====
prompt want this info about empls who are Analysts or Managers
prompt =====

select empl_last_name, job_title, salary
from   empl
where  job_title IN ('Analyst', 'Manager');

/*=====
    we FREQUENTLY do further projections from equi-joins!

    if I just want the empl_last_name and dept_name from
    the equi-join of empl and dept,
    JUST project those columns!
=====*/

prompt =====
prompt projecting JUST empl_last_name and dept_name from the equi-join
prompt of empl and dept
prompt =====

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

/*=====
    BUT!
    the SCOPE of a SELECT statement, if you will,
    is the table(s) from its FROM clause....

    IF an attribute has the same name in MORE than one table expr
    in the FROM clause, you MUST precede it by the name of the table
    you want it from;
=====*/

prompt ========
prompt WILL GET ERROR!
prompt dept_num is in dept and empl, MUST say which table's version you want
prompt ========

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

prompt =====
prompt indicate which table's version of dept_num you want, and that works:
prompt =====

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

/*=====
    AND OR NOT - are supported by SQL

    can use these in the WHERE clause
    (because you can only HAVE ONE WHERE clause in a SELECT statement!)

    select blah
    from   blah
    where  (bool_expr)
           AND (bool_expr);

    select blah
    from   blah
    where  (bool_expr)
           OR (bool_expr);

    (and CAN have as "big" an expression as you want built
    from these...

    where (bool_expr)
          AND (bool_expr)
          AND (bool_expr)
          ... etc.! )
=====*/

/*=====
    BETWEEN operator CAN be used in WHERE clauses, also

    (we used to restrict an attributes domain in a create table
    statement earlier this semester)
=====*/

prompt =====
prompt projecting JUST empl_last_name and salary for empls
prompt whose salary is between 1100 and 1600 inclusive:
prompt =====

select empl_last_name, salary
from   empl
where  salary BETWEEN 1100 and 1600;

/*=====
    LIKE operator can select rows where an attribute matches
    a pattern!

    (see the SQL Reading Packet for options for these patterns)
    *   put the pattern in single quotes
    *   the pattern must match the entire attribute value to be true
    *   % matches 0 or more characters
    *   _ matches exactly one character

=====*/

prompt =====
prompt asking for employees whose empl_num ENDS with a 9:
prompt =====

select empl_num, empl_last_name
from   empl
where  empl_num like '%9';

prompt =====
prompt asking for employees whose job_title is ONE letter followed by
prompt anager
prompt =====

select job_title, empl_last_name
from   empl
where  job_title like '_anager';

/*===
    OUT OF TIME - moving table aliases, column aliases,
                         computed columns, and aggregate functions to 
                         NEXT WEEK's asynchronous material
===*/

spool off