create table Branch (bid integer primary key, 
                    brname varchar (30), 
                    brcity varchar (10));

create table Customer (cno integer primary key, 
                      cname varchar (20), 
                      caddr varchar (35), 
                      city varchar (15));

create table Loan_application (lno integer primary key, 
                                l_amt_required money, 
                                lamtapproved money, 
                                l_date date);

create table Ternary (bid integer references Branch(bid),
                      cno integer references  Customer(cno),
                      lno integer references  Loan_application(lno));

insert into Branch (Bid,brname,brcity)
            values(011,'Badnera','Amrawati'),
                  (012,'Alandi','Pune'),
                  (013,'Aundh','Nashik'),
                  (014,'Primpari','Aundh');
                  
insert into Customer(cno,cname,caddr,city)
              values(201,'Sachin','Alandi','Pune'),
                    (202,'Shubham','Primperi','Primpari'),
                    (203,'Pranav','Parner','Nashik'),
                    (204,'Rohit','Tapkir Nagar','Aundh');

insert into Loan_application(lno,l_amt_required,lamtapproved,l_date)
                    values(301,'45000','20000','20 jun 2022'),
                          (304,'55000','25000','12 jan 2023'),
                          (305,'95000','65000','26 oct 2022'),
                          (306,'200000','35000','24 Sept 2022');

insert into Ternary (bid,cno,lno)
                values(011,'201','301'),
                      (012,'202','304'),
                      (013,'203','305'),
                      (014,'204','306');

select * from branch;

select * from customer;

select * from Loan_application;

select * from Ternary;


create or replace view s1 as
select cname
from branch, customer, Ternary
where branch.bid = ternary.bid and
      customer.cno = ternary.cno and 
      brname = 'Pimpari';

select * from s1;

create or replace view s2 as
select cname
from branch, customer, ternary
where branch.bid = Ternary.bid and
      customer.cno = ternary.cno and
      brcity = city;
select * from s2; 


create or replace function f11() returns trigger as'
declare
begin
  if (new.cno!=old.cno)then
  raise notice ''cannot change '';
end if;
  return old;
end;
'language 'plpgsql';

create or replace trigger t11
before update on customer
for each row
execute procedure f11();

update customer
set cno = cno+10
where cname = 'Harsh';

select * from customer;


create or replace function f12(name varchar) returns void as'
declare
  r1 record;
begin
for r1 in 
	select Loan_application.lno 
	from branch, Loan_application, Ternary
	where branch.bid = Ternary.bid and 
		    Loan_application.lno = ternary.lno and 
		    brname = name
loop
raise notice''%'',r1.l_no;
end loop;
end;
'language 'plpgsql';

select f12('Nashik'); 



create or replace view s21 as 
select cname 
from customer,Loan_application,ternary
where customer.cno = ternary.cno and 
      Loan_application.lno = ternary.Lno and 
      l_amt_required ='95000';

select * from s21;

create or replace view s22 as
select Loan_application.lno 
from branch,Loan_application,ternary
where branch.bid=ternary.bid and 
      Loan_application.lno=ternary.lno and 
      brname='Aundh';

select * from s22;



create or replace function f22() returns trigger as'
declare
begin
  if(new.lamtapproved >= new.l_amt_required)then
  raise exception''invalid approval'';
end if;
  return new;
end;
'language'plpgsql';



create or replace trigger t1
before insert on Loan_application
for each row
execute procedure f22();

insert into Loan_application values(307,'70000','38000','25 Sept 2022');

select * from Loan_application;


create or replace function f2(name varchar) returns int as'
declare
  cnt int;
begin
  select count(cname) into cnt 
  from branch,customer,ternary
  where branch.bid=ternary.bid and 
        customer.cno=ternary.cno and 
        brname = name;
if(cnt!=0)then
return cnt;
else
raise exception''invalid branch name'';
end if;
end;
'language'plpgsql';



create view s31 as
	select cname 
	from customer,Loan_application,ternary
	where customer.cno=ternary.cno and 
	      Loan_application.lno=ternary.Lno and 
	      lamtapproved > '200000';
select * from s31;
	

create view s32 as
select  brname,cname 
from branch,customer,ternary
where branch.bid=ternary.bid and 
      customer.cno=ternary.cno
	    group by brname,cname;
select * from s32;


create or replace function f33() returns trigger as'
declare
begin
	if(new.cno<=0)then
	raise exception''invalid data'';
end if;
	return new;
end;
'language'plpgsql';


create or replace trigger t33
before insert on customer
for each row
execute procedure f33();
insert into customer values(205,'Neha','Parner','ANagar');


create or replace function f12() returns void as'
declare
	r1 record;
c1 cursor for 
	select cname,i_amt_appr 
	from loanapp,customer,ternry
	where loanapp.l_no=ternry.l_no and 
	      customer.c_no=ternry.c_no;
begin
	open c1;
loop
	fetch c1 into r1;
exit when not found;
	raise notice''%  %'',r1.c_name,r1.i_amt_appr;
end loop;
end;
'language'plpgsql';

select f12();