Customer and Account Table Having one To Many Relationship. Consider the Following Entities and Their Relationship. Customer(cno,cname,city) Account(ano,acc_type,balance) Constraints: Primary Key, acc_type not be Null

SQL> create table customer

  2  (cno number primary key,

  3  cname varchar(20),

  4  city varchar(20));


Table created.


SQL> create table account

  2  (ano number primary key,

  3  acc_type varchar(20),

  4  balance number check(balance>100),

  5  cno number references customer);


Table created.



SQL> select * from customer;


       CNO CNAME                CITY

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

         1 Raj                  Pune

         2 Ramesh               Pune

         3 Ganesh               Mumbai

         4 Harsh                Nasik

         5 Prince               Delhi


SQL> select * from account;


       ANO ACC_TYPE                BALANCE        CNO

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

       101 Current                   15000          1

       102 Saving                    21000          1

       103 Recuring                  30000          2

       104 Saving                    11000          3

       105 Current                   25000          3

       106 Current                   19000          4

       107 Recuring                  18000          5


7 rows selected.


a) Create or Replace a PL/SQL Procedure To Find Total Balance of All The Customers of 'Pune' City.


SQL> set serveroutput on;

SQL> create or replace procedure p7

  2  (v_balance out number)

  3  as

  4  t_balance number;

  5  begin

  6  select sum(balance) into t_balance from account,customer

  7  where customer.cno=account.cno

  8  and city='Pune';

  9  v_balance:=t_balance;

 10  exception

 11  when NO_DATA_FOUND then

 12  v_balance:=0;

 13  end p7;

 14  /


Procedure created.


***CALL PROGRAM***

SQL> declare

  2  total_balance number;

  3  begin

  4  p7(total_balance);

  5  if(total_balance=0)then

  6  dbms_output.put_line('Record Does Not Exist');

  7  else

  8  dbms_output.put_line('The Total Balance of Customers of Pune City is ' || total_balance );

  9  end if;

 10  end;

 11  /

The Total Balance of Customers of Pune City is 66000


PL/SQL procedure successfully completed.


b) Write a Cursor to add interest of 3% to the balance of all accounts whose balance is greater than 10000.

SQL> declare

  2  cursor c100 is

  3  select balance from account

  4  where balance>10000;

  5  y c100%rowtype;

  6  cnt number;

  7  begin

  8  for y in c100 loop

  9  update account

 10  set balance=(balance+balance*0.03)

 11  where balance>10000;

 12  cnt:=c100%rowcount;

 13  end loop;

 14  dbms_output.put_line( cnt || 'Rows Updated');

 15  end;

 16  /

2Rows Updated


PL/SQL procedure successfully completed.


SQL> select * from account;


       ANO ACC_TYPE                BALANCE        CNO

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

       101 Current                 15913.5          1

       102 Saving                     9000          1

       103 Recuring                   5000          2

       104 Saving                  12730.8          3

       105 Current                    6000          3

       106 Current                    9500          4

       107 Recuring                   8000          5


7 rows selected.