Slip 21
College(code,
college_name, address)
Teacher(teacher_id,
teacher_name, Qualification, specialization, salary, Desg)
Relation
between Teacher and College is Many to One.
Constraint:
Primary Key, qualification should not be null.
1)Write a function
which will accept college name from user and display total number of “Ph.D”
qualified teachers.
SQL>create
table teacher62(tid int primary key,teacher_name varchar(10),qualification
varchar(10),specialization varchar(10),salary varchar(10),designation
varchar(10));
SQL>create
table college62(cid int primary key,college_name varchar(10),address
varchar(10),tid int);
SQL>insert
into teacher62 values(1,'tina','ph.d','drawing','50000','head');
SQL>insert
into college62 values(102,'ness','pune','1');
create
or replace function fun1(XYZ in varchar)return number as ABC number;
begin
select
sum(teacher62.tid)into ABC from teacher62,college62 where
teacher62.tid=college62.tid
and
college_name = ‘ness’;
if
sql%found then
return(ABC);
else
return
null;
end
if;
end
fun1;
/
Function
created.
Begin
dbms_output.put_line('teachers-'||fun1('ph.d'));
end;
/
teachers-1
PL/SQL
procedure successfully completed.
2)Write a cursor which
will display college wise teacher details.
SQL> set
serveroutput on
SQL> declare
2
cursor c1 is select teacher_name, qualification from teacher62,college62 where
teacher62. tid = college62.tid order by college_name;
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. teacher_name ||''||c.qualification);
10
end loop;
11
close c1;
12
end;
13 /
