-----
-- CIS 180 - Intro to SQL
-- Class 8 - 3-28-03
-----

-- last modified: 3-28-03, post-class

spool 180class8_results.txt

-- setting up a clean set of empl, dept, customer tables

@ set_up_ex_tbls

-----
-- UNION, INTERSECT, MINUS
-----

-- if you think of a table as a SET of ROWS,
-- then you can do these SET operations on any two
-- UNION-COMPATIBLE tables...

-- (union-compatible: same number of columns, columns
-- are respectively of compatible types)

-- (sub-select)
-- union
-- (sub-select);

-- (sub-select)
-- intersect
-- (sub-select);

-- (sub-select)
-- minus
-- (sub-select);

-- union gives you all rows in either table...
-- ("true" union --- no duplicate rows in result)

-- what if I want the names of departments in Dallas
-- or names of departments with Clerks in them?

(select	dept_name
 from	dept
 where  dept_loc = 'Dallas')
union
(select dept_name
 from   dept d, empl e
 where  d.dept_num = e.dept_num
 and    job_title = 'Clerk');

-- how about union-ing the employees involved?

(select empl_last_name
 from   dept d, empl e
 where  dept_loc = 'Dallas'
 and    d.dept_num = e.dept_num)
union
(select empl_last_name
 from   empl e
 where  job_title = 'Clerk');

-- to get two rows for those in BOTH tables being
-- union'd, you can use UNION ALL

(select empl_last_name
 from   dept d, empl e
 where  dept_loc = 'Dallas'
 and    d.dept_num = e.dept_num)
union all
(select empl_last_name
 from   empl e
 where  job_title = 'Clerk');

-- INTERSECT gives you only rows in BOTH sets...

(select empl_last_name
 from   dept d, empl e
 where  dept_loc = 'Dallas'
 and    d.dept_num = e.dept_num)
intersect
(select empl_last_name
 from   empl e
 where  job_title = 'Clerk');

-- MINUS gives you rows of the first set NOT in the second
-- (CAN carefully use as a NOT EXISTS alternative, sometimes!)

(select empl_last_name
 from   dept d, empl e
 where  dept_loc = 'Dallas'
 and    d.dept_num = e.dept_num)
minus
(select empl_last_name
 from   empl e
 where  job_title = 'Clerk');

-- notice that the "pair", if you will, can serve as an outer select;
-- an ORDER BY can be on the end!

(select empl_last_name, salary
 from   dept d, empl e
 where  dept_loc = 'Dallas'
 and    d.dept_num = e.dept_num)
union
(select empl_last_name, salary
 from   empl e
 where  job_title = 'Clerk')
order by salary desc;

prompt FAILS --- don't put order by INSIDE one of the sub-selects!

(select empl_last_name, salary
 from   dept d, empl e
 where  dept_loc = 'Dallas'
 and    d.dept_num = e.dept_num)
union
(select empl_last_name, salary
 from   empl e
 where  job_title = 'Clerk'
 order by salary desc);

prompt FAILS -- not union compatible!

(select empl_last_name, salary
 from   dept d, empl e
 where  dept_loc = 'Dallas'
 and    d.dept_num = e.dept_num)
union
(select empl_last_name
 from   empl e
 where  job_title = 'Clerk')
order by salary desc;

prompt FAILS -- not union compatible!

(select salary, empl_last_name
 from   dept d, empl e
 where  dept_loc = 'Dallas'
 and    d.dept_num = e.dept_num)
union
(select empl_last_name, salary
 from   empl e
 where  job_title = 'Clerk')
order by salary desc;

-- BUT --- there's a limit to what SQL*Plus can check:
-- USER must make sure the results will be "meaningful"...

(select empl_last_name, job_title
 from   dept d, empl e
 where  dept_loc = 'Dallas'
 and    d.dept_num = e.dept_num)
union
(select job_title, empl_last_name
 from   empl e
 where  job_title = 'Clerk')
order by job_title desc;

-- caveat: if rename columns in sub-selects, order-by
-- needs to use the NEW names:

prompt FAILS --- order by expects 2nd column to be called payrate

(select empl_last_name, salary payrate
 from   dept d, empl e
 where  dept_loc = 'Dallas'
 and    d.dept_num = e.dept_num)
union
(select empl_last_name, salary
 from   empl e
 where  job_title = 'Clerk')
order by salary desc;

(select empl_last_name, salary payrate
 from   dept d, empl e
 where  dept_loc = 'Dallas'
 and    d.dept_num = e.dept_num)
union
(select empl_last_name, salary
 from   empl e
 where  job_title = 'Clerk')
order by payrate desc;

-- union is nice for including results that an equi-join
-- will miss...

-- consider a new department:

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

-- here's how I can get a count of employees in ALL
-- departments, even those with NO employees:

