Consider the banking example we used in lecture branch branc

Consider the banking example we used in lecture:

branch (branch-name, branch-city, assets)

customer (customer-name, customer-street, customer-city)

account (account-number, branch-name, balance)

loan (loan-number, branch-name, amount)

depositor (customer-name, account-number)

borrower (customer-name, loan-number)

Write and execute the following queries in SQL:

1. Find the names of customers on streets with names ending in \"Hill“.

2. Print loan data, ordered by decreasing amounts, then increasing loan numbers.

3. Print the names and cities of customers who have a loan at Perryridge branch.

Solution

1. Find the names of customers on streets with names ending in \"Hill“.

Answer: select customer-name from customer where customer-street like \'%Hill\';

2. Print loan data, ordered by decreasing amounts, then increasing loan numbers.

Answer: select loan-number, branch-name, amount from loan order by amount desc, loan-number asc;

3. Print the names and cities of customers who have a loan at Perryridge branch.

Answer: select c.customer-name , c.customer-city from customer c, borrower b, loan l where c.customer-name = b.customer-name and b.loan-number = l.loan-number and l.branch-name = \'Perryridge\';