Please send questions to
st10@humboldt.edu .
--------
-- 315lab14-projected.sql
--------
-- last modified: 12-1-2010 (after lab)
--------
-- let's start all of this with a nice, clean set of example tables;
-- (copy and paste from course web page --- under "SQL and Lab-related
-- Examples List" --- if you do not have this script handy.)
@ set-up-ex-tbls
spool lab14-results.txt
--*************************************************************
-- OUTER JOINS
--*************************************************************
--------
-- you should be VERY comfortable with equi-joins and natural joins
-- at this point...!
-- ...so you know: the result ONLY includes rows where the join
-- condition is SATISFIED;
--------
insert into dept
values
('999', 'SQL Queries', 'Arcata');
insert into empl(empl_num, empl_last_name, job_title, mgr, hiredate, salary)
values
('9999', 'Brown', 'Coach', '7839', sysdate, 13);
-- so, if you do an equi-join of empl and dept, will dept 999 appear?
-- NO; there are no rows of empl where dept_num = 999, and so
-- the dept 999 row isn't joined with any of the empl rows in the result;
-- will empl Brown appear?
-- NO; there can be no rows of dept where dept_num is null, and so
-- the empl Brown row isn't joined with any of the dept rows in the
-- result;
select *
from empl e, dept d
where e.dept_num = d.dept_num;
-- ah, but we mentioned there ARE other kinds of joins besides
-- equi-joins and natural joins;
-- here's another kind: an OUTER JOIN
-- an outer join includes information from a joined table even if
-- there is no matching record in the other joined table;
-- an outer join acts as though one table has a row for each of the
-- values of the other table, although with null values in all
-- fields (which would of course not be permitted in a true
-- relation; this is just one way of describing the effect of an
-- outer join):
-- there are SEVERAL ways to write an outer join;
-- you'll see Brown with null dept_name from this one:
select empl_last_name, dept_name
from empl e LEFT OUTER JOIN dept d on e.dept_num = d.dept_num;
-- you'll see null empl name with SQL Queries from this one:
select empl_last_name, dept_name
from empl e RIGHT OUTER JOIN dept d on e.dept_num = d.dept_num;
-- "left" and "right" is relative to the order of the tables
-- in the from clause, in this one:
-- you'll see Brown with null dept_name from this one:
select empl_last_name, dept_name
from dept d RIGHT OUTER JOIN empl e on e.dept_num = d.dept_num;
-- how about a "full" outer join, both "sides"?
select empl_last_name, dept_name
from empl e FULL OUTER JOIN dept d on e.dept_num = d.dept_num;
-- here is ALTERNATE syntax for outer joins: ("older" Oracle way...?)
-- like an equi-join, except you put (+)
-- on the join condition, on the field from the table
-- which is "missing" the rows;
-- consider:
-- (+) by d.dept_num -- SO get empl Brown, with null dept_name, for this:
select empl_last_name, dept_name
from empl e, dept d
where e.dept_num = d.dept_num (+);
-- (+) by e.dept_num -- SO get dept SQL Queries, with null empl_last_name,
-- for this:
select empl_last_name, dept_name
from empl e, dept d
where d.dept_num = e.dept_num (+);
--*************************************************************
-- INTRO to PL/SQL (via TRIGGERS)
--*************************************************************
--------
-- PLEASE NOTE:
-- NEED this so that dbms_output lines within triggers
-- WILL be shown on-screen
--------
set serveroutput on
--------
-- PL/SQL - Oracle adding structures (if, loops) and variables
-- and functions and procedures to SQL -- making it a full
-- programming language (PL)
--------
--------
-- trigger: an odd object that you don't call -- instead, it gets
-- "triggered" when the specified action to a database table is
-- done
--------
--------
-- to print a line from within a trigger:
--------
-- dbms_output.put_line('string to print');
--------
-- to give details about trigger compilation errors: type
--------
-- show errors
-- ...at line after SQL*Plus complaint about compilation errors
--------
-- basic STRUCTURE of a trigger: (if remove -- comments, of course!!)
--------
--create [or replace] trigger <trigger_name> {BEFORE | AFTER}
-- {DELETE | INSERT | UPDATE {OF column_name1 [, column_name2] ]}
-- [OR {DELETE | INSERT | UPDATE [OF column [, column] ]}]
-- ON <table_name>
-- [FOR EACH ROW
-- [WHEN (condition)]]
--[declare]
--
--begin
--
--[exception
--]
--end;
--/ -- put this to RUN/create trigger!!! Drove me NUTS when I omitted
-- can have only ONE trigger per table --- how?!
-- can have ONE trigger for MULTIPLE actions, and then:
-- (remove -- comments, again):
--IF INSERTING THEN
--
--END IF;
--
--IF UPDATING THEN
--
--END IF;
--
--IF DELETING THEN
--
--END IF;
-- yeah, that's the if syntax:
-- if condition then
-- actions;
-- else
-- actions;
-- end if;
--
-- for MORE on PL/SQL, see Oracle on-line doc
-- set up inventory and orders tables
-- (includes bonus: naming of primary and foreign key constraints)
--------
-- set up tables for trigger example
--------
-- set up an inventory table
-- (bonus aspect: giving a name to the primary key, which is
-- actually a constraint object)
drop table inventory cascade constraints;
create table inventory
(item_num varchar2(3),
item_name varchar2(15),
item_quantity integer default(0),
item_price decimal(5,2),
constraint inventory_pk primary key(item_num));
-- set up a simple orders table
drop table orders cascade constraints;
create table orders
(order_num varchar2(3),
cust_name varchar2(20),
item_num varchar2(3) not null,
order_quantity integer default(1),
constraint orders_pk primary key(order_num),
constraint inventory_orders_fk
foreign key (item_num) references inventory);
-- stick some initial contents into these tables
insert into inventory
values
(1, 'widget', 100, 1.11);
insert into inventory
values
(2, 'gadget', 200, 2.22);
insert into inventory
values
(3, 'whatsit', 300, 3.33);
insert into inventory
values
(4, 'doohickey', 400, 4.44);
prompt Inventory now contains:
select *
from inventory;
prompt But, there are NO orders yet:
select *
from orders;
-- run the script containing the trigger inventory_update:
-- (also available as inventory_update.sql)
@ trigger1
-- TEST 1 --- insert an order that SHOULD be fillable.
-- part 1 of test 1 --- what is CURRENTLY in orders, inventory?
prompt Test 1: Inserting a fillable order
prompt Contents of INVENTORY before Test 1
select *
from inventory;
prompt Contents of ORDERS before Test 1:
select *
from orders;
-- part 2 of test 1 --- let customer HUGHES attempt to order 10
-- of item num 1; let's make this order number 100.
prompt customer HUGHES tries to order 10 of item_num 1 (order number 100)
insert into orders
values
('100', 'Hughes', '1', 10);
-- part 3 of test 1 --- are tables NOW in the desired state?
-- (is there an order 100 as entered above?)
-- (are there 10 FEWER of item 1 in the Inventory table?)
prompt in Orders, is there now an Order 100?
select *
from orders;
prompt in Inventory, are there now 90 of item 1?
select *
from inventory;
-- TEST 2 --- is the order REFUSED if you try to order
-- too much?
prompt Test 2: See if an insert of a too-big order FAILS
-- what if Tuttle tries to order 91 of item 1?
prompt TEST 2 --- TRY to order 91 of item_num 1 (order number 200)
insert into orders
values
('200', 'Tuttle', '1', 91);
prompt there should be NO order 200 here:
select *
from orders;
prompt there should STILL be 90 of item 1:
select *
from inventory;
-- TEST 3: could also test that it DOES allow you to order ALL
-- remaining in inventory of an item...
prompt Test 3: Will an order for ALL of an item succeed?
-- what if Shmoo tries to order 200 of item 2?
prompt TEST 3 --- TRY to order 200 of item_num 2 (order number 300)
insert into orders
values
('300', 'Shmoo', '2', 200);
prompt there should be an order 300 here:
select *
from orders;
prompt there should be 0 of item 2:
select *
from inventory;
-- TEST 4: does it now fail if an order for a negative
-- number of items is attempted?
prompt Test 4: Will an order for a NEGATIVE number of items succeed?
-- what if Bob tries to order -23 of item 4?
prompt TEST 4 --- TRY to order -23 for item_num 4 (order number 400)
prompt does this fail?
insert into orders
values
('400', 'Bob', '4', -23);
prompt there should be NO order 400 here:
select *
from orders;
prompt there should be NO change to quantity of item 4 here:
select *
from inventory;
--------
-- a few more trigger examples
--------
drop table prof cascade constraints;
create table prof
(prof_id char(4),
prof_lname varchar2(25),
prof_fname varchar2(20),
primary key (prof_id));
drop table stud cascade constraints;
create table stud
(stud_id char(6),
stud_lname varchar2(30),
stud_fname varchar2(20),
advised_by char(4),
primary key (stud_id),
foreign key (advised_by) references prof(prof_id));
-- this new table allows limit_advisees trigger to
-- work. I would not have included this table by
-- design --- but, because the "before update" operation
-- does not allow even selects on the trigger's table,
-- (and not even selects on a view built based on the trigger's table!!)
-- it is being included.
-- attempted a view advisee_ct, but limit_advisees did not work with it;
--drop view advisee_ct;
drop table advisee_ct;
/*
create view advisee_ct(prof_id, num_advisees) as
select prof_id, count(*)
from prof p, stud s
where p.prof_id = s.advised_by
group by prof_id;
*/
create table advisee_ct
(prof_id char(4),
num_advisees number,
primary key (prof_id),
foreign key (prof_id) references prof
);
---------------------------------------------------------------------------
-- TRIGGER EXAMPLE #2
---------------------------------------------------------------------------
-- trigger: add_prof_ct
-- this trigger adds rows
-- advisee_ct table as professors are added
-- (yes, this WAS allowed --- evidently you can have one
-- "before actions" trigger, and one "after actions" trigger,
-- per table?!)
--
-- also available as add_prof_ct.sql
---------------------------------------------------------------------------
@ trigger2
----------------------------------------------------------------------------
-- TRIGGER EXAMPLE #3
----------------------------------------------------------------------------
-- before delete a prof, see if he/she has any advisees;
-- if so, make it so they have *no* advisor, so deletion
-- can proceed.
--
-- also available as clear_advisor.sql
----------------------------------------------------------------------------
@ trigger3
----------------------------------------------------------------------------
-- TRIGGER EXAMPLE #4
----------------------------------------------------------------------------
-- prevent insertions or updates into stud if they
-- will result in some prof having more than 3 advisees
--
-- also available as limit_advisees.sql
----------------------------------------------------------------------------
@ trigger4
prompt PRE-TEST
insert into prof
values
('0001', 'stoob', 'jack');
insert into prof
values
('0002', 'burroughs', 'ann');
insert into prof
values
('0003', 'amoussou', 'guy-alain');
insert into prof
values
('0004', 'tuttle', 'sharon');
insert into stud
values
('000001', 'alpha', 'ann', '0001');
insert into stud
values
('000002', 'beta', 'bill', '0001');
insert into stud
values
('000003', 'channel', 'charles', '0001');
insert into stud
values
('000004', 'delta', 'dawn', '0002');
insert into stud
values
('000005', 'epsilon', 'emilie', '0002');
insert into stud
values
('000006', 'function', 'frank', '0002');
insert into stud
values
('000007', 'gamma', 'gracie', '0003');
insert into stud
values
('000008', 'hogwarts', 'howard', '0003');
insert into stud
values
('000009', 'increment', 'inky', '0003');
select *
from prof;
select *
from stud;
select *
from advisee_ct;
prompt TEST 1 - deleting prof
delete from prof
where prof_id = '0001';
select *
from prof;
select *
from stud;
select *
from advisee_ct;
prompt TEST 2 - insert 1st advisee
insert into stud
values
('000010', 'jacinta', 'joseph', '0004');
select *
from stud;
select *
from advisee_ct;
prompt TEST 3 - insert 4th advisee
insert into stud
values
('000011', 'knuth', 'karl', '0002');
select *
from stud;
select *
from advisee_ct;
prompt TEST 4 - update so no advisor
update stud
set advised_by = null
where stud_id = '000004';
select *
from stud;
select *
from advisee_ct;
prompt TEST 5 - update so 2nd advisee
update stud
set advised_by = '0004'
where stud_id = '000004';
select *
from stud;
select *
from advisee_ct;
prompt TEST 6 - update so 4th advisee
update stud
set advised_by = '0003'
where stud_id = '000004';
select *
from stud;
select *
from advisee_ct;
prompt EXTRA TEST 7 --- are advisee counts correct when change advisors?
update stud
set advised_by = '0004'
where stud_id = '000007';
select *
from stud;
select *
from advisee_ct;
spool off
-- end of 315lab14-projected.sql