CS 325 - Week 6 Labs - 11:00 lab

spool 325lab06-1-out.txt

--========
-- we OFTEN combine relational operations in
--    a SQL select statement, of course!

prompt ========
prompt further projection from an equi-join:
prompt employee last names, dept names, and dept locations:

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

--========
-- also common to combine projection and selection

prompt ========
prompt last names and salaries of managers

select empl_last_name, salary
from   empl
where  job_title = 'Manager';

prompt ========
prompt get job_titles and hiredates for employees
prompt with commissions greater than 0

select job_title, hiredate
from   empl
where  commission > 0;

--========
-- SQL GOTCHA!!!!
-- BE CAREFUL when trying to select rows
--    based on whether an attribute is NULL
--    or not!

prompt ========
prompt THIS does not work like you might expect!! (using = with NULL);
prompt does NOT show last names of employees with null commissions:

select empl_last_name
from   empl
where  commission = null;

--========
-- you CANNOT use = to see if an attribute's value
--    is null!! (or not null!!!)

-- INSTEAD, you use the operator:
--
--      IS

prompt ========
prompt WILL give you what you want (using IS with NULL);
prompt NOW shows last names of employees with null commissions:

select empl_last_name
from   empl
where  commission IS null;

prompt ========
prompt also a problem (using != or <> with NULL);
prompt does NOT show employees with NON-null commissions:

select empl_last_name
from   empl
where  commission != null;

prompt ========
prompt need IS NOT here (to select rows with non-NULL attribute);
prompt NOW shows employees with NON-null commissions:

select empl_last_name
from   empl
where  commission is not null;

-- oh, this works too:

prompt ========
prompt (oh, you can use not with an IS expression, too):

select empl_last_name
from   empl
where not(commission is NULL);

