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.
USE THE FOLLOWING SCHEMA TO WRITE SQL STATEMENTS FOR QUESTIONS 5-11 BELOW
- 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));
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.
Trending now
This is a popular solution!
Step by step
Solved in 2 steps
