[考題] 101高考 SQL
小弟SQL觀念薄弱
作了很多題目都覺得似是而非...有點慌
請問一下(二)作答是否正確?
是否有更有效率的作法?
(三)沒有頭緒...請問此小題該如何作答
謝謝
=======================101高考二級=============================
二、考慮以下的討論版資料庫綱目（Database schema）：
Member(mId, name, bDate, gender)
//會員編號，姓名，生日，性別
Article(mId, aId, title, content, postDate)
//會員編號，文章編號，標題，內容，張貼日期
Reply(mId, aId, rTime, content, responder)
//會員編號，文章編號，回覆日期時間，回覆內容，回覆者
若我們有以下的SQL敘述：
SELECT mId, name
FROM Member AS M
WHERE gender = ‘M’ AND
EXISTS
(SELECT *
FROM Article AS A
WHERE mId = M.mId AND (mId, aId) IN
(SELECT DISTINCT mId, aId
FROM Reply
WHERE mId = A.mId AND aId = A.aId
GROUP BY mId, aId
HAVING COUNT(*) > 10));
(二)以上查詢句的執行效率可能不佳，請指出造成其效率不佳的語法，並改寫成一個效率
較佳的敘述。（10分）
(三)針對你所改寫的查詢句，若想進一步提升其執行效率，請問可以建置那些索引（
Index）？請寫出建置這些索引的SQL敘述並說明原因。（10分）
============================================================================
擬作答:
(二)
SELECT mId, name
FROM Member AS M , Article AS A
WHERE gender = ‘M’ AND
A.mId = M.mId AND
(mId, aId) IN
(SELECT DISTINCT mId, aId
FROM Reply
WHERE mId = A.mId AND aId = A.aId
GROUP BY mId, aId
HAVING COUNT(*) > 10
)
);
--
※ 發信站: 批踢踢實業坊(ptt.cc), 來自: 123.195.151.104
※ 文章網址: http://www.ptt.cc/bbs/Examination/M.1400089170.A.E0E.html
推
05/15 10:17, , 1F
05/15 10:17, 1F
→
05/15 10:19, , 2F
05/15 10:19, 2F
→
05/15 10:19, , 3F
05/15 10:19, 3F
→
05/15 21:14, , 4F
05/15 21:14, 4F
推
05/15 21:38, , 5F
05/15 21:38, 5F
→
05/15 21:39, , 6F
05/15 21:39, 6F
→
05/15 22:41, , 7F
05/15 22:41, 7F
→
05/15 23:48, , 8F
05/15 23:48, 8F
→
05/16 01:18, , 9F
05/16 01:18, 9F
→
05/16 01:19, , 10F
05/16 01:19, 10F
→
05/16 01:20, , 11F
05/16 01:20, 11F
→
05/16 01:20, , 12F
05/16 01:20, 12F
Examination 近期熱門文章
PTT職涯區 即時熱門文章
41
91
98
221