--========
-- another predicate:
--
--     IN
--
--     the IN operator returns true
--     if the attribute on its left hand side
--     has a value that is one of those in
--     the set given on the right hand side
--     (write the set's values in parentheses)

prompt ======== 
prompt using IN: if I want last names, job titles, and salaries
prompt of managers and analysts

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

--========
-- yes, there is OFTEN more than one way to
--   achieve an answer to a question!

prompt ========
prompt providing the same info using OR

select empl_last_name, job_title, salary
from   empl
where  job_title = 'Analyst'
       OR job_title = 'Manager';

--========
-- sure, you can have boolean expressions
--   including a join condition...

prompt ========
prompt I just want names, dept names, and locations
prompt   for employees hired AFTER 1991-12-01

select empl_last_name, dept_name, dept_loc
from   empl, dept
where  empl.dept_num = dept.dept_num
       AND hiredate > '01-dec-1991';

--========
-- (no AND needed in this particular query for the ANSI-99 join style)

prompt ========
prompt (now using ANSI-99 join style - no AND needed in this case)

select empl_last_name, dept_name, dept_loc
from   empl JOIN dept ON empl.dept_num = dept.dept_num
where  hiredate > '01-dec-1991';

--========
-- NEXT reminder:
-- ANYWHERE in a SELECT in which a column is
--    mentioned that is in more than one relation
--    in the FROM clause,
-- you MUST precede it by the name of the table
--    whose version you want!

prompt ========
prompt DOES NOT work, need to say WHICH table version
prompt    of dept_num (to project last names, dept nums,
prompt    dept names, and dept locations):

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

prompt ========
prompt WORKS when indicate which dept_num:

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

--========
-- remember that the "usual" comparison
--   and boolean operators are available

prompt ========
prompt get last names of employees who are in Sales
prompt OR have salary >= 1500

select empl_last_name
from   empl
where  job_title = 'Sales'
       or salary >= 1500;

--========
-- STYLE: combining AND and OR? that's fine, but
--   use parentheses for understandability!

prompt ========
prompt get last names and hiredates of employees 
prompt hired after March 1, 1991, AND
prompt (who are in Sales OR have salary >= 1500)

select empl_last_name, hiredate
from   empl
where  hiredate > '01-Mar-1991'
       and (job_title = 'Sales'
            or salary > 1500);

--========
-- a few NOT-related examples

prompt ========
prompt THREE ways to ask for employees whose job title is NOT 'Sales':

select *
from   empl 
where  not job_title = 'Sales';

prompt ========

select *
from   empl
where  job_title != 'Sales';

prompt ========

select *
from   empl
where  job_title <> 'Sales';

--========
-- remember BETWEEN from our check-clause example!
--    it is fine in WHERE clauses, also!

prompt ========
prompt employees whose salary is in [1100, 1600], TWO ways:

select *
from   empl
where  salary between 1100 and 1600;

-- same effect as:

select *
from   empl
where  salary >= 1100 
       and salary <= 1600;

--========
-- LIKE operator
--     can be used to search for attributes
--     whose values match a PATTERN,
--     (written as a SQL string!!)
-- available wildcard symbols:
--     %  - matches 0 or more characters
--     _  - matches exactly one character

--========
-- employees whose empl_nums end with a 9?

prompt ========
prompt employees whose empl_nums end with a 9?

select *
from   empl
where  empl_num like '%9';

--========
-- employees with a 7 ANYWHERE in their empl_num?

prompt ========
prompt employees with a 9 ANYWHERE in their empl_num?

select *
from   empl
where  empl_num like '%9%';

--========
-- employees whose job_title is 1 character
--   followed by anager:

prompt ========
prompt employees whose job_title is 1 character
prompt followed by anager:

select *
from   empl
where  job_title like '_anager';

--========
-- if you want a 6 in the 2nd position

prompt ========
prompt if you want a 6 in the 2nd position

select *
from   empl
where  empl_num like '_6__';

--========
-- COMPUTED columns and column aliases

-- you can project a computation! based on an attribute!

-- * / + - like you probably know

prompt ========
prompt you can project salaries if multiplied by two:
prompt (notice the default column heading)

select salary*2
from   empl;

--========
-- notice the column heading (by default) IS some
--   version of the computation;

-- BUT!! you can give a projected column
--   a DIFFERENT name (in that single select's
--   result) with a COLUMN ALIAS
--
--   in the select clause,
--   follow the column expression by at least one
--     blank, then the desired alias

prompt ========
prompt using a column alias for the salary-times-2

select salary*2 doubled_sal
from   empl;

--========
-- IF you want a blank in the column alias,
--    OR you want to specify its case (and
--    have it SHOW),
-- write that alias in DOUBLE QUOTES (!!!)

prompt ========
prompt putting column alias in double quotes so can contain
prompt a blank, and can get specified case:

select salary*2  "doubled sal"
from   empl;

--========
-- NOTE: null values do NOT participate
--   in computed column computations!

prompt ========
prompt NOTE that null values do NOT participate
prompt in computed column computations;
prompt (trying to project salary + commission):

select salary + commission 
from   empl;

--========
-- there are also TABLE aliases

-- you can give a TABLE a nickname!
--    in the from clause, after the table/relation,
--    put white space, then the desired alias
--
-- (you MUST then USE that alias THROUGHOUT this
--    select statement!) (YES, even in the select
--    clause!)

-- make the alias REASONABLY READABLE!!
--   d for dept is fine --
--   x for dept is NOT fine!!!

prompt ========
prompt using a table alias in a further projection
prompt from an equi-join, projecting employee
prompt last names, dept nums, and dept names

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

--========
-- AGGREGATE functions
--    called in the select clause,
--    BUT (normally) projects a SINGLE result
--    based on the selected rows

-- often available:
--    avg min max sum count

prompt ========
prompt project avg, min, max, sum, and count
prompt of (non-null) salaries

select avg(salary), min(salary), max(salary),
       sum(salary), count(salary)
from   empl;

prompt ========
prompt ...of salaries JUST of those in Sales:

select avg(salary), min(salary), max(salary),
       sum(salary), count(salary)
from   empl
where  job_title = 'Sales';

spool off

-- end of 325lab06-1.sql