USE THE FOLLOWING SCHEMA TO WRITE SQL STATEMENTS FOR QUESTIONS 5-7 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
USE THE FOLLOWING SCHEMA TO WRITE SQL STATEMENTS FOR QUESTIONS 5-7 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.
Step by step
Solved in 2 steps
