Sql assignment questions with answers

Write SQL statements to answer the following questions using Assignment 3’s schema (tables from part 1). 1- Find how many branches had have loans over $2000.00. 2- For each branch, find the most expensive loan. Your output should include Branch Id, loan amount for the highest loan for that Branch. 3- Find how many accounts there are for each customer. The output should include customer id and number of accounts for that customer. 4- Find the total balance amount of all accounts by each Branch. The output should be a list of Branch Id and for each Branch Id, the total balance of accounts in that Branch. 5- Find Customer ID, Customer name and the number of loans for each Customer. 6- Find Customer ID, Customer name for all accounts, sorted by Customer name. 7- Find Loan number and Branch Id of the loan with the lowest amount. 8- Create a view called MPL_Branch_V that contains Branch Id, Branch name, and number of loans for each Branch that is in the city of Minneapolis. 9- For each Customer in Hopkins, find the total amount of all their loans. 10- Find how many different accounts each customer has at each Branch. The output should be a list of Customer ID and for each Customer ID, the number of accounts for this customer by Branch ID.

TABLES

1. Branch(

branch_id:integer

, branch_name:varchar(50), branch_city:varchar(50),

assets:numeric(11,2)

2. Loan(

loan_number:integer

, branch_id:integer, amount:numeric(8,2))

foreign key branch_id references Branch(branch_id)

3. Customer(

customer_id:integer

, customer_name:varchar(30),

customer_street:varchar(30), customer_city:varchar(50))

4. Borrower(

customer_id:integer

,

loan_number:integer)

foreign key (customer_id) references Customer(customer_id)

foreign key (loan_number) references Loan(loan_number)

5. Depositor(

customer_id:integer

,

account_number:integer)

foreign key (customer_id) references Customer(customer_id)

foreign key (account_number) references Account(account_number)

6. Account(

account_number:integer

, branch_id:integer, balance:numeric(8,2))

foreign key branch_id references Branch(branch_id