地特三等申論題
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 AND
dno IN (SELECT dno
FROM EMPLOYEE
GROUP BY dno
HAVING COUNT(*) > 2)
GROUP BY dno
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
📝 此題為申論題
思路引導 VIP
先拆解內層的子查詢（Subquery）找出符合特定條件的部門編號（dno）集合，再將結果代入外層的主查詢，逐一過濾 WHERE 條件（如薪水需大於 25000）後進行分組統計。
🤖
AI 詳解
AI 專屬家教
【解題思路】先拆解內層子查詢（Subquery）找出符合條件的部門編號，再將結果代入外層主查詢，結合其他 WHERE 條件後進行分組計數。 【詳解】 已知：
▼ 還有更多解析內容