Database Management Systems:

Part 1:

create table Branch (branch_id int NOT NULL PRIMARY KEY, branch_name VARCHAR2(50), branch_city VARCHAR2(50), assets NUMBER(11,2));

CREATE TABLE Loan (loan_number int NOT NULL PRIMARY KEY, branch_id int, amount number (8,2),FOREIGN KEY (branch_id) REFERENCES Branch(branch_id));

Create table Customer(customer_id int NOT NULL PRIMARY KEY, customer_name varchar2(30), customer_street varchar2(30), customer_city varchar2(50));

Create table Borrower (customer_id int NOT NULL PRIMARY KEY, loan_number int,foreign key (customer_id) references Customer(customer_id), foreign key (loan_number) references Loan(loan_number));

Create table Depositor (customer_id int NOT NULL PRIMARY KEY, account_number int ,foreign key (customer_id) references Customer(customer_id), foreign key (account_number) references Account(account_number));

create table Account(account_number int NOT NULL PRIMARY KEY, branch_id int, balance number(8,2),foreign key (branch_id) references Branch(branch_id));

Part2:

1.Branch:

insert into branch values (1, 'DowntownChi', 'Chicago', 40000000.00);

insert into branch values (2, 'UptownMPL', 'Minneapolis', 32000000.00);

insert into branch values (3, 'DowntownSP', 'St. Paul', 21000000.00);

2.Loan:

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

3.Customer:

insert into customer values (1, 'Gretzky', '14 S 6th St.', 'St. Paul');

insert into customer values (4, 'Carr', '16699 39th Ave N', 'Gary');

insert into customer values (2, 'Kilmer', '205 Dupont Ave. N', 'Minneapolis');

insert into customer values (3, 'Smith', '3598 Jones Rd.', 'Hopkins');

insert into customer values (5, 'Smith', '9873 5Th. St.', 'Chicago');

4.Borrower:

insert into borrower values(4, 1001);

insert into borrower values(5, 1002);

insert into borrower values(2, 1003);

insert into borrower values(1, 1004);

5.Depositor:

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

6.Account:

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

Part 3:

List the Customer Code, Name for all customers, sorted by Name.

select customer_id,customer_name from customer order by customer_name;

2.List the loan number and loan amount for all loans belonging to branch 1.

select loan_number,amount from loan where branch_id ='1';

3.List the loan number, loan amount from all branches with the branch name UptownMPL (use branch name, if you have a 1000 branches, you are not going to know the branch number for each branch name).

select l.loan_number,l.amount from loan l ,branch b where b.branch_name='UptownMPL';

4.List all account numbers for customers with the name of Smith. select a.account_number from account a,customer c where c.customer_name='Smith';

5.List all the loan numbers for the branches in the city of Duluth.

this query will return no rows because there are no city like Duluth.

6.For each branch list the branch name, customer name, and customer city for all customers that have an account at that branch. (note: accounts, not loans)

select b.branch_name,c.customer_name,c.customer_city from customer c,branch b where b.branch_id in (select branch_id from branch);

Part4:

1.Insert the following entry in CUSTOMER

insert into customer values (4, 'Ford', '19 14th Ave N', 'Mankato');

this query will not work because previously we are having 1 row which is having the value as 4 in customer_id which is unique. 2. Insert the following entry in LOAN

insert into loan values (1005, 2, 30000.00);

3. Insert the following entry in Borrower

insert into borrower values (3, 1005);

4. Insert the following entry to the LOAN table

insert into loan values (1006, 4, 45000.00);

5. Insert the following entry in Branch table

insert into branch values ('4', 'DowntownMK', 'Mankato', 2100000.00);

According to this Information Please give me this question.

Write SQL statements to answer the following questions using Assignment 3s schema.

1- Find how many branches had have loans over $2000.00.

2- For each branch, find the most expensive loan. Your output should include Branch Id, loan amount for the highest loan 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 total balance amount of all accounts by each Branch. The output should be a list of Branch Id and for each Branch Id, the total balance of accounts in that Branch.

5- Find Customer ID, Customer name and the number of loans for each Customer.

6- Find Customer ID, Customer name for all accounts, sorted by Customer name.

7- Find Loan number and Branch Id of the loan with the lowest amount.

8- Create a view called MPL_Branch_V that contains Branch Id, Branch name, and number of loans for each Branch that is in the city of Minneapolis.

9- For each Customer in Hopkins, find the total amount of all their loans.

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.