exercise2.sql

ssd7数据库系统练习二

1.
select t.name,t.year
from title t,author a
where t.callnumber=a.callnumber
  and a.lname='Churchill';
2.
select distinct t.name
from title t,book b,member m
where t.callnumber=b.callnumber and b.BorrowerMemNo=m.MemNo
  and (m.Fname='John' or m.Fname='Susan');
3.
select m1.MemNo,m1.Lname
from member m1
where m1.MemNo in (select b1.BorrowerMemNo
                   from title t1,book b1
                   where t1.callnumber=b1.callnumber and t1.name='Iliad')
and m1.MemNo in (select b2.BorrowerMemNo
                   from title t2,book b2
                   where t2.callnumber=b2.callnumber and t2.name='Odyssey');
4.
select m.MemNo,m.Lname
from member m
where not exists(select *
                 from author a
                 where a.Lname='Collins' and not exists(select *
                                                        from book b
                                                        where a.callnumber=b.callnumber and m.MemNo=b.BorrowerMemNo));
5.
select PhoneNumber
from member
where memno in(select BorrowerMemNo
               from book b,author a
               where b.CallNumber=a.CallNumber and a.lname='Tanenbaum');

It can also be written this way:

select PhoneNumber
from member10:22 2008-3-29
where memno in(select BorrowerMemNo
               from book
               where callnumber in(select CallNumber
                                   from author
                                   where lname='Tanenbaum'));

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

7.
select memno,lname
from member
where (memno not in(select borrowermemno
                   from book)) is false;

8.
select fname
from member
where PhoneNumber like '412%'
and (memno not in(select b.borrowermemno
                  from book b,title t
                  where b.callnumber=t.callnumber
                  and t.name='Pitt Roads')) is not false;