Sales report using SQL

Objective

A sales report contains the  record with the following fields: itemid, itemname, salesprice, quantity and total price. Write a PL/SQL procedure to generate sales report.

Program

SQL>create table sales(itemid varchar(10), itemname varchar(20), salesprice number(6,2), quantity number(4));

Table created

SQL>insert into sales values(‘s001’,’pen’,10,10);

1 row created

SQL>insert into sales values(‘s002’,’pencil’,4,20);

1 row created

SQL>insert into sales values(‘s003’,’scale’,5,10);

1 row created

SQL>insert into sales values(‘s004’,’eraser’,6,20);

1 row created

SQL>insert into sales values(‘s005’,’notebook’,15,10);

1 row created

SQL> alter table sales add (tot_price number(6,2));

Table altered

SQL>declare

2 cursor c is select * from sales;

3 v_tot sales.tot_price%type;

4 begin

5 for  i in c loop

6 v_tot=i.salesprice*i.quantity;

7 update sales set tot_price=v_tot where itemid=i.itemid;

8 end loop;

9 end;

10 /

PL/SQL procedure successfully completed

SQL> select * from sales;

ITEMID       ITEMNAME   SALESPRICE    QUANTITY     TOT_PRICE

s001                 pen                  10                      10                 100

s002                 pencil               4                       20                  80

s003                 scale                 5                       10                  50

s004                 eraser              6                        20                  120

s005                 notebook        15                      10                  150