地特三等申論題
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
FROM DEPARTMENT, EMPLOYEE
WHERE dnum = dno AND salary > 25000
GROUP BY dno
HAVING COUNT(*) > 2
📝 此題為申論題
思路引導 VIP
解此類 SQL 題需嚴格遵循資料庫引擎處理子句的執行順序：FROM → WHERE → GROUP BY → HAVING → SELECT。請先在腦中依 WHERE 條件篩選出薪水大於 25000 的員工，再按 dno (部門) 分組計數，最後用 HAVING 挑選出該計數大於 2 的部門即可得出結果。
🤖
AI 詳解
AI 專屬家教
【解題關鍵】依序執行 SQL 子句：WHERE 篩選單筆資料、GROUP BY 進行分組計數、HAVING 篩選分組後的結果。 【解答】 計算：Step 1→2→3 逐步推導
▼ 還有更多解析內容