-----
-- CIS 180 - Intro to SQL
-- Class 2 - 2-28-03
-----

-- last modified: 2-28-03

-- write script results to file 180class2_results.txt

spool 180class2_results.txt

-----
-- simple SELECTION using SQL select statements
-----

-- select *
-- from  tbl
-- where cond;
--
-- select those rows from table tbl where cond is true.


-----
-- relational operators: > < >= <= = != <> 
-----

select *
from   empl
where  mgr = '7782';

select *
from   empl
where  hiredate > '30-mar-1991';

-- beware --- case MATTERS inside of single quotes (used for strings)!

-- this WON'T find any managers:

select *
from   empl
where  job_title = 'manager';

-- this won't work, either --- string literals MUST be inside single
-- quotes:

select *
from   empl
where  job_title = manager;

-- this one WILL work:

select *
from   empl
where  job_title = 'Manager';


-----
-- boolean operators - AND, OR, NOT
-----

select *
from   empl
where  salary > 1500
and    hiredate > '01-mar-1991';


select *
from   empl
where  salary > 1500
or     hiredate > '01-mar-1991';

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

-- yes, there's more than 1 way to do this...

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

-- this DOESN'Y work, though...

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

-- if mixing ands, ors, and nots, USE PARENTHESES to make
-- sure you are doing what you want...

select *
from   empl
where (job_title = 'Clerk'
      and hiredate > '01-Mar-1991')
or   (job_title = 'Salesman'
     and commission > 500);

-----
-- the IN predicate
-----

-- att in (val1, va2, ..., valN)
--
-- is TRUE if att's value is one of the set val1, val2, ... , valN

select *
from   empl
where  job_title in ('Manager', 'President');

select *
from   empl
where  job_title in ('Manager', 'Custodian');

select *
from   empl
where  job_title not in ('Manager', 'Custodian');

-----
-- the BETWEEN predicate
-----

-- att between val1 and val2
-- ...is TRUE if att's value is between val1 and val2, INCLUSIVE
-- (if it is >= val1 and <= val2)

select *
from   empl
where  hiredate between '23-sep-1991' and '03-dec-1991';

-----
-- is null, is not null
-----

-- what if you want to see those columns that contain NO value?
-- that's null
--
-- you HAVE to say, is null or is not null:

-- this shows empls with a null commission:

select *
from   empl
where  commission is null;

-- this shows empls with a non-null commission:

select *
from   empl
where  commission is not null;

-- saying = null DOESN'Y work, and DOESN'T give you an error message ---
-- it just doesn't select the rows you want!!!

select *
from   empl
where  commission = null;

-----
-- seeing rows in the order YOU want: ORDER BY
-----

-- new SELECT clause! write as the LAST line in your select
-- statement;

-- order by att
-- ...rows will be shown in increasing order of that att

select   *
from     empl
order by hiredate;

select   *
from     dept
order by dept_loc;

select   *
from     dept
order by dept_name;


-- what if you want to specify order in case of "ties"?
-- put a 2nd attribute to order rows where 1st att is the same,
-- etc.!

select   *
from     empl
order by job_title, mgr, hiredate;


-- what if you want to see rows in REVERSE order? desc

select   *
from     empl
where    job_title = 'Salesman'
order by commission desc;

select   *
from     empl
order by job_title, mgr desc, hiredate;

-----
-- COMBINING selection and projection
-----

-- see just the hiredates and salaries of Salesmen

select hiredate, salary
from   empl
where  job_title = 'Salesman';

-----
-- (if time) the LIKE predicate, with _, %
-----




-- stop writing results to file 180class2_results.txt

spool off