exercise 2.txt

Run the SQL script given to you to create a Library database. Note that each row in the Book table d

/*第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 DriverLicNo = SOME ( SELECT BorrowerMemNo
                           FROM Book
                           WHERE CallNumber = SOME ( SELECT CallNumber
                                                     FROM Title
                                                     WHERE Name = 'Iliad' ) ) AND DriverLicNo = SOME ( SELECT BorrowerMemNo
                                                                                                       FROM Book
                                                                                                       WHERE CallNumber =  SOME ( SELECT CallNumber
       FROM Title
       WHERE Name = 'Odyssey' ) )

/*第4题*/
SELECT Fname, Lname
FROM Member
Where MemNo IN (SELECT BorrowerMemNo
                FROM  (SELECT BorrowerMemNo, CallNumber
                       FROM Book
                       WHERE CallNumber IN ( SELECT CallNumber
                                                 FROM Author
                                                 WHERE Lname = 'Collins' )
                       UNION
                       SELECT BorrowerMemNo, CallNumber
                       FROM Book
                       WHERE CallNumber IN ( SELECT CallNumber
                                                 FROM Author
                                                 WHERE Lname = 'Collins' )) AS MidTab
                GROUP BY BorrowerMemNo
                HAVING COUNT(BorrowerMemNo) = (SELECT COUNT(Lname)
                                               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 Book.Book_ID, Member.Fname, Member.Lname, Member.MemNo
FROM Member, Book
WHERE Member.MemNo = Book.BorrowerMemNo
GROUP BY Book.Book_ID, Member.Fname, Member.Lname, Member.MemNo
HAVING (SELECT COUNT(Book.BorrowerMemNo)
        FROM Book
        WHERE Member.MemNo = Book.BorrowerMemNo) > 3
ORDER BY COUNT ( Book.Book_ID ) 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%'