?? exercise2.sql
字號:
--第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
?? 快捷鍵說明
復(fù)制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -