高考申論題
107年
[資訊處理] 資料庫應用
第 ⑴ 題
📖 題組：
三、給予如下兩個關聯綱要（Relation Schemas）及其內容，EMPLOYEE 關聯記錄員工編號、薪水及所在部門，DEPARTMENT 關聯記錄部門編號及名稱，其中 EMPLOYEE 的外來鍵（Foreign Key）‟dno”參考到DEPARTMENT 的主鍵（Primary Key）‟dnum”，執行下列⑴與⑵的 SQL查詢後的結果分別為何？其意義分別為何？（20 分） EMPLOYEE ssn salary dno 1 30000 5 2 40000 5 3 38000 5 4 25000 5 5 25000 4 6 43000 4 7 25000 4 8 55000 1 9 45000 1 10 30000 1 11 50000 2 12 25000 2 13 30000 3 DEPARTMENT dnum dname 1 R&D 2 Administration 3 Accounting 4 Finances 5 Personnel ⑴SELECT dno, COUNT(*) FROM DEPARTMENT, EMPLOYEE WHERE dnum = dno AND salary > 25000 GROUP BY dno HAVING COUNT(*) > 2 ⑵SELECT dno, COUNT(*) FROM DEPARTMENT, EMPLOYEE WHERE dnum = dno AND salary > 25000 AND dno IN (SELECT dno FROM EMPLOYEE GROUP BY dno HAVING COUNT(*) > 2) GROUP BY dno
三、給予如下兩個關聯綱要（Relation Schemas）及其內容，EMPLOYEE 關聯記錄員工編號、薪水及所在部門，DEPARTMENT 關聯記錄部門編號及名稱，其中 EMPLOYEE 的外來鍵（Foreign Key）‟dno”參考到DEPARTMENT 的主鍵（Primary Key）‟dnum”，執行下列⑴與⑵的 SQL查詢後的結果分別為何？其意義分別為何？（20 分） EMPLOYEE ssn salary dno 1 30000 5 2 40000 5 3 38000 5 4 25000 5 5 25000 4 6 43000 4 7 25000 4 8 55000 1 9 45000 1 10 30000 1 11 50000 2 12 25000 2 13 30000 3 DEPARTMENT dnum dname 1 R&D 2 Administration 3 Accounting 4 Finances 5 Personnel ⑴SELECT dno, COUNT(*) FROM DEPARTMENT, EMPLOYEE WHERE dnum = dno AND salary > 25000 GROUP BY dno HAVING COUNT(*) > 2 ⑵SELECT dno, COUNT(*) FROM DEPARTMENT, EMPLOYEE WHERE dnum = dno AND salary > 25000 AND dno IN (SELECT dno FROM EMPLOYEE GROUP BY dno HAVING COUNT(*) > 2) GROUP BY dno
⑴SELECT dno, COUNT(*)
FROM DEPARTMENT, EMPLOYEE
WHERE dnum = dno AND salary > 25000
GROUP BY dno
HAVING COUNT(*) > 2
📝 此題為申論題
思路引導 VIP
解題時應遵循 SQL 的標準執行順序：先處理 WHERE 條件過濾出薪水大於 25000 的資料，接著進行 GROUP BY 分組，最後透過 HAVING 篩選出分組後筆數大於 2 的結果。
🤖
AI 詳解
AI 專屬家教
【解題思路】按照 SQL 邏輯執行順序（FROM → WHERE → GROUP BY → HAVING → SELECT）逐步推導資料過濾與分組結果。 【詳解】 一、查詢意義解析
▼ 還有更多解析內容