Expert answer:Structured Query Language

Solved by verified expert: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.NOTEI want to screen capture of the last tables on the word document.
lab7.docx

make_banking_yki1._sql.txt

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

How it works

1. Paste your instructions in the instructions box. You can also attach an instructions file
2. Select the writer category, deadline, education level and review the instructions 
3. Make a payment for the order to be assignment to a writer
4.  Download the paper after the writer uploads it 

Will the writer plagiarize my essay?

You will get a plagiarism-free paper and you can get an originality report upon request.

Is this service safe?

All the personal information is confidential and we have 100% safe payment methods. We also guarantee good grades