國中教甄｜資訊科技資訊科技109 年第 93 題單選題

select c_name, clsname from studata inner join score using (s_id) where score.math > 90; 下列那個 SQL 指令可和上述 SQL 指令得到相同的查詢結果？

Aselect c_name, clsname from studata inner join score having studata.s_id =score.s_id and score.math>90;

Bselect c_name, clsname from studata where studata.s_id=score.s_id and score.math>90;

Cselect c_name, clsname from studata, score where score.math>90;

Dselect c_name, clsname from studata, score where studata.s_id=score.s_id and score.math>90;正確答案

答案與詳解

正確答案

INNER JOIN … USING (s_id) 就是以 studata.s_id = score.s_id 做等值連接;把連接條件改寫到 WHERE,再加上 score.math > 90,就是 D,結果相同。

Aselect c_name, clsname from studata inner join score having studata.s_id =score.s_id and score.math>90;✕ 易錯陷阱

連接條件應寫在 ON/USING 或 WHERE;HAVING 是用來篩選 GROUP BY 分組後的結果。這句沒有 ON/USING,又把連接條件與篩選條件放進 HAVING,不是標準且等價的寫法。

Bselect c_name, clsname from studata where studata.s_id=score.s_id and score.math>90;✕ 錯誤

FROM 只列出 studata,WHERE 卻引用了 score 資料表的欄位,score 沒有出現在 FROM 子句中,查詢會發生錯誤。

Cselect c_name, clsname from studata, score where score.math>90;✕ 易錯陷阱

Dselect c_name, clsname from studata, score where studata.s_id=score.s_id and score.math>90;✓ 正確

這題的完整詳解,註冊就能看 · 每天 2 題免費

精選詳解