1. Write down the following SQL queries and also write down their outputs a) Find out the loan amount of the customers whose customer street is Main. [4] b) Find out the name of the city of the customers whose Bank branch has the second maximum amount of assets. [6] c) Find the average account balance of those branches where the average account balance is greater than $1000 and the branch balance is 900000 or 1700000.(You dont need to find output table for this one) [5]

26

d) Find out the name and account number of the customers whose Branch city is in Horseneck and the account balance of the customers are more than 400. [6] e) Write down the account number of the customer whose name starts with J or H and whose bank branch name is Downtown. [5]

Depositor CUSTOMER_N AME Johnson Customer CUSTOME R_NAME Adams Brooks Curry Glenn Green Hayes Johnson Jones Smith Turner Williams CUSTOMER STREET Spring Senator North Sand Hill Walnut Main Smith Hayes Tumer CUSTOME R_CITY Pittsfield Brooklyn Rye Woodside Stamford Harrison Palo Alto Harrison Rye Stamford Princeton ACCOUNT NUMB ER A-101 A-215 A-102 A-305 A-201 A-217 Jolinson Jones Alma Lindsay A-222 Main Main Putnam Borrower CUSTOMER NA ME LOAN NUMBE R Account ACCOUN T NUMB ER A-101 A-215 A-102 A-305 A-201 A-222 A-217 BRANCH NAME Downtown Mianus Perryridge Round Hill Perryridye Redwood Brighton BALANCE 500 700 400 350 900 Jones Smith Hayes Jackson Curry Smith L-17 L-23 L-15 L-14 1.93 L-11 L-17 L-16 700 Williams 750 Adams Loan LOAN NU MBER L-17 L-23 L-15 L-14 L-93 BRANCH NA ME Downtown Redwood Perryridge Downtown Mianus AMOUN T 1000 2000 1500 150X) 500 Brunch BRANCH N AME Downtown Redwood Perryridge Mianus Round Hill Pownal BRANCH CITY Brooklyn Palo Alto Horseneck Horseneck Horseneck Bennington Rye Brooklyn ASSETS XX000 2100000 1700000 4X1200 BOXXO000 40XX000 L-11 Round Hill 900 1300) 1-16 Perryridge North Town 3700000 700000 Brighton