Try the following queries. (To execute, highlight the query then hit or click the green triangle.)

select * from loan;

select * from loan where branch_name = 'Perryridge' and amount > 1200;

select count(amount) from loan;

select count(distinct amount) from loan; Why is this answer different?

select count(*) from loan;

select count(*) from borrower;

select count(*) from borrower, loan; Why so much records?

Try the following queries. Describe the results.

select customer_name, count(*) from depositor group by customer_name order by count(*) desc;

select branch_name, avg(amount) from loan group by branch_name order by avg(amount);

select branch_name, avg(amount) from loan where amount > 1000 group by branch_name;

select branch_name, avg(amount) from loan group by branch_name having avg(amount) > 1000;

select * from loan L, borrower B where B.loan_number = L.loan_number;

Write queries to answer the following questions.

List the numbers for all loans for more than $1000.

List the customer name with their loan number for all loans.

List the names for customers who have borrowed more than $1000.

List the names of all customers who are depositors at the Perryridge branch

/* clean up old tables; must drop tables with foreign keys first due to referential integrity constraints */

delete from depositor; drop table depositor;

delete from borrower; drop table borrower;

delete from account; drop table account;

delete from loan; drop table loan;

delete from branch; drop table branch;

delete from customer; drop table customer;

create table branch (branch_name varchar2(15) not null , branch_city varchar2(15) not null, assets number(12,2) not null, primary key(branch_name), check (assets >= 0));

create table account (account_number varchar2(15) not null, branch_name varchar2(15) not null, balance number(12,2) not null, primary key(account_number), foreign key(branch_name) references branch(branch_name), check (balance >= 0));

create table loan (loan_number varchar2(15) not null , branch_name varchar2(15) not null, amount number(12,2) not null, primary key(loan_number), foreign key(branch_name) references branch(branch_name), check (amount >= 0));

create table customer (customer_name varchar2(15) not null , customer_street varchar2(12) not null, customer_city varchar2(15) not null, primary key(customer_name));

create table depositor (customer_name varchar2(15) not null, account_number varchar2(15) not null, primary key(customer_name, account_number), foreign key(account_number) references account(account_number), foreign key(customer_name) references customer(customer_name));

create table borrower (customer_name varchar2(15) not null, loan_number varchar2(15) not null, primary key(customer_name, loan_number), foreign key(customer_name) references customer(customer_name), foreign key(loan_number) references loan(loan_number));

/* populate relations */

insert into customer values ('Jones', 'Main', 'Harrison'); insert into customer values ('Smith', 'Main', 'Rye'); insert into customer values ('Hayes', 'Main', 'Harrison'); insert into customer values ('Curry', 'North', 'Rye'); insert into customer values ('Lindsay', 'Park', 'Pittsfield'); insert into customer values ('Turner', 'Putnam', 'Stamford'); insert into customer values ('Williams', 'Nassau', 'Princeton'); insert into customer values ('Adams', 'Spring', 'Pittsfield'); insert into customer values ('Johnson', 'Alma', 'Palo Alto'); insert into customer values ('Glenn', 'Sand Hill', 'Woodside'); insert into customer values ('Brooks', 'Senator', 'Brooklyn'); insert into customer values ('Green', 'Walnut', 'Stamford'); insert into customer values ('Jackson', 'University', 'Salt Lake'); insert into customer values ('Majeris', 'First', 'Rye'); insert into customer values ('McBride', 'Safety', 'Rye');

insert into branch values ('Downtown', 'Brooklyn', 900000); insert into branch values ('Redwood', 'Palo Alto', 2100000); insert into branch values ('Perryridge', 'Horseneck', 1700000); insert into branch values ('Mianus', 'Horseneck', 400200); insert into branch values ('Round Hill', 'Horseneck', 8000000); insert into branch values ('Pownal', 'Bennington', 400000); insert into branch values ('North Town', 'Rye', 3700000); insert into branch values ('Brighton', 'Brooklyn', 7000000); insert into branch values ('Central', 'Rye', 400280);

insert into account values ('A-101', 'Downtown', 500); insert into account values ('A-215', 'Mianus', 700); insert into account values ('A-102', 'Perryridge', 400); insert into account values ('A-305', 'Round Hill', 350); insert into account values ('A-201', 'Perryridge', 900); insert into account values ('A-222', 'Redwood', 700); insert into account values ('A-217', 'Brighton', 750); insert into account values ('A-333', 'Central', 850); insert into account values ('A-444', 'North Town', 625);

insert into depositor values ('Johnson','A-101'); insert into depositor values ('Smith', 'A-215'); insert into depositor values ('Hayes', 'A-102'); insert into depositor values ('Hayes', 'A-101'); insert into depositor values ('Turner', 'A-305'); insert into depositor values ('Johnson','A-201'); insert into depositor values ('Jones', 'A-217'); insert into depositor values ('Lindsay','A-222'); insert into depositor values ('Majeris','A-333'); insert into depositor values ('Smith', 'A-444');

insert into loan values ('L-17', 'Downtown', 1000); insert into loan values ('L-23', 'Redwood', 2000); insert into loan values ('L-15', 'Perryridge', 1500); insert into loan values ('L-14', 'Downtown', 1500); insert into loan values ('L-93', 'Mianus', 500); insert into loan values ('L-11', 'Round Hill', 900); insert into loan values ('L-16', 'Perryridge', 1300); insert into loan values ('L-20', 'North Town', 7500); insert into loan values ('L-21', 'Central', 570);

insert into borrower values ('Jones', 'L-17'); insert into borrower values ('Smith', 'L-23'); insert into borrower values ('Hayes', 'L-15'); insert into borrower values ('Jackson', 'L-14'); insert into borrower values ('Curry', 'L-93'); insert into borrower values ('Smith', 'L-11'); insert into borrower values ('Williams','L-17'); insert into borrower values ('Adams', 'L-16'); insert into borrower values ('McBride', 'L-20'); insert into borrower values ('Smith', 'L-21');

commit;