Write SQL statements to answer the following questions using Assignment 3’s schema (tables below). You can add more data to the tables if you want, just follow the PK and FK rules. Accounts are not Loans and Loans are not Accounts. NOTE I already have Answers for part 1 BELOW PART 1 IS FOR ONLY REFRENCE TO ANSWER QUESTIONS 1-5 1- Find how many branches have loans between $4,100.00 and $7,000.00. HINT: Do not manually count the rows, have the DBMS engine do the work. 2- For each branch, find the Min and Max loan amounts. Your output should include Branch Id, min loan amount and max loan amount for that Branch. 3- Find how many accounts there are for each customer. The output should include customer id and number of accounts for that customer. 4- Find the average account balance for each Branch. The output should be a list of Branch Id and for each Branch Id, the average account balance in that Branch. 5- Find Customer ID, Customer name and Customer City for all accounts, sorted by Customer City, then Customer Last name. PART 1 WITH ANSWERES USE THIS FOR REFRENCE TO ANSWER QUESTIONS 1-5 ABOVE. Create the following tables create table Branch (branch_id integer, branch_name varchar(50), branch_location varchar(40), money_on_hand numeric(15,2), primary key (branch_id)); create table Loan (loan_number integer, branch_id integer, amount numeric(8,2), primary key (loan_number), foreign key (branch_id) references Branch (branch_id)); create table Customer (customer_id integer, customer_last_name varchar(35), customer_first_name varchar(25), customer_street varchar(30), customer_zip integer, primary key (customer_id)); create table Borrower (customer_id integer, loan_number integer, primary key (customer_id, loan_number), foreign key (customer_id) references Customer (customer_id), foreign key (loan_number) references Loan (loan_number)); Note: as the Account table is a referenced table, you have to create that table first. create table Depositor (customer_id integer, account_number integer, primary key (customer_id, account_number), foreign key (customer_id) references Customer (customer_id), foreign key (account_number) references Account (account_number)); create table Account (account_number integer, branch_id integer, balance numeric(8,2), primary key (account_number), foreign key (branch_id) references Branch (branch_id)); Branch: insert into Branch values (1, 'DowntownDet', 'Detroit', 40000000.00); insert into Branch values (2, 'UptownChi', 'Chicago', 32000000.00); insert into Branch values (3, 'DowntownGR', 'Grand Rapids', 21000000.00); insert into Loan values (1001, 1, 2008.08); insert into Loan values (1002, 1, 3201.06); insert into Loan values (1003, 2, 4508.03); insert into Loan values (1004, 3, 21008.00); insert into Customer values (1, 'Gretzky', 'Wayne', '14 S 6th St.', 55234); insert into Customer values (4, 'Carr', 'Paul', '16699 39th Ave N', 55231); insert into Customer values (2, 'Kilmer', 'Ellen', '205 Dupont Ave. N', 65031); insert into Customer values (3, 'Smith', 'Sam', '3598 Jones Rd.', 55305); insert into Customer values (5, 'Smith', 'Adam', '9873 5Th. St.', 55234); insert into Borrower values (4, 1001); insert into Borrower values (5, 1002); insert into Borrower values (2, 1003); insert into Borrower values (1, 1004); Depositor: Note: you have to load the Account table data first. insert into Depositor values (1, 232); insert into Depositor values (2, 235); insert into Depositor values (4, 294); insert into Depositor values (3, 295); insert into Depositor values (5, 249); insert into Account values (232, 3, 456.23); insert into Account values (235, 2, 4500.19); insert into Account values (294, 1, 6003.63); insert into Account values (295, 3, 7500.00); insert into Account values (249, 1, 670.85);
Write SQL statements to answer the following questions using Assignment 3’s schema (tables below). You can add more data to the tables if you want, just follow the PK and FK rules. Accounts are not Loans and Loans are not Accounts.
NOTE I already have Answers for part 1 BELOW PART 1 IS FOR ONLY REFRENCE TO ANSWER QUESTIONS 1-5
1- Find how many branches have loans between $4,100.00 and $7,000.00. HINT: Do not manually count the rows, have the DBMS engine do the work.
2- For each branch, find the Min and Max loan amounts. Your output should include Branch Id, min loan amount and max loan amount for that Branch.
3- Find how many accounts there are for each customer. The output should include customer id and number of accounts for that customer.
4- Find the average account balance for each Branch. The output should be a list of Branch Id and for each Branch Id, the average account balance in that Branch.
5- Find Customer ID, Customer name and Customer City for all accounts, sorted by Customer City, then Customer Last name.
PART 1 WITH ANSWERES USE THIS FOR REFRENCE TO ANSWER QUESTIONS 1-5 ABOVE.
Create the following tables
create table Branch
(branch_id integer,
branch_name varchar(50),
branch_location varchar(40),
money_on_hand numeric(15,2),
primary key (branch_id));
create table Loan
(loan_number integer,
branch_id integer,
amount numeric(8,2),
primary key (loan_number),
foreign key (branch_id) references Branch (branch_id));
create table Customer
(customer_id integer,
customer_last_name varchar(35),
customer_first_name varchar(25),
customer_street varchar(30),
customer_zip integer,
primary key (customer_id));
create table Borrower
(customer_id integer,
loan_number integer,
primary key (customer_id, loan_number),
foreign key (customer_id) references Customer (customer_id),
foreign key (loan_number) references Loan (loan_number));
Note: as the Account table is a referenced table, you have to create that table first.
create table Depositor
(customer_id integer,
account_number integer,
primary key (customer_id, account_number),
foreign key (customer_id) references Customer (customer_id),
foreign key (account_number) references Account (account_number));
create table Account
(account_number integer,
branch_id integer,
balance numeric(8,2),
primary key (account_number),
foreign key (branch_id) references Branch (branch_id));
- Branch:
insert into Branch values (1, 'DowntownDet', 'Detroit', 40000000.00);
insert into Branch values (2, 'UptownChi', 'Chicago', 32000000.00);
insert into Branch values (3, 'DowntownGR', 'Grand Rapids', 21000000.00);
insert into Loan values (1001, 1, 2008.08);
insert into Loan values (1002, 1, 3201.06);
insert into Loan values (1003, 2, 4508.03);
insert into Loan values (1004, 3, 21008.00);
insert into Customer values (1, 'Gretzky', 'Wayne', '14 S 6th St.', 55234);
insert into Customer values (4, 'Carr', 'Paul', '16699 39th Ave N', 55231);
insert into Customer values (2, 'Kilmer', 'Ellen', '205 Dupont Ave. N', 65031);
insert into Customer values (3, 'Smith', 'Sam', '3598 Jones Rd.', 55305);
insert into Customer values (5, 'Smith', 'Adam', '9873 5Th. St.', 55234);
insert into Borrower values (4, 1001);
insert into Borrower values (5, 1002);
insert into Borrower values (2, 1003);
insert into Borrower values (1, 1004);
- Depositor:
Note: you have to load the Account table data first.
insert into Depositor values (1, 232);
insert into Depositor values (2, 235);
insert into Depositor values (4, 294);
insert into Depositor values (3, 295);
insert into Depositor values (5, 249);
insert into Account values (232, 3, 456.23);
insert into Account values (235, 2, 4500.19);
insert into Account values (294, 1, 6003.63);
insert into Account values (295, 3, 7500.00);
insert into Account values (249, 1, 670.85);
Trending now
This is a popular solution!
Step by step
Solved in 4 steps with 4 images
