queries.sql

SSD7

/*第一题*/
select Title.Name,Title.Year
from Title,Author
where Title.CallNumber=Author.CallNumber and Author.Lname='Churchill'
order by Title.Year;


/*第二题*/
select Title.Name
from Title,Book,Member
where Title.CallNumber=Book.CallNumber and Book.BorrowerMemNo=Member.MemNo and Member.Fname ='John' 
union
select Title.Name
from Title,Book,Member
where Title.CallNumber=Book.CallNumber and Book.BorrowerMemNo=Member.MemNo and Member.Fname ='Susan' ;


/*第三题*/
SELECT Member.Fname, Member.Lname, Member.DriverLicNo
FROM Member,Book,Title
WHERE Member.memno = Book.BorrowerMemNo
       and  Book.CallNumber = Title.CallNumber
                                   and Title.Name = 'Iliad'
intersect
SELECT Member.Fname, Member.Lname, Member.DriverLicNo
FROM Member,Book,Title
WHERE Member.memno = Book.BorrowerMemNo
       and  Book.CallNumber = Title.CallNumber
                                   and Title.Name ='Odyssey' ;


/*第四题*/
select distinct fname,lname,driverlicno 
from Member MemberX
where not exists
(
 select *
 From Title,Author
 where Title.Callnumber=Author.Callnumber and Author.lname='Collins' and
   not exists
   (
    select *
    from Member MemberY,Book
    where MemberX.memno=MemberY.memno and Book. borrowermemno=MemberY.memno and Book.Callnumber=Title.Callnumber
   )
);


/*第五题*/
select distinct Member.phonenumber
from Member,Book,Title,Author
where Member.memno=Book.borrowermemno and Book.Callnumber=Title.Callnumber
      and Title.Callnumber=Author.Callnumber
      and Author.lname='Tanenbaum';


/*第六题*/
select Member.fname,Member.lname,Member.driverlicno,count(Book.book_id) as my_count
from Book,Member
where Book.borrowermemno=Member.MEMNO
group by Member.fname,Member.lname,Member.driverlicno
having count(Book.book_id)>3
order by count(Book.book_id) desc;


/*第七题*/
(select Member.fname,Member.lname,Member.driverlicno
from Book,Member
)
except
(select Member.fname,Member.lname,Member.driverlicno
from Book,Member
where Book.borrowermemno=Member.memno
);


/*第八题*/
(select Member.fname,Member.lname,Member.driverlicno
from Member
where Member.address like '%Pittsburgh%' and Member.phonenumber like '412%'
order by Member.fname)
except
( select Member.fname,Member.lname,Member.driverlicno
  from Book,Member,Title
  where Book.borrowermemno=Member.memno and Book.Callnumber=Title.Callnumber and Title.name='Pitt Roads'
);