Slip 11 - C) Consider the following entities and their relationships. Student (rollno, sname, class, timetable) Lab (LabNo, LabName, capacity, equipment) Relation between Student and Lab is Many to One. Constraint: Primary Key, capacity should not be null. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1) Write a function which will accept Lab number from user and display total number of student allocated in that lab. 2) Write a cursor which will display lab wise student details.

Solution:

SQL>create table lab(labno int primary key,labname varchar(11),capacity int,equipment varchar(20));

SQL>insert into lab values(1,’computer’,100,’scope’);

SQL>create table student10(rollno int primary key,sname varchar(20),class varchar(20),timetable varchar(20),labno int);

SQL>insert into student10 values(102,'raj','sy','monday',2);

1)Write a function which will accept Lab number from user and display total number of student allocated in that lab.

set serveroutput on

create or replace function f2(abc in varchar) return number as xyz number;

begin

select count(student10.rollno) into xyz from student10,lab where lab.labno=student10.labno and lab.labno=1;

if sql %found then

return (xyz);

else

return null;

end if;

end f2;

/

begin

dbms_output.put_line ('no of student'||f2(‘1’));

end;

/

2)Write a cursor which will display lab wise student details.

SQL>  set serveroutput on

SQL>  declare

  2   cursor c1 is select  labname , sname , class from lab, student10  where lab.labno=student10.labno order by labname ;

  3   c c1%rowtype;

  4    begin

  5    open c1;

  6    loop

  7    fetch c1 into c;

  8    exit when c1%notfound;

  9    dbms_output.put_line(c. labname  ||''||c. sname  ||''||c. class);

 10    end loop;

 11    close c1;

 12    end;

 13  /