-----
-- CIS 180 - Intro to SQL
-- Class 6 - 3-14-03
-----

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

spool 180class6_results.txt

-----
-- SQL COMMIT, ROLLBACK statements
-----

-- how SQL supports TRANSACTIONS ("logical" units of work that really
-- take several steps to complete --- that you want to be COMPLETELY
-- done, or completely NOT done, not ever PARTIALLY done...)

-- commit:
-- 
--* IN ORACLE SQL, done automatically on your behalf when you exit 
--  SQL*Plus, create a table, drop a table, or alter a table's structure 
--  (alter command, we haven't yet discussed)
--
-- * you can type it as a command:
--   commit;
--   ...whenever you want changes (insertions, updates, deletes, a few 
--   others) to become permanent at that point.

-- rollback:
--
-- * you can type it as a command:
--   rollback;
--   ...whenever you want all changes (insertions, updates, deletes, a 
--   few others) to be undone UP TO the point of the LATEST commit.

-- so, consider our dept table:

select  *
from	dept;

-- let's insert a new row 600:

insert into dept
values
('600', 'Operating Sys', 'Ferndale');

select	*
from	dept;

-- rollback, and 600 goes away:

rollback;

select 	*
from	dept;

-- inserting 600 again:

insert into dept
values
('600', 'Operating Sys', 'Ferndale');

select	*
from	dept;

-- COMMITTING this change:

commit;

-- inserting a new department 700:

insert into dept
values
('700', 'Compilers', 'Fortuna');

select	*
from	dept;

-- NOW if you rollback, you ONLY get rid of 700, because
-- the LATEST commit; latest commit came AFTER adding 600, so it stays:

rollback;

select	*
from	dept;

-----
-- SQL UPDATE statement
-----

-- how you can UPDATE or modify something ALREADY in a table: 
--
-- update tbl_name
-- set    attrib1 = expression1
-- where  condition;
--
-- note that EVERY row where condition is true will have attrib1
-- set to the value of the given expression1! 

-- note that the WHERE clause is optional --- if you leave it off,
-- EVERY row will (try to) have attrib1 set to expression1.

update empl
set    hiredate = sysdate;

rollback;

-- with the WHERE clause, ONLY rows where the WHERE clause
-- condition is true will (try to) have the update made;

-- let's increase the salaries of Clerks by 10%

update empl
set    salary = salary * 1.1
where  job_title = 'Clerk';

select *
from   empl;

rollback;

-- show that an update might be forbidden if messes up a row or
-- table constraint

prompt this FAILS --- vioates length limit for dept_name
update dept
set    dept_name = 'Very Fancy Operating Systems'
where  dept_num = '600';

prompt this FAILS --- cannot leave empls with the old dept_num
prompt    without a 'parent'
update dept
set    dept_num = '900'
where  dept_num = '100';

-- so, how CAN I make this change?

-- here's one way: 
-- * change the children affected by the change --- empl's with 
--   dept_num of 100 --- to have a dept_num of null
-- * change the dept_num as desired
-- * update those children to have the new dept_num 
-- WARNING --- be careful! This assumes that empl's HAVE dept_num's
-- normally, even though they're not required. Else, how will I know
-- which empl's to change from null to 900?????

-- verify that no empls currently have a null dept_num

select  *
from	empl
where   dept_num is null;

update 	empl
set	dept_num = null
where	dept_num = '100';

update  dept
set 	dept_num = '900'
where	dept_num = '100';

update 	empl
set	dept_num = '900'
where	dept_num is null; 

-- show that set expression can be quite complex, also
-- show that the where cnodition can be as complex as you'd like

-- what if you'd like to change the salary of any Salesman
-- making less than the highest-paid Clerk to be 10% more
-- than the highest-paid Clerk.

update  empl
set     salary = 1.10 * (select distinct salary
                         from   empl
                         where  job_title = 'Clerk'
                         and    salary >=ALL
                                (select salary
                                 from empl
                                 where job_title = 'Clerk'))
where   job_title = 'Salesman'
and	salary <ANY
        (select salary
         from   empl
         where job_title = 'Clerk');

-----
-- SQL DELETE statement
-----

-- here's how you can DELETE a row
-- from a table without deleting the entire table: 
--
-- delete from tbl_name
-- where  condition;
--
-- again, note that ALL rows in which condition is true will
-- be deleted;

-- WHERE clause is optional, again --- if it ISN'T there, ALL the
-- rows in the table will be deleted!!!

@ set_up_ex_tbls

-- (note that empl's who are cust_rep's CANNOT be deleted...
-- so, this WHOLE delete fails!!!

prompt this FAILS --- some customers have empl reps!!
delete from empl;

delete from customer;

-- a more typical delete...

-- let's delete the Clerks's

delete from empl
where  job_title = 'Clerk';

select *
from   empl;

rollback;

-- empty the customer table

delete from customer;

-- can we now delete employees who are NOT Managers in
-- location 'Chicago'?

delete from empl
where  job_title != 'Manager'
and    dept_num in
       (select dept_num
        from   dept
        where  dept_loc = 'Chicago');

-----
-- the LIKE predicate (and wildcards %, _ )
-----

-- LIKE is true if the column value MATCHES the string on the right...
-- oh by the way: % is a wildcard, it matches ANY 0 or more characters;
--                _ is a wildcard, it matches ANY SINGLE character
-- (the string on the right HAS to be surrounded by single quotes!!)

-- which employees has an 'a' as the second letter of their last name?

rollback;

select *
from   empl
where  empl_last_name like '_a%';

-- who have names that END with a lower-case 'd' (case matters!!)?

select 	*
from	empl
where	empl_last_name like '%d';

-- what if I want to change all job_titles that begin with Sale
-- to Sales Rep?

update empl
set    job_title = 'Sales Rep'
where  job_title like 'Sale%';

rollback;

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