churchill.sql

SSD7 练习1 自己写的哈 不保证全对

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

2. select distinct t.name,m.fname
from book b,member m,title t
where b.BorrowerMemNo=m.MemNo and b.CallNumber=t.CallNumber and
      (m.fname='John' or m.fname='Susan');

3. **select m.fname,m.lname
from Member m
where memno=(select Borrowermemno
             from book b
             where callnumber=(select callnumber
                               from title
                               where name='Iliad' and name='Odyssey'));

4. select m.fname,m.lname,b.book_id
from book b,member m,author a
where b.callnumber=a.callnumber and b.borrowermemno=m.memno and a.lname='Collins';
 
5. select m.phonenumber
from member m,author a,book b
where  b.callnumber=a.callnumber and b.borrowermemno=m.memno and a.lname='Tanenbaum';

// 6 //select m.fname,m.lname,b.book_id, count(distinct b.book_id)as theyborowed 
from book b,member m
where b.borrowermemno=m.memno  
group by b.book_id,m.fname,m.lname;


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

7. select m.fname,m.lname
from member m
where not exists
          (select b.borrowermemno
           from book b
           where b.borrowermemno=m.memno);
           
           
           
8. select distinct m.fname
from member m,title t,book b
where b.borrowermemno=m.memno and t.callnumber=b.callnumber and
m.address!='Pittsburgh' and m.phonenumber not like '412%'and t.name!='Pitt Roads';