[SOLVED] database SQL SQL exercises

SQL exercises

Carlos Matos
Department of Computer Science

Royal Holloway, University of London

1 Introduction

These few exercises are meant to be used as examples on how to build queries. We use the
banking scenario schemas that we have been discussing in the lectures for this purpose.

2 Exercises

The following relational schemas are used for the exercises below:

branch = (branch name, branch city, assets)
customer = (customer name, customer street, customer city)
loan = (loan number, branch name, amount)
account = (account number, branch name, balance)
borrower = (customer name, loan number)
depositor = (customer name, account number)

1. Consider the following view creation in the database:

CREATE VIEW all_customer AS

(SELECT branch_name, customer_name

FROM depositor, account

WHERE depositor.account_number = account.account_number)

UNION

(SELECT branch_name, customer_name

FROM borrower, loan

WHERE borrower.loan_number = loan.loan_number);

Using that view, write a query that finds all customers that have some product at
the Perryridge branch.

2. Write a query that returns the customer(s) holding the most money at one given
account at the bank.

3. Write a query that returns, for each customer, the number of products held in the
bank. Show the results in descendent order.

1