exercise2.sql

ssd7的exercise2的完整答案

--第1题
SELECT Name, Year
FROM Title
WHERE CallNumber = SOME ( SELECT CallNumber
                          FROM Author
                          WHERE Lname = 'Churchill' ) 

--第2题
SELECT Name
FROM Title
WHERE CallNumber = SOME ( SELECT CallNumber
                          FROM Book
                          WHERE BorrowerMemNo = SOME ( SELECT MemNo
                                                       FROM Member
                                                       WHERE Fname = 'Jhon' OR Fname = 'Susan' ))

--第3题
SELECT Fname, Lname, DriverLicNo
FROM Member
WHERE MemNo = SOME ( SELECT BorrowerMemNo
                     FROM Book
                     WHERE CallNumber = SOME ( SELECT CallNumber
                                               FROM Title
                                               WHERE Name = 'Iliad' ) ) AND MemNo = SOME ( SELECT BorrowerMemNo
                                                                                           FROM Book
                                                                                           WHERE CallNumber =  SOME ( SELECT CallNumber
                                                                                                                      FROM Title
                                                                                                                      WHERE Name = 'Odyssey' ) )
--第4题
SELECT Fname, Lname ,DriverLicNo
FROM Member
WHERE MemNo IN (SELECT BorrowerMemNo
				FROM Book
				WHERE CallNumber IN (SELECT CallNumber
									 FROM Author
									 WHERE Lname = 'Collins'))

--第5题
SELECT PhoneNumber
FROM Member
Where MemNo IN ( SELECT BorrowerMemNo
                 FROM Book
                 WHERE CallNumber = SOME ( SELECT CallNumber
                                           FROM Author
                                           WHERE Lname = 'Tanenbaum' ) )

--第6题
SELECT Member.Fname, Member.Lname, Member.MemNo, count(Book.BorrowerMemNo) NumberOfBook
FROM Member, Book
WHERE Member.MemNo = Book.BorrowerMemNo
GROUP BY Member.Fname, Member.Lname, Member.MemNo
HAVING (SELECT COUNT(Book.BorrowerMemNo)
        FROM Book
        WHERE Member.MemNo = Book.BorrowerMemNo) > 3
ORDER BY COUNT ( Book.BorrowerMemNo ) DESC  

--第7题
SELECT Member.Fname, Member.Lname, Member.MemNo
FROM Member
WHERE Member.MemNo NOT IN ( SELECT Member.MemNo  
                            FROM Member, Book
                            WHERE Member.MemNo = Book.BorrowerMemNo
                            GROUP BY Member.MemNo
                            HAVING COUNT ( Book.BorrowerMemNo ) >= 1 )

--第8题
SELECT Member.Fname
FROM Member
WHERE Member.MemNo NOT IN ( SELECT Book.BorrowerMemNo
                            FROM Book
                            WHERE  Book.CallNumber = (
                                   SELECT  Title.CallNumber  
                                   FROM Title
                                   WHERE Title.Name = 'Pitt Roads' ))  AND PhoneNumber LIKE '412%'
ORDER BY Member.Fname