/*=====
    CS 325 - Week 12 Labs - 3: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-2-out.txt

/*=====
  REMINDER: update command

  basic syntax:

  UPDATE tbl_name
  SET attrib = expression
  WHERE condition;

  *   where clause IS optional;
      if do NOT put, you WILL make that change
      to ALL the rows

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

/*=====
 I would like to be able to roll back to this point
   later on, after playing with update and delete
======*/

commit;

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

prompt ========
prompt notice ALL parts now have a last-inspected date of
prompt one week ago:

select * 
from   parts;

/*=====
    updates with nesting in set AND where clauses:
=====*/

update parts
set    last_inspected = ((select max(hiredate)
                         from empl) - 14)
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 two weeks earlier 
prompt than the max hiredate from empl...!:

select *
from   parts;

update parts
set    last_inspected = (select max(hiredate) - 21
                         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 THREE weeks earlier 
prompt than the max hiredate from empl...!:

select *
from   parts;

/*=====
  REMINDER: delete command

  basic syntax:

  DELETE FROM tbl_name
  WHERE condition;

  *   where IS optional, BUT you'll delete all the
      rows from the given table without it;

      (otherwise you just delete the rows meeting
      the given condition)

  *   the FROM keyword in the DELETE statement is
      optional,

      BUT note that DELETE does NOT remove a table --

      it JUST can remove contents!

      *   to get rid of a table,
          you need DROP TABLE

      *   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 inch bolt', 5000, 0.03, '005', sysdate);

insert into parts
values
('10607', '7/8 inch bolt', 2655, 0.05, '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 deleting from parts those rows whose quantity-on-hand
prompt is less 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 parts 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;

select *
from   parts;

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

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

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

rollback;

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

    ALTER

    *   update, insert, delete do NOT change
        database structure

        create, drop, and ALTER DO change
        database structure
        (and I think by default include
        an auto-commit in the Oracle student
        database...)

   *   CONSIDER if you have 2 "base" tables
       that need to reference each other;
       
       you can create table A with the 
           needed attribute, NOT specified
           yet as the foreign key referencing
           B

       you can create table B with the desired
           foreign key referencing A

       NOW you can ALTER table A, 
           adding the foreign key constraint;

    *   not ALL alter-ations will be allowed;
        (you can't make one that existing
	 rows would violate, for example)

    an example of the basic syntax:

    ALTER TABLE tbl_name
    ADD
    (desired_change);

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

    alter table A_table
    add
    (foreign key(b_id) references B_table);
=====*/

prompt ========
prompt FAILS; CANNOT alter such that existing rows
prompt    would violate a new domain constraint

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

select *
from   parts;

prompt HEY you can use MODIFY to modify
prompt the constraints of an existing column

alter table parts
modify
(supplier  varchar2(20) not null);
      
/*======
  another database object: SEQUENCES

  *   different DBMS may provide different tools;
      different ways to generate primary key
      values, for example

  *   Oracle provides sequences as one such
      means

      *   it is a separate object in the database
      *   you can ask it its current value
      *   you can ask for the "next" value
      *   (maybe more?)

  *   example basic syntax:

      DROP SEQUENCE desired_seq_name;

      CREATE SEQUENCE desired_seq_name;

      *   this increments by 1 by default,
          I forget if it starts at 0 or 1

      *   there are optional clauses for
          specifying such things:

      CREATE SEQUENCE desired_seq_name
      INCREMENT BY incr_amt
      START WITH   start_val;
=====*/

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
=====*/

/*=====
   my_seq.nextval gets me the next value in
       my_seq

   my_seq.currval gets me the current value
       in my_seq

   BUT warning -- I have ISSUES with these
   in SELECT statements! (they seem to work
   fine in inserts)
=====*/

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 project currval of a sequence from a select using
prompt   special table 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 yes you CAN abuse sequences (but avoid...)

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

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

select *
from   parts;

/*=====
   SQL views

   another database object!

   a view is a DERIVED "table" --
   *   you know that a database relation is a
       set of tuples/rows

   *   a view JUST contains HOW to GENERATE 
       the desired information whenever a view
       is used;

       *   you can then treat the view as IF it
           were a table in SELECT statements

       *   BUT!!! you can do so without adding
           update hassles,

           AND you can grant and revoke access
           to views as you can to tables;

           (you cannot grant/revoke access
           to PART of a table --

           but you can make a view the generates
           the info from PART of a table,
           and grant/revoke access to that
           view)

        ...these are USEFUL for
           convenience AND
           enhancing data security!!!!

    *   basic syntax:

    DROP VIEW desired_view;

    CREATE VIEW desired_view AS
         desired_select_statement;

    *   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(col_name, col_name, ...) AS
            desired_select_statement;

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

commit;

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 CAN project "job category" from short_empl2:

select "job category", name
from   short_empl2;

/*=====
    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
=====*/

prompt ========
prompt FAILS; when defining a view,
prompt MUST give an alias to a computation-based column

drop view salary_avgs;

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 as
    select job_title, avg(salary) salary_avg
    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-2.sq;