阿摩線上測驗
題組內容
一、某書店資料庫中有數個資料表，其DDL如下，請回答下列問題：（3題，共16分）
(二)當某讀者把某作者的所有書都買過，我們稱該讀者為「鐵粉」。試以SQL語法，列出達文西(Author.name＝’達文西’)的所有「鐵粉」姓名。(3分)
詳解 (共 3 筆)
佳佳
詳解 #6234972
努力再努力
詳解 #6248174
SELECT M.name // ...
(共 469 字，隱藏中）
前往觀看
b00892
詳解 #6279626
SELECT M.name AS "鐵粉姓名"
FROM Member M
JOIN Buy B ON M.mId = B.mId
JOIN Author A ON B.pNo = A.pNo
WHERE A.name = '達文西'
GROUP BY M.mId, M.name
HAVING COUNT(DISTINCT B.pNo) = (
SELECT COUNT(DISTINCT P.pNo)
FROM Author A
JOIN Product P ON A.pNo = P.pNo
WHERE A.name = '達文西'
);
FROM Member M
JOIN Buy B ON M.mId = B.mId
JOIN Author A ON B.pNo = A.pNo
WHERE A.name = '達文西'
GROUP BY M.mId, M.name
HAVING COUNT(DISTINCT B.pNo) = (
SELECT COUNT(DISTINCT P.pNo)
FROM Author A
JOIN Product P ON A.pNo = P.pNo
WHERE A.name = '達文西'
);