SALES REPORT

create table report(item_id varchar2(4),item_name varchar2(15),sales_price number(5),
quantity number(3),unit_price number(7),salestax number(5),total number(7));
insert into report values('f10','T.V',null,1,3000,null,null);
insert into report values('f11','Grinder',null,2,3500,null,null);
insert into report values('f12','Hometheatre',null,3,2500,null,null);
insert into report values('f13','DVDplayer',null,1,1000,null,null);
insert into report values('f14','Ipod',null,2,1000,null,null);
insert into report values('f15','Computer',null,1,18000,null,null);
insert into report values('f16','Mobile',null,4,10000,null,null);
insert into report values('f17','Stabilizer',null,3,750,null,null);
insert into report values('f18','Washing machine',null,2,4000,null,null);
insert into report values('f19','Vaccumcleaner',null,3,5000,null,null);
insert into report values('f20','Pen drive',null,5,900,null,null);
declare
cursor c1 is
select*from report order by item_id;
vrep c1%rowtype;
begin
dbms_output.put_line('* THE ABC MARKETING COMPANY,CALICUT,KERALA
SALES REPORT FOR THE MONTH OF JANUARY 2010');
dbms_output.put_line('--------------------------------------------------------------------------------------');
dbms_output.put_line('Item_id Item_name Sales_price Quantity Unit Price Salestax Total');
dbms_output.put_line('-------------------------------------------------------------------------');
open c1;
loop
fetch c1 into vrep;
exit when c1%notfound;
vrep.sales_price:=vrep.unit_price*vrep.quantity;
vrep.salestax:=(vrep.sales_price*6)/100;
vrep.total:=vrep.sales_price-vrep.salestax;
update report set sales_price=vrep.sales_price,salestax=vrep.salestax,total=vrep.total where
item_id=vrep.item_id;
dbms_output.put_line('vrep.item_id,vrep.item_name,vrep.sales_price,vrep.quantity,
vrep.unit_price,vrep.salestax,vrep.total');
end loop;
close c1;
dbms_output.put_line('-----------------------------------------------------------------------------------');
end;
/

OUTPUT

THE ABC MARKETTING COMPANY,CALICUT,KERALA SALES REPORT FOR
THE MONTH OF JANUARY 2010
--------- --------- --------- --------- ----------- ---------- --------- --------- --------- ---------
ITEM ITEM_NAME SALES_PRICE QUANTITY UNIT_PRICE SALESTAX TOTAL
---- --------------- ----------- --------- ---------- --------- --------- ---- --------------- ----------- --------
f10 T.V 3000 1 3000 180 2820
f11 Grinder 7000 2 3500 420 6580
f12 Hometheatre 7500 3 2500 450 7050
f13 DVDplayer 1000 1 1000 60 940
f14 Ipod 2000 2 1000 120 1880
f15 Computer 18000 1 18000 1080 16920
f16 Mobile 40000 4 10000 2400 37600
f17 Stabilizer 2250 3 750 135 2115
f18 Washing machine 8000 2 4000 480 7520
f19 Vaccumcleaner 15000 3 5000 900 14100
f20 Pen drive 4500 5 900 270 4230
--------- --------- --------- --------- ----------- ---------- --------- --------- --------- ------------