Customer and Loan Table Having One To Many Relationship. Consider the Following Entities and Their Relationship. Customer(cno,cname,city) Loan(lno,loan_amt,no_of_years) Constraints: Primary Key loan_amt should be>0

SQL> create table cust

  2  (cno number primary key,

  3  cname varchar(20),

  4  city varchar(20));


Table created.


SQL> create table loan

  2  (lno number primary key,

  3  loan_amt number check(loan_amt>0),

  4  no_of_years number,

  5  cno number references cust);


Table created.


SQL> select * from cust;


       CNO CNAME                CITY

---------- -------------------- -----------------

         1 Raj                  Mumbai

         2 Ramesh               Mumbai

         3 Ganesh               Pune

         4 Harsh                Delhi

         5 Gagan                Delhi

         6 Satish               Mumbai


6 rows selected.


SQL> select * from loan;


       LNO   LOAN_AMT NO_OF_YEARS        CNO

---------- ---------- ----------- ----------------

       101     200000           4          1

       102     100000           2          1

       103     500000           6          2

       104     150000           1          3

       105     300000           8          4

       106     100000           1          4

       107     250000           2          5

       108     800000           6          6


8 rows selected.


a) Create or Replace a PL/SQL Function to Find Total Loan Amout From 'Mumbai' City.


SQL> set serveroutput on;

SQL> create or replace function f12

  2  return number

  3  as

  4  loanamt number;

  5  begin

  6  select sum(loan_amt) into loanamt from loan,cust

  7  where cust.cno=loan.cno

  8  and city='Mumbai';

  9  if SQL%Found Then

 10  return loanamt;

 11  else

 12  return 0;

 13  end if;

 14  end f12;

 15  /


Function created.


***CALL PROGRAM***

SQL> declare

  2  a number;

  3  begin

  4  a:=f12;

  5  if(a=0)then

  6  dbms_output.put_line('No Record Found');

  7  else

  8  dbms_output.put_line('The Total Loan Amt From Mumbai City is' ||' '|| a);

  9  end if;

 10  end;

 11  /

The Total Loan Amt From Mumbai City is 1600000


PL/SQL procedure successfully completed.


b) Write a Cursor To display Details of all Customers who have Taken Loan For More Than 3 Years.


SQL> declare

  2  cursor c12 is

  3  select cust.cno,cname,city,loan_amt,no_of_years from cust,loan

  4  where cust.cno=loan.cno

  5  and no_of_years>3;

  6  y c12%rowtype;

  7  begin

  8  dbms_output.put_line('CNO  CNAME     CITY    LOANAMT  NO_OF_YEARS');

  9  dbms_output.put_line('-------------------------------------------');

 10  for y in c12

 11  loop

 12  dbms_output.put_line(y.cno ||'  '|| y.cname ||'      '|| y.city ||'   '|| y.loan_amt ||'   '|| y.no_of_years);

 13  end loop;

 14  end;

 15  /

CNO  CNAME     CITY    LOANAMT  NO_OF_YEARS

-------------------------------------------

1  Raj         Mumbai   200000   4

2  Ramesh      Mumbai   500000   6

4  Harsh        Delhi   300000   8

6  Satish      Mumbai   800000   6


PL/SQL procedure successfully completed.