Homework Help is Here – Start Your Trial Now!
Engineering
Computer Science
USE this part to write SQL statements at the bottom questions 1-11   Branch(branch_id:integer, branch_name:varchar(50), branch_location:varchar(40), money_on_hand:numeric(15,2)       create table Branch             (branch_id                              integer,     branch_name                                 varchar(50),     branch_location                 varchar(40),     money_on_hand                 numeric(15,2),     primary key (branch_id));   Loan(loan_number:integer, branch_id:integer, amount:numeric(8,2))                   foreign key branch_id references Branch(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));   Customer(customer_id:integer, customer_last_name:varchar(35),customer_first_name:varchar(25), customer_street:varchar(30), customer_zip:integer)       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));   Borrower(customer_id:integer, loan_number:integer) foreign key (customer_id) references Customer(customer_id) foreign key (loan_number) references Loan(loan_number)   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));   Depositor(customer_id:integer, account_number:integer) foreign key (customer_id) references Customer(customer_id) foreign key (account_number) references Account(account_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));   Account(account_number:integer, branch_id:integer, balance:numeric(8,2)) foreign key branch_id references Branch(branch_id)   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)); Insert the following data in the tables using insert into statements:   Branch: 1, DowntownDet, Detroit, 40000000.00 2, UptownChi, Chicago, 32000000.00 3, DowntownGR, Grand Rapids, 21000000.00   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);   Loan: 1001, 1, 2008.08 1002, 1, 3201.06 1003, 2, 4508.03 1004, 3, 21008.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);   Customer: 1, Gretzky, Wayne, 14 S 6th St., 55234 4, Carr, Paul, 16699 39th Ave N, 55231 2, Kilmer,Ellen, 205 Dupont Ave. N, 65031 3, Smith, Sam, 3598 Jones Rd., 55305 5, Smith, Adam, 9873 5Th. St., 55234   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);       Borrower: 4, 1001 5, 1002 2, 1003 1, 1004   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: 1, 232 2, 235 4, 294 3, 295 5, 249   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);   Account: 232, 3, 456.23 235, 2, 4500.19 294, 1, 6003.63 295, 3, 7500.00 249, 1, 670.85   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 the above schema.  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. Please see attachments for questions 1-11

USE this part to write SQL statements at the bottom questions 1-11   Branch(branch_id:integer, branch_name:varchar(50), branch_location:varchar(40), money_on_hand:numeric(15,2)       create table Branch             (branch_id                              integer,     branch_name                                 varchar(50),     branch_location                 varchar(40),     money_on_hand                 numeric(15,2),     primary key (branch_id));   Loan(loan_number:integer, branch_id:integer, amount:numeric(8,2))                   foreign key branch_id references Branch(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));   Customer(customer_id:integer, customer_last_name:varchar(35),customer_first_name:varchar(25), customer_street:varchar(30), customer_zip:integer)       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));   Borrower(customer_id:integer, loan_number:integer) foreign key (customer_id) references Customer(customer_id) foreign key (loan_number) references Loan(loan_number)   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));   Depositor(customer_id:integer, account_number:integer) foreign key (customer_id) references Customer(customer_id) foreign key (account_number) references Account(account_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));   Account(account_number:integer, branch_id:integer, balance:numeric(8,2)) foreign key branch_id references Branch(branch_id)   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)); Insert the following data in the tables using insert into statements:   Branch: 1, DowntownDet, Detroit, 40000000.00 2, UptownChi, Chicago, 32000000.00 3, DowntownGR, Grand Rapids, 21000000.00   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);   Loan: 1001, 1, 2008.08 1002, 1, 3201.06 1003, 2, 4508.03 1004, 3, 21008.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);   Customer: 1, Gretzky, Wayne, 14 S 6th St., 55234 4, Carr, Paul, 16699 39th Ave N, 55231 2, Kilmer,Ellen, 205 Dupont Ave. N, 65031 3, Smith, Sam, 3598 Jones Rd., 55305 5, Smith, Adam, 9873 5Th. St., 55234   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);       Borrower: 4, 1001 5, 1002 2, 1003 1, 1004   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: 1, 232 2, 235 4, 294 3, 295 5, 249   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);   Account: 232, 3, 456.23 235, 2, 4500.19 294, 1, 6003.63 295, 3, 7500.00 249, 1, 670.85   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 the above schema.  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. Please see attachments for questions 1-11

Database System Concepts
Database System Concepts
7th Edition
ISBN:9780078022159
Author:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Chapter1: Introduction
Section: Chapter Questions
Problem 1PE
See similar textbooks
icon
Related questions
Question

USE this part to write SQL statements at the bottom questions 1-11

 

  1. Branch(branch_id:integer, branch_name:varchar(50), branch_location:varchar(40), money_on_hand:numeric(15,2)

 

    create table Branch

            (branch_id                              integer,

    branch_name                                 varchar(50),

    branch_location                 varchar(40),

    money_on_hand                 numeric(15,2),

    primary key (branch_id));

 

  1. Loan(loan_number:integer, branch_id:integer, amount:numeric(8,2))

                  foreign key branch_id references Branch(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));

 

  1. Customer(customer_id:integer, customer_last_name:varchar(35),customer_first_name:varchar(25), customer_street:varchar(30), customer_zip:integer)

 

    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));

 

  1. Borrower(customer_id:integer, loan_number:integer)

