CS 330
Homework 3
Due: 9/28/97 before class

Part 1

Using the Banking database, write SQL code for the following queries. All answers should be on paper (printed or legibly handwritten) and solved under the prescribed constraints (e.g. using nested queries even when it might be easier to do them with the basic select structure).

Aggregate functions

  1. Find the average account balance at the Perryridge branch.
  2. Find the average account balance at every branch.
  3. Find the number of depositors for each branch.
  4. Find those branches where the average account balance is > 1200.
  5. Find the average balance for all accounts.
  6. Find the number of tuples in customer relation.
  7. Find the average balance for each customer who lives in Harrison and has at least 3 accounts.

Nested Queries

  1. Find all customers who have both a loan and an account.
  2. Find all customers who have both an account and a loan at the Perryridge branch.
  3. Find all customers who do have a loan, but do not have an account at the bank.

Set Comparisons.

  1. Find names of all branches that have assets greater than those of at least one branch located in Brooklyn.
  2. Find names of all branches that have assets greater than the assets of each branch located in Brooklyn.
  3. Find the branch that has the highest average balance.

Empty relations.

  1. Find all customers who have both an account and a loan at the bank.
  2. Find all customers who have an account at all branches located in Brooklyn.

Test for absence of duplicates.

  1. Find all customers who have only one account at the Perryridge branch.
  2. Find all customers who have at least two accounts at the Perryridge branch.

Part 2

Using the Banking Access database you have created for Hw1, write SQL queries for all of the above. Name them agg1 to agg7 (aggregate), nest1 to nest 3 (nested), set1 to set 3 (set comparisons), empty1 and empty2 (empty relations) and unique1 and unique2 (absence of duplicates). Careful, UNIQUE does not seem to work in Access as described in the book, but do it nonetheless to see what you get this way.

Extra credit: find a solution to the last two questions (absence of duplicates) using SQL in Access.
Ileana Streinu