withSerial_thirty.sql

Qry1. select customer.customer_name, customer.customer_city from customer, borrower where customer.customer_name = borrower.customer_name;
-- >>>>>>> select customer_name, customer_city from customer where customer_name in(select customer_name from borrower);
 
Qry2. select borrower.customer_name, customer.customer_city from customer, borrower, loan
where customer.customer_name = borrower.customer_name and borrower.loan_number = loan.loan_number and loan.branch_name='Perryridge' ;
-- >>>>>>> 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'));
 
Qry3. select account_number, balance from account where balance in (select balance from account where balance between 700 and 900);
-- >>>>>>>  select account.account_number, account.balance from account where balance between 700 and 900;
 
Qry4. select customer_name, customer_street from customer where customer_street like '%Hill';
 
Qry5. select distinct customer_name from depositor, account where account.account_number = depositor.account_number and branch_name = 'Perryridge' and customer_name in(select customer_name from loan, borrower where borrower.loan_number = loan.loan_number and branch_name = 'Perryridge');
 
Qry6. select distinct customer_name from depositor, account where account.account_number = depositor.account_number and branch_name = 'Perryridge' and customer_name not in(select customer_name from loan, borrower where borrower.loan_number = loan.loan_number and branch_name = 'Perryridge');
 
Qry7. select customer_name, customer_city from customer where customer_name in(select customer_name from borrower);
 
Qry8. select distinct depositor.customer_name from depositor, account where depositor.account_number = account.account_number and branch_name in (select branch_name from depositor, account where depositor.account_number = account.account_number and depositor.customer_name='Hayes');
 
Qry9.  select branch.branch_name, branch.assets from branch where assets > some (select assets from branch where branch_city='Brooklyn');
 
Qry10.  select branch.branch_name, branch.assets from branch where assets > all (select assets from branch where branch_city='Brooklyn');
 
Qry13. select customer_name from depositor where account_number in(select account_number from account where branch_name='Perryridge') order by customer_name;
 
Qry14. select * from loan order by amount desc, loan_number asc;
 
-- Aggregate function needs group by clause then having clause
Qry15. select branch_name, avg(balance) from account group by branch_name;
 
Qry16. select branch_name, count(account_number) from account group by branch_name;
 
Qry17. select avg(balance) from account;
 
Qry18. select branch_name, avg(balance) from account group by branch_name having avg(balance) > 700;
 
Qry19. select branch_name, avg(balance) as balance from account group by branch_name having avg(balance) >= all(select avg(balance) from account group by branch_name);
 
Qry20. select count(customer_name) from customer;
 
Qry21. select customer_name from borrower where loan_number in (select loan_number from loan where branch_name='Downtown');
 
Qry24. select customer_name from borrower where loan_number in(select loan_number from loan where amount between 1500 and 2500);
 
Qry25. select customer_name from customer where customer_city='Rye' and exists(select customer_name from borrower where customer.customer_name = borrower.customer_name);
 
Qry27. select branch_name, count(loan_number) from loan group by branch_name;
 
Qry28. select branch_name, avg(amount) from loan group by branch_name having avg(amount) >= all(select avg(amount) from loan group by branch_name);
 
Qry29. select customer_name, loan_number from borrower where loan_number=(select loan_number from loan where amount=(select MAX(amount) from loan));
 
   -- >>>>>>> select borrower.customer_name, borrower.loan_number, loan.amount from borrower, loan where borrower.loan_number = loan.loan_number and loan.amount =(select Max(loan.amount) from loan);
 
Qry30. select customer_name from customer where customer_name

Q13 : SELECT CUSTOMER_NAME FROM CUSTOMER WHERE CUSTOMER_NAME IN (SELECT CUSTOMER_NAME FROM DEPOSITOR WHERE ACCOUNT_NUMBER IN (SELECT ACCOUNT_NUMBER FROM ACCOUNT WHERE BRANCH_NAME = 'Perryridge')) OR CUSTOMER_NAME IN (SELECT CUSTOMER_NAME FROM BORROWER WHERE LOAN_NUMBER IN (SELECT LOAN_NUMBER FROM LOAN WHERE BRANCH_NAME = 'Perryridge'))ORDER BY CUSTOMER_NAME;