ENTIRE LABS IN ONE

CREATE TABLE Customer (
    customerName VARCHAR(25) PRIMARY KEY,
    street VARCHAR(25),
    customerCity VARCHAR(25));

CREATE TABLE Branch (
    branchName VARCHAR(25) PRIMARY KEY,
    branchCity VARCHAR(25),
    Assest INT);

CREATE TABLE Deposit (
    accountNumber INT PRIMARY KEY,
    balance DECIMAL(9,2),
    customerName VARCHAR(25),
    branchName VARCHAR(25),
    FOREIGN KEY (customerName) REFERENCES Customer (customerName),
    FOREIGN KEY (branchName) REFERENCES Branch (branchName));

CREATE TABLE Loan (
    customerName VARCHAR(25),
    branchName VARCHAR(25),
    loanNumber INT PRIMARY KEY,
    amount INT,
    FOREIGN KEY (customerName) REFERENCES Customer (customerName),
    FOREIGN KEY (branchName) REFERENCES Branch (branchName));


---BRANCH---
insert into branch (branchName, branchCity, Assest) VALUES ('Yorkshire', 'Nottingham', 10000);
insert into branch (branchName, branchCity, Assest) VALUES ('Midlands', 'Nottingham', 20000);
insert into branch (branchName, branchCity, Assest) VALUES ('RoyalBank', 'Nottingham', 25000);
insert into branch (branchName, branchCity, Assest) VALUES ('HFE', 'Derby', 15000);
insert into branch (branchName, branchCity, Assest) VALUES ('Southern', 'Derby', 30000);

---CUSTOMER---
insert into Customer (customerName, street, customerCity) VALUES ('Jones', 'Victoria', 'Nottingham');
insert into Customer (customerName, street, customerCity) VALUES ('Patel', 'Church', 'Nottingham');
insert into Customer (customerName, street, customerCity) VALUES ('Smith', 'Derby', 'Leicester');
insert into Customer (customerName, street, customerCity) VALUES ('Ahmed', 'Church', 'Derby');
insert into Customer (customerName, street, customerCity) VALUES ('Braun', 'Alfred', 'Derby');
insert into Customer (customerName, street, customerCity) VALUES ('Chan', 'Victoria', 'Nottingham');

---DEPOSIT---
insert into Deposit (customerName, branchName, accountNumber, balance) VALUES ('Jones', 'Yorkshire', 1, 100);
insert into Deposit (customerName, branchName, accountNumber, balance) VALUES ('Braun', 'Midlands', 20, 150);
insert into Deposit (customerName, branchName, accountNumber, balance) VALUES ('Ahmed', 'RoyalBank', 30, 480);
insert into Deposit (customerName, branchName, accountNumber, balance) VALUES ('Smith', 'Midlands', 21, 600);
insert into Deposit (customerName, branchName, accountNumber, balance) VALUES ('Patel', 'RoyalBank', 31, 450);
insert into Deposit (customerName, branchName, accountNumber, balance) VALUES ('Patel', 'Midlands', 22, 70);
insert into Deposit (customerName, branchName, accountNumber, balance) VALUES ('Braun', 'Southern', 41, 2000);
insert into Deposit (customerName, branchName, accountNumber, balance) VALUES ('Jones', 'HFE', 42, 4100);

---LOAN---
insert into Loan (customerName, branchName, loanNumber, amount) VALUES ('Jones', 'Yorkshire', 11, 3000);
insert into Loan (customerName, branchName, loanNumber, amount) VALUES ('Chan', 'Yorkshire', 12, 2500);
insert into Loan (customerName, branchName, loanNumber, amount) VALUES ('Ahmed', 'Yorkshire', 13, 1800);
insert into Loan (customerName, branchName, loanNumber, amount) VALUES ('Smith', 'Midlands', 50, 5000);
insert into Loan (customerName, branchName, loanNumber, amount) VALUES ('Smith', 'RoyalBank', 6, 500);
insert into Loan (customerName, branchName, loanNumber, amount) VALUES ('Patel', 'Midlands', 51, 1000);
insert into Loan (customerName, branchName, loanNumber, amount) VALUES ('Jones', 'Midlands', 61, 2000);

--- Q1 ---
select customerName
from deposit
union
select customerName
from loan;

--- Q2 ---
select loan.customerName, branchName
from loan, customer
where loan.customerName = customer.customerName
    and customerCity = 'Nottingham';

--- Q3 ---
select loan.customerName, loan.amount, deposit.balance
from loan, deposit
where loan.branchName = 'Yorkshire'
    and loan.customerName = deposit.customerName;

--- Q4 ---
select b.customerName
from loan a, loan b
where a.customerName = 'Smith'
    and a.branchName = b.branchName;

--- Q5 ---
select MIN(balance) smallest, MAX(balance) biggest, AVG(balance) mean
from deposit
where branchName = 'Midlands';

