4_SQL

create view 'loan_deduction' as
select l.loan-number, 
	case 
		when amount <700 then amount * 0.995
		else amount * 0.997
	end as pred_amount
from l.loan;

create view v-numacct-bycategory as
select b.branch-name, b.branch-city, 
	sum
	(case 
		when a.balance < 1000 then 1 
		else 0 
	end) as nums1,
	sum (
	case 
		when a.balance >= 1000 and a.balance<=10000 then 1 
		else 0 
	end) as nums2,
	sum( 
	case 
		when a.balance > 10000 then 1 
		else 0 
	end) as nums3
from branch b 
JOIN account a ON b.branch_name = a.branch_name
group by b.branch-name, b.brainch-city;

insert into branch (branch_name, branch_city, assets)
select distinct branch_name, 'vice city', 100000
from account
where branch_name not in (select branch_name from branch);

update branch
set assets = assets + 300
where branch_name in (select branch_name from account)
and branch_name not in (select branch_name from loan);

delete from borrower
where customer_name in (
    select customer_name 
    from customer 
    where customer_city = 'salt lake'
)
and customer_name not in (select customer_name from depositor);

create table payment (
    loan_number varchar(20),
    payment_date date,
    amount_paid decimal(15,2),
    primary key (loan_number, payment_date),
    foreign key (loan_number) references loan(loan_number)
);
 
alter table loan add total_paid decimal(15,2);
 
update loan l
set total_paid = (
    select sum(amount_paid) 
    from payment p 
    where p.loan_number = l.loan_number
);