Question

Need the SQL query for the questions below

CUSTOMER Columns Data Model l Constraints Grants Statistics Triggers Flashback Dependencies Details Parttionsl ACCOUNT Columns Data Model lconstraints Grants Statisticsl Triggers Flashback lDependencies Details Partitions STREET BNAME Southdalc BAL Ridgedale Second 123.5 Southdale rance Columns Data Model Constraints Grants Statistics Triggers Flashback Dependencies Details Partitions ??? sort.. Filter :| E BRANCH Columns Data Model Constraints Grants Statistics Triggers Flashback Dependencies Details Partitions ??? ?Actions ? sort.. I Filter AMT ASSETS 150000 Minnetonka Southdale 2 Nain 100000 Minnetonka 4 Southdale 30000 9 3rd Street 10 Airport

1) Print the name of customers who do not live in MPLS or Edina, have a loan with an amount of more than 500 in any branch of the bank but not in the France branch, and have one or more accounts only in the France branch.

2) Show the name of all Customers who have only one account with a balance of more than $1000 in any branch of the bank and one or more loan with an amount of more than $4000 in any branch in Edina in two different ways:

3) Print the branch name and the average balance for all accounts in the branch if one or more customers who live in Eden Prairie have accounts in the branch and the average balance of their accounts in this branch is more than $800

Example: Assume Rahimi lives in Eden Prairie and has two accounts one in York and one is Southdale. Rahimi’s average account balance is 1111 which is more than 800. Since that is the case, we need to print the average balance of all accounts in the York (3141.75) and Southdale (1055.50) branches.

4) Find the name of all customers who live in Minnetonka OR Eden Prairie, have one or more accounts in any branch in Edina, and have a loan in every branch in Minnetonka that has assets of less than 125000 but do not have a loan in those branches in Minnetonka that have assets of >= 125000.

Answer 1

question 1:

SELECT * FROM CUSTOMER as cus LEFT JOIN LOAN AS `loan` ON `cus`.`CNAME` = `loan`.`CNAME` LEFT JOIN ACCOUNT AS `acc` ON `acc`.`CNAME`=`cus`.`CNAME` WHERE (`cus`.`CCITY`='MPLS' OR `cus`.`CCITY`='Edina') AND `loan`.`AMT`>500 AND `loan`.`BNAME` !='France' AND `acc`.`BNAME` ='France'

Question 2:

SELECT * FROM CUSTOMER as cus LEFT JOIN LOAN AS `loan` ON `cus`.`CNAME` = `loan`.`CNAME` LEFT JOIN ACCOUNT AS `acc` ON `acc`.`CNAME`=`cus`.`CNAME` WHERE COUNT(`acc`.`Bname`)=1 AND `acc`.`BAL`>1000 AND `loan`.`AMT`>4000

Question 3:

SELECT `bran`.`BNAME`,AVG(`acc`.`BAL`) FROM CUSTOMER as cus LEFT JOIN ACCOUNT AS `acc` ON `acc`.`CNAME`=`cus`.`CNAME` LEFT JOIN BRANCH AS `bran` ON `acc`.`BNAME` = `bran`.`BNAME` WHERE `cus`.`CCITY` = 'Eden Prairie' AND AVG(`acc`.`BAL`) >800

Question 4:

SELECT * FROM CUSTOMER as cus LEFT JOIN ACCOUNT AS `acc` ON `acc`.`CNAME`=`cus`.`CNAME` LEFT JOIN BRANCH AS `bran` ON `acc`.`BNAME` = `bran`.`BNAME` LEFT JOIN LOAN AS `loan` ON `cus`.`CNAME` = `loan`.`CNAME` WHERE (`cus`.`CCITY` = 'Eden Prairie' OR `cus`.`CCITY` = 'Minnetonka') AND `bran`.`BCITY`='Edina' AND `bran`.`ASSETS`<125000