Slip 21 - C) Consider the following entities and their relationships. 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. Create a RDB in 3NF and write PL/SQL blocks in Oracle for the following: 1) Write a function which will accept college name from user and display total number of “Ph.D” qualified teachers. 2) Write a cursor which will display college wise teacher details.

Solution

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  /