--- Q1 ---
select branchName, SUM(balance) total
from Deposit
group by branchName;

--- Q2 ---
select branchName, COUNT(branchName)
from Deposit
group by branchName
having COUNT(branchName) > 1;

--- Q3a ---
select distinct customerName
from Deposit
left join Branch
on Branch.branchName = Deposit.branchName;

--- Q3b ---
select distinct customerName
from Deposit
where branchName in (select branchName from Branch);

--- Q4 ---
select customerName 
from Deposit
where balance >= all(select balance from Deposit);

--- Q5 ---
create or replace view customerDetails 
(customerName, customerCity, branchName, branchCity)
as select Customer.customerName, Customer.customerCity, Branch.branchName, Branch.branchCity
from Customer, Branch, Deposit
where Customer.customerName = Deposit.customerName and Deposit.branchName = Branch.branchName;

select * from customerDetails;

--- Q1 ---
create or replace procedure changeIntrest
(newbranchName Deposit.branchName%TYPE, intrestRate Deposit.balance%TYPE) as
begin
update Deposit
set balance = balance * intrestRate
where branchName = Deposit.branchName;
end;
/

--- Q2 ---
create or replace procedure changeIntrest
(newbranchName Deposit.branchName%TYPE, intrestRate Deposit.balance%TYPE) as
-- cursor
cursor listOfvalues is
select balance, branchName 
from Deposit 
where newbranchName = Deposit.branchName;
--- vars
selectedBranchName Deposit.branchName%TYPE;
newBalance Deposit.balance%TYPE;
--- update
begin
update Deposit
set balance = balance * intrestRate
where newbranchName = Deposit.branchName;
--- new balance
open listOfvalues;
loop
fetch listOfvalues into newBalance, selectedBranchName;
dbms_output.put_line(newBalance||','||selectedBranchName);
exit when listOfvalues%NOTFOUND;
end loop;
close listOfvalues;
end;
/

execute changeIntrest('Yorkshire', 1.1);

--- Q3 ---
create or replace procedure amountLoaned(newBranchName Loan.branchName%type) as
totalLoaned Loan.amount%type;
begin
    select sum(amount) into totalLoaned
    from Loan
    where newBranchName = branchName;
    dbms_output.put_line('total amount loaned: '||totalLoaned);
end;
/

execute amountLoaned('Midlands');
select branchName, amountLoaned(branchName) from Branch;

--- Q1a ---
create table BankStats (
    when date primary key,
    numberOfLoans int
);

--- Q1b ---
insert into BankStats (when, numberofLoans) 
values (sysdate, (select count(loanNumber) from Loan));

--- Q1c ---
create or replace trigger updateBankStats
after insert or delete on Loan
begin 
insert into BankStats (when, numberofLoans) 
values (sysdate, (select count(loanNumber) from Loan));
end;
/

--- Q1d --- 
delete from Loan where loanNumber = 11;

insert into Loan (customerName, branchName, loanNumber, amount) VALUES ('Jones', 'Yorkshire', 10, 3000);

select * from bankStats;

--- Q2a ---
create table bankStats2 (
    branchName varchar(25) primary key,
    numberOfLoans int
);

--- Q2b ---
insert into BankStats2 select Branch.branchName, count(Loan.loanNumber)
from Branch
left join Loan
on Branch.branchName = Loan.branchName
group by Branch.branchName;

--- Q2c/d ---
create or replace trigger updateBankStats2
after delete or insert on Loan
for each row
declare
numOfBranchName int;
begin 
case 
when inserting then 
    select count(*) into numOfBranchName from bankStats2 where branchName = :new.branchName;
    if (numOfBranchName = 0) then
        insert into bankStats2 values(:new.branchName, 1);
    else  
        update bankStats2 set numberOfLoans = numberOfLoans + 1
        where branchName = :new.branchName;
    end if;
when deleting then
    update bankStats2 set numberOfLoans = numberOfLoans - 1
    where branchName = :old.branchName;
end case;
end;
/

update Branch set branchName = 'Royal' where branchName = 'RoyalBank';

update Deposit set branchName = 'Royal' where branchName = 'RoyalBank';

update Deposit set branchName = 'HFE' where branchName = 'RoyalBank';

delete from Branch where branchName = 'RoyalBank';

delete from Deposit where branchName = 'RoyalBank';

--- Q1 ---
create index balanceIndex
on Deposit (balance);

--- Q2a ---
alter table Deposit add dateAdded date default sysdate;
alter table Loan add dateAdded date default sysdate;

--- Q2b ---
create or replace trigger setDateDeposit
before update on Deposit
for each row
begin 
    :new.dateAdded := sysdate;
end;
/

create or replace trigger setDateLoan
before update on Loan
for each row
begin 
    :new.dateAdded := sysdate;
end;
/

