/* clean up old tables; must drop tables with foreign keys first due to referential integrity constraints */ use XXXdb; DROP TABLE IF EXISTS depositor; DROP TABLE IF EXISTS borrower; DROP TABLE IF EXISTS account; DROP TABLE IF EXISTS loan; DROP TABLE IF EXISTS branch; DROP TABLE IF EXISTS customer; create table branch (branch_name varchar(15) not null, branch_city varchar(15) not null, assets FLOAT(10,2) not null, primary key(branch_name))ENGINE=InnoDB; create table customer (customer_name varchar(15) not null, customer_street varchar(12) not null, customer_city varchar(15) not null, primary key(customer_name))ENGINE=InnoDB; create table loan (loan_number varchar(15) not null, branch_name varchar(15) not null, amount FLOAT(10,2) not null, primary key(loan_number), foreign key(branch_name) references branch(branch_name))ENGINE=InnoDB; create table account (account_number varchar(15) not null, branch_name varchar(15) not null, balance FLOAT(10,2) not null, primary key(account_number), foreign key(branch_name) references branch(branch_name))ENGINE=InnoDB; create table depositor (customer_name varchar(15) not null, account_number varchar(15) not null, primary key(customer_name, account_number), foreign key(account_number) references account(account_number), foreign key(customer_name) references customer(customer_name))ENGINE=InnoDB; create table borrower (customer_name varchar(15) not null, loan_number varchar(15) not null, primary key(customer_name, loan_number), foreign key(customer_name) references customer(customer_name), foreign key(loan_number) references loan(loan_number))ENGINE=InnoDB; /* populate relations */ insert into customer values ('Jones', 'Main', 'Harrison'); insert into customer values ('Smith', 'Main', 'Rye'); insert into customer values ('Hayes', 'Main', 'Harrison'); insert into customer values ('Curry', 'North', 'Rye'); insert into customer values ('Lindsay', 'Park', 'Pittsfield'); insert into customer values ('Turner', 'Putnam', 'Stamford'); insert into customer values ('Williams', 'Nassau', 'Princeton'); insert into customer values ('Adams', 'Spring', 'Pittsfield'); insert into customer values ('Johnson', 'Alma', 'Palo Alto'); insert into customer values ('Glenn', 'Sand Hill', 'Woodside'); insert into customer values ('Brooks', 'Senator', 'Brooklyn'); insert into customer values ('Green', 'Walnut', 'Stamford'); insert into customer values ('Jackson', 'University', 'Salt Lake'); insert into customer values ('Majeris', 'First', 'Rye'); insert into customer values ('McBride', 'Safety', 'Rye'); insert into branch values ('Downtown', 'Brooklyn', 900000); insert into branch values ('Redwood', 'Palo Alto', 2100000); insert into branch values ('Perryridge', 'Horseneck', 1700000); insert into branch values ('Mianus', 'Horseneck', 400200); insert into branch values ('Round Hill', 'Horseneck', 8000000); insert into branch values ('Pownal', 'Bennington', 400000); insert into branch values ('North Town', 'Rye', 3700000); insert into branch values ('Brighton', 'Brooklyn', 7000000); insert into branch values ('Central', 'Rye', 400280); insert into account values ('A-101', 'Downtown', 500); insert into account values ('A-215', 'Mianus', 700); insert into account values ('A-102', 'Perryridge', 400); insert into account values ('A-305', 'Round Hill', 350); insert into account values ('A-201', 'Perryridge', 900); insert into account values ('A-222', 'Redwood', 700); insert into account values ('A-217', 'Brighton', 750); insert into account values ('A-333', 'Central', 850); insert into account values ('A-444', 'North Town', 625); insert into depositor values ('Johnson','A-101'); insert into depositor values ('Smith', 'A-215'); insert into depositor values ('Hayes', 'A-102'); insert into depositor values ('Hayes', 'A-101'); insert into depositor values ('Turner', 'A-305'); insert into depositor values ('Johnson','A-201'); insert into depositor values ('Jones', 'A-217'); insert into depositor values ('Lindsay','A-222'); insert into depositor values ('Majeris','A-333'); insert into depositor values ('Smith', 'A-444'); insert into loan values ('L-17', 'Downtown', 1000); insert into loan values ('L-23', 'Redwood', 2000); insert into loan values ('L-15', 'Perryridge', 1500); insert into loan values ('L-14', 'Downtown', 1500); insert into loan values ('L-93', 'Mianus', 500); insert into loan values ('L-11', 'Round Hill', 900); insert into loan values ('L-16', 'Perryridge', 1300); insert into loan values ('L-20', 'North Town', 7500); insert into loan values ('L-21', 'Central', 570); insert into borrower values ('Jones', 'L-17'); insert into borrower values ('Smith', 'L-23'); insert into borrower values ('Hayes', 'L-15'); insert into borrower values ('Jackson', 'L-14'); insert into borrower values ('Curry', 'L-93'); insert into borrower values ('Smith', 'L-11'); insert into borrower values ('Williams','L-17'); insert into borrower values ('Adams', 'L-16'); insert into borrower values ('McBride', 'L-20'); insert into borrower values ('Smith', 'L-21'); 

