========
notice tire currently has a price of 39.99
PART_NUM PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
---------- ------------------------- ---------------- ---------- --- ---------
10603 hexagonal wrench 13 9.99 003 05-SEP-00
10604 tire 287 39.99 333 06-SEP-00
10605 hammer 30 9.99 003 01-SEP-00
1 row updated.
========
notice tire now has a new price of 66.66
PART_NUM PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
---------- ------------------------- ---------------- ---------- --- ---------
10603 hexagonal wrench 13 9.99 003 05-SEP-00
10604 tire 287 66.66 333 06-SEP-00
10605 hammer 30 9.99 003 01-SEP-00
2 rows updated.
========
notice that parts with a price of 9.99 now have a
quantity on hand of 0:
PART_NUM PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
---------- ------------------------- ---------------- ---------- --- ---------
10603 hexagonal wrench 0 9.99 003 05-SEP-00
10604 tire 287 66.66 333 06-SEP-00
10605 hammer 0 9.99 003 01-SEP-00
3 rows updated.
========
notice ALL parts now have a last-inspected date of
today:
PART_NUM PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
---------- ------------------------- ---------------- ---------- --- ---------
10603 hexagonal wrench 0 9.99 003 11-NOV-16
10604 tire 287 66.66 333 11-NOV-16
10605 hammer 0 9.99 003 11-NOV-16
2 rows updated.
========
parts whose quantity on hand is less than that of part 10604
now have a last-inspected date that is the max hiredate
from empl...!:
PART_NUM PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
---------- ------------------------- ---------------- ---------- --- ---------
10603 hexagonal wrench 0 9.99 003 23-JAN-92
10604 tire 287 66.66 333 11-NOV-16
10605 hammer 0 9.99 003 23-JAN-92
1 row deleted.
========
note that hammer part has now been deleted:
PART_NUM PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
---------- ------------------------- ---------------- ---------- --- ---------
10603 hexagonal wrench 0 9.99 003 23-JAN-92
10604 tire 287 66.66 333 11-NOV-16
========
adding two more parts, two kinds of bolts:
1 row created.
1 row created.
PART_NUM PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
---------- ------------------------- ---------------- ---------- --- ---------
10603 hexagonal wrench 0 9.99 003 23-JAN-92
10604 tire 287 66.66 333 11-NOV-16
10606 3/8 in bolt 5000 .03 005 11-NOV-16
10607 7/8 in bolt 2655 .04 005 11-NOV-16
=====
demo that the FROM keyword is optional
in DELETE: deleting parts whose level code is 005:
2 rows deleted.
PART_NUM PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
---------- ------------------------- ---------------- ---------- --- ---------
10603 hexagonal wrench 0 9.99 003 23-JAN-92
10604 tire 287 66.66 333 11-NOV-16
========
re-inserting those bolts:
1 row created.
1 row created.
PART_NUM PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
---------- ------------------------- ---------------- ---------- --- ---------
10603 hexagonal wrench 0 9.99 003 23-JAN-92
10604 tire 287 66.66 333 11-NOV-16
10606 3/8 in bolt 5000 .03 005 11-NOV-16
10607 7/8 in bolt 2655 .04 005 11-NOV-16
========
deleting from parts those rows whose quantity-on-hand
is greater than the average quantity-on-hand:
2 rows deleted.
PART_NUM PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
---------- ------------------------- ---------------- ---------- --- ---------
10603 hexagonal wrench 0 9.99 003 23-JAN-92
10604 tire 287 66.66 333 11-NOV-16
========
demoing deleting ALL rows from the table:
...oops, cannot! There is an order depending on one of them!
delete from parts
*
ERROR at line 1:
ORA-02292: integrity constraint (ST10.SYS_C00882899) violated - child record
found
PART_NUM PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
---------- ------------------------- ---------------- ---------- --- ---------
10603 hexagonal wrench 0 9.99 003 23-JAN-92
10604 tire 287 66.66 333 11-NOV-16
========
FAILS, there is an order of the part
I am trying to delete...!
delete from parts
*
ERROR at line 1:
ORA-02292: integrity constraint (ST10.SYS_C00882899) violated - child record
found
========
FAILS, there is not a part with the number
I am trying to update this order's
part number to:
update orders
*
ERROR at line 1:
ORA-02291: integrity constraint (ST10.SYS_C00882899) violated - parent key not
found
========
demoing deleting ALL rows from the orders table:
1 row deleted.
no rows selected
========
rolling back all these changes
Rollback complete.
========
this fails, cannot add a NOT NULL column if
rows currently exist
alter table parts
*
ERROR at line 1:
ORA-01758: table must be empty to add mandatory (NOT NULL) column
========
can add a new column that CAN be null
Table altered.
========
note that parts now also has a supplier column:
Name Null? Type
----------------------------------------- -------- ----------------------------
PART_NUM NOT NULL NUMBER(38)
PART_NAME VARCHAR2(25)
QUANTITY_ON_HAND NUMBER(38)
PRICE NUMBER(6,2)
LEVEL_CODE CHAR(3)
LAST_INSPECTED DATE
SUPPLIER VARCHAR2(20)
========
(and can now give supplier column a value using update):
2 rows updated.
PART_NUM PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
---------- ------------------------- ---------------- ---------- --- ---------
SUPPLIER
--------------------
10603 hexagonal wrench 13 9.99 003 05-SEP-00
Acme
10604 tire 287 39.99 333 06-SEP-00
Acme
10605 hammer 30 9.99 003 01-SEP-00
========
(re-)creating painter and painting tables for sequence play
Table dropped.
Table created.
Table dropped.
Table created.
========
I would like painter_seq to be a sequence object
that starts at 100 and increments by 2
Sequence dropped.
Sequence created.
========
using painter_seq in inserts into painter:
1 row created.
1 row created.
1 row created.
PTR_NUM PTR_LNAME PTR_FNAME
---------- ------------------------------ ---------------
102 Van Gogh Vincent
104 Monet Claude
106 Da Vinci Leonardo
========
and using currval to give a painting the same
current value of ptr_num as just used for parent painter row
1 row created.
PTG_NUM PTR_NUM PTG_TITLE
---------- ---------- ------------------------------
1 106 Mona Lisa
========
seems to be problematic to use nextval and currval
in select statements:
where ptr_num = painter_seq.currval
*
ERROR at line 3:
ORA-02287: sequence number not allowed here
========
you CAN get the current value of a sequence querying
using dual:
CURRVAL
----------
106
========
...but that query cannot be used as a sub-select?!
where ptr_num = (select painter_seq.currval
*
ERROR at line 3:
ORA-02287: sequence number not allowed here
========
sequence is a tool, use it wisely,
and not goofily:
1 row created.
========
goofy result..!
PART_NUM PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
---------- ------------------------- ---------------- ---------- --- ---------
SUPPLIER
--------------------
10603 hexagonal wrench 13 9.99 003 05-SEP-00
Acme
10604 tire 287 39.99 333 06-SEP-00
Acme
10605 hammer 30 9.99 003 01-SEP-00
PART_NUM PART_NAME QUANTITY_ON_HAND PRICE LEV LAST_INSP
---------- ------------------------- ---------------- ---------- --- ---------
SUPPLIER
--------------------
10614 stuff108 108 .13 005 11-NOV-16
Robot108
View dropped.
View created.
========
selecting all "rows" from view short_empl:
EMPL EMPL_LAST_NAME JOB_TITLE MGR
---- --------------- ---------- ----
7839 King President
7566 Jones Manager 7839
7698 Blake Manager 7839
7782 Raimi Manager 7839
7902 Ford Analyst 7566
7369 Smith Clerk 7902
7499 Michaels Sales 7698
7521 Ward Sales 7698
7654 Martin Sales 7698
7788 Scott Analyst 7566
7844 Turner Sales 7698
EMPL EMPL_LAST_NAME JOB_TITLE MGR
---- --------------- ---------- ----
7876 Adams Clerk 7788
7900 James Clerk 7698
7934 Miller Clerk 7782
14 rows selected.
========
deleting Clerks from empl,
4 rows deleted.
=======
...and now the removed empl clerks do not show up when you
next use this view:
EMPL EMPL_LAST_NAME JOB_TITLE MGR
---- --------------- ---------- ----
7839 King President
7566 Jones Manager 7839
7698 Blake Manager 7839
7782 Raimi Manager 7839
7902 Ford Analyst 7566
7499 Michaels Sales 7698
7521 Ward Sales 7698
7654 Martin Sales 7698
7788 Scott Analyst 7566
7844 Turner Sales 7698
10 rows selected.
========
describe does work with views:
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPL_NUM NOT NULL CHAR(4)
EMPL_LAST_NAME NOT NULL VARCHAR2(15)
JOB_TITLE VARCHAR2(10)
MGR CHAR(4)
========
can use a view in a select as if it were a "real" table
using it in a projection from a join:
EMPL_LAST_NAME CUST_LNAME
--------------- --------------------
Michaels Firstly
Martin Secondly
Michaels Thirdly
========
and you can use a view in creating another view:
View dropped.
View created.
========
selecting all "rows" from view cust_rep_display:
EMPL_LAST_NAME CUST_LNAME
--------------- --------------------
Michaels Firstly
Martin Secondly
Michaels Thirdly
========
AVOID using double-quoted column aliases
in views, you then must express those resulting columns in
double quotes from then on out
(we will have a prettier way, maybe next week)
View dropped.
View created.
========
describing, then selecting all "rows" from
view short_empl2:
Name Null? Type
----------------------------------------- -------- ----------------------------
NAME NOT NULL VARCHAR2(15)
job category VARCHAR2(10)
MANAGER CHAR(4)
NAME job catego MANA
--------------- ---------- ----
King President
Jones Manager 7839
Blake Manager 7839
Raimi Manager 7839
Ford Analyst 7566
Michaels Sales 7698
Ward Sales 7698
Martin Sales 7698
Scott Analyst 7566
Turner Sales 7698
10 rows selected.
========
this FAILS
you MUST use the column names you have given for that view
when using the view...
select empl_last_name
*
ERROR at line 1:
ORA-00904: "EMPL_LAST_NAME": invalid identifier
========
so, this works:
NAME
---------------
King
Jones
Blake
Raimi
Ford
Michaels
Ward
Martin
Scott
Turner
10 rows selected.
========
and column name given with double quotes must
continue to be specified with double quotes
when view is used:
order by job category
*
ERROR at line 3:
ORA-00933: SQL command not properly ended
========
CAN order short_empl2 by "job category":
NAME job catego MANA
--------------- ---------- ----
Ford Analyst 7566
Scott Analyst 7566
Blake Manager 7839
Raimi Manager 7839
Jones Manager 7839
King President
Turner Sales 7698
Michaels Sales 7698
Ward Sales 7698
Martin Sales 7698
10 rows selected.
========
and can project "job category" from short_empl2, also:
job catego
----------
Sales
Sales
Sales
Sales
President
Manager
Manager
Manager
Analyst
Analyst
10 rows selected.
View dropped.
View created.
========
selection of all "rows" from short_empl3
(with column names given as column aliases in
its defining select):
LAST_NAME POSITION
--------------- ----------
King President
Jones Manager
Blake Manager
Raimi Manager
Ford Analyst
Michaels Sales
Ward Sales
Martin Sales
Scott Analyst
Turner Sales
10 rows selected.
View dropped.
========
FAILS; when defining a view,
MUST give projected computations a column alias:
select job_title, avg(salary)
*
ERROR at line 2:
ORA-00998: must name this expression with a column alias
========
succeeds when do give such computations a proper column
alias:
View created.
========
selection of all "rows" from view salary_avgs:
JOB SALARY_AVG
---------- ----------
Manager 2758.33333
Analyst 3000
President 5000
Sales 1400