Solving lab_5 sql problems

lab_5_solve.sql

1)>> select customer_name,customer_city from customer where customer_name in (select customer_name from borrower); 

2)>> select customer_name,customer_city from customer where customer_name in (select customer_name from borrower where loan_number in (select loan_number from loan where branch_name='Perryridge'));


3)>> select account_number,balance from account where balance between 700 and 900;


4)>> select customer_name,customer_street from customer where customer_street like '%Hill';


5)>> select customer_name from borrower where loan_number in (select loan_number from loan where 

branch_name='Perryridge') and customer_name in (select customer_name from depositor where 

account_number in (select account_number from account where branch_name='Perryridge'));


6)>> (select customer_name from depositor where account_number in (select account_number from account where branch_name='Perryridge')) MINUS (select customer_name 

from borrower where loan_number in (select loan_number from loan where branch_name='Perryridge'));


7)>> select customer_name,customer_city from customer where customer_name in (select customer_name from 

borrower where loan_number in (select loan_number from loan));

8)>> select customer_name
 from depositor where account_number in (select account_number from account 

where branch_name=(select branch_name from account where account_number=(select account_number from 

depositor where customer_name='Hayes')));

9)>> select branch_name,assets from branch where assets in (select assets from branch where assets> (select assets from branch where branch_city='Brooklyn' and rownum<=1));

10)>> select branch_name,assets from branch where assets>(select sum(assets) from branch group by branch_city having branch_city='Brooklyn');

13)>> (select customer_name from depositor where account_number in (select account_number from account where branch_name='Perryridge')) UNION (select customer_name from borrower where loan_number in (select loan_number from loan where branch_name='Perryridge')) order by customer_name;

14)>> select * from loan order by amount DESC,loan_number;

15)>> select distinct branch_name,avg(balance) from account group by branch_name;

16)>> select distinct branch_name,count(account_number) from account group by branch_name;

17)>> select avg(balance) from account;

18)>> select distinct branch_name,avg(balance) from account group by branch_name having avg(balance) > 700;

19)>> select distinct branch_name,avg(balance) from account group by branch_name having avg(balance)=(select max(avg(balance)) from account group by branch_name);

20)>> select count(customer_name) from customer;

23)>> select customer_name from borrower where loan_number in (select loan_number from loan where branch_name='Downtown');

24)>> select customer_name from borrower where loan_number in (select loan_number from loan where amount between 1500 and 2500);

25)>> select customer_name from customer where customer_name in (select customer_name from borrower) and customer_name in (select customer_name from customer where customer_city='Rye');

27)>> select loan.branch_name,count(borrower.customer_name) from loan,borrower where borrower.loan_number=loan.loan_number group by loan.branch_name;

28)>> select branch_name,avg(amount) from loan group by branch_name having avg(amount)=(select max(avg(amount)) from loan group by branch_name);


29)>> select distinct borrower.customer_name,loan.loan_number,loan.amount from borrower,loan where loan.loan_number in (select loan_number from loan where amount=(select max(amount) from loan)) and borrower.customer_name in (select customer_name from borrower where loan_number in (select loan_number from loan where amount=(select max(amount) from loan)));

30)>> select customer_name from customer where customer_name like 'G%';