database.sql

ssd7 exercise2 答案

/*QUESTION 1*/
SELECT Name, Year 
FROM Title
WHERE CallNumber = SOME ( 
       SELECT CallNumber 
       FROM Author 
       WHERE Lname = 'Churchill' )
        
       
/*QUESTION 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' ))

/*QUESTION 3*/
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')))


/*QUESTION 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'))


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


/*QUESTION 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  



/*QUESTION 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 )


/*QUESTION 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%'