1 row created.
1 row created.
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP DEP
---- --------------- ---------- ---- --------- ---------- ---------- --- ---
DEPT_NAME DEPT_LOC
--------------- ---------------
7839 King President 17-NOV-91 5000 500 500
Management New York
7566 Jones Manager 7839 02-APR-91 2975 200 200
Research Dallas
7698 Blake Manager 7839 01-MAY-91 2850 300 300
Sales Chicago
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP DEP
---- --------------- ---------- ---- --------- ---------- ---------- --- ---
DEPT_NAME DEPT_LOC
--------------- ---------------
7782 Raimi Manager 7839 09-JUN-91 2450 100 100
Accounting New York
7902 Ford Analyst 7566 03-DEC-91 3000 200 200
Research Dallas
7369 Smith Clerk 7902 17-DEC-90 800 200 200
Research Dallas
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP DEP
---- --------------- ---------- ---- --------- ---------- ---------- --- ---
DEPT_NAME DEPT_LOC
--------------- ---------------
7499 Michaels Salesman 7698 20-FEB-91 1600 300 300 300
Sales Chicago
7521 Ward Salesman 7698 22-FEB-91 1250 500 300 300
Sales Chicago
7654 Martin Salesman 7698 28-SEP-91 1250 1400 300 300
Sales Chicago
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP DEP
---- --------------- ---------- ---- --------- ---------- ---------- --- ---
DEPT_NAME DEPT_LOC
--------------- ---------------
7788 Scott Analyst 7566 09-NOV-91 3000 200 200
Research Dallas
7844 Turner Salesman 7698 08-SEP-91 1500 0 300 300
Sales Chicago
7876 Adams Clerk 7788 23-SEP-91 1100 400 400
Operations Boston
EMPL EMPL_LAST_NAME JOB_TITLE MGR HIREDATE SALARY COMMISSION DEP DEP
---- --------------- ---------- ---- --------- ---------- ---------- --- ---
DEPT_NAME DEPT_LOC
--------------- ---------------
7900 James Clerk 7698 03-DEC-91 950 300 300
Sales Chicago
7934 Miller Clerk 7782 23-JAN-92 1300 100 100
Accounting New York
14 rows selected.
EMPL_LAST_NAME DEPT_NAME
--------------- ---------------
Miller Accounting
Raimi Accounting
Scott Research
Smith Research
Ford Research
Jones Research
James Sales
Turner Sales
Martin Sales
Ward Sales
Michaels Sales
EMPL_LAST_NAME DEPT_NAME
--------------- ---------------
Blake Sales
Adams Operations
King Management
Brown
15 rows selected.
EMPL_LAST_NAME DEPT_NAME
--------------- ---------------
King Management
Jones Research
Blake Sales
Raimi Accounting
Ford Research
Smith Research
Michaels Sales
Ward Sales
Martin Sales
Scott Research
Turner Sales
EMPL_LAST_NAME DEPT_NAME
--------------- ---------------
Adams Operations
James Sales
Miller Accounting
SQL Queries
15 rows selected.
EMPL_LAST_NAME DEPT_NAME
--------------- ---------------
Miller Accounting
Raimi Accounting
Scott Research
Smith Research
Ford Research
Jones Research
James Sales
Turner Sales
Martin Sales
Ward Sales
Michaels Sales
EMPL_LAST_NAME DEPT_NAME
--------------- ---------------
Blake Sales
Adams Operations
King Management
Brown
15 rows selected.
EMPL_LAST_NAME DEPT_NAME
--------------- ---------------
King Management
Jones Research
Blake Sales
Raimi Accounting
Ford Research
Smith Research
Michaels Sales
Ward Sales
Martin Sales
Scott Research
Turner Sales
EMPL_LAST_NAME DEPT_NAME
--------------- ---------------
Adams Operations
James Sales
Miller Accounting
Brown
SQL Queries
16 rows selected.
EMPL_LAST_NAME DEPT_NAME
--------------- ---------------
Miller Accounting
Raimi Accounting
Scott Research
Smith Research
Ford Research
Jones Research
James Sales
Turner Sales
Martin Sales
Ward Sales
Michaels Sales
EMPL_LAST_NAME DEPT_NAME
--------------- ---------------
Blake Sales
Adams Operations
King Management
Brown
15 rows selected.
EMPL_LAST_NAME DEPT_NAME
--------------- ---------------
King Management
Jones Research
Blake Sales
Raimi Accounting
Ford Research
Smith Research
Michaels Sales
Ward Sales
Martin Sales
Scott Research
Turner Sales
EMPL_LAST_NAME DEPT_NAME
--------------- ---------------
Adams Operations
James Sales
Miller Accounting
SQL Queries
15 rows selected.
Table dropped.
Table created.
Table dropped.
Table created.
1 row created.
1 row created.
1 row created.
1 row created.
Inventory now contains:
ITE ITEM_NAME ITEM_QUANTITY ITEM_PRICE
--- --------------- ------------- ----------
1 widget 100 1.11
2 gadget 200 2.22
3 whatsit 300 3.33
4 doohickey 400 4.44
But, there are NO orders yet:
no rows selected
Trigger created.
No errors.
Test 1: Inserting a fillable order
Contents of INVENTORY before Test 1
ITE ITEM_NAME ITEM_QUANTITY ITEM_PRICE
--- --------------- ------------- ----------
1 widget 100 1.11
2 gadget 200 2.22
3 whatsit 300 3.33
4 doohickey 400 4.44
Contents of ORDERS before Test 1:
no rows selected
customer HUGHES tries to order 10 of item_num 1 (order number 100)
Successfully updated inventory item number: 1
1 row created.
in Orders, is there now an Order 100?
ORD CUST_NAME ITE ORDER_QUANTITY
--- -------------------- --- --------------
100 Hughes 1 10
in Inventory, are there now 90 of item 1?
ITE ITEM_NAME ITEM_QUANTITY ITEM_PRICE
--- --------------- ------------- ----------
1 widget 90 1.11
2 gadget 200 2.22
3 whatsit 300 3.33
4 doohickey 400 4.44
Test 2: See if an insert of a too-big order FAILS
TEST 2 --- TRY to order 91 of item_num 1 (order number 200)
insert into orders
*
ERROR at line 1:
ORA-20601: Order number 200 cannot be placed, because order quantity 91 of item
1 is more than stock on hand of 90
ORA-06512: at "ST10.INVENTORY_UPDATE", line 75
ORA-04088: error during execution of trigger 'ST10.INVENTORY_UPDATE'
there should be NO order 200 here:
ORD CUST_NAME ITE ORDER_QUANTITY
--- -------------------- --- --------------
100 Hughes 1 10
there should STILL be 90 of item 1:
ITE ITEM_NAME ITEM_QUANTITY ITEM_PRICE
--- --------------- ------------- ----------
1 widget 90 1.11
2 gadget 200 2.22
3 whatsit 300 3.33
4 doohickey 400 4.44
Test 3: Will an order for ALL of an item succeed?
TEST 3 --- TRY to order 200 of item_num 2 (order number 300)
Successfully updated inventory item number: 2
1 row created.
there should be an order 300 here:
ORD CUST_NAME ITE ORDER_QUANTITY
--- -------------------- --- --------------
100 Hughes 1 10
300 Shmoo 2 200
there should be 0 of item 2:
ITE ITEM_NAME ITEM_QUANTITY ITEM_PRICE
--- --------------- ------------- ----------
1 widget 90 1.11
2 gadget 0 2.22
3 whatsit 300 3.33
4 doohickey 400 4.44
Test 4: Will an order for a NEGATIVE number of items succeed?
TEST 4 --- TRY to order -23 for item_num 4 (order number 400)
does this fail?
insert into orders
*
ERROR at line 1:
ORA-20600: Order number 400 cannot be placed, because order quantity -23 must
be at least 1.
ORA-06512: at "ST10.INVENTORY_UPDATE", line 49
ORA-04088: error during execution of trigger 'ST10.INVENTORY_UPDATE'
there should be NO order 400 here:
ORD CUST_NAME ITE ORDER_QUANTITY
--- -------------------- --- --------------
100 Hughes 1 10
300 Shmoo 2 200
there should be NO change to quantity of item 4 here:
ITE ITEM_NAME ITEM_QUANTITY ITEM_PRICE
--- --------------- ------------- ----------
1 widget 90 1.11
2 gadget 0 2.22
3 whatsit 300 3.33
4 doohickey 400 4.44
Table dropped.
Table created.
Table dropped.
Table created.
Table dropped.
Table created.
Trigger created.
No errors.
Trigger created.
Trigger created.
No errors.
PRE-TEST
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
1 row created.
PROF PROF_LNAME PROF_FNAME
---- ------------------------- --------------------
0001 stoob jack
0002 burroughs ann
0003 amoussou guy-alain
0004 tuttle sharon
STUD_I STUD_LNAME STUD_FNAME ADVI
------ ------------------------------ -------------------- ----
000001 alpha ann 0001
000002 beta bill 0001
000003 channel charles 0001
000004 delta dawn 0002
000005 epsilon emilie 0002
000006 function frank 0002
000007 gamma gracie 0003
000008 hogwarts howard 0003
000009 increment inky 0003
9 rows selected.
PROF NUM_ADVISEES
---- ------------
0001 3
0002 3
0003 3
0004 0
TEST 1 - deleting prof
1 row deleted.
PROF PROF_LNAME PROF_FNAME
---- ------------------------- --------------------
0002 burroughs ann
0003 amoussou guy-alain
0004 tuttle sharon
STUD_I STUD_LNAME STUD_FNAME ADVI
------ ------------------------------ -------------------- ----
000001 alpha ann
000002 beta bill
000003 channel charles
000004 delta dawn 0002
000005 epsilon emilie 0002
000006 function frank 0002
000007 gamma gracie 0003
000008 hogwarts howard 0003
000009 increment inky 0003
9 rows selected.
PROF NUM_ADVISEES
---- ------------
0002 3
0003 3
0004 0
TEST 2 - insert 1st advisee
1 row created.
STUD_I STUD_LNAME STUD_FNAME ADVI
------ ------------------------------ -------------------- ----
000001 alpha ann
000002 beta bill
000003 channel charles
000004 delta dawn 0002
000005 epsilon emilie 0002
000006 function frank 0002
000007 gamma gracie 0003
000008 hogwarts howard 0003
000009 increment inky 0003
000010 jacinta joseph 0004
10 rows selected.
PROF NUM_ADVISEES
---- ------------
0002 3
0003 3
0004 1
TEST 3 - insert 4th advisee
insert into stud
*
ERROR at line 1:
ORA-20600: Student 000011 cannot be inserted with advisor of ann burroughs,
because he/she already has 3 advisees
ORA-06512: at "ST10.LIMIT_ADVISEES", line 39
ORA-04088: error during execution of trigger 'ST10.LIMIT_ADVISEES'
STUD_I STUD_LNAME STUD_FNAME ADVI
------ ------------------------------ -------------------- ----
000001 alpha ann
000002 beta bill
000003 channel charles
000004 delta dawn 0002
000005 epsilon emilie 0002
000006 function frank 0002
000007 gamma gracie 0003
000008 hogwarts howard 0003
000009 increment inky 0003
000010 jacinta joseph 0004
10 rows selected.
PROF NUM_ADVISEES
---- ------------
0002 3
0003 3
0004 1
TEST 4 - update so no advisor
1 row updated.
STUD_I STUD_LNAME STUD_FNAME ADVI
------ ------------------------------ -------------------- ----
000001 alpha ann
000002 beta bill
000003 channel charles
000004 delta dawn
000005 epsilon emilie 0002
000006 function frank 0002
000007 gamma gracie 0003
000008 hogwarts howard 0003
000009 increment inky 0003
000010 jacinta joseph 0004
10 rows selected.
PROF NUM_ADVISEES
---- ------------
0002 2
0003 3
0004 1
TEST 5 - update so 2nd advisee
1 row updated.
STUD_I STUD_LNAME STUD_FNAME ADVI
------ ------------------------------ -------------------- ----
000001 alpha ann
000002 beta bill
000003 channel charles
000004 delta dawn 0004
000005 epsilon emilie 0002
000006 function frank 0002
000007 gamma gracie 0003
000008 hogwarts howard 0003
000009 increment inky 0003
000010 jacinta joseph 0004
10 rows selected.
PROF NUM_ADVISEES
---- ------------
0002 2
0003 3
0004 2
TEST 6 - update so 4th advisee
update stud
*
ERROR at line 1:
ORA-20600: Student 000004 cannot be inserted with advisor of guy-alain
amoussou, because he/she already has 3 advisees
ORA-06512: at "ST10.LIMIT_ADVISEES", line 39
ORA-04088: error during execution of trigger 'ST10.LIMIT_ADVISEES'
STUD_I STUD_LNAME STUD_FNAME ADVI
------ ------------------------------ -------------------- ----
000001 alpha ann
000002 beta bill
000003 channel charles
000004 delta dawn 0004
000005 epsilon emilie 0002
000006 function frank 0002
000007 gamma gracie 0003
000008 hogwarts howard 0003
000009 increment inky 0003
000010 jacinta joseph 0004
10 rows selected.
PROF NUM_ADVISEES
---- ------------
0002 2
0003 3
0004 2
EXTRA TEST 7 --- are advisee counts correct when change advisors?
1 row updated.
STUD_I STUD_LNAME STUD_FNAME ADVI
------ ------------------------------ -------------------- ----
000001 alpha ann
000002 beta bill
000003 channel charles
000004 delta dawn 0004
000005 epsilon emilie 0002
000006 function frank 0002
000007 gamma gracie 0004
000008 hogwarts howard 0003
000009 increment inky 0003
000010 jacinta joseph 0004
10 rows selected.
PROF NUM_ADVISEES
---- ------------
0002 2
0003 2
0004 3