VARCHAR2(10)
SQL> desc corder10;
Name Null? Type
------------------------ ------------------ ----------------------------
ORDERNO NOT NULL NUMBER(10)
ORDERDATE NOT NULL DATE
CUSTNO NUMBER(10)
ORDMAT NUMBER(8,2)
SQL> desc orderitem10;
Name Null? Type
------------------ ---------------- ----------------------------
ORDERNO NOT NULL NUMBER(10)
ITEMNO NOT NULL NUMBER(10)
QTY NOT NULL NUMBER(10)
SQL> desc item10;
Name Null? Type
-------------------- ---------------- ----------------------------
ITEMNO NOT NULL NUMBER(10)
UNITPRICE NUMBER(8,2)
SQL> desc shipment10;
Name Null? Type
------------------------ -------- ------------------------
ORDERNO NUMBER(10)
WAREHOUSENO CHAR(2)
SHIPDATE DATE
INSERTING VALUES IN CUSTOMER10 TABLE:
SQL> insert into customer10 values(1001, 'roots', 'coimbatore');
1 row created.
SQL> insert into customer10 values(1002, 'pricol', 'erode');
1 row created.
SQL> insert into customer10 values(1003, 'lmw', 'chennai');
1 row created.
INSERTING VALUES IN CORDER10 TABLE:
SQL> insert into corder10 values(1, '15-jan-1999', 1001, 1500.75);
1 row created.
SQL> insert into corder10 values(2, '5-july-1998', 1002, 1750.23);
1 row created.
SQL> insert into corder10 values(4, '20-dec-1999', 1002, 359.75);
1 row created.
INSERTING VALUES IN ITEM10 TABLE:
SQL> insert into item10(itemno, unitprice)values(501,400.25);
1 row created.
SQL> insert into item10(itemno, unitprice)values(205,220.75);
1 row created.
SQL> insert into item10(itemno, unitprice)values(503,4550.75);
1 row created.
SQL> insert into item10(itemno, unitprice)values(505,100.20);
1 row created.
INSERTING VALUES IN ORDERITEM10 TABLE:
SQL> insert into orderitem10 values(1, 501, 55);
1 row created.
SQL> insert into orderitem10 values(2, 205, 50);
1 row created.
SQL> insert into orderitem10 values(4, 501, 78);
1 row created.
INSERTING VALUES IN SHIPMENT10 TABLE:
SQL> insert into shipment10 values(1, 'w2', '25-mar-1995');
1 row created.
SQL> insert into shipment10 values(2, 'w2', '1-jan-1998');
1 row created.
SQL> insert into shipment10 values(3, 'w2', '20-sep-2000');
1 row created.
SQL> insert into shipment10 values(4, 'w2', '16-feb-2005');
1 row created.
SQL> insert into shipment10 values(5, 'w2', '15-aug-1998');
1 row created.
DISPLAYING TABLE CONTENTS:
SQL> select * from customer10;
CUSTNO CNAME CITY
----------- ----------------- -------------------
1001 roots coimbatore
1002 pricol erode
1003 lmw chennai
SQL> select * from corder10;
ORDERNO ORDERDATE CUSTNO ORDMAT
------------- ------------------ ------------- --------------
1 15-JAN-99 1001 1500.75
2 05-JUL-98 1002 1750.23
4 20-DEC-99 1002 359.75
SQL> select * from orderitem10;
ORDERNO ITEMNO QTY
-------------- --------------- ----------
1 501 55
2 205 50
4 501 78
SQL> select * from item10;
ITEMNO UNITPRICE
------------ ---------------
501 400.25
205 220.75
503 4550.75
505 100.2
SQL> select * from shipment10;
ORDERNO WA SHIPDATE
------------- ----- ------------------
1 w2 25-MAR-95
2 w2 01-JAN-98
3 w2 20-SEP-00
4 w2 16-FEB-05
5 w2 15-AUG-98
DML QURIES:
a. List the orederno and shipdate for all order shipped from warehouse 'w2'.
SQL> select orderno, shipdate from shipment10 where warehouseno='w2';
ORDERNO SHIPDATE
------------- ---------------
1 25-MAR-95
2 01-JAN-98
3 20-SEP-00
4 16-FEB-05
5 15-AUG-98
b. Create a view consisting custname, no of_orders, avg_order_amt where no of orders is the total
number of orders by the customer and avg_order_amt is the average order amount of the customer.
SQL> create or replace view cust_ord_view as(
2 select cname, count(*) as noof_orders, avg(ordmat) as
3 avg_orderr_amt from corder10, customer10
4 where corder10.custno=customer10.custno group by cname);
View created.
SQL> select * from cust_ord_view;
CNAME NOOF_ORDERS AVG_ORDERR_AMT
-------------- ---------------------- ------------------------------
pricol 2 1054.99
roots 1 1500.75
A. WRITE A PROCEDURE TO INSERT THE DETAILS INTO ORDER10 TABLE.
SQL> create or replace procedure ins_proc(orno number,
2 ordt date, csno number, ordmat number) is begin
3 insert into corder10(orderno, orderdate, custno, ordmat)
4 values(orno, ordt, csno, ordmat);
5 end ins_proc; /
Procedure created.
BEFORE PROCEDURE EXECUTION:
SQL> select * from corder10;
ORDERNO ORDERDATE CUSTNO ORDMAT
------------- ------------------- ------------- ---------------
1 15-JAN-99 1001 1500.75
2 05-JUL-98 1002 1750.23
4 20-DEC-99 1002 359.75
PROCEDURE EXECUTION:
SQL> begin
2 ins_proc(5,'31-dec-2009', 1003, 1525.32);
3 dbms_output.put_line('data inserted into the orders table successfully');
4 end; /
data inserted into the orders table successfully
PL/SQL procedure successfully completed.
AFTER PROCEDURE EXECUTION:
SQL> select * from corder10;
ORDERNO ORDERDATE CUSTNO ORDMAT
------------- ------------------- --------------- -------------------
1 15-JAN-99 1001 1500.75
2 05-JUL-98 1002 1750.23
4 20-DEC-99 1002 359.75
5 31-DEC-09 1003 1525.32
B. WRITE A PROCEDURE TO DELETE THE RECORD FROM SHIPMENT WHEN ORDER NOT
SHIPPED WITHIN 30 DAYS OF ORDERING.
SQL> create or replace procedure ship_del is
2 begin
3 delete from shipment10
4 where orderno in (select corder10.orderno
5 from corder10, shipment10 where(months_between(
6 orderdate, shipment10.shipdate)>=1)); end ship_del;
7 /
Procedure created.
BEFORE PROCEDURE EXECUTION:
SQL> select * from shipment10;
ORDERNO WA SHIPDATE
-------------- ----- ------------------
1 w2 25-MAR-95
2 w2 01-JAN-98
3 w2 20-SEP-00
4 w2 16-FEB-05
5 w2 15-AUG-98
PROCEDURE EXECUTION:
SQL> declare begin ship_del(); end;
2 /
PL/SQL procedure successfully completed.
AFTER PROCEDURE EXECUTION:
SQL> select * from shipment10;
no rows selected.
Comments