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