Please send questions to
st10@humboldt.edu .
--
-- 315lab10-projected.sql
--
-- last modified: 10-27-10 (during lab)
-- uncomment if needed -- should already have these tables in your
-- database, though
--
-- @ set-up-ex-tbls
-- SET UP for WEEK 10 LAB EXERCISE:
-- * ssh to nrs-labs
-- * mkdir 315lab10
-- * chmod 700 315lab10
-- * cd 315lab10
-- * nano lab10.sql
-- * ...and start up today's lab script with:
-- * comments containing your name, Week 10 lab, today's date
-- * also spool on and spool off commands:
-- spool lab10-results.txt
-- spool off
-- REMEMBER: put the queries for today's lab exercise
-- IN BETWEEN the spool and spool off commands!
spool lab10-results.txt
--********************************************************************
-- examples of ADDITIONAL checks you can make, IN the create
-- table statement
-- (useful for more-precisely specifying the domain of a column!)
-- (allows the DBMS to help enforce data integrity...)
--*******************************************************************
-- maxpoints integer not null, -- this column MUST have a value
-- quantity integer default 1, -- put 1 in if NO value is
-- -- inserted EXPLICITLY for this column
-- car_color varchar2(10) check(car_color IN ('red', 'green', 'white')),
-- quiz_grade integer check(quiz_grade >= 0 AND quiz_grade <= 100),
-- quiz_grade integer check(quiz_grade between 0 and 100),
--****************************************
-- union, intersect, minus, and
-- the "overall" select statement;
-- union, intersect, and minus are SET-THEORETIC operations --
-- they are set operations, where the relations are considered
-- sets of ROWS (which is what they are, based on the mathematical
-- definition of a relation, remember)
-- thing to note -- these each expects two select statements as operands!
-- (sub-select)
-- UNION or INTERSECT or MINUS
-- (sub-select)
--
-- ...and the UNION'd or INTERSECT'd or MINUS'd result is the OUTER SELECT,
-- in this case!
--
-- COURSE STYLE STANDARD: to make this clear, each select operand of
-- of UNION or INTERSECT or MINUS is expected to be surrounded by
-- parentheses (as you will see in today's examples, below)
-- (want an order-by? it goes OUTSIDE the second operand select statement --
-- order-by's must be at the "outer" level, and in this case the overall
-- UNION or INTERSECT or MINUS is the outer select...)
-----
-- UNION
-----
-- union of two sets A and B: all of the elements in set A and all of the
-- elements in set B
-- union of two relations A and B, then?
-- all of the tuples/rows in relation A and
-- all of the tuples/rows in relation B
-- these relations must be UNION-COMPATIBLE -- must have the same number
-- of columns, where the columns are of compatible domains;
-- some UNION examples:
-- the union of department numbers of departments in Chicago, and
-- department numbers of employees who are managers
(select dept_num
from dept
where dept_loc = 'Chicago')
union
(select dept_num
from empl
where job_title = 'Manager');
-- now, order these by reverse order of dept_num (see how the
-- order by clause is OUTSIDE the second sub-select's parentheses?)
(select dept_num
from dept
where dept_loc = 'Chicago')
union
(select dept_num
from empl
where job_title = 'Manager')
order by dept_num desc;
-- the two relations being "union'ed" must be union-compatible ---
-- these ARE NOT: (this will fail)
(select dept_num, dept_name
from dept
where dept_loc = 'Chicago')
union
(select dept_num
from empl
where job_title = 'Manager');
-- these are not union-compatible, either: (this will fail, too)
(select dept_num
from dept
where dept_loc = 'Chicago')
union
(select salary
from empl
where job_title = 'Manager');
-- sadly, the SQL interpreter cannot tell if two compatible types
-- are not compatible in terms of meaning... (this runs, but results
-- are nonsensical)
(select dept_num
from dept
where dept_loc = 'Chicago')
union
(select empl_num
from empl
where job_title = 'Manager');
-- so, remember ---make those union sub-selects union-compatible...
-- LAB EX #1 - write a query that performs the union of job_title and mgr
-- for employees whose salary is less than 2000 and job_title and mgr
-- for employees with a non-NULL commission
(select job_title, mgr
from empl
where salary < 2000)
UNION
(select job_title, mgr
from empl
where commission is not null);
-- a nice example where union is QUITE useful ---
-- what if you'd like to know the "total" salary for all,
-- including commissions IF applicable? UNION can help you
-- with that!
-- (note, too --- in the context of THIS union, there are TWO
-- columns, and you can ORDER by EITHER by including the desired
-- COLUMN HEADING --- even if it is an ALIAS!!!)
-- (SQL sees the 2 relations, at order-by's TOP level!)
-- this doesn't work like you'd like:
select empl_last_name, salary + commission
from empl;
-- ...but a union CAN make it work:
(select empl_last_name "Employee", salary "Total Compensation"
from empl
where commission is null)
union
(select empl_last_name "Employee", salary + commission "Total Compensation"
from empl
where commission is not null)
order by "Total Compensation";
-- another handy UNION use...
-- add another department:
insert into dept
values
('600', 'Computing', 'Arcata');
-- how many employees are in each department?
select dept_name, count(*)
from empl e, dept d
where e.dept_num = d.dept_num
group by dept_name;
select dept_name, count(*)
from empl e join dept d on e.dept_num = d.dept_num
group by dept_name;
-- uh oh --- cannot get departments with a count of 0 this way!
-- BUT:
(select dept_name, count(*) "# of Employees"
from empl e, dept d
where e.dept_num = d.dept_num
group by dept_name)
union
(select dept_name, 0 "# of Employees"
from dept d
where not exists
(select 'a'
from empl e
where e.dept_num = d.dept_num))
order by "# of Employees" desc;
(select dept_name, count(*) "# of Employees"
from empl e join dept d on e.dept_num = d.dept_num
group by dept_name)
union
(select dept_name, 0 "# of Employees"
from dept d
where not exists
(select 'a'
from empl e
where e.dept_num = d.dept_num))
order by "# of Employees" desc;
-----
-- UNION ALL
-----
-- NOTE -- "union" operator DOES remove duplicates (there will NOT be
-- duplicate rows in the union operator's result in SQL);
-- ...it is a "true" set-theoretic union, then, in that sense;
-- IF you want duplicates, use "union all" in place of "union" above.
-- (to get a NOT-quite-"true" set-theoretic union, including duplicates
-- if a tuple is in BOTH relations)
(select empl_last_name, dept_num, hiredate
from empl
where hiredate > '01-Jul-1991')
union all
(select empl_last_name, d.dept_num, hiredate
from empl e, dept d
where d.dept_num = e.dept_num
and dept_loc = 'Dallas')
order by empl_last_name;
(select empl_last_name, dept_num, hiredate
from empl
where hiredate > '01-Jul-1991')
union all
(select empl_last_name, d.dept_num, hiredate
from empl e join dept d on d.dept_num = e.dept_num
where dept_loc = 'Dallas')
order by empl_last_name;
-- no duplicates here!
(select empl_last_name, dept_num, hiredate
from empl
where hiredate > '01-Jul-1991')
union
(select empl_last_name, d.dept_num, hiredate
from empl e, dept d
where d.dept_num = e.dept_num
and dept_loc = 'Dallas')
order by empl_last_name;
(select empl_last_name, dept_num, hiredate
from empl
where hiredate > '01-Jul-1991')
union
(select empl_last_name, d.dept_num, hiredate
from empl e join dept d on d.dept_num = e.dept_num
where dept_loc = 'Dallas')
order by empl_last_name;
-----
-- "intersect" works similarly to union --- AND NOTE THAT
-- UNION-COMPATIBILITY IS STILL REQUIRED
-----
-- intersect: the intersection of two sets A and B is the set of
-- elements that are each in set A and in set B
-- the intersection of two relations A and B is the set of
-- tuples/rows that are each in relation A and in relation B
-- the INTERSECTION of employees hired after 7-1-91 intersected with
-- employees located in Dallas (just project out the employee last name,
-- dept_num, and hiredate)
(select empl_last_name, dept_num, hiredate
from empl
where hiredate > '01-Jul-1991')
intersect
(select empl_last_name, d.dept_num, hiredate
from empl e, dept d
where d.dept_num = e.dept_num
and dept_loc = 'Dallas')
order by empl_last_name;
(select empl_last_name, dept_num, hiredate
from empl
where hiredate > '01-Jul-1991')
intersect
(select empl_last_name, d.dept_num, hiredate
from empl e join dept d on d.dept_num = e.dept_num
where dept_loc = 'Dallas')
order by empl_last_name;
-- LAB EXERCISE #2
-- write a query that performs the intersection of:
-- * job_title and mgr of employees whose salary is less than 2000
-- * job_title and mgr of employees with a non-null commission
-----
-- MINUS
-----
-- "minus" is the Oracle keyword used for the DIFFERENCE set-theoretic
-- operation (A-B are those rows in A not also in B)...
-- (SQL minus is used in an analogous way to intersect, union, and
-- UNION COMPATIBILITY IS STILL REQUIRED.)
(select empl_last_name, dept_num, hiredate
from empl
where hiredate > '01-Jul-1991')
minus
(select empl_last_name, d.dept_num, hiredate
from empl e, dept d
where d.dept_num = e.dept_num
and dept_loc = 'Dallas')
order by empl_last_name;
(select empl_last_name, dept_num, hiredate
from empl
where hiredate > '01-Jul-1991')
minus
(select empl_last_name, d.dept_num, hiredate
from empl e join dept d on d.dept_num = e.dept_num
where dept_loc = 'Dallas')
order by empl_last_name;
-- UNION and INTERSECT are commutative -- which operand comes
-- first doesn't matter.
-- MINUS is NOT commutative -- the operand order DOES matter.
-- ...that is,
-- this does NOT have the same result:
(select empl_last_name, d.dept_num, hiredate
from empl e, dept d
where d.dept_num = e.dept_num
and dept_loc = 'Dallas')
minus
(select empl_last_name, dept_num, hiredate
from empl
where hiredate > '01-Jul-1991')
order by empl_last_name;
(select empl_last_name, d.dept_num, hiredate
from empl e join dept d on d.dept_num = e.dept_num
where dept_loc = 'Dallas')
minus
(select empl_last_name, dept_num, hiredate
from empl
where hiredate > '01-Jul-1991')
order by empl_last_name;
-- hmm --- doesn't minus provide another way to ask which departments
-- have no employees? Those rows in dept not also in the join between dept
-- and empl --- those rows for which there does not exist an employee...
(select dept_name
from dept)
minus
(select dept_name
from dept d, empl e
where d.dept_num = e.dept_num);
(select dept_name
from dept)
minus
(select dept_name
from dept d join empl e on d.dept_num = e.dept_num);
-- be careful: in the world of the outer select, here, the
-- only columns known to the order-by-clause are those
-- actually projected by the sub-selects;
-- below, this is fine -- you can order by any column of empl,
-- because that is the "scope" of this outer select,
-- the columns of the table in the from clause...
select empl_last_name
from empl
order by salary;
-- but, this will NOT work -- because the outer-level's order-by only
-- knows about the 3 columns projected by the minus'd sub-selects:
(select empl_last_name, dept_num, hiredate
from empl
where hiredate > '01-Jul-1991')
minus
(select empl_last_name, d.dept_num, hiredate
from empl e, dept d
where d.dept_num = e.dept_num
and dept_loc = 'Dallas')
order by salary;
(select empl_last_name, dept_num, hiredate
from empl
where hiredate > '01-Jul-1991')
minus
(select empl_last_name, d.dept_num, hiredate
from empl e join dept d on d.dept_num = e.dept_num
where dept_loc = 'Dallas')
order by salary;
-- be sure to look over "full" select statement summary,
-- to be posted to course web page...
-- LAB EXERCISE #3
-- write a query that performs the difference of:
-- * job_title and mgr of employees whose salary is less than 2000
-- * job_title and mgr of employees with a non-null commission
-- here is parts table again
drop table parts cascade constraints;
create table parts
(part_num char(5),
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)
);
-- let's put some rows into this table
insert into parts
values
('10601', '3/8 in lug nut', 1000, 0.02, '002', '09-SEP-2002');
-- this WILL NOT WORK ---part name is longer than column part_name
-- allows
insert into parts
values
('10602', '5/8 in lug nut from Argentina or Brazil', 16, 4.50, '105',
'04-SEP-2000');
-- ...price is too large
insert into parts
values
('10602', '5/8 in lug nut', 16, 10000.00, '105',
'04-SEP-2000');
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');
insert into parts
values
('10606', '3/8 in bolt', 5000, 0.03, '005', '04-SEP-2000');
insert into parts
values
('10607', '7/8 in bolt', 2655, 0.04, '005', '02-SEP-2000');
prompt the contents of the parts table:
select *
from parts;
-------
-- UPDATE command
-------
-- here's a first, simple example of how you can UPDATE or modify
-- something ALREADY in a table: (MORE on update command later!)
--
-- update tbl_name
-- set attrib1 = expression1
-- where attrib2 = expression2;
--
-- note that EVERY row where attrib2 = expression2 will have attrib1
-- set to the value of the given expression1!
-- (no where clause? ALL the rows in the table will be updated!)
update parts
set price = 66.66
where part_num = '10604';
prompt is 10604's price now 66.66?
select *
from parts;
update parts
set quantity_on_hand = 0
where price = 9.99;
prompt note that BOTH wrench AND hammer have price of 9.99,
prompt and now both indeed have quantity_on_hand of 0
select *
from parts;
-- if you leave off the where clause, ALL rows in the table
-- shall be updated
-- (sysdate, in Oracle, returns the current date/time)
update parts
set last_inspected = sysdate;
select *
from parts;
-- note that the set and where clauses can get as complicated as you
-- would like (or can think of)
update parts
set last_inspected = (select max(hiredate)
from empl)
where quantity_on_hand < (select quantity_on_hand
from parts
where part_num = '10607');
-- LAB EXERCISE #4
-- write an update command that will set the commission attribute
-- to 10 for all employees whose job_title is 'Manager'
--
-- THEN show all of the contents of the empl table
-------
-- DELETE command
-------
-- here's a first, simple example of how you can DELETE a row
-- from a table without deleting the entire table: (MORE on
-- delete command later!)
--
-- delete from tbl_name
-- where attrib1 = expression;
--
-- again, note that ALL rows in which attrib1 = expression will
-- be deleted;
-- (and no where clause? ALL the rows are deleted! But the empty table
-- remains...)
delete from parts
where price = 66.66;
prompt is 10604, which had price 66.66, now deleted?
select *
from parts;
delete from parts
where level_code = '005';
prompt are both 10606 and 10607, which both had level_code 005, now deleted?
select *
from parts;
-- and if you leave off the where clause, ALL rows in the
-- table are deleted:
delete from parts;
select *
from parts;
-- hey! the from is optional!
delete parts;
-- ...works, too
-- LAB EXERCISE #5:
-- write a delete command that will delete employees whose job_title
-- is 'Clerk'
-- and then write a query to show all the contents of the empl table
-- let's put some rows BACK into this table
insert into parts
values
('10601', '3/8 in lug nut', 1000, 0.02, '002', '09-SEP-2002');
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');
insert into parts
values
('10606', '3/8 in bolt', 5000, 0.03, '005', '04-SEP-2000');
insert into parts
values
('10607', '7/8 in bolt', 2655, 0.04, '005', '02-SEP-2000');
prompt the contents of the parts table:
select *
from parts;
-- and a delete can have as big'n'ugly a where clause as you
-- can imagine
delete from parts
where quantity_on_hand > (select avg(quantity_on_hand)
from parts);
select *
from parts;
-- NOW: create orders table, which has a foreign key referencing
-- the parts table
-- NOTE: part_num had to be defined/declared within orders BEFORE
-- it could be made a foreign key; be sure that its type matches
-- the column it is referring to, also.
drop table orders cascade constraints;
create table orders
(order_num char(6),
cust_num char(8),
part_num char(5),
order_date date,
quantity integer,
order_code char(1),
primary key (order_num),
foreign key (part_num) references parts
);
--******************************************************
-- REMEMBER: many DBMS's support REFERENTIAL INTEGRITY ---
-- if a foreign key is defined, then no value may go
-- in a foreign key column that is not ALSO in the
-- referenced column. (a child must have a parent)
-- (likewise, you cannot remove a row that is referenced
-- by a foreign key column in another table ---
-- you cannot remove a parent if it has a child)
--*******************************************************
-- these inserts into orders will work --- they are orders
-- for existing parts:
insert into orders
values
('111111', '11111111', '10601', '01-Feb-2000', 6, 'B');
-- THIS WILL FAIL --- an order cannot be placed for a part
-- whose number is NOT in the parts table:
insert into orders
values
('111112', '11111111', '10106', '01-Feb-2000', 6, 'B');
-- note that part 10601 cannot be removed now that an order
-- is referencing it: (so, THIS ALSO FAILS):
delete from parts
where part_num = '10601';
-- you cannot update an order to have a non-existent part,
-- either
update parts
set part_num = '13'
where part_num = '10601';
--********************************************************************
-- examples of ADDITIONAL checks you can make, IN the create
-- table statement
--*******************************************************************
-- maxpoints integer not null, -- this column MUST have a value
-- quantity integer default 1, -- put 1 in if NO value is
-- -- inserted EXPLICITLY for this column
-- car_color varchar2(10) check(car_color IN ('red', 'green', 'white')),
-- quiz_grade integer check(quiz_grade >= 0 AND quiz_grade <= 100),
-- quiz_grade integer check(quiz_grade between 0 and 100),
-- now, let's use some of these in a new version of table orders:
drop table orders cascade constraints;
create table orders
(order_num char(6),
cust_num char(8) not null,
part_num char(5) not null,
order_date date,
quantity integer default 1 not null,
order_code char(1) check(order_code in ('B',
'I',
'G')),
delivery_code char(1) check(delivery_code in
('U', 'F', 'P')) not null,
primary key (order_num),
foreign key (part_num) references parts
);
-- notice that, in all of these inserts into orders,
-- the part number is a part number ALREADY in the parts
-- table;
insert into orders
values
('111111', '11111111', '10601', '01-Feb-2000', 6, 'B', 'U');
-- even though order_code has a check clause, it can still be null...
insert into orders(order_num, cust_num, part_num, order_date, quantity,
delivery_code)
values
('333333', '33333333', '10601', '01-Feb-2000', 8, 'F');
insert into orders(order_num, part_num, cust_num, order_date, quantity,
delivery_code)
values
('222222', '10605', '22222222', '1-Jan-00', 4, 'P');
-- show that default clause works for quantity, if NO value put in
insert into orders(order_num, part_num, cust_num, order_date, delivery_code)
values
('444444', '10601', '22222222', '1-Feb-00', 'U');
-- note, EXPLICIT insertion of null OVERRIDES default of quantity!
-- ...and so this FAILS because it violates the not-null constraint
insert into orders
values
('555555', '44444444', '10601', '3-Mar-98', NULL, 'G', 'U');
-- BAD inserts, no biscuit
-- order_code MUST be 'B', 'I' or 'G':
insert into orders
values
('666666', '44444444', '10601', '25-Dec-99', 5, 'b', 'P');
-- cust_num CANNOT be null because I specified it to be "not null"
insert into orders(order_num, part_num, delivery_code)
values
('777777', '10601', 'U');
-- part_num MUST be for a part already in parts
insert into orders
values
('888888', '22222222', '99999', '07-Feb-2001', 88, 'I', 'P');
select *
from orders;
-- note that changing a table's CONTENTS is different from
-- changing a table's STRUCTURE ---
-- delete deletes a table's rows, but the TABLE (even if empty)
-- remains;
-- to get rid of a whole table structure, you use the DROP command
-- likewise, update lets you change the contents of an existing
-- row or rows --
-- but what if you want to change an existing table's STRUCTURE?
-- ...you use the ALTER command
--INCLUDED JUST FOR REFERENCE - NOT COVERED IN LAB
--*********************************************************************
-- you should not regularly HAVE to alter tables after the fact ---
-- if they are designed well --- BUT, well, sometimes...
--*********************************************************************
-- here are some examples, JUST in case
-- what if I want to add a NEW column to the parts table --- say,
-- a supplier column?
alter table parts
add
(supplier varchar2(20)
);
-- see the new column in parts' structure?
describe parts
-- see how its value is null for all currently-existing rows?
-- (yes, it is ugly! too wide a line... it is wrapping)
select *
from parts;
-- yes, you can use the 'update' command to add a supplier to
-- existing rows as desired
update parts
set supplier = 'Acme'
where part_num in ('10603', '10604');
-- see?
select *
from parts;
-- oh, let's make all the suppliers 'Acme'
-- (if there's no WHERE clause, ALL rows will be updated;)
update parts
set supplier = 'Acme';
-- see?
select *
from parts;
-- experiment: how DOES default clause work when adding a column?
alter table parts
add
(whatsit varchar2(20) default 'hi'
);
-- look --- default IS applied to the new column in the existing rows!
select *
from parts;
-- here's adding a foreign key after the fact (DON'T make a
-- habit of this --- see class coding standards)
-- (this is ONLY for UNUSUAL cases, such as "circular" relationships
-- as discussed in the Week 10 Lecture - Database Design Part 2)
drop table customer cascade constraints;
create table customer
(cust_num char(8),
cust_lname varchar2(20) not null,
cust_fname varchar2(20),
cust_phone varchar2(12),
primary key (cust_num)
);
insert into customer
values
('11111111', 'One', 'First', '111-1111');
insert into customer
values
('22222222', 'Two', 'Second', '222-2222');
insert into customer
values
('33333333', 'Three', 'Third', '333-3333');
insert into customer
values
('44444444', 'Four', 'Fourth', '444-4444');
-- now, example of adding foreign key after the fact:
alter table orders
add
(foreign key (cust_num) references customer);
-- so, this will FAIL, because it is an order for a non-existent
-- customer:
insert into orders
values
('666666', '12121212', '10601', '01-Feb-00', 4, 'I', 'U');
-- I can make fields "bigger", but generally cannot make them "smaller"
alter table customer
modify
(cust_lname varchar2(30));
-- notice the new length of cust_lname:
describe customer
-- this WILL work?!? (OK if existing fname's don't violate?!?)
alter table customer
modify
(cust_fname varchar2(10));
-- BUT this fails, some existing fname's violate:
-- (can't make cust_fname shorter than those there...)
alter table customer
modify
(cust_fname varchar2(3));
--END of section INCLUDED JUST FOR REFERENCE - NOT COVERED IN LAB
-------
-- SEQUENCES
-------
-- sequence-related examples
-- first, tables to help us play with sequences...
drop table painter cascade constraints;
create table painter
(ptr_num char(3),
ptr_lname varchar2(30) not null,
ptr_fname varchar2(15),
primary key (ptr_num)
);
drop table painting cascade constraints;
create table painting
(ptg_title varchar2(30),
ptr_num char(3),
primary key (ptg_title),
foreign key (ptr_num) references painter
);
-- create a sequence to help me set good primary keys
-- over time for table painter
drop sequence painter_seq;
-- painter_seq will start at 100, the next will be 102,
-- the next will be 104, etc.
-- (the increment and start clauses are optional --
-- the sequence increments by 1 if not specified,
-- and I THINK it starts at 1 if not specified...)
create sequence painter_seq
increment by 2
start with 100;
-- let's use the sequence to help set primary keys...
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;
-- [sequence_name].currval should get you the sequences CURRENT value;
-- if I know that the painting I'm adding is by the
-- 'latest' painter added, can I do this? Yes;
insert into painting
values
('Mona Lisa', painter_seq.currval);
select *
from painting;
-- sequences can SOMETIMES be used in queries, too...
-- IF you query them using an Oracle built-in
-- "dummy" table named DUAL:
-- (with thanks to Dan Dula! 8-) )
select painter_seq.currval
from dual;
-- [note: you DO have to call nextval at least once on the
-- sequence before you can use currval with it --
-- and that first nextval call gives you the starting
-- value]
-- you can use the sequence wherever you'd like...
insert into parts
values
('10614', 'stuff' || painter_seq.currval,
painter_seq.currval, .13, '005', sysdate, 'Napa', 'lo');
select *
from parts;
--***********************************
-- SQL*LOADER
--***********************************
-- SQL*Loader is a program that comes with the Oracle DBMS
-- to import data from files into database tables;
-- three SIMPLE examples, look at Google/Oracle doc for more!
-- basically: you set up a .ctl (control) file for SQL*Loader,
-- that says WHERE the data is and HOW to handle it
-- and WHERE to put it,
-- and then run SQL*Loader program with that control file
-- and (possibly) data files
-- consider: the empltemp example (the 4 empltemp files)
spool off
-- end of 315lab10-projected.sql