Slip 11
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.
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 /
