exercise2.txt

ssd7 exercise 2 ,绝对有用~欢迎下载

1.****************************************************************************************************
  FROM   Title
  WHERE  Title.CallNumber IN (SELECT Author.CallNumber
	       	     	    FROM   Author
		     	    WHERE  Author.Lname = 'Churchill');  

2.**************************************************************************************************** 
  SELECT Title.Name
  FROM   Title
  WHERE  Title.CallNumber IN (SELECT Book.CallNumber
		            FROM   Book
		            WHERE  Book.BorrowerMemNo IN (SELECT Member.MemNo
							  FROM   Member
							  WHERE  Member.Fname = 'John'
							  OR     Member.Fname ='Susan'));

3.**************************************************************************************************** 
  (SELECT fname,mi,lname,memno
  FROM book,title,member
  WHERE member.memno=book.borrowermemno AND book.callnumber=title.callnumber
        AND Title.Name = 'Iliad')
  INTERSECT
  (SELECT fname,mi,lname,memno
  FROM book,title,member
  WHERE member.memno=book.borrowermemno AND book.callnumber=title.callnumber
        AND Title.Name ='Odyssey' );

4.**************************************************************************************************** 
  SELECT Member.Fname,Member.MI,Member.Lname,Member.MemNo
  FROM   Member
  WHERE  NOT EXISTS (SELECT *
		   FROM   Author 
	           WHERE  Author.Lname='Collins' 
		   AND    NOT EXISTS(SELECT *
			             FROM   Book
			  	     WHERE  Member.MemNo=Book.BorrowerMemNo
 	       			     AND    Author.CallNumber=Book.CallNumber));  

 5.**************************************************************************************************** 
  SELECT phonenumber
  FROM member,book,title,author
  WHERE member.memno=book.borrowermemno AND book.callnumber=title.callnumber
  AND author.lname='Tanenbaum' AND author.callnumber=title.callnumber ;

6.****************************************************************************************************   
  select newtable.fname,newtable.lname,newtable.memno, newtable.number
  from
  (select fname,lname,memno, count(memno) as number 
  from member,book
  where (member.memno=book.borrowermemno)  
  group by fname,lname,memno
  order by number )as newtable
  where newtable.number>3
  order by newtable.number

7.***************************************************************************************************  
  select member.*
  from member
  except 
  select distinct member.*
  from book,member
  where book.borrowermemno=member.memno

8.****************************************************************************************************   
  SELECT Member.*
  FROM   Member 
  WHERE  PhoneNumber LIKE '412%' 
  EXCEPT 
  SELECT Member.*
  FROM   Member,Book,Title
  WHERE  (Member.MemNo = Book.borrowermemno) AND (Book.CallNumber = Title.CallNumber) AND (Title.Name          
         = 'Pitt Roads')
  ORDER BY FNAME