CS 325 - Week 6 Labs - 3:00 lab

spool 325lab06-2-out.txt

--========
-- we COMBINE relational operations within a
--   single SQL select statement FREQUENTLY!

prompt ========
prompt just project certain columns 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!!
--
-- the = operator does NOT WORK for selecting
--    rows with a null value for some attribute!!
--    (you don't even get an error message!)
--
-- you need to use this operator instead:
--
--       IS
--
-- (and for selecting rows for an attribute
--     NOT being null, use:
--  
--       IS NOT
--- )

prompt ========
prompt WILL NOT WORK like you think (using = with NULL);
prompt does NOT show last names of employees with null commissions:

select empl_last_name
from   empl
where commission = NULL;

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 WILL NOT WORK like you think (using != or <> with NULL);
prompt does NOT show employees with NON-null commissions:

select empl_last_name
from   empl
where  commission != NULL;

prompt ========
prompt WILL give you what you want (use IS NOT to find non-NULL);
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
-- this operator returns true if
--    the value of the attribute on its left hand side
--    is contained in the set given on its right hand
--    side
--    (you can put that set in a comma-separated
--    list inside a set of parentheses)

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

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

--========
-- in SQL, there is FREQUENTLY more than one
--   way to answer the same 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 combine an equi-join with further
--     selection

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';

--========
-- in ANSI-99 version, no AND needed in this particular query...

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';

--========
-- REMINDER:
-- when you have a column whose name is the same
--    in more than one relation from the FROM clause,
--    you MUST indicate WHICH table's 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, dept
where  empl.dept_num = dept.dept_num
       AND hiredate > '01-DEC-1991';

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

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

--========
-- reminder: you have the usual comparison and boolean
--    operations available: = != <> AND OR NOT

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: when mixing ANDs and ORs,
--    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
from   empl
where  hiredate > '01-Mar-1991'
       and (job_title = 'Sales'
            or salary >= 1500);

--========
-- a few negation 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';

--========
-- reminder:
--    BETWEEN operator is fine in WHERE clauses, too!

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

select *
from   empl
where  salary between 1100 and 1600;

-- this IS the same result as:

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

--========
-- the LIKE operator
--
-- you can use LIKE to select rows
--    with an attribute that matches
--    a certain pattern;
--    you write that pattern in single quotes,
--    and here are some available wildcards:
--
--    % - matches any 0 or more characters
--    _ - matches any single character

-- employees whose emp 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 9 ANYWHERE in their emp num

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

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

-- employees with a 7 as the 3rd character

prompt ========
prompt if you want a 7 in the 3rd position

select *
from   empl
where  empl_num like '__7_';

-- for finding case issues..

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

select *
from   empl
where  job_title like '_anager';

--===========
-- computed columns

-- you can project the results of expressions
--   as well as attributes!

-- you have, for example, * / + -

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

select salary * 2
from   empl;

-- NOTE: computations will NOT be done on
--    null attributes!!! (on attributes whose
--    value is null)

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;

--========
-- column alias
-- 
-- in a SELECT, you can give a projected column
--   a different name with a column alias;
--   in the SELECT clause, follow the column
--   expression with white space and then the
--   desired alias

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

select salary *	2 doubled_salary
from   empl;

--========
-- IF you want a blank in the column alias
--   OR you want to specify the case,
--   you put the 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 salary"
from   empl;

--=======
-- tables can have aliases, too
--
-- in the FROM clause, you can follow a relation expression
--    with white space and a name, and that
--    becomes the alias for that relation
--    THROUGHOUT that select statement
-- YES, even in the SELECT clause!!!!!!!!!!!

-- STYLE: make a reasonably-readable alias!
--   -  d is OK for dept
--   -  x or a is NOT OK for dept

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

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

--========
-- aggregate functions - give you ONE result from
--    a computation on multiple selected rows

-- typically 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-2.sql