ssd7-exercise2.sql

卡耐基的SSD7-Exercise2~~答案的部分~~

/*1*/
select name,year from title where callnumber=(select callnumber from
author where lname='Churchill');

select name,year from title,author where title.callnumber=author.callnumber and author.lname='Churchill';

/*2*/
select name from title where callnumber in(select callnumber from book
where borrowermemno in(select memno from member where fname='John' or fname='Susan'));

select name from title,member,book where member.memno=book.borrowermemno and book.callnumber=title.callnumber and member.fname in('Jonh','Susan');

/*3*/
select fname,lname,driverlicno from member where memno in ((select borrowermemno from book where callnumber in (select callnumber from title where name='Iliad')) intersect (select borrowermemno from book where callnumber in (select callnumber from title where name='Odyssey')));

select fname,mi,lname,driverlicno from member,title,book where member.memno=book.borrowermemno and book.callnumber=title.callnumber and title.name='Iliad' intersect select fname,mi,lname,driverlicno from member,title,book where member.memno=book.borrowermemno and book.callnumber=title.callnumber and title.name=
'Odyssey';


/*4*/
select fname,lname,driverlicno from member where memno in(select borrowermemno from book where callnumber in (select callnumber from author where lname='Collins'));

select member.fname,member.mi,member.lname,driverlicno from member,author,book where member.memno=book.borrowermemno and book.callnumber=author.callnumber and author.lname='Collins' group by member.fname,member.mi,member.lname,driverlicno;


/*5*/
select m.phonenumber from member m, book b,title t,author a where m.memno=b.borrowermemno and b.callnumber=t.callnumber and t.callnumber=a.callnumber and a.lname='Tanenbaum';

select phonenumber from member where memno in(select borrowermemno from book where callnumber in(select callnumber from author where lname='Tanenbaum'));


/*6*/
select m.fname,m.lname, m.memno,count(b.borrowermemno) as NumberOfBook from member m,book b where m.memno=b.borrowermemno group by m.memno,m.fname,m.lname having(select count(b.borrowermemno) from book b where m.memno=b.borrowermemno)>3 order by count(b.borrowermemno) desc;

select member.fname,member.mi,member.lname,driverlicno,count(book.borrowermemno) from member,book where member.memno=book.borrowermemno group by member.fname,member.mi,member.lname,driverlicno having count(book.borrowermemno)>3 order by count(book.borrowermemno) desc;


/*7*/
select m.* from member m where m.memno not in(select m.memno from book b,member m where m.memno=b.borrowermemno group by m.memno having count(b.borrowermemno)>=1);

select * from member where memno not in(select borrowermemno from book group by borrowermemno having count(borrowermemno)>=1);


/*8*/
select m.* from member m where m.phonenumber like '412%' and m.memno not in(select b.borrowermemno from book b,title t,member m where t.name='Pitt Roads' and t.callnumber=b.callnumber and m.memno=b.borrowermemno) order by m.fname;

select * from member where phonenumber like '412%' and memno not in(select borrowermemno from book where callnumber in(select callnumber from title where name='Pitt Roads')) order by fname;