Consider the banking schema below:

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 who live in cities with names ending in on.

2. Find the names and cities of customers who have an account at Downtown branch.

3. Print in descending order the names of customers who have a loan amount between 1000 and 2000.

4. Find the names of branches with average branch balance above 700.

5. Find all customers who have a loan, an account, or both.

6. Find the average account balance at the Perryridge branch.

7. Find the number of depositors for each branch.

Submission Instructions: For each problem, include both your SQL code and running result in your submission. You can either take a screenshot or copy/paste the SQL code with results into a text file or Word document. Submit your solutions through the assignment link on Blackboard.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Query 1: All customer data. customer_name customer_street customer_city Adams Spring Pittsfield Brooks Senator Brooklyn Curry North Rye Glenn Sand Hill Woodside Green Walnut Stamford Hayes Main Harrison Jackson University Salt Lake Johnson Alma Palo Alto Jones Main Harrison Lindsay Park Pittsfield Majeris First Rye McBride Safety Rye Smith Main Rye Turner Putnam Stamford Williams Nassau Princeton - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Query 2: All branch data. branch_name branch_city assets Brighton Brooklyn 7000000.00 Central Rye 400280.00 Downtown Brooklyn 900000.00 Mianus Horseneck 400200.00 North Town Rye 3700000.00 Perryridge Horseneck 1700000.00 Pownal Bennington 400000.00 Redwood Palo Alto 2100000.00 Round Hill Horseneck 8000000.00 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Query 3: All account data. account_number branch_name balance A-101 Downtown 500.00 A-102 Perryridge 400.00 A-201 Perryridge 900.00 A-215 Mianus 700.00 A-217 Brighton 750.00 A-222 Redwood 700.00 A-305 Round Hill 350.00 A-333 Central 850.00 A-444 North Town 625.00 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Query 4: All depositor data. customer_name account_number Hayes A-101 Johnson A-101 Hayes A-102 Johnson A-201 Smith A-215 Jones A-217 Lindsay A-222 Turner A-305 Majeris A-333 Smith A-444 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Query 5: All loan data. loan_number branch_name amount L-11 Round Hill 900.00 L-14 Downtown 1500.00 L-15 Perryridge 1500.00 L-16 Perryridge 1300.00 L-17 Downtown 1000.00 L-20 North Town 7500.00 L-21 Central 570.00 L-23 Redwood 2000.00 L-93 Mianus 500.00 - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - Query 6: All borrower data. customer_name loan_number Smith L-11 Jackson L-14 Hayes L-15 Adams L-16 Jones L-17 Williams L-17 McBride L-20 Smith L-21 Smith L-23 Curry L-93