[GTU] SQL & PL/SQL SET - 11

Definition of SET - 11

Supplier (sid, sname, contactnum)
Parts (pid, pname, color, unit rate)
Catalog (sid, pid, qty)
Implement the following:
A)

1. Find those suppliers who haven’t ordered any Parts..
2. Create a View that displays the supplier details who have ordered any item having
   unit rate greater than Rs.500.

B)

1. Create a PL/ SQL block to prepare invoice in following format.
   Prepare this report Part information wise. Use parameterized cursor.

TABLE CREATION

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

Table created.

SQL> create table parts
  2  (
  3   pid number(10) primary key,
  4   pname varchar2(10),
  5   color varchar2(10),
  6   unitrate number(5)
  7  );

Table created.

SQL> create table catalog
  2  (
  3   sid number(10) REFERENCES supplier(sid),
  4   pid number(10) REFERENCES parts(pid),
  5   qty number(10)
  6  );

Table created.

INSERTION IN TABLES

SQL> insert into supplier(sid,sname,contactno)
  2  values(1,'dave',9016511999);

1 row created.

SQL> insert into supplier(sid,sname,contactno)
  2  values(2,'saveji',9016511777);

1 row created.

SQL> insert into supplier(sid,sname,contactno)
  2  values(3,'sammy',9016511888);

1 row created.


SQL> insert into parts(pid,pname,color,unitrate)
  2  values(21,'battery','black',200);

1 row created.

SQL> insert into parts(pid,pname,unitrate)
  2  values(22,'RAM',1500);

1 row created.


SQL> insert into parts(pid,pname,color,unitrate)
  2  values(23,'keyboard','black',600);

1 row created.

SQL> insert into catalog(sid,pid,qty)
  2  values(1,21,3);

1 row created.

SQL> insert into catalog(sid,pid,qty)
  2  values(2,22,2);

1 row created.

SQL> insert into catalog(sid,pid,qty)
  2  values(2,23,1);

1 row created.

TABLES DISPLAY

SQL> select * from parts;

       PID PNAME      COLOR        UNITRATE
---------- ---------- ---------- ----------
        21 battery    black             200
        22 RAM                         1500
        23 keyboard   black             600

SQL> select * from supplier;

       SID SNAME                 CONTACTNO
---------- -------------------- ----------
         1 dave                 9016511999
         2 saveji               9016511777
         3 sammy                9016511888

SQL> select * from catalog;

       SID        PID        QTY
---------- ---------- ----------
         1         21          3
         2         22          2
         2         23          1

A. [1]    Find those suppliers who haven’t ordered any Parts..

SQL> select * from supplier where sid NOT IN (select distinct(sid) from catalog );

       SID SNAME                 CONTACTNO
---------- -------------------- ----------
         3 sammy                9016511888

A. [2]    Create a View that displays the supplier details who have ordered any item having
unit rate greater than Rs.500.

SQL> create view vw11 as select * from supplier where sid IN (select distinct(sid) from
  2  catalog where pid IN (select pid from parts where unitrate > 500) );

View created.

SQL> SELECT * FROM VW11;

       SID SNAME                 CONTACTNO
---------- -------------------- ----------
         2 saveji               9016511777

B. [1]     Create a PL/ SQL block to prepare invoice in following format.
Prepare this report Part information wise. Use parameterized cursor.

SQL> DECLARE
  2   cursor c_11(p number) is select * from parts where pid = p;
  3   p number(10);
  4   r_parts parts%rowtype;
  5   cnt number(5):= 0;
  6  BEGIN
  7   p:= &p;
  8  
  9   dbms_output.put_line('Parts No ::: '|| to_char(p) );
 10 
 11 
 12  
 13  dbms_output.put_line('--------------------------------------------------------------');
 14  dbms_output.put_line('Parts_id    PartsNAme    UNitPrice');
 15  dbms_output.put_line('--------------------------------------------------------------');
 16 
 17   open c_11(p);
 18 
 19 
 20   FETCH c_11 into r_parts;
 21    cnt := cnt + 1;
 22 
 23   if c_11%FOUND then
 24    dbms_output.put_line(r_parts.pid ||'          '||r_parts.pname||'           '||r_parts.unitrate||chr(10));
 25   else
 26    dbms_output.put_line(' data not found');
 27   end if;
 28  dbms_output.put_line('--------------------------------------------------------------');
 29   close c_11; 
 30 
 31  dbms_output.put_line('Total Parts Available : ' || cnt);
 32 
 33  END;
 34  /
Enter value for p: 21
old   7:  p:= &p;
new   7:  p:= 21;
Parts No ::: 21
--------------------------------------------------------------
Parts_id    PartsNAme    UNitPrice
--------------------------------------------------------------
21          battery           200

--------------------------------------------------------------
Total Parts Available : 1

PL/SQL procedure successfully completed.

---

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