Definition of SET - 4
Supplier (sid, sname, contactnum)Parts (pid, pname, color, unit rate)
Catalog (sid, pid, qty)
A)
- Find the top three Parts been ordered and have the highest sale till date.
- 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
0 comments :