(select dept_name, count(*) "# Empls"
 from	dept d, empl e
 where  d.dept_num = e.dept_num
 group by dept_name)
union
((select dept_name, 0
  from   dept)
  minus
  (select distinct dept_name, 0 
   from   dept d, empl e
   where  d.dept_num = e.dept_num));

rpompt ODDNESS: cannot get order-by to work here! WHY?

(select dept_name, count(*) "# Empls"
 from   dept d, empl e
 where  d.dept_num = e.dept_num
 group by dept_name)
union
((select dept_name, 0
  from   dept)
  minus
  (select distinct dept_name, 0
   from   dept d, empl e
   where  d.dept_num = e.dept_num))
order by "# Empls";

(select dept_name, count(*) "# Empls"
 from   dept d, empl e
 where  d.dept_num = e.dept_num
 group by dept_name)
union
(select dept_name, 0
 from   dept d
 where  not exists
        (select 'a'
         from   empl e
         where  d.dept_num = e.dept_num))
order by "# Empls";

-----
-- VIEWS  
-----

-- to get of a view...
--
-- drop view viewname;
-- 
-- to create a view:
--
-- create view viewname(col1, col2, ...) AS
-- <select sttmt>;
--
-- creates a view with the columns col1, col2, ...

-- viewname can then be sued like a table in any select!
-- (can SOMETIMES make updates using a view, and sometimes not...)

-- what if I want a view that gives "public" employee info?

drop view public_empl;

create view public_empl(name, title, dept, loc) as
select empl_last_name, job_title, dept_name, dept_loc
from   dept d, empl e
where  e.dept_num = d.dept_num;

-- use it like a table!

select *
from   public_empl;

select name, loc
from   public_empl
order by loc;

prompt FAILS -- gotta use VIEW's name!

select empl_last_name
from   public_empl;

update empl
set    dept_num = '300'
where  empl_num = '7934';

-- see Miller's new location?
select *
from   public_empl;

-- CAN use query's column names, if you wish...

drop view empl_dept;

create view empl_dept as
select empl_last_name, dept_name
from   empl e, dept d
where  d.dept_num = e.dept_num;

select dept_name, empl_last_name
from   empl_dept;

-- MUST rename a computation result!

prompt FAILS --- need to rename function result!

drop view dept_pop;

create view dept_pop as
select dept_name, count(*)
from   dept d, empl e
where  e.dept_num = d.dept_num
group  by dept_name;

create view dept_pop(dept_name, num_empls) as
select dept_name, count(*)
from   dept d, empl e
where  e.dept_num = d.dept_num
group  by dept_name;

select	dept_name, num_empls
from	dept_pop
order by num_empls;

drop view dept_pop;

-- this works, too:

create view dept_pop as
select dept_name, count(*) num_empls
from   dept d, empl e
where  e.dept_num = d.dept_num
group  by dept_name;


select  dept_name, num_empls
from    dept_pop
order by num_empls;


-----
-- SEQUENCES
-----

-- first, tables to help us play with sequences...

drop table painter cascade constraints;

create table painter
(ptr_num        char(3),
 ptr_lname      varchar2(30) not null,
 ptr_fname      varchar2(15),
 primary key    (ptr_num)
);

drop table painting cascade constraints;

create table painting
(ptg_title      varchar2(30),
 ptr_num        char(3),
 primary key    (ptg_title),
 foreign key    (ptr_num) references painter
);

-- create a sequence to help me set good primary keys
-- over time for table painter

drop sequence painter_seq;

create sequence painter_seq
increment by    2
start with      100;

-- seqname.nextval gets the NEXT values in the sequence, and
-- resets the sequence accordingly...
-- 
-- seqname.currval gets the CURRENT value of the sequence,
-- without changing it;

-- let's use the sequence to help set primary keys...

-- this first one is weird, BUT it shows that you could
-- concatenate sequences with alpha characters to build
-- table attribute values...

insert into painter
values
(painter_seq.nextval, 'Van Gogh', 'a' || painter_seq.currval);

insert into painter
values
(painter_seq.nextval, 'Monet', 'Claude');

insert into painter
values
(painter_seq.nextval, 'Da Vinci', 'Leonardo');

select  *
from    painter;

-- [sequence_name].currval should get you the sequence's CURRENT value;
-- if I know that the painting I'm adding is by the
-- 'latest' painter added, can I do this? Yes;

insert into painting
values
('Mona Lisa', painter_seq.currval);

select  *
from    painting;

-- sequences can be used in queries, too... maybe???
-- (OK, these didn't work...)

prompt FAILS --- not sure why!
select  *
from    painter
where   ptr_num = painter_seq.currval;

select  *
from    painter
where   ptr_num = (select painter_seq.currval
                   from   painter);             

select  *
from    painter
where   ptr_num = (select painter_seq.currval
                   from   painter_seq);
                 

-- stop writing results to file 180class8_results.txt
spool off