/*=====
    CS 325 - Week 12 Labs - 11:00 lab - 2016-11-09
=====*/

/*=====
    some SETUP for being able to run (and re-run) this
    lab script, I hope...
=====*/

prompt ========
prompt (assumes set-up-ex-tbls.sql is in CURRENT directory...)

start set-up-ex-tbls.sql

prompt ========
prompt and also setting up new versions of parts, orders tables

drop table parts cascade constraints;

create table parts
(part_num               integer,
 part_name              varchar2(25),
 quantity_on_hand       smallint,
 price                  decimal(6,2),
 level_code             char(3),       -- level code must be 3 digits
 last_inspected         date,
 primary key            (part_num)
);      

insert into parts
values
('10603', 'hexagonal wrench', 13, 9.99, '003', '05-SEP-2000');

insert into parts
values
('10604', 'tire', 287, 39.99, '333', '06-SEP-2000');

insert into parts
values
('10605', 'hammer', 30, 9.99, '003', '01-SEP-2000');

drop table orders cascade constraints;

create table orders
(order_num   char(6),
 cust_num    char(8),
 part_num    int,
 order_date  date,
 quantity    integer,
 order_code  char(1),
 primary key (order_num),
 foreign key (part_num) references parts
);

insert into orders
values
('111111', '11111111', '10604', '01-Feb-2000', 6, 'B');

/*=====
  NOW start spooling...
=====*/

spool 325lab12-1-out.txt

/*=====
  UPDATE command reminder:

  UPDATE tbl_name
  SET   attrib1 = expression
  WHERE condition;

  *  where clause optional! without it,
     SET gets done to EVERY row!

  *  note that expression and condition above
     CAN involve nested selects...
=====*/

commit;   /* committing db at this point before
             play with update and delete */

prompt ========
prompt notice tire currently has a price of 39.99

select *
from   parts;

update parts
set    price = 66.66
where  part_num = '10604';

prompt ========
prompt notice tire now has a new price of 66.66

select *
from   parts;

update parts
set    quantity_on_hand = 0
where  price = 9.99;

prompt ========
prompt notice that parts with a price of 9.99 now have a
prompt quantity on hand of 0:

select * 
from   parts;

update parts
set    last_inspected = sysdate;

prompt ========
prompt notice ALL parts now have a last-inspected date of
prompt today:

select * 
from   parts;

/*=====
    an update with nesting in set AND where clauses:
=====*/

update parts
set last_inspected = (select max(hiredate)
                      from empl)
where quantity_on_hand < (select quantity_on_hand
                          from   parts 
                          where  part_num = '10604');

prompt ========
prompt parts whose quantity on hand is less than that of part 10604
prompt now have a last-inspected date that is the max hiredate 
prompt from empl...!:

select *
from   parts;

/*=====
    DELETE command reminder:

    DELETE FROM tbl_name
    where condition;

    *   again, the where is optional --
        without it, ALL the rows in the given
	 table are deleted

        (table remains, just its contents are gone!)

    *   and there can be nested selects in
        that where clause, also...
=====*/

delete from parts
where  part_name = 'hammer';

prompt ========
prompt note that hammer part has now been deleted:

select *
from parts;

prompt ========
prompt adding two more parts, two kinds of bolts:

insert into parts
values
('10606', '3/8 in bolt', 5000, 0.03, '005', sysdate);

insert into parts
values
('10607', '7/8 in bolt', 2655, 0.04, '005', sysdate);

select *
from   parts;

prompt =====
prompt demo that the FROM keyword is optional
prompt    in DELETE: deleting parts whose level code is 005:

delete parts
where level_code = '005';

select *
from   parts;

prompt ========
prompt re-inserting those bolts:

insert into parts                                            
values                                                       
('10606', '3/8 in bolt', 5000, 0.03, '005', sysdate);        
                                                             
insert into parts                                            
values                                                       
('10607', '7/8 in bolt', 2655, 0.04, '005', sysdate); 

select *
from   parts;

prompt ========
prompt deleting from parts those rows whose quantity-on-hand
prompt is greater than the average quantity-on-hand:

delete from parts
where quantity_on_hand > (select avg(quantity_on_hand)
                          from parts);

select *
from   parts;

prompt ========
prompt demoing deleting ALL rows from the table:
prompt ...oops, cannot! There is an order depending on one of them!

