Need Oralce SQL Query ( In proper format) for the questions below 1) Print the n

Question

Need Oralce SQL Query ( In proper format) for the questions below

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.

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

Explanation / Answer

FROM CUSTOMER C,LOAN L,ACCOUNTS A

WHERE C.CITY NOT IN (‘MPLS’,’Edina’)

AND L.AMT > 500

AND L.BRANCH NOT IN (‘FRANCE’)

AND COUNT(A.BNAME) > 1

2. SELECT A.CNAME

FROM ACCOUNT A.LOAN L,BRANCH B

WHERE COUNT(A.BNAME)=1

AND A.BAL >1000

AND L.AMT>4000

AND B.CITY=’Edina’

3. SELECT BNAME,AVG(BAL)

FROM ACCOUNT

WHERE AVG(SELECT A.BAL

FROM ACCOUNT A,CUSTOMER C

WHERE C.CCITY=’Eden Prairie’) > 800

4. SELECT C.CNAME

FROM CUSTOMER C,BRANCH B,LOAN L

WHERE C.CCITY IN (‘Minnetonka’,’Eden Prairie’)

AND B.CITY=’Edina’

AND A.BNAME=( SELECT BNAME FROM BRANCH WHERE

BCITY=’Minnetonka’ AND ASSETS<125000)

AND B.BNAME <> (SELECT BNAME FROM BRANCH WHERE

BCITY=’Minnetonka’ AND ASSETS>125000)

Related Questions

Navigate

• Browse (All)
• Browse N
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.