foreign key (customer_id) references Customer(customer_id)

foreign key (loan_number) references Loan(loan_number)

 

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));

 

  1. Depositor(customer_id:integer, account_number:integer)

foreign key (customer_id) references Customer(customer_id)

foreign key (account_number) references Account(account_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));

 

  1. Account(account_number:integer, branch_id:integer, balance:numeric(8,2))

foreign key branch_id references Branch(branch_id)

 

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));

Insert the following data in the tables using insert into statements:

 

  1. Branch:

1, DowntownDet, Detroit, 40000000.00

2, UptownChi, Chicago, 32000000.00

3, DowntownGR, Grand Rapids, 21000000.00

 

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);

 

  1. Loan:

1001, 1, 2008.08

1002, 1, 3201.06

1003, 2, 4508.03

1004, 3, 21008.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);

 

  1. Customer:

1, Gretzky, Wayne, 14 S 6th St., 55234

4, Carr, Paul, 16699 39th Ave N, 55231

2, Kilmer,Ellen, 205 Dupont Ave. N, 65031

3, Smith, Sam, 3598 Jones Rd., 55305

5, Smith, Adam, 9873 5Th. St., 55234

 

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);

 

 

 

  1. Borrower:

4, 1001

5, 1002

2, 1003

1, 1004

 

insert into Borrower values (4, 1001);

insert into Borrower values (5, 1002);

insert into Borrower values (2, 1003);

insert into Borrower values (1, 1004);

 

  1. Depositor:

1, 232

2, 235

4, 294

3, 295

5, 249

 

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);

 

  1. Account:

232, 3, 456.23

235, 2, 4500.19

294, 1, 6003.63

295, 3, 7500.00

249, 1, 670.85

 

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 the above schema.  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.

Please see attachments for questions 1-11

 

 

 

 

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. 6- Find Customer ID, Customer name and the number of loans for each Customer. 7- Find Loan number and Customer Id of the loan with the lowest amount. 8- Create a view called Gary Branch V that contains Branch Id, Branch name, and number of loans for each Branch that is in the city of Gary. 9- For each Customer in Hopkins, find the balance in their account(s). 10- Find how many different accounts each customer has at each Branch. The output should be a list of Customer ID and for each Customer ID, the number of accounts for this customer by Branch ID. 11- Find the branch with the highest or largest Average loan amount. List the Branch ID, Branch Name, and the Highest Average loan amount.
Transcribed Image Text: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. 6- Find Customer ID, Customer name and the number of loans for each Customer. 7- Find Loan number and Customer Id of the loan with the lowest amount. 8- Create a view called Gary Branch V that contains Branch Id, Branch name, and number of loans for each Branch that is in the city of Gary. 9- For each Customer in Hopkins, find the balance in their account(s). 10- Find how many different accounts each customer has at each Branch. The output should be a list of Customer ID and for each Customer ID, the number of accounts for this customer by Branch ID. 11- Find the branch with the highest or largest Average loan amount. List the Branch ID, Branch Name, and the Highest Average loan amount.
Expert Solution
Step 1

As per our company guidelines we are supposed to answer only first  sub-parts. Kindly repost other parts in next question.

steps

Step by step

Solved in 2 steps with 3 images

SEE SOLUTIONCheck out a sample Q&A here
Blurred answer
Similar questions
  • SEE MORE QUESTIONS
Recommended textbooks for you
Database System Concepts
Database System Concepts
Computer Science
ISBN:
9780078022159
Author:
Abraham Silberschatz Professor, Henry F. Korth, S. Sudarshan
Publisher:
McGraw-Hill Education
Starting Out with Python (4th Edition)
Starting Out with Python (4th Edition)
Computer Science
ISBN:
9780134444321
Author:
Tony Gaddis
Publisher:
PEARSON
Digital Fundamentals (11th Edition)
Digital Fundamentals (11th Edition)
Computer Science
ISBN:
9780132737968
Author:
Thomas L. Floyd
Publisher:
PEARSON
C How to Program (8th Edition)
C How to Program (8th Edition)
Computer Science
ISBN:
9780133976892
Author:
Paul J. Deitel, Harvey Deitel
Publisher:
PEARSON
Database Systems: Design, Implementation, & Manag…
Database Systems: Design, Implementation, & Manag…
Computer Science
ISBN:
9781337627900
Author:
Carlos Coronel, Steven Morris
Publisher:
Cengage Learning
Programmable Logic Controllers
Programmable Logic Controllers
Computer Science
ISBN:
9780073373843
Author:
Frank D. Petruzella
Publisher:
McGraw-Hill Education
SEE MORE TEXTBOOKS