Friday 25 May 2012

[GTU] SQL & PL/SQL SET - 4

Definition of SET - 4

Supplier (sid, sname, contactnum)
Parts (pid, pname, color, unit rate)
Catalog (sid, pid, qty)
A)
  1. Find the top three Parts been ordered and have the highest sale till date.
B)
  1. Create a PL/ SQL block to prepare invoice in following format.
    Display the invoice in the following format. Use parameterized cursor.

    Invoice ::: <Supplier’s Name>
    Part Id Part Name Quantity Unit Price Total Price
    ----------------------------------------------------------------------------------------
    ----------------------------------------------------------------------------------------
    Total: _______
    ----------------------------------------------------------------------------------------

TABLE CREATION


SQL> create table supplier4
  2  (
  3   sid number(2) primary key,
  4   sname varchar2(20),
  5   contactnum number(10)
  6  );

Table created.

SQL> create table parts4
  2  (
  3   pid number(3) primary key,
  4   pname varchar2(20),
  5   color varchar2(30),
  6   unit_rate number(2)
  7  );

Table created.

SQL> create table catalog4
  2  (
  3   sid number(2) references supplier4(sid),
  4   pid number(3) references parts4(pid),
  5   qty number(3)
  6  );

Table created.

INSERTION IN TABLES

SQL> insert into supplier4(sid,sname,contactnum)values(1,'jenny',987258851);

1 row created.

SQL> insert into supplier4(sid,sname,contactnum)values(2,'zil',8885555541);

1 row created.

SQL> insert into supplier4(sid,sname,contactnum)values(3,'mikky',8588845551);

1 row created.



SQL> insert into parts4(pid,pname,color,unit_rate)
values(101,'printer','yellow',10);

1 row created.

SQL> insert into parts4(pid,pname,color,unit_rate)
values(102,'mouse','black',20);

1 row created.

SQL> insert into parts4(pid,pname,color,unit_rate)
values(103,'keyboard','white',30);

1 row created.

SQL> insert into parts4(pid,pname,color,unit_rate)
values(104,'monitor','red',10);

1 row created.

SQL> insert into parts4(pid,pname,color,unit_rate)
values(105,'cpu','green',20);

1 row created.


SQL> insert into catalog4(sid,pid,qty)
values(1,101,10);

1 row created.

SQL> insert into catalog4(sid,pid,qty)values(1,102,20);

1 row created.

SQL> insert into catalog4(sid,pid,qty)values(2,103,30);

1 row created.

SQL> insert into catalog4(sid,pid,qty)values(2,104,10);

1 row created.

SQL> insert into catalog4(sid,pid,qty)values(3,105,20);

1 row created.

SQL> insert into catalog4(sid,pid,qty)values(3,101,10);

1 row created.

TABLES DISPLAY


SQL> select * from supplier4;

       SID SNAME                CONTACTNUM
---------- -------------------- ----------
         1 jenny                 987258851
         2 zil                  8885555541
         3 mikky                8588845551

SQL> select * from parts4;

PID PNAME       COLOR   UNIT_RATE
---------------------------
101 printer     yellow    10
102 mouse       black     20
103 keyboard    white     30
104 monitor     red       10
105 cpu         green     20

SQL> select * from catalog4;

       SID        PID        QTY
---------- ---------- ----------
         1        101         10
         1        102         20
         2        103         30
         2        104         10
         3        105         20
         3        101         10

6 rows selected.

A. [1]    Find the top three Parts been ordered and have the highest sale till date.


SQL> select pid, pname, qty from ( select p.pid as pid, pname, qty from catalog4 c, parts4 p  where p.pid = c.pid order by

qty desc) where rownum < 4;

       PID PNAME                       QTY
---------- -------------------- ----------
       103 keyboard                     30
       102 mouse                        20
       105 cpu                          20

B. [1]    Create a PL/ SQL block to prepare invoice in following format.
Display the invoice in the following format. Use parameterized cursor.
Invoice ::: <Supplier’s Name>
Part Id Part Name Quantity Unit Price Total Price
----------------------------------------------------------------------------------------
----------------------------------------------------------------------------------------
Total: _______
----------------------------------------------------------------------------------------

SQL> declare
  2   cursor c4(name varchar2) is select p.pid,pname,qty,unit_rate from supplier4 s,parts4 p,catalog4 c where sname=name
  3  and s.sid=c.sid and p.pid=c.pid;
  4
  5   sname varchar2(10);
  6   total number := 0 ;
  7   totalprice number:= 0;
  8   pid number(3);
  9   pname varchar2(20);
 10   qty number(3);
 11   unit_rate number(3);
 12
 13  begin
 14   dbms_output.put_line('Invoice ::: '||sname);
 15   dbms_output.put_line('------------------------------------
------------------------------------');
 16   dbms_output.put_line('Part id'||chr(9)||'Part name'||chr(9)||'quantity'||chr(9)||'unit_rate'|| chr(9) || 'TotalPrice');
 17   dbms_output.put_line('--------------------------------
----------------------------------------');
 18 
 19   sname:='&sname';
 20 
 21
 22   open c4(sname);
 23 
 24   LOOP
 25   fetch c4 into pid,pname,qty,unit_rate;
 26   exit when c4%notfound;
 27
 28    totalprice :=(qty * unit_rate);
 29    total := total + totalprice;
 30
 31
 32   dbms_output.put_line(to_char(pid)||chr(9)||pname||chr(9)||
chr(9)||to_char(qty)||chr(9)||chr(9)||to_char(unit_rate) ||chr(9)||chr(9)|| to_char(totalprice));
 33   END LOOP;
 34
 35
 36   dbms_output.put_line('--------------------------------------
----------------------------------');
 37   dbms_output.put_line(chr(9)||chr(9)||'Total :'||to_char(total));
 38   dbms_output.put_line('--------------------------------------
----------------------------------');
 39  end;
 40  /
Enter value for sname: zil
old  19:  sname:='&sname';
new  19:  sname:='zil';
Invoice :::
----------------------------------------------------------
Part id Part name       quantity        unit_rate       TotalPrice
----------------------------------------------------------
103     keyboard        30              30              900
104     monitor         10              10              100
----------------------------------------------------------
                Total :1000
----------------------------------------------------------

PL/SQL procedure successfully completed.

*due to small space , output is messed up

*this program is only for reference purpose, actual logic may differ from programmer to programmer

Kindly Bookmark and Share it:

0 comments :

Post a Comment

Any Query ? any suggestion ? comment here

 

Recent Post

Recent Comments

© 2010 IamLearningHere Template by MBT