exercise2.txt

自己动手完成

1.List the titles of all books written by "Churchill," along with their Year of Publication. 

Answer:
Select title.Name, title.Year
	From title, author
	WHERE  title.callnumber=author.callNumber AND	
 		Author.Lname = 'Churchill';

Result: 
	
	name(varchar)		year(varchar)	
      ===========================================	
	"Second World War"	"1986-05-01" 

	

2.Retrieve the titles of all books borrowed by members whose first name is "John" or "Susan". 

Answer:
Select DISTINCT title.name
	From book,title,member
	   where
		book.borrowerMemNo=member.memNo and
		book.callnumber = title.callnumber and
		(member.fname = 'John' or member.fname = 'Susan')

Result:
	1	"Database Systems"
	2 	"Financial Accounting"
	3	"Iliad"
	4	"Networks"
	5	"Odyssey"
	6	"Pitt Roads"

	

3.List the names and IDs of all members who have borrowed the "Iliad" and the "Odyssey"—both books. 

Answer:
Select member.lname, memno
	From member
		where MemNo in (select book.BorrowerMemNo
                   from title, book
                   where title.callnumber = book.callnumber and name = 'Iliad')
		and MemNo in (select BorrowerMemNo
                   from title, book
                   where title.callnumber= book.callnumber and name='Odyssey');

Result:
        lname		memno
      =========================
	Carlione	125
		     


4.List the names and IDs of all the members who have borrowed all titles written by "Collins". Assume that a member may have borrowed multiple copies of the same title. 

Answer:
Select member.memNo, lname
	From member
	where not exists(select * 
		From author
                 where author.Lname='Collins' and not exists(select * 
		From book
                 where author.callnumber = book.callnumber and member.MemNo = book.BorrowerMemNo));

Result:
	memno(numeric)	lname(varchar)
      ==================================	     
	125		Carlione


		
5.Find the phone numbers of all members who have borrowed a book written by an author whose last name is "Tanenbaum." 

Answer:
Select member.phonenumber
	From member, author, book
	where author.lname = 'Tanenbaum' and
	author.callnumber = book.callnumber and
	book.borrowermemno = member.memno

Result:
	phonenumber(varchar)
  ===============================
   1.   412-200-0001
   2.	421-268-0001



6.Find those members who have borrowed more than three books and list their names, IDs, and the number of books they borrowed. Sort the results in descending order based on the number of books borrowed. 

Answer:
Select member.memno, lname, count(*)
	from member ,book
		where member.memno = book.BorrowerMemNo
		group by member.memno, lname
		having count(*)>=3
		order by count(*) desc;

Result:
		memno(numeric)		lname(varchar)		count(int8)
	1.	125			Carlione		6
	2.	124			Butterworth		3


7.List all members who have not borrowed any book. 

Answer:
Select DISTINCT MemNo, lname
	From Member
		Where NOT EXISTS 
		(Select *
		 FROM book
			 where  borrowerMemNo = member.MemNo );

Result:
		memno(numeric)		lname(varchar)
	1.	127			Tanaka



8.List in alphabetical order the first names of all the members who are residents of Pittsburgh (Phone numbers starting with "412") and who have not borrowed the book titled "Pitt Roads." 

Answer:
Select fname
From Member 
Where    
	phoneNumber like '412%'and
	  (memNo not in  
	(Select BorroWerMemNo
		From book, title
			Where book.callNumber=title.callNumber and
			 title.name = 'Pitt Roads'           
))

Result:
		fname(varchar)
	      ==================	
		Asorio