Consider the following sehema of a bank: customer (name: string, credit: integer

Question

Consider the following sehema of a bank: customer (name: string, credit: integer) loan (no: string, type: string, minCredit: integer) borrower (cname: string, Ino: string, due: date) where borrower (cname and borrower.Ino are foreign keys referencing customer, respectively loan, whose keys are name, respectively no (number). Attribute loan.minCredit indicates the minimum credit required of a customer to qualify for that loan. Write the following queries in SQL. Find the names of customers who took a "jumbo mortgage" loan. Find the names of customers who took a "jumbo mortgage" loan or have a credit rating of at least 750. Find the names of customers who took a "jumbo mortgage" loan arid a "student" loan. Find the names of customers who took every loan. Find the names of customers who took every "jumbo mortgage" or "student" loan. Find the names of customers who took every "jumbo mortgage" loan or took every "student" loan. Find pairs of names of customers who share the same loan. Avoid listing a customer with himself (c.g. do not list (Joe, Joe)). Also avoid repeating pairs which are equal modulo swapping the components (e.g. only one of (John, Jane), (Jane, John) should be listed). Find the loan numbers (no) shared by at least two different customers. Find the numbers for the loans with highest minCredit requirement among the loans taken by borrowers named "Smith". Find the loan numbers shared by every customer with a credit rating below 600.

Explanation / Answer

create table customer(name varchar2(20) primary key,credit number);
create table loan(no number primary key,type varchar2(20),mincredit number);
create table borrower(cname varchar2(20),loanno number,duedate date, primary key(cname,loanno) foreign key(cname) references customer(name),foreign key(loanno) references loan(no) );
  
insert into customer values('mike',5000);
insert into customer values('john',6000);
insert into customer values('joe',5000);
insert into customer values('joseph',2000);
insert into customer values('marsh',3000);
insert into customer values('tina',4000);

insert into loan values(100,'jumbomortage',3000);
insert into loan values(101,'studentloan',4000);

insert into borrower values('mike',100,'13-dec-2015');
insert into borrower values('mike',101,'13-nov-2015');

insert into borrower values('john',100,'16-dec-2015');
insert into borrower values('joe',101,'13-dec-2015');
insert into borrower values('joseph',100,'17-dec-2015');
insert into borrower values('joseph',101,'13-dec-2015');
insert into borrower values('marsh',100,'13-dec-2015');
insert into borrower values('tina',101,'13-dec-2015');

Queries
1:
select c.name from customer c,loan l,borrower b where c.name=b.cname and b.loanno=l.no;

2.
select c.name from customer c,loan l,borrower b where c.name=b.cname and b.loanno=l.no and c.credit>=750 or l.type='jumbomortage';

3.
select c.name from customer c,loan l,borrower b where c.name=b.cname and b.loanno=l.no and l.type like 'jumbomortage' and 'studentloan';
4.
select c.name from customer c,loan l,borrower b where c.name=b.cname and b.loanno=l.no and l.type like 'jumbomortage' and 'studentloan';
5.
select c.name from customer c,loan l,borrower b where c.name=b.cname and b.loanno=l.no and l.type like 'jumbomortage' or 'studentloan';
6.
select c.name from customer c,loan l,borrower b where c.name=b.cname and b.loanno=l.no and l.type like 'jumbomortage' or 'studentloan';

7.
more information is needed for query 7
8.

more information needed for query 8

9.

select count(l.no) as noofloans ,max(l.mincredit) from customer c,loan l,borrower b where l.no=b.loanno and c.name='mike';

note: in 9th query make 'mike' as 'smith' as given in question

10.more information needed for query 10

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

---