exercise2.txt

ssd7 exercise2 答案

SELECT Year, Name
FROM Title
WHERE CallNumber = 
      (SELECT CallNumber
       FROM Author
       WHERE Lname = 'churchill');


SELECT Name
FROM Title
WHERE CallNumber = ( 
       SELECT CallNumber                          
       FROM Book                          
       WHERE BorrowerMemNo = ( 
                 SELECT MemNo                                                       
                 FROM Member                                                       
                 WHERE Fname = 'Jhon' OR Fname = 'Susan' ))



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


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'))


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