--- Q3b --- no point in 3a
update Deposit set balance = 6900 where accountNumber = 1;
select * from Deposit;

update Loan set amount = 6900 where loanNumber = 11;
select * from Loan;

--- Q4 --- 
select Deposit.customerName, 
replace(Deposit.branchName, 'RoyalBank', 'RoyalNorthernBank') BranchName, 
to_char(Deposit.dateAdded, 'HH24:MI DD Month YYYY') QueryDateTime, 
to_char(Deposit.balance, '999,999.99') balance, 
to_char(Loan.amount, '999,999.99') loanAmount
from Deposit
left join Loan
on Deposit.customerName = Loan.customerName and Deposit.branchName = Loan.branchName;

--- Q1 ---
update Deposit set balance = balance * 1.1
where branchName = 'RoyalBank';

--- Q2 ---
update loan set amount = amount * 1.02
where (loan.customerName, loan.branchName) in
(select loan.customerName, loan.branchName from loan
join deposit
on deposit.customerName = loan.customerName
and deposit.branchName = loan.branchName);

--- Q1 ---
select distinct customer.customerName, customer.customerCity, nvl2(sum(deposit.balance), sum(deposit.balance), 0)
from customer
left join deposit
on customer.customerName = deposit.customerName
group by customer.customerName, customer.customerCity;

--- Q2 ---
select c.customerName, 
case 
    when c.customerName not in (select customerName from deposit) 
    and c.customerName not in (select customerName from loan)
        then 'not in deposit and not in loan'
    when c.customerName in (select customerName from deposit) 
    and c.customerName not in (select customerName from loan)
        then 'in deposit and not in loan'
    when c.customerName not in (select customerName from deposit) 
    and c.customerName in (select customerName from loan)
        then 'not in deposit and in loan'
    when c.customerName in (select customerName from deposit) 
    and c.customerName in (select customerName from loan)
        then 'in deposit and in loan'
end status
from customer c
group by c.customerName;

--- Q3 ---
with totalB as (
    select customer.customerName, customer.customerCity, sum(nvl2(deposit.balance, deposit.balance, 0)) balance
    from customer
    left join deposit
    on deposit.customerName = customer.customerName
    group by customer.customerName, customer.customerCity
)
select totalB.customerName, totalB.customerCity, totalB.balance as "total balance", sum(nvl2(loan.amount, loan.amount, 0)) as "total amount"
from totalB
left join loan
on totalB.customerName = loan.customerName
group by totalB.customerName, totalB.customerCity, totalB.balance
having totalB.balance < sum(nvl2(loan.amount, loan.amount, 0))
order by "total balance" desc;

--- Q1 ---
create or replace type PersonType as object (
    dateOfBirth DATE,
    firstName varchar(25),
    lastName varchar(25),
    member function getAge return integer) 
final;

--- Q2 ---
create or replace type body PersonType as
	member function getAge return integer is 
	currentDate date;
	age integer;
		begin 
		    currentDate := sysdate();
		    age := (extract(year from currentDate) - extract(year from dateOfBirth));
		    return age;
		end getAge;
	end;
/

--- Q3 --- 
create table newCustomer (
    Person PersonType,
    Product VARCHAR(15),
    Cost DECIMAL(6,2)
);

--- Q4 ---
insert into newCustomer values(PersonType('26-OCT-60', 'Chris', 'Smith'), 'Computer', 534.99);

--- Q5 ---
select n.Person.getAge() as "age", n.Product from newCustomer n;

--- Q1 ---
alter type PersonType NOT FINAL cascade convert to substitutable;

--- Q2a ---
create type StudentType under PersonType (
    degreeCourse varchar(25)
)
FINAL;
/

--- Q2b ---
create type employeeType under PersonType (
    roleStartDate date,
    member function durationInRole return integer
)
FINAL;
/

--------------
create type employeeType under PersonType (
    roleStartDate date,
    member function durationInRole return integer,
		overrinding member function getAge return integer
)
FINAL;
/


create or replace type body employeeType as 
overriding member function getAge return integer is
	# varss
        begin
						# code
        end durationInRole;
    end;
/
-------------

--- Q3 ---
create or replace type body employeeType as 
member function durationInRole return integer is
    currentDate date;
    timeInRole integer;
        begin
            currentDate := sysdate();
            timeInRole := extract(year from currentDate) - extract(year from roleStartDate);
            return timeInRole;
        end durationInRole;
    end;
/

--- Q4 ---
insert into newCustomer values(studentType('27-SEP-62', 'Dazza', 'Jones', 'CS'), 'MasterRace PC', 999.99);
insert into newCustomer values(employeeType('02-JAN-77', 'Pipe', 'Up', '26-SEP-2001'), 'house', 21.99);

--- Q5 ---
select n.Person.getAge() from newCustomer n;

select treat(n.Person as employeeType).durationInRole() from newCustomer n;