Transcript Examples of Queries: branch-name loan-number customer-name amount Downtown

Examples of Queries:
branch-name loan-number
Downtown
17
Redwood
23
Perryridge
15
Downtown
14
Mianus
93
Round Hill
11
Pownal
29
North Town 16
Downtown
18
Perryridge
25
Brighton
10
customer-name
Jones
Smith
Hayes
Jackson
Curry
Turner
Williams
Adams
Johnson
Glenn
Brooks
The borrow relation.
amount
1000
2000
1500
1500
500
900
1200
1300
2000
2500
2200
branch-name
Downtown
Mianus
Perryridge
Round Hill
Perryridge
Redwood
account-number
101
215
102
305
201
222
customer-name
Johnson
Smith
Hayes
Turner
Williams
Lindsay
The deposit relation.
balance
500
700
400
350
900
700
customer-name
Jones
Smith
Hayes
Curry
Lindsay
Turner
Williams
Adams
Johnson
Glenn
Brooks
Green
street
Main
North
Main
North
Park
Putnam
Nassau
Spring
Alma
Sand Hill
Senator
Walnut
customer-city
Harrison
Rye
Harrison
Rye
Pittsfield
Stamford
Princeton
Pittsfield
Palo Alto
Woodside
Brooklyn
Stamford
The customer relation.
branch-name
Downtown
Redwood
Perryridge
Mianus
Round Hill
North Town
Brighton
assets
250,000
800,000
420,000
380,000
1,100,000
950,000
540,000
branch-city
Pittsfield
Brooklyn
Stamford
Brooklyn
Princeton
Palo Alto
Harrison
The branch relation
1.
Find the names of all branches in
deposit relation:
2.
Find all customers having an account at
the Perryridge branch:
3.
Find all customers having a loan from the
Perryridge branch:
4.
Find all customers having a loan, an account
or both at Perryridge branch:
5.
Find all customers having both a loan and an
account at Perryridge branch:
6.
Find all customers at Perryridge branch who
have an account but no loan there
7.
Find all customers and their city info
for those who have a loan at some branch.
8.
Find the names of all customers having a
loan at the Perryridge branch and their
respective city:
9.
Find all customers who have both a loan and
an account at the Perryridge branch:
10. Find all customers who have an account at
the Perryridge branch but do not have a loan
at Perryridge branch:
11.
Find all branches that have greater assets
than some branch located in Brooklyn.
12.
Find all branches that have greater assets
than any branch located in Brooklyn.
13.
Find all customers who have an account at
all branches located in Brooklyn:
14.
List in alphabetical order all customers
having a loan at Perryridge branch:
15.
Find the average account balance at all branches:
16.
Find the number of tuples in customer relation:
17.
Find branches where the average account
balance is more than $1200:
Additional SQL Clauses:
1.
Find all customers who have a deposit at the
Perryridge branch, but for whom no
address is on file.
2.
Same query as 1 use EXIST or NOT EXIST.
3.
Find all customers who have a deposit at
the Perryridge branch and have their
address on file.
4.
Find all customers who have an account
at all branches in Brooklyn.
5.
Find the customers who have no address on
file.
7.
Find all customers who live in cities with
names that start with ‘H’.