DBMS Assignment 2

create table branch(branch_name varchar(40) primary key,
                    branch_city varchar(40),
                    assets int
                );

create table account(account_number varchar(10) primary key,
                    balance int,
                    branch_name varchar(40),
                    foreign key(branch_name) references branch    
                )

create table customer(customer_name varchar(40) primary key,
                    customer_street varchar(40),
                    customer_city varchar(40)
                );

create table depositor(customer_name varchar(40),
                    account_number varchar(10),
                    foreign key(customer_name) references customer,
                    foreign key(account_number) references account
                );

create table loan(loan_number varchar(10) primary key,
                branch_name varchar(40),
                amount int,
                foreign key(branch_name) references branch
            );

create table borrower(customer_name varchar(40),
                    loan_number varchar(10),
                    foreign key(customer_name) references customer,
                    foreign key(loan_number) references loan
                );

create or replace procedure borrower_details as  
    cursor c_unique_name is select distinct customer_name from borrower; 
    r_unique_name c_unique_name%rowtype; 
     
    cursor c_borrower(c_name borrower.customer_name%type) is select * from borrower where customer_name=c_name; 
    r_borrower c_borrower%rowtype; 
     
    cursor c_loan(l_number borrower.loan_number%type) is select * from loan where loan_number=l_number; 
    r_loan c_loan%rowtype; 
     
    cursor c_branch(b_name loan.branch_name%type) is select * from  branch where branch_name=b_name; 
    r_branch c_branch%rowtype; 
     
    borrower_record varchar(2000) := '   '; 
    total_amount int; 
begin 
    for r_unique_name in c_unique_name loop 
        dbms_output.put_line('Borrower Name: ' || r_unique_name.customer_name); 
        dbms_output.put_line('    Loan No.  Branch Name         Branch City         Loan Amount'); 
         
        -- clearing previos records from the variables 
        total_amount := 0; 
         
        for r_borrower in c_borrower(r_unique_name.customer_name) loop 
            -- clearing previos records from the variables 
            borrower_record := '    '; 
             
            -- Adding loan number 
            borrower_record := borrower_record  || rpad(to_char(r_borrower.loan_number), 10); 
             
            open c_loan(r_borrower.loan_number); 
            fetch c_loan into r_loan; 
             
            -- Adding branch name 
            borrower_record := borrower_record || rpad(to_char(r_loan.branch_name), 20); 
             
            open c_branch(r_loan.branch_name); 
            fetch c_branch into r_branch; 
            -- Adding branch city 
            borrower_record := borrower_record || rpad(to_char(r_branch.branch_city), 20); 
            close c_branch; 
             
            -- Adding loan amount 
            borrower_record := borrower_record || r_loan.amount; 
            total_amount := total_amount + r_loan.amount; 
             
            close c_loan; 
         
            dbms_output.put_line(borrower_record); 
             
        end loop; 
     
            dbms_output.put_line(chr(10) || '    Total amount of ' || r_unique_name.customer_name || ' : ' || total_amount || chr(10)); 
     
    end loop; 
end; 
/

Borrower Name: Adams
    Loan No.  Branch Name         Branch City         Loan Amount
    L-16      Perryridge          Horseneck           1300

    Total amount of Adams : 1300

Borrower Name: Curry
    Loan No.  Branch Name         Branch City         Loan Amount
    L-93      Mianus              Horseneck           500

    Total amount of Curry : 500

Borrower Name: Hayes
    Loan No.  Branch Name         Branch City         Loan Amount
    L-15      Perryridge          Horseneck           1500

    Total amount of Hayes : 1500

Borrower Name: Johnson
    Loan No.  Branch Name         Branch City         Loan Amount
    L-14      Downtown            Brooklyn            1500

    Total amount of Johnson : 1500

Borrower Name: Jones
    Loan No.  Branch Name         Branch City         Loan Amount
    L-17      Downtown            Brooklyn            1000

    Total amount of Jones : 1000

Borrower Name: Smith
    Loan No.  Branch Name         Branch City         Loan Amount
    L-11      Round Hill          Horseneck           900
    L-23      Redwood             Palo Alto           2000

    Total amount of Smith : 2900

Borrower Name: Williams
    Loan No.  Branch Name         Branch City         Loan Amount
    L-17      Downtown            Brooklyn            1000

    Total amount of Williams : 1000

