exercise2.sql

icarnegie上数据库课程中的exercise2的答案

--question1：

SELECT Name , Year 
FROM Title a , Author b
WHERE b.CallNumber = a.CallNumber AND 
      b.Lname = 'Churchill'

--question2:

SELECT Name 
FROM Title 
WHERE CallNumber IN
	(SELECT CallNumber
	 FROM Book , Member
	 WHERE Book.BorrowerMemNo = Member.MemNo AND
	 	    (Member.Fname = 'john' OR  Member.Fname = 'Susan' )
	)


--question3:

SELECT Fname , Lname , DriverLicNo
FROM Member
WHERE MemNo = SOME (SELECT BorrowerMemNo
		    FROM Book
		    WHERE CallNumber = (SELECT CallNumber
				        FROM Title 
				        WHERE Name ='Odyssey' ))AND
      MemNo = SOME (SELECT BorrowerMemNo
		    FROM Book
		    WHERE ( CallNumber =(SELECT CallNumber
				         FROM Title 
				         WHERE Name ='Iliad' )
				))

--question4:

SELECT Fname , MI , Lname ,DriverLicNo
FROM Member
WHERE MemNo IN (SELECT BorrowerMemNo
		FROM Book
		WHERE CallNumber IN (SELECT CallNumber
				     FROM Author
				     WHERE Lname = 'Collins'))

--question5:

SELECT PhoneNumber
FROM Member
WHERE MemNo IN ( SELECT BorrowerMemNo
		 FROM Book
		 WHERE CallNumber IN ( SELECT CallNumber 
				       FROM Author
				       WHERE Lname ='Tanenbaum'))

--question6:

SELECT Member.Fname , Member.Lname , Member.DriverLicNo , COUNT (Member.DriverLicNo) NumberOfBook 
FROM Member , Book
WHERE Member.MemNo = Book.BorrowerMemNo
GROUP BY  Member.MemNo , Member.Fname , Member.Lname , Member.DriverLicNo
HAVING (SELECT COUNT(Book.BorrowerMemNo)
        FROM Book
	WHERE Member.MemNo = Book.BorrowerMemNo)>3
ORDER BY NumberOfBook  DESC

--question7:

SELECT *
FROM Member
WHERE MemNo NOT IN(SELECT Member.MemNo
		   FROM Book , Member
		   WHERE Member.MemNo = Book.BorrowerMemNo
		   GROUP BY Member.MemNo
		   HAVING COUNT(Book.BorrowerMemNo)>0)

--question8:

SELECT Fname , Lname
FROM Member
WHERE Address LIKE '%Pittsburgh%' AND PhoneNumber LIKE '412%' AND
      MemNo NOT IN (SELECT Book.BorrowerMemNo
		    FROM Book
		    WHERE Book.CallNumber=(SELECT Title.CallNumber
					   FROM Title
					   WHERE Title.Name='Pitt Roads'))

ORDER BY Fname , Lname