Branch(branch-name, branch-city, assets) Customer(customer-name, customer-street

Question

Branch(branch-name, branch-city, assets) Customer(customer-name, customer-street, customer-city) Loan(loan-number, branch-name, amount) Borrow(customer-name, loan-number) Accountlaccount-number, branch-name, balance) Depositor(customer-name, account-number) Find the customer names, loan numbers, and loan amounts for all loans at the Chester branch. Find all loan numbers for loans made at the Chester branch with loan amounts great than $2,500. Find all customers who do have a loan at the bank, but do not have an account at the bank. Find the number of depositors for each branch where the average account balance is more than $2,500 1. 2. 3. 4. 5. Find the average balance for each customer who lives in Chester and has at least three accounts

Explanation / Answer

SQL queries :  

1. select b.customer_name,l.loan_number,amount from Borrow b, Loan l, Branch br where b.loan_number=l.loan_number and l.branch_name = br.branch_name and br.branch_city = 'Chester';

2. select loan_number from Loan l, Branch br where l.branch_name = br.branch_name and br.branch_city = 'Chester' and amount > 2500;

3. select distinct(customer_name) from Borrow

minus

select customer_name from Depositor;

4. select branch_name,count(*) from Account a, Depositor d where a.account_number = d.account_number group by branch_name having avg(balance) > 2500;

5. select avg(balance),customer_name from Depositor d, Account a, Customer c where a.account_number = d.account_number and d.customer_name = c.customer_name and c.customer_city = 'Chester' group by customer_name having count(a.account_number) >=3;

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

---