create or replace procedure city_wise_details as
    cursor c_city is select distinct branch_city from branch;
    r_city c_city%rowtype;
    
    cursor c_branch(c_name branch.branch_city%type) is 
        select distinct branch_name from branch 
            where branch_city=c_name;
    r_branch c_branch%rowtype;
    
    cursor c_borrower(b_name branch.branch_name%type) is 
        select distinct borrower.customer_name, loan.branch_name 
            from loan inner join borrower 
                on loan.loan_number = borrower.loan_number
            where loan.branch_name=b_name;  
    r_borrower c_borrower%rowtype;
    
    cursor c_loan(br_name loan.branch_name%type, bo_name borrower.customer_name%type) is
        select loan.loan_number, loan.amount, borrower.customer_name, loan.branch_name
            from loan inner join borrower
                on loan.loan_number = borrower.loan_number
            where (
                loan.branch_name = br_name and
                borrower.customer_name = bo_name
            );
    r_loan c_loan%rowtype;
    
    branch_total int := 0;
    city_total int := 0;
    overall_total int := 0;
begin
    for r_city in c_city loop
        -- resetting the variable
        city_total := 0;
        
        dbms_output.put_line('    City: ' || r_city.branch_city);
        
        for r_branch in c_branch(r_city.branch_city) loop
            -- resetting the variable
            branch_total := 0;
            
            dbms_output.put_line('        Branch: ' || r_branch.branch_name);
            
            for r_borrower in c_borrower(r_branch.branch_name) loop
                dbms_output.put_line('            Borrower Name:' || r_borrower.customer_name);
                dbms_output.put_line('                ' || rpad(to_char('Loan Number'), 15) || 'Loan Amount'); 
                
                for r_loan in c_loan(r_branch.branch_name, r_borrower.customer_name) loop
                    dbms_output.put_line('                ' || rpad(to_char(r_loan.loan_number), 15) || r_loan.amount); 
                    
                    branch_total := branch_total + r_loan.amount;
                    city_total := city_total + r_loan.amount;
                    overall_total := overall_total + r_loan.amount;
                end loop;
            end loop;
            
            dbms_output.put_line('        Total amount collected at branch ' || r_branch.branch_name || ' : ' || branch_total);
        end loop;
        
        dbms_output.put_line('    Total amount collected at city ' || r_city.branch_city || ' : ' || city_total);
    end loop;
    
    dbms_output.put_line('Overall toatal amount : ' || overall_total);
end;
/

exec city_wise_details;

    City: Bennington
        Branch: Pownal
        Total amount collected at branch Pownal : 0
    Total amount collected at city Bennington : 0
    City: Palo Alto
        Branch: Redwood
            Borrower Name:Smith
                Loan Number    Loan Amount
                L-23           2000
        Total amount collected at branch Redwood : 2000
    Total amount collected at city Palo Alto : 2000
    City: Brooklyn
        Branch: Brighton
        Total amount collected at branch Brighton : 0
        Branch: Downtown
            Borrower Name:Johnson
                Loan Number    Loan Amount
                L-14           1500
            Borrower Name:Williams
                Loan Number    Loan Amount
                L-17           1000
            Borrower Name:Jones
                Loan Number    Loan Amount
                L-17           1000
        Total amount collected at branch Downtown : 3500
    Total amount collected at city Brooklyn : 3500
    City: Horseneck
        Branch: Mianus
            Borrower Name:Curry
                Loan Number    Loan Amount
                L-93           500
        Total amount collected at branch Mianus : 500
        Branch: Perryridge
            Borrower Name:Adams
                Loan Number    Loan Amount
                L-16           1300
            Borrower Name:Hayes
                Loan Number    Loan Amount
                L-15           1500
        Total amount collected at branch Perryridge : 2800
        Branch: Round Hill
            Borrower Name:Smith
                Loan Number    Loan Amount
                L-11           900
        Total amount collected at branch Round Hill : 900
    Total amount collected at city Horseneck : 4200
    City: Rye
        Branch: North Town
        Total amount collected at branch North Town : 0
    Total amount collected at city Rye : 0
Overall toatal amount : 9700

create or replace procedure city_branch_params(c_name branch.branch_city%type, b_name branch.branch_name%type)  as
    cursor c_borrower(c_name branch.branch_city%type, b_name branch.branch_name%type) is 
        select distinct borrower.customer_name, loan.branch_name 
            from loan inner join borrower 
                on loan.loan_number = borrower.loan_number
            where loan.branch_name=b_name;  
    r_borrower c_borrower%rowtype;
    
    cursor c_loan(br_name loan.branch_name%type, bo_name borrower.customer_name%type) is
        select loan.loan_number, loan.amount, borrower.customer_name, loan.branch_name
            from loan inner join borrower
                on loan.loan_number = borrower.loan_number
            where (
                loan.branch_name = br_name and
                borrower.customer_name = bo_name
            );
    r_loan c_loan%rowtype;
    
    branch_total int := 0;
    city_total int := 0;
