Oracle 基礎練習 25 題：SCOTT.EMP 表完整解題與函數應用詳解

測試環境建立

-- 標準 SCOTT 結構
CONN scott/tiger
-- 或任意結構建立 EMP/DEPT 表並插入標準測試資料

練習題與解答

1. 選擇部門 30 所有員工

SELECT * FROM emp WHERE deptno = 30;

2. 列出所有辦事員 (CLERK) 姓名、編號、部門編號

SELECT ename, empno, deptno FROM emp WHERE job = 'CLERK';

3. 找出傭金高於薪金的員工

SELECT * FROM emp WHERE comm > sal;

4. 找出傭金高於薪金 60% 的員工

SELECT * FROM emp WHERE comm > sal * 0.6;

5. 部門 10 經理 + 部門 20 辦事員

SELECT * FROM emp
WHERE (job = 'MANAGER' AND deptno = 10)
   OR (job = 'CLERK' AND deptno = 20);

6. 複合條件：部門10經理 + 部門20辦事員 + 非經理非辦事員且薪資>=2000

SELECT * FROM emp
WHERE (job = 'MANAGER' AND deptno = 10)
   OR (job = 'CLERK' AND deptno = 20)
   OR (job NOT IN ('MANAGER','CLERK') AND sal >= 2000);

7. 找出領傭金員工的不同職稱

SELECT DISTINCT job FROM emp WHERE comm > 0;

8. 找出無傭金或傭金<100的員工

SELECT * FROM emp WHERE comm IS NULL OR comm < 100;

9. 找出各月倒數第 3 天受僱的員工

SELECT * FROM emp
WHERE hiredate = LAST_DAY(hiredate) - 2;
-- LAST_DAY 回傳該月最後一天

10. 找出 12 年前 (144 個月) 受僱員工

SELECT * FROM emp
WHERE MONTHS_BETWEEN(SYSDATE, hiredate) >= 144;

11. 姓名首字大寫顯示

SELECT INITCAP(ename) FROM emp;

12. 顯示姓名長度恰好 5 字元的員工

SELECT ename FROM emp WHERE LENGTH(ename) = 5;

13. 顯示不含 'R' 的員工姓名

SELECT ename FROM emp WHERE ename NOT LIKE '%R%';

14. 顯示所有員工姓名前 3 字元

SELECT SUBSTR(ename, 1, 3) FROM emp;
-- SUBSTR(str, start, length)；start 從 1 開始

15. 以 'a' 取代所有 'A'

SELECT REPLACE(ename, 'A', 'a') FROM emp;

16. 滿 10 年服務年限員工姓名與受僱日

SELECT ename, hiredate FROM emp
WHERE MONTHS_BETWEEN(SYSDATE, hiredate) >= 120;

17. 員工明細按姓名排序

SELECT * FROM emp ORDER BY ename;

18. 姓名與受僱日，依資深度排序 (最老在前)

SELECT ename, hiredate FROM emp ORDER BY hiredate;

19. 姓名、職稱、薪資，職稱降序、薪資升序

SELECT ename, job, sal FROM emp ORDER BY job DESC, sal;

20. 姓名、入職年月，按月份排序、同月份按年份排序

SELECT ename, TO_CHAR(hiredate, 'YYYY-MM') AS yr_mo
FROM emp
ORDER BY TO_CHAR(hiredate, 'MM'), TO_CHAR(hiredate, 'YYYY');

21. 假設月薪 30 天，計算日薪 (捨去小數)

SELECT TRUNC(sal/30, 0) AS daily_sal FROM emp;
-- TRUNC 截斷，ROUND 四捨五入

22. 找出 2 月受聘員工 (任何年份)

SELECT * FROM emp WHERE TO_CHAR(hiredate, 'MM') = '02';

23. 顯示每位員工受僱至今天數

SELECT TRUNC(SYSDATE - hiredate) AS days_employed FROM emp;
-- 日期相減 = 天數 (含小數)，TRUNC 取整數

24. 找出姓名含 'A' 的員工

SELECT ename FROM emp WHERE ename LIKE '%A%';

25. 以 年-月-日 格式顯示服務年限 (近似)

SELECT TO_CHAR(
         TO_DATE('0001-01-01','YYYY-MM-DD') + (SYSDATE - hiredate) - 365 - 31 - 1,
         'YYYY-MM-DD') AS service_years
FROM emp;
-- 技巧：利用日期運算模擬年月日差，實際建議用 MONTHS_BETWEEN 計算年/月

核心函數速查表

函數	用途	範例
INITCAP	首字大寫	INITCAP('hello')='Hello'
LENGTH	字串長度	LENGTH('Oracle')=6
SUBSTR	子字串	SUBSTR('ABCDE',2,3)='BCD'
REPLACE	取代	REPLACE('JACK','J','B')='BACK'
LAST_DAY	月最後一天	LAST_DAY(SYSDATE)
MONTHS_BETWEEN	月份差	MONTHS_BETWEEN(d1,d2)
TO_CHAR(date,'fmt')	日期轉字串	TO_CHAR(SYSDATE,'YYYY-MM')
TRUNC(num,0)	截斷整數	TRUNC(123.45,0)=123

結語

這 25 題涵蓋 WHERE 過濾、字串/日期/數值函數、排序、條件邏輯等 SQL 核心技能。建議實際在 SCOTT 環境逐題執行，觀察執行計畫與結果集，將語法內化為肌肉記憶。