Oracle Scott 架構進階查詢：員工薪資等級與主管資訊關聯分析

題目需求

查詢每位員工的姓名、薪資、部門名稱、薪資等級，以及其主管的姓名、主管薪資、主管薪資等級。使用 Oracle 內建 Scott 架構下的 EMP、DEPT、SALGRADE 三張表。

資料表結構速覽

步驟式查詢建構

1. 先瞭解薪資等級表內容

SELECT * FROM salgrade;

2. 員工基本資料 + 部門 + 薪資等級

SELECT DISTINCT e.ename, e.sal, d.dname, g.grade AS e_grade
FROM emp e, dept d, salgrade g
WHERE e.deptno = d.deptno
  AND e.sal BETWEEN g.losal AND g.hisal;

3. 加入主管姓名 (自聯結 EMP 兩次)

SELECT e.ename, e.sal, d.dname, g.grade, s.ename AS mgr_name
FROM emp e, dept d, salgrade g, emp s
WHERE e.deptno = d.deptno
  AND e.sal BETWEEN g.losal AND g.hisal
  AND s.empno = e.mgr;

4. 完整版：主管薪資與主管等級 (需再 JOIN 一次 SALGRADE)

SELECT e.ename, e.sal, d.dname, g.grade AS e_grade,
       s.ename AS mgr_name, s.sal AS mgr_sal, g2.grade AS m_grade
FROM emp e, dept d, salgrade g, emp s, salgrade g2
WHERE e.deptno = d.deptno
  AND e.sal BETWEEN g.losal AND g.hisal
  AND s.empno = e.mgr
  AND s.sal BETWEEN g2.losal AND g2.hisal;

5. 進階：等級轉中文敘述 (使用 DECODE)

SELECT e.ename, e.sal, d.dname,
       DECODE(g.grade, 5, '第一等薪資', 4, '第二等薪資', 3, '第三等薪資', 2, '第四等薪資', 1, '第五等薪資') AS e_grade,
       s.ename AS mgr_name, s.sal AS mgr_sal,
       DECODE(g2.grade, 5, '第一等薪資', 4, '第二等薪資', 3, '第三等薪資', 2, '第四等薪資', 1, '第五等薪資') AS m_grade
FROM emp e, dept d, salgrade g, emp s, salgrade g2
WHERE e.deptno = d.deptno
  AND e.sal BETWEEN g.losal AND g.hisal
  AND s.empno = e.mgr
  AND s.sal BETWEEN g2.losal AND g2.hisal;

關鍵技術點解析

1. 自聯結：EMP 表出現兩次別名 e (員工) 與 s (主管)，透過 e.mgr = s.empno 關聯。
2. 非等值聯結：薪資落在 LOSAL ~ HISAL 區間，使用 BETWEEN ... AND ...。
3. 多表聯結：共 5 個表實例 (EMP×2, DEPT, SALGRADE×2)，Oracle 舊式逗號語法與 ANSI JOIN 皆可。
4. DECODE / CASE：將數值等級轉為業務可讀文字，提升報表親和力。

ANSI JOIN 寫法參考 (更易維護)

SELECT e.ename, e.sal, d.dname, g.grade AS e_grade,
       s.ename AS mgr_name, s.sal AS mgr_sal, g2.grade AS m_grade
FROM emp e
JOIN dept d ON e.deptno = d.deptno
JOIN salgrade g ON e.sal BETWEEN g.losal AND g.hisal
LEFT JOIN emp s ON e.mgr = s.empno          -- KING 無主管，用 LEFT JOIN 保留
LEFT JOIN salgrade g2 ON s.sal BETWEEN g2.losal AND g2.hisal;

常見陷阱提醒

• 忘記 SALGRADE 需 JOIN 兩次 (員工、主管各一次)。
• 使用 INNER JOIN 導致無主管員工 (如 KING) 被過濾掉，應改 LEFT JOIN。
• BETWEEN 邊界包含，確認 SALGRADE 區間無重疊缺口。