begin
    dbms_output.put_line('    City: ' || c_name);
    dbms_output.put_line('        Branch: ' || b_name);
   
    for r_borrower in c_borrower(c_name, b_name) loop
        dbms_output.put_line('            Borrower Name:' || r_borrower.customer_name);
        dbms_output.put_line('                ' || rpad(to_char('Loan Number'), 15) || 'Loan Amount'); 
        
        for r_loan in c_loan(b_name, r_borrower.customer_name) loop
            dbms_output.put_line('                ' || rpad(to_char(r_loan.loan_number), 15) || r_loan.amount); 
            
            branch_total := branch_total + r_loan.amount;
            city_total := city_total + r_loan.amount;
        end loop;
    end loop;
            
    dbms_output.put_line('        Total amount collected at branch ' || b_name || ' : ' || branch_total);
    dbms_output.put_line('    Total amount collected at city ' || c_name || ' : ' || city_total);
end;
/

exec city_branch_params('Horseneck', 'Perryridge');

Statement processed.
    City: Horseneck
        Branch: Perryridge
            Borrower Name:Adams
                Loan Number    Loan Amount
                L-16           1300
            Borrower Name:Hayes
                Loan Number    Loan Amount
                L-15           1500
        Total amount collected at branch Perryridge : 2800
    Total amount collected at city Horseneck : 2800

create or replace procedure dep_and_bor as
    cursor c_customer is select * from customer;
    r_customer c_customer%rowtype;
    
    cursor c_borrower(name customer.customer_name%type) is select * from borrower where customer_name= name;
    r_borrower c_borrower%rowtype;
    
    cursor c_depositor(name customer.customer_name%type) is select * from depositor where customer_name= name;
    r_depositor c_depositor%rowtype;
begin
    dbms_output.put_line(rpad(to_char('Custome name'), 20) || rpad(to_char('customer city'), 20) || rpad(to_char('customer street'), 20)); 
    for r_customer in c_customer loop
        open c_borrower(r_customer.customer_name);
        fetch c_borrower into r_borrower;
        
        open c_depositor(r_customer.customer_name);
        fetch c_depositor into r_depositor;
        
        if(c_borrower%found and c_depositor%found) then
            dbms_output.put_line(rpad(to_char(r_customer.customer_name), 20) || rpad(to_char(r_customer.customer_city), 20) || rpad(to_char(r_customer.customer_street), 20)); 
        end if;
        close c_depositor;
        close c_borrower;
    end loop;
end;
/

exec dep_and_bor;

Statement processed.
Custome name        customer city       customer street     
Hayes               Harrison            Main                
Johnson             Palo Alto           Alma                
Jones               Harrison            Main                
Smith               Rye                 North           

create or replace function get_customers(b_name branch.branch_name%type) return int as 
    cursor c_account(br_name branch.branch_name%type) is select count(*) cnt from account where branch_name = br_name;
    r_account c_account%rowtype;
begin 
    open c_account(b_name);
        fetch c_account into r_account;
        return r_account.cnt;
    close c_account;
end;
/

select get_customers('Brighton') from dual;
select get_customers('Perryridge') from dual;
select get_customers('Palo Alto') from dual;

create or replace function get_branches(c_name branch.branch_city%type) return int as
    cursor c_branch(city branch.branch_city%type) is select count(*) cnt from branch where branch_city=city;
    r_branch c_branch%rowtype;
begin
    open c_branch(c_name);
        fetch c_branch into r_branch;
        return r_branch.cnt;
    close c_branch;
end;
/

select get_branches('Brooklyn') from dual;
select get_branches('Rye') from dual;
select get_branches('Horseneck') from dual;

create or replace function has_same_city(c_name customer.customer_name%type) return boolean as
    cursor c_c_city(customer customer.customer_name%type) is select customer_city from customer where customer_name=customer;
    r_c_city c_c_city%rowtype;
    
    cursor c_account(customer customer.customer_name%type) is select account_number from depositor where customer_name=customer;
    r_account c_account%rowtype;
    
    cursor c_branch(a_num account.account_number%type) is select branch_name from account where account_number=a_num;
    r_branch c_branch%rowtype;
    
    cursor c_city(branch branch.branch_name%type) is select branch_city from branch where branch_name = branch;
    r_city c_city%rowtype;
begin
    open c_c_city(c_name);
        fetch c_c_city into r_c_city;
        
        open c_account(c_name);
            fetch c_account into r_account;
            
            open c_branch(r_account.account_number);
                fetch c_branch into r_branch;
                
                open c_city(r_branch.branch_name);
                    fetch c_city into r_city;
                    
                    if r_city.branch_city=r_c_city.customer_city then
                        return true;
                    else 
                        return false;
                    end if;
                close c_city;
            close c_branch;
        close c_account;
    close c_c_city;    
end;
/

declare 
    cursor c_customer is select customer_name from customer;
    r_customer c_customer%rowtype;