delete from parts;

select *
from   parts;

/*===== 
    referential integrity checking can affect
    delete and update, also!
=====*/

prompt ========
prompt FAILS, there is an order of the part
prompt I am trying to delete...!

delete from parts
where  price = 66.66;

prompt ========
prompt FAILS, there is not a part with the number
prompt    I am trying to update the part number of
prompt    this order to:

update orders
set part_num = '2';

prompt ========
prompt demoing deleting ALL rows from the orders table:

delete from orders;

select *
from   orders;

/*=====
    rollback to state of previous commit
=====*/

prompt ========   
prompt rolling back all these changes

rollback;

/*=====
    a command I hope you do not need:

    ALTER

    ...lets you (in some cases) change the
    STRUCTURE of an existing database relation

    (remember: NOT the same as its contents!!!!!!!)

    a couple of simple examples:

    alter table parts
    add
    (supplier varchar2(20));

    alter table blah
    add
    (foreign key (moo_id) references cow);

    ^   what if table A needs table B as a parent,
        AND vice versa?

	 create table A NOT referencing B,
	 create table B referencing A,
	 THEN use ALTER to make the appropriate attribute
	     in A reference the now-existing B...
=====*/

/*=====
   WARNING -- typically will not allow you to alter
       a table in such a way that existing rows
       would violate DBMS integrity constraints...
=====*/

prompt ========
prompt this fails, cannot add a NOT NULL column if
prompt    rows currently exist

alter table parts
add
(supplier varchar2(20) not null);

prompt ========
prompt can add a new column that CAN be null

alter table parts
add
(supplier varchar2(20));

prompt ========
prompt note that parts now also has a supplier column:

describe parts

prompt ========
prompt (and can now give supplier column a value using update):

update parts
set supplier = 'Acme'
where part_num in ('10603', '10604');

select *
from   parts;

/*=====
    SEQUENCES

    one of SEVERAL ways DBMS's might provide
    tools to make it more convenient
    to generate unique primary key values
    over time

    sequence - an Oracle database object
        that can generate a sequence of values

    a simple form:
    
    create sequence desired_seq_name
    increment by 2
    start with   100;

    *   increment clause optional -- default is by 1
    *   start with is optional -- I am not sure
        of the default

    seq_name.nextval generates and returns
        the next value in the sequence

    seq_name.currval returns the current/most recent
        value in the sequence

    ^ unreasonably persnickety!!!
      ... I have trouble querying using these
=====*/
 
prompt ========
prompt (re-)creating painter and painting tables for sequence play

drop table painter cascade constraints;

create table painter
(ptr_num  int,
 ptr_lname varchar2(30),
 ptr_fname varchar2(15),
 primary key (ptr_num)
);

drop table painting cascade constraints;

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

prompt ========
prompt I would like painter_seq to be a sequence object
prompt    that starts at 100 and increments by 2

drop sequence painter_seq;

create sequence painter_seq
increment by 2
start with   100;

/*====
  note: it appears to be inconsistent in the Oracle student
  database whether the FIRST nextval returns
  the start value or start + increment value
=====*/

prompt ========
prompt using painter_seq in inserts into painter:

insert into painter
values
(painter_seq.nextval, 'Van Gogh', 'Vincent');

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

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

select *
from   painter;

prompt ========
prompt and using currval to give a painting the same
prompt     current value of ptr_num as just used for parent painter row

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

select *
from   painting;

prompt ========
prompt seems to be problematic to use nextval and currval
prompt in select statements:

select *
from   painter
where  ptr_num = painter_seq.currval;

prompt ========
prompt you CAN get the current value of a sequence querying
prompt  using dual:

select painter_seq.currval
from   dual;

prompt ========
prompt ...but that query cannot be used as a sub-select?!

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

prompt ========
prompt sequence is a tool, use it wisely,
prompt   and not goofily:

insert into parts
values
('10614', 'stuff' || painter_seq.nextval,
 painter_seq.currval, .13, '005', sysdate, 
 'Robot' || painter_seq.nextval);

prompt ========
prompt goofy result..!

select *
from   parts;

