exercise2sql.sql

卡耐基教程联系高分答案。。。不可错过哦。。。

-- My Answer:

-- 1

SELECT Name, Year
FROM Title, Author
WHERE Title.CallNumber = Author.CallNumber AND
	(Author.Fname = 'Churchill' OR Author.Lname = 'Churchill');

-- 2

SELECT Name
FROM Title
WHERE Title.CallNumber IN
	(SELECT CallNumber
	 FROM Book
	 WHERE BorrowerMemNo IN
		(SELECT MemNo
		 FROM Member
		 WHERE Member.Fname = 'John'OR Member.Fname = 'Susan'));

-- 3

SELECT Fname, MI, Lname, MemNo
FROM Member
WHERE Member.MemNo IN(
	(SELECT BorrowerMemNo
	 FROM Book
	 WHERE Book.CallNumber IN
		(SELECT Title.CallNumber
		 FROM Title
		 WHERE Title.Name = 'Iliad'))
	INTERSECT  		/*intersect operation: gather the both books*/
	(SELECT book.BorrowerMemNo
	 FROM Book book 
	 where book.CallNumber IN
		(SELECT title.CallNumber 
		FROM Title title 
		WHERE title.Name = 'Odyssey')))

-- 4

SELECT Fname, MI, Lname, MemNo
FROM Member
WHERE Member.MemNo IN
	(SELECT BorrowerMemNo
	 FROM Book
	 WHERE Book.CallNumber IN
		(SELECT Author.CallNumber
		 FROM Author
		 WHERE Author.Fname='Collins' OR Author.Lname = 'Collins'))
-- 5

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

-- 6

SELECT Fname, MI, Lname, MemNo, COUNT(MemNo)AS Count 
FROM Member, Book
WHERE Member.MemNo =Book.BorrowerMemNo
GROUP BY Fname, MI, Lname, MemNo, Book.BorrowerMemNo
HAVING COUNT(MemNo) > 3
ORDER BY Count DESC;  --compositor


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

-- 8

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