Q1)
create or replace function f1
return varchar is
pnm varchar(15);
begin
select p_name into pnm
from policy_info,cp_info
where policy_info.p_no=cp_info.p_no
group by p_name having count(cp_info.c_no)<=all(select count(c_no) from cp_info
group by c_no);
return pnm;
end;
Q2)
Declare
cursor c1 is select c_name from client;
cursor c2 (cnm client.c_name%type) is
select p_name,m_amt,p_amt,p_term,dop
from client,policy,clientp
where client.c_no=clientp.c_no
and policy.p_no=clientp.p_no
and c_name=cnm;
r1 c1%rowtype;
r2 c2%rowtype;
Begin
open c1;
loop
fetch c1 into r1;
exit when c1%notfound;
dbms_output.put_line(r1.c_name);
open c2(r1.c_name);
loop
fetch c2 into r2;
exit when c2%notfound;
dbms_output.put_line(r2.p_name||r2.m_amt||r2.p_amt||r2.p_term||r2.dop);
end loop;
close c2;
end loop;
close c1;
end;