Assignment 09-11 - David Bozarth - CIS 134B - 14 July 2002 ..................................................................... /* qry01 */ create table cust_past_due_1nf as select * from cust_past_due_load where null = 0 / SQL> desc cust_past_due_1nf Name Null? Type ----------------------------------------- -------- ---------------------------- CUSTID VARCHAR2(30) CUSTNAME VARCHAR2(30) CUSTPHONE VARCHAR2(30) SLSREPID VARCHAR2(6) SLSREPNAME VARCHAR2(30) ORDERID VARCHAR2(30) ORDERDATE VARCHAR2(30) PAIDSTAT VARCHAR2(16) ORDERAMOUNT VARCHAR2(30) SQL> select * from cust_past_due_1nf 2 / no rows selected ..................................................................... /* qry02 */ alter table cust_past_due_1nf modify ( custid number not null , custname varchar2(30) not null , custphone varchar2(30) not null , slsrepid number not null , slsrepname varchar2(30) not null , orderid number not null , orderdate date not null , paidstat varchar2(16) not null , orderamount number not null ) / SQL> desc cust_past_due_1nf Name Null? Type ----------------------- -------- ---------------- CUSTID NOT NULL NUMBER CUSTNAME NOT NULL VARCHAR2(30) CUSTPHONE NOT NULL VARCHAR2(30) SLSREPID NOT NULL NUMBER SLSREPNAME NOT NULL VARCHAR2(30) ORDERID NOT NULL NUMBER ORDERDATE NOT NULL DATE PAIDSTAT NOT NULL VARCHAR2(16) ORDERAMOUNT NOT NULL NUMBER ..................................................................... /* qry03 */ insert into cust_past_due_1nf select to_number(custid), custname, custphone, to_number(slsrepid), // might want to leave as string to get "03" etc. slsrepname, nvl( substr( orderid, 1, instr( orderid, ',' )-1 ), orderid ), to_date( nvl( nvl( substr( orderdate, 1, instr( orderdate, ',' )-1 ), orderdate ), '0000/00/00' ), 'yyyy/mm/dd' ), paidstat, to_number( nvl( nvl( substr( orderamount, 1, instr( orderamount, ',' )-1 ), orderamount ), '0' ), '$9999.99') from cust_past_due_load / 5 rows created. SQL> select * from cust_past_due_1nf 2 / CUSTID CUSTNAME CUSTPHONE ---------- ------------------------------ ------------------------------ SLSREPID SLSREPNAME ORDERID ORDERDATE PAIDSTAT ---------- ------------------------------ ---------- --------- --------- ORDERAMOUNT ----------- 124 Willis 478.5454 3 Jones 12250 05-NOV-00 No 471.02 . . . ..................................................................... /* qry04 */ insert into cust_past_due_1nf select to_number(custid), custname, custphone, to_number(slsrepid), slsrepname, nvl( substr( orderid, instr( orderid, ',' )+1 ), orderid ), to_date( nvl( nvl( substr( orderdate, instr( orderdate, ',' )+1 ), orderdate ), '0000/00/00' ), 'yyyy/mm/dd' ), paidstat, to_number( nvl( nvl( substr( orderamount, instr( orderamount, ',' )+1 ), orderamount ), '0' ), '$9999.99') from cust_past_due_load where instr(orderid, ',') <> 0 / 2 rows created. SQL> select custid, orderid, orderdate, orderamount 2 from cust_past_due_1nf 3 order by custid, orderid 4 / CUSTID ORDERID ORDERDATE ORDERAMOUNT ---------- ---------- --------- ----------- 124 12250 05-NOV-00 471.02 124 12500 08-DEC-00 6532.6 256 12495 12-NOV-00 571.01 308 13412 05-JAN-01 321.21 311 12499 27-NOV-00 171.31 311 14159 20-FEB-01 6532.03 315 12494 12-NOV-00 321.21 7 rows selected. ..................................................................... /* qry05 */ create table cust_order as select custid, orderid, orderdate, paidstat, orderamount from cust_past_due_1nf / Table created. SQL> select * from cust_order 2 / CUSTID ORDERID ORDERDATE PAIDSTAT ORDERAMOUNT ---------- ---------- --------- ---------------- ----------- 124 12250 05-NOV-00 No 471.02 256 12495 12-NOV-00 No 571.01 308 13412 05-JAN-01 No 321.21 311 12499 27-NOV-00 No 171.31 315 12494 12-NOV-00 No 321.21 124 12500 08-DEC-00 No 6532.6 311 14159 20-FEB-01 No 6532.03 7 rows selected. ..................................................................... /* qry06 */ alter table cust_order add constraint cust_order_cid_oid_pk primary key (custid, orderid) / Table altered. SQL> select constraint_name, constraint_type, table_name, status 2 from all_constraints 3 where table_name = 'CUST_ORDER' 4 and constraint_type = 'P' 5 / CONSTRAINT_NAME C TABLE_NAME STATUS ------------------------------ - ------------------------------ -------- CUST_ORDER_CID_OID_PK P CUST_ORDER ENABLED ..................................................................... /* qry07 */ SQL> create table cust_slsrep_2nf as 2 select custid, custname, custphone, slsrepid, slsrepname 3 from cust_past_due_1nf 4 where null = 0 5 / Table created. SQL> select constraint_name, constraint_type, table_name, status 2 from all_constraints 3 where table_name = 'CUST_SLSREP_2NF' 4 and constraint_type = 'P' 5 / no rows selected SQL> ..................................................................... /* qry08 */ SQL> create table customer1 as 2 select custid, custname, custphone, slsrepid 3 from cust_slsrep_2nf 4 where null = 0 5 / Table created. SQL> select constraint_name, constraint_type, table_name, status 2 from all_constraints 3 where table_name = 'CUSTOMER1' 4 and constraint_type = 'P' 5 / no rows selected ..................................................................... /* qry09 */ SQL> alter table customer1 2 add constraint cust1_cid_pk 3 primary key (custid) 4 / Table altered. SQL> select constraint_name, constraint_type, table_name, status 2 from all_constraints 3 where table_name = 'CUSTOMER1' 4 and constraint_type = 'P' 5 / CONSTRAINT_NAME C TABLE_NAME STATUS ------------------------------ - ------------------------------ -------- CUST1_CID_PK P CUSTOMER1 ENABLED ..................................................................... /* qry10 */ SQL> alter table cust_order 2 add constraint cust_order_cid_cust1_fk 3 foreign key (custid) 4 references customer1 (custid) 5 / add constraint cust_order_cid_cust1_fk * ERROR at line 2: ORA-02298: cannot validate (SYSTEM.CUST_ORDER_CID_CUST1_FK) - parent keys not found SQL> desc customer1 Name Null? Type ----------------------------------------- -------- ---------------------------- CUSTID NOT NULL NUMBER CUSTNAME NOT NULL VARCHAR2(30) CUSTPHONE NOT NULL VARCHAR2(30) SLSREPID NOT NULL NUMBER SQL> insert into customer1 2 select custid, custname, custphone, slsrepid 3 from cust_past_due_load 4 / 5 rows created. SQL> alter table cust_order 2 add constraint cust_order_cid_cust1_fk 3 foreign key (custid) 4 references customer1 (custid) 5 / Table altered. SQL> select constraint_name, constraint_type, table_name, status 2 from all_constraints 3 where table_name = 'CUST_ORDER' 4 and constraint_type = 'R' 5 / CONSTRAINT_NAME C TABLE_NAME STATUS ------------------------------ - ------------------------------ -------- CUST_ORDER_CID_CUST1_FK R CUST_ORDER ENABLED ..................................................................... /* qry11 */ SQL> create table sales_rep as 2 select slsrepid, slsrepname 3 from cust_slsrep_2nf 4 where null = 0 5 / Table created. // note: I did not need DISTINCT in this case because I separated the // CREATE & INSERT steps (also, I put no data in cust_slsrep_2nf). ..................................................................... /* qry12 */ SQL> alter table sales_rep 2 add constraint slsrep_sid_pk 3 primary key (slsrepid) 4 / Table altered. SQL> insert into sales_rep 2 select distinct slsrepid, slsrepname 3 from cust_past_due_load 4 / 4 rows created. ..................................................................... /* qry13 */ SQL> alter table customer1 2 add constraint cust__sid_slsrep_fk 3 foreign key (slsrepid) 4 references sales_rep (slsrepid) 5 / Table altered. ..................................................................... /* qry14 */ create table collection ( orderid number , collection_date date , collection_amount number , custid number , collection_no number constraint collect_collid_pk primary key ) / Table created. SQL> desc collection Name Null? Type ----------------------------------------- -------- ---------------------- ORDERID NUMBER COLLECTION_DATE DATE COLLECTION_AMOUNT NUMBER CUSTID NUMBER COLLECTION_NO NOT NULL NUMBER SQL> select constraint_name, constraint_type, table_name, status 2 from all_constraints 3 where table_name = 'COLLECTION' 4 and constraint_type = 'P' 5 / CONSTRAINT_NAME C TABLE_NAME STATUS ------------------------------ - ------------------------------ -------- COLLECT_COLLID_PK P COLLECTION ENABLED ..................................................................... /* qry15 */ alter table collection add constraint collect_cid_oid_cust_ord_fk foreign key (custid, orderid) references cust_order (custid, orderid) / Table altered. SQL> select constraint_name, constraint_type, table_name, status 2 from all_constraints 3 where table_name = 'COLLECTION' 4 and constraint_type = 'R' 5 / CONSTRAINT_NAME C TABLE_NAME STATUS ------------------------------ - ------------------------------ -------- COLLECT_CID_OID_CUST_ORD_FK R COLLECTION ENABLED ..................................................................... /* qry16 */ SQL> create sequence collection_no_seq 2 / Sequence created. ..................................................................... /* qry17 */ insert into collection values ( 12250 , to_date('24-FEB-01', 'DD-MON-RR') , to_number('300.00', '9999.99') , 124 , collection_no_seq.nextval ) / 1 row created. SQL> insert into collection values 2 ( 14159 3 , to_date('26-MAR-01', 'DD-MON-RR') 4 , to_number('1500.00', '9999.99') 5 , 311 6 , collection_no_seq.nextval 7 ) 8 / 1 row created. SQL> insert into collection values 2 ( 12495 3 , to_date('30-JUN-01', 'DD-MON-RR') 4 , to_number('500.00', '9999.99') 5 , 256 6 , collection_no_seq.nextval 7 ) 8 / 1 row created. SQL> select * from collection 2 / ORDERID COLLECTIO COLLECTION_AMOUNT CUSTID COLLECTION_NO ---------- --------- ----------------- ---------- ------------- 12250 24-FEB-01 300 124 1 14159 26-MAR-01 1500 311 2 12495 30-JUN-01 500 256 3 ..................................................................... /* qry18 */ create or replace view collections_by_cust as select cu.custid, cu.custname, o.orderid, lpad( to_char( nvl( o.orderamount, 0 ), '$9990.99'), 9, ' ' ) owed, lpad( to_char( sum( nvl( co.collection_amount, 0 ) ), '$9990.99' ), 9, ' ' ) paid, lpad( to_char( nvl( o.orderamount, 0 ) - sum( nvl( co.collection_amount, 0 ) ), '$9990.99'), 9, ' ' ) balance from customer1 cu, cust_order o, collection co where cu.custid = o.custid(+) and o.orderid = co.orderid(+) group by cu.custid, cu.custname, o.orderid, o.orderamount order by cu.custid, o.orderid / View created. SQL> select * from collections_by_cust 2 / CUSTID CUSTNAME ORDERID OWED PAID BALANCE ---------- -------------------- ---------- --------- --------- --------- 124 Willis 12250 $471.02 $300.00 $171.02 124 Willis 12500 $6532.60 $0.00 $6532.60 256 Stevens 12495 $571.01 $500.00 $71.01 308 Olms 13412 $321.21 $0.00 $321.21 311 Button 12499 $171.31 $0.00 $171.31 311 Button 14159 $6532.03 $1500.00 $5032.03 315 Dancer 12494 $321.21 $0.00 $321.21 7 rows selected. ..................................................................... /* qry19 */ create or replace view collections_by_slsrep as select s.slsrepid, s.slsrepname, o.orderid, lpad( to_char( nvl( o.orderamount, 0 ), '$9990.99'), 9, ' ' ) owed, lpad( to_char( sum( nvl( co.collection_amount, 0 ) ), '$9990.99' ), 9, ' ' ) paid, lpad( to_char( nvl( o.orderamount, 0 ) - sum( nvl( co.collection_amount, 0 ) ), '$9990.99'), 9, ' ' ) balance from sales_rep s, customer1 cu, cust_order o, collection co where s.slsrepid = cu.slsrepid and cu.custid = o.custid(+) and o.orderid = co.orderid(+) group by s.slsrepid, s.slsrepname, o.orderid, o.orderamount order by s.slsrepid, o.orderid / View created. SQL> select * from collections_by_slsrep 2 / SLSREPID SLSREPNAME ORDERID OWED PAID BALANCE ---------- -------------------- ---------- --------- --------- --------- 3 Jones 12250 $471.02 $300.00 $171.02 3 Jones 12500 $6532.60 $0.00 $6532.60 6 Smith 12494 $321.21 $0.00 $321.21 6 Smith 12495 $571.01 $500.00 $71.01 12 Diaz 12499 $171.31 $0.00 $171.31 12 Diaz 14159 $6532.03 $1500.00 $5032.03 19 Chapman 13412 $321.21 $0.00 $321.21 7 rows selected. ..................................................................... /* qry20 */ -- sql20.txt -- David Bozarth - CIS 134B - 14 July 2002 set verify off set termout off set feedback off set pagesize 0 set heading off set echo off spool f:\scripts\dropTables.sql select 'drop table ' || table_name || ';' from user_tables where table_name like '%NF' or table_name like '%LOAD' / spool off set echo on set heading on set pagesize 80 set feedback on set termout on set verify on / @f:\scripts\dropTables.sql -- host del f:\scripts\dropTables.sql SQL> @f:\scripts\sql20.txt SQL> -- sql20.txt SQL> -- David Bozarth - CIS 134B - 14 July 2002 SQL> SQL> set verify off SQL> set termout off SQL> set verify on SQL> / 'DROPTABLE'||TABLE_NAME||';' ------------------------------------------ drop table CUST_PAST_DUE_1NF; drop table CUST_PAST_DUE_LOAD; drop table CUST_SLSREP_2NF; 3 rows selected. SQL> SQL> @f:\scripts\dropTables.sql SQL> drop table CUST_PAST_DUE_1NF; Table dropped. SQL> drop table CUST_PAST_DUE_LOAD; Table dropped. SQL> drop table CUST_SLSREP_2NF; Table dropped. SQL> SQL> -- host del f:\scripts\dropTables.sql SQL> edit f:\scripts\dropTables.sql drop table CUST_PAST_DUE_1NF; drop table CUST_PAST_DUE_LOAD; drop table CUST_SLSREP_2NF; ..................................................................... /* qry21 */ -- sql21.txt -- David Bozarth - CIS 134B - 14 July 2002 set verify off set termout off set feedback off set pagesize 0 set heading off set echo off spool f:\scripts\dropAll.sql select 'drop view ' || view_name || ';' from user_views / select 'drop table ' || table_name || ';' from user_tables / select 'drop sequence ' || sequence_name || ';' from user_sequences / spool off set echo on set heading on set pagesize 80 set feedback on set termout on set verify on / @f:\scripts\dropAll.sql -- host del f:\scripts\dropAll.sql ..................................................................... /* qry22 */ -- sql22.txt -- David Bozarth - CIS 134B - 14 July 2002 set verify off set termout off set feedback off set pagesize 0 set heading off set echo off spool f:\scripts\do9_11.sql create table cust_past_due_1nf as select * from cust_past_due_load where null = 0 / alter table cust_past_due_1nf modify ( custid number not null , custname varchar2(30) not null , custphone varchar2(30) not null , slsrepid number not null , slsrepname varchar2(30) not null , orderid number not null , orderdate date not null , paidstat varchar2(16) not null , orderamount number not null ) / insert into cust_past_due_1nf select to_number(custid), custname, custphone, to_number(slsrepid), // might want to leave as string to get "03" etc. slsrepname, nvl( substr( orderid, 1, instr( orderid, ',' )-1 ), orderid ), to_date( nvl( nvl( substr( orderdate, 1, instr( orderdate, ',' )-1 ), orderdate ), '0000/00/00' ), 'yyyy/mm/dd' ), paidstat, to_number( nvl( nvl( substr( orderamount, 1, instr( orderamount, ',' )-1 ), orderamount ), '0' ), '$9999.99') from cust_past_due_load / insert into cust_past_due_1nf select to_number(custid), custname, custphone, to_number(slsrepid), slsrepname, nvl( substr( orderid, instr( orderid, ',' )+1 ), orderid ), to_date( nvl( nvl( substr( orderdate, instr( orderdate, ',' )+1 ), orderdate ), '0000/00/00' ), 'yyyy/mm/dd' ), paidstat, to_number( nvl( nvl( substr( orderamount, instr( orderamount, ',' )+1 ), orderamount ), '0' ), '$9999.99') from cust_past_due_load where instr(orderid, ',') <> 0 / create table cust_order as select custid, orderid, orderdate, paidstat, orderamount from cust_past_due_1nf / alter table cust_order add constraint cust_order_cid_oid_pk primary key (custid, orderid) / create table cust_slsrep_2nf as select custid, custname, custphone, slsrepid, slsrepname from cust_past_due_1nf where null = 0 / create table customer1 as select custid, custname, custphone, slsrepid from cust_slsrep_2nf where null = 0 / alter table customer1 add constraint cust_cid_pk primary key (custid) / alter table cust_order add constraint cust_order_cid_cust1_fk foreign key (custid) references customer1 (custid) / create table sales_rep as select slsrepid, slsrepname from cust_slsrep_2nf where null = 0 / alter table sales_rep add constraint slsrep_sid_pk primary key (slsrepid) / alter table customer1 add constraint cust__sid_slsrep_fk foreign key (slsrepid) references sales_rep (slsrepid) / create table collection ( orderid number , collection_date date , collection_amount number , custid number , collection_no number constraint collect_collid_pk primary key ) / alter table collection add constraint collect_cid_oid_cust_ord_fk foreign key (custid, orderid) references cust_order (custid, orderid) / create sequence collection_no_seq / insert into collection values ( 12250 , to_date('24-FEB-01', 'DD-MON-RR') , to_number('300.00', '9999.99') , 124 , collection_no_seq.nextval ) / insert into collection values ( 14159 , to_date('26-MAR-01', 'DD-MON-RR') , to_number('1500.00', '9999.99') , 311 , collection_no_seq.nextval ) / insert into collection values ( 12495 , to_date('30-JUN-01', 'DD-MON-RR') , to_number('500.00', '9999.99') , 256 , collection_no_seq.nextval ) / create or replace view collections_by_cust as select cu.custid, cu.custname, o.orderid, lpad( to_char( nvl( o.orderamount, 0 ), '$9990.99'), 9, ' ' ) owed, lpad( to_char( sum( nvl( co.collection_amount, 0 ) ), '$9990.99' ), 9, ' ' ) paid, lpad( to_char( nvl( o.orderamount, 0 ) - sum( nvl( co.collection_amount, 0 ) ), '$9990.99'), 9, ' ' ) balance from customer1 cu, cust_order o, collection co where cu.custid = o.custid(+) and o.orderid = co.orderid(+) group by cu.custid, cu.custname, o.orderid, o.orderamount order by cu.custid, o.orderid / create or replace view collections_by_slsrep as select s.slsrepid, s.slsrepname, o.orderid, lpad( to_char( nvl( o.orderamount, 0 ), '$9990.99'), 9, ' ' ) owed, lpad( to_char( sum( nvl( co.collection_amount, 0 ) ), '$9990.99' ), 9, ' ' ) paid, lpad( to_char( nvl( o.orderamount, 0 ) - sum( nvl( co.collection_amount, 0 ) ), '$9990.99'), 9, ' ' ) balance from sales_rep s, customer1 cu, cust_order o, collection co where s.slsrepid = cu.slsrepid and cu.custid = o.custid(+) and o.orderid = co.orderid(+) group by s.slsrepid, s.slsrepname, o.orderid, o.orderamount order by s.slsrepid, o.orderid / @@f:\scripts\sql20.txt spool off set echo on set heading on set pagesize 80 set feedback on set termout on set verify on / @f:\scripts\do9_11.sql host del f:\scripts\do9_11.sql -- end of script -------------------------------