Structured Query Language

Anonymous
timer Asked: Oct 21st, 2017
account_balance_wallet $15

Question Description

Consider the banking example we used in lecture:

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 branches with average balance above 700.

2. Find the names of customers with accounts at a branch where Hayes has an account.

3. Find the name and average balance for customers who live in Harrison and have at least 2 accounts.



NOTE

I want to screen capture of the last tables on the word document.

Unformatted Attachment Preview

Consider the banking example we used in lecture: 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 branches with average balance above 700. 2. Find the names of customers with accounts at a branch where Hayes has an account. 3. Find the name and average balance for customers who live in Harrison and have at least 2 accounts. /* clean up old tables; must drop tables with foreign keys first due to referential integrity constraints */ use yki1db; 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'); ...
Purchase answer to see full attachment

Tutor Answer

Froggy
School: Carnegie Mellon University

Attached.

SQL Problem and solution

Consider the banking example we used in lecture:
Branch (branch-name, branch-city, assets)
Customer (customer-name, customer-street, customer-city)
Account (account-number, branch-name, balance)
Loan (loan-num...

flag Report DMCA
Review

Anonymous
awesome work thanks

Similar Questions
Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors