Try the following queries. (To execute, highlight the query then hit or click th

Question

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;

Explanation / Answer

Please find the SQL queries below.

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

SELECT loan_number FROM loan WHERE amount > 1000;

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

SELECT b.customer_name, b.loan_number FROM borrower b, loan l WHERE b.loan_number = l.loan_number;

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

SELECT b.customer_name FROM borrower b, loan l WHERE b.loan_number = l.loan_number AND b.amount > 1000;

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

SELECT d.customer_name FROM depositor d, account a WHERE d.account_number = a.account_number AND a.branch_name='Perryridge';

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.