begin
    for r_customer in c_customer loop
        if has_same_city(r_customer.customer_name) then 
            dbms_output.put_line(r_customer.customer_name || ' has account in the same city');
        else 
            dbms_output.put_line(r_customer.customer_name || ' has account in the different city');
        end if;
    end loop;
end;
/

Statement processed.
Adams has account in the different city
Brooks has account in the different city
Curry has account in the different city
Glenn has account in the different city
Green has account in the different city
Hayes has account in the different city
Johnson has account in the different city
Jones has account in the different city
Lindsay has account in the different city
Smith has account in the different city
Turner has account in the different city
Williams has account in the different city

create or replace trigger check_balance before insert or update on account
    for each row
begin
    if(:new.balance < 200) then
        raise_application_error(-20001, 'Balance should be greater than 200');
    end if;
end;
/

-- desc account;

insert into account values ('A-111', 155, 'Jamnagar'); 

ORA-20001: Balance should be greater than 200 ORA-06512: at "SQL_NJLXHIBJXBXLRRSYWYSPUCMWD.CHECK_BALANCE", line 3
ORA-06512: at "SYS.DBMS_SQL", line 1721

-- 1. creating the table city_assets
create table city_assets(city_name varchar(40) primary key, 
                        total_assets int 
                    );
                    
-- 2. procedure to make the initial updates, executing and checking the table
create or replace procedure initial_city_updates as 
    cursor c_city is select distinct branch_city from branch; 
    r_city c_city%rowtype; 
     
    cursor c_branch(city branch.branch_city%type) is select * from branch where branch_city=city; 
    r_branch c_branch%rowtype; 
     
    city_assets int; 
begin 
    for r_city in c_city loop 
        city_assets := 0; 
         
        for r_branch in c_branch(r_city.branch_city) loop  
            city_assets := city_assets + r_branch.assets;     
        end loop; 
         
    insert into city_assets values(r_city.branch_city, city_assets); 
    end loop; 
end; 
/

exec initial_city_updates;

select * from city_assets;

-- 3. Creating the trigger
create or replace trigger update_assets 
    before insert on branch
    for each row
declare
    cursor c_assets(c_name city_assets.city_name%type) is
        select * from city_assets 
        where city_name=c_name;
    r_assets c_assets%rowtype;
begin
    open c_assets(:new.branch_city); 
        fetch c_assets into r_assets; 
         
        if (c_assets%found) then 
            update city_assets  
                set total_assets = (r_assets.total_assets + :new.assets) 
            where 
                city_name = :new.branch_city; 
        else 
            insert into city_assets values(:new.branch_city, :new.assets); 
        end if; 
    close c_assets; 
end;
/

-- 4. Adding data and checking the city_assets table
insert into branch values('abc_xyz', 'Brooklyn', 190000);
insert into branch values('xyz', 'xyz_abc', 990000);

select * from city_assets;

-- 1. Creating the trapped table
create table trapped(user_name varchar(50),
                    date_time timestamp default current_timestamp
                );

-- 2. creating the trigger
create or replace trigger trap 
    before insert or update or delete on borrower
    for each row
declare     
    weekday varchar(10);
    hrs number;
begin
    weekday := rtrim(to_char(sysdate, 'Day'));
    hrs := to_number(to_char(sysdate, 'hh24'));
    
    if(weekday = 'Saturday' or weekday = 'Sunday') then
        insert into trapped values(:new.customer_name, default);   
    else 
        if(hrs > 22 or hrs < 6) then 
            insert into trapped values(:new.customer_name, default);   
        end if;
    end if;
end;
/

-- adding data to borrower and checking trapped
insert into borrower values('Adams', 'L-93');

select * from trapped;

-- Creating the table
create table redolog_values(c_date date,
                            field_name varchar(20),
                            before_value varchar(50),
                            after_value varchar(50)
                        );

-- Creating the trigger
create or replace trigger track_account 
    before update on account
    for each row
begin
    if updating('account_number') then
        insert into redolog_values values(sysdate, 'account_number', :old.account_number, :new.account_number);
    end if;
    if updating('branch_name') then
        insert into redolog_values values(sysdate, 'branch_name', :old.branch_name, :new.branch_name);
    end if;
    if updating('balance') then
        insert into redolog_values values(sysdate, 'balance', to_char(:old.balance), to_char(:new.balance));
    end if;
end;
/

-- Updating and checking
update account set 
    account_number='A-119',
    branch_name='Brighton',
    balance=700
where account_number='A-101';

select * from redolog_values;  

-- 1. creating the trigger
create or replace trigger delete_child 
    before delete on customer
    for each row
begin
    delete from borrower
        where customer_name = :old.customer_name;
    delete from depositor
        where customer_name = :old.customer_name;
end;
/

-- deleting and checking
delete from customer
    where customer_name = 'Johnson';
    
select * from borrower;
select * from depositor;