/*=====
    SQL views!

    another object in your database

    a DERIVED "table" --
    unlike a database relation,
    that contains 0 or more tuples/rows,
 
    a view just contains HOW to generate
    the desired data whenever the view is
    used

    ...BUT that view can be used as IF it
    were a REAL relation IN SELECT
    statements!

    (and in SOME other SQL statements as well)

    *   this can be CONVENIENT
    
    ...AND you can grant, revoke ACCESS to a
    view as you can to a table;

    *   this can improve SECURITY
    *   (you cannot give a person accesss to just
        SOME of the columns of a table --

	 BUT!!! you can make a view that contains
	 JUST those columns,
	 and give a person access JUST to that VIEW)

    DROP VIEW desired_view_name;

    CREATE VIEW desired_view_name AS
    desired_select_stmt;
  
    *   you can specify column names of the new view
        in more than one way

	 *   if you just do the above,
	     you get the column names projected by
	         the desired_select_statement

            (yes, if you use a column alias,
            that view gets that...)
		       
            UNLESS

            *   you can also give them in a comma
                separated list 
                in a set of parentheses
                after the desired_view_name:

            CREATE VIEW desired_view(col1, col2, ..., colN) AS
                desired_select_stmt;

    *   NOTE that any computations in the
        desired_select_stmt MUST be given a "legal"
        column name using one of the two above
        approaches
=====*/

drop view short_empl;

create view short_empl as
    select empl_num, empl_last_name, job_title, mgr
    from empl;

prompt ========
prompt selecting all "rows" from view short_empl:

select *
from   short_empl;

prompt ========
prompt deleting Clerks from empl,

delete from empl
where job_title = 'Clerk';

prompt =======
prompt ...and now the removed empl clerks do not show up when you
prompt   next use this view:

select *
from   short_empl;

prompt ========
prompt describe does work with views:

describe short_empl

prompt ========
prompt can use a view in a select as if it were a "real" table;
prompt using it in a projection from a join:

select empl_last_name, cust_lname
from   short_empl, customer
where  short_empl.empl_num = customer.empl_rep;

prompt ========
prompt and you can use a view in creating another view:

drop view cust_rep_display;

create view cust_rep_display as
    select empl_last_name, cust_lname
    from   short_empl, customer
    where  short_empl.empl_num = customer.empl_rep;

prompt ========
prompt selecting all "rows" from view cust_rep_display:

select *
from   cust_rep_display;

prompt ========
prompt AVOID using double-quoted column aliases
prompt in views, you then must express those resulting columns in
prompt double quotes from then on out
prompt (we will have a prettier way, maybe next week)

drop view short_empl2;

create view short_empl2(name, "job category", manager) as
    select empl_last_name, job_title, mgr
    from   empl;

prompt ========
prompt describing, then selecting all "rows" from 
prompt view short_empl2:

describe short_empl2

select *
from   short_empl2;

prompt ========
prompt this FAILS; 
prompt you MUST use the column names you have given for that view
prompt     when using the view...

select empl_last_name
from   short_empl2;

prompt ========
prompt so, this works:

select name
from   short_empl2;

prompt ========
prompt and column name given with double quotes must
prompt     continue to be specified with double quotes
prompt     when view is used:

select *
from  short_empl2
order by job category;

prompt ========
prompt CAN order short_empl2 by "job category":

select *
from  short_empl2
order by "job category";

prompt ========
prompt and can project "job category" from short_empl2, also:

select "job category"
from   short_empl2
order by "job category" desc;

/*=====
    demo of specifying the column name for a view column
    as a column alias in its defining select
=====*/

drop view short_empl3;

create view short_empl3 as
    select empl_last_name last_name, job_title position
    from   empl;

prompt ========
prompt selection of all "rows" from short_empl3
prompt (with column names given as column aliases in
prompt its defining select):

select *
from   short_empl3;

/*=====
    demo how MUST give projected computations in
    a view a proper "legal" column name
=====*/

drop view salary_avgs;

prompt ========
prompt FAILS; when defining a view,
prompt MUST give projected computations a column alias:

create view salary_avgs as
    select   job_title, avg(salary)
    from     empl 
    group by job_title;

prompt ========
prompt succeeds when do give such computations a proper column 
prompt alias:

create view salary_avgs(job, salary_avg) as
    select   job_title, avg(salary)
    from     empl 
    group by job_title;

prompt ========
prompt selection of all "rows" from view salary_avgs:

select *
from   salary_avgs;

spool off

-- end of 325lab12-1.sq;