資料庫表查詢操作實戰演練（二）：單表、多表、嵌套、集合查詢全覆蓋

實驗目的

掌握 SELECT 各類查詢操作：單表、多表連接、嵌套查詢、集合查詢，鞏固關聯運算基礎。

一、單表查詢

1. 簡單查詢

SELECT * FROM teacher;
SELECT tno, tname FROM teacher;

-- 列別名
SELECT tno AS 工號, tname AS 姓名, temail AS 電子郵箱 FROM teacher;

-- TOP 子句
SELECT TOP 2 * FROM teacher;
SELECT TOP 67 PERCENT * FROM teacher;

-- DISTINCT 去重
SELECT DISTINCT tposition FROM teacher;

2. 計算欄位

SELECT tno, tname, tsalary * 0.95 FROM teacher;
SELECT tno AS 工號, tname AS 姓名, tsalary * 0.95 AS 預發工資 FROM teacher;

3. ORDER BY 排序

SELECT tno, tname FROM teacher WHERE tsalary > 2800 ORDER BY tsalary ASC;

4. 條件查詢

• 關係運算子：WHERE tamount > 288 ORDER BY tamount DESC
• BETWEEN AND：WHERE tamount BETWEEN 144 AND 288
• IN：WHERE tposition IN ('教授','副教授')
• LIKE：WHERE tname LIKE '王%'、WHERE tname LIKE '_[莉，軒]%'

二、多表查詢

1. ANSI-92 內連接 vs 舊式 WHERE 連接

-- ANSI 推薦
SELECT student.sno, sname, cno, grade 
FROM student INNER JOIN SC ON student.sno = SC.sno;

-- 三表連接
SELECT student.sno, student.sname, sc.cno, course.cname, sc.grade
FROM student, sc, course
WHERE student.sno = sc.sno AND sc.cno = course.cno;

2. UNION 集合查詢

SELECT sno AS 學號或工號, sname AS 姓名 FROM student
UNION
SELECT tno, tname FROM teacher;

3. GROUP BY 分組彙總

-- 無 HAVING
SELECT '學號'=sno, '總成績'=SUM(Grade) FROM SC GROUP BY Sno;

-- 有 HAVING
SELECT '學號'=sno, '總成績'=SUM(Grade) FROM SC GROUP BY Sno HAVING SUM(Grade) > 160;

4. COMPUTE / COMPUTE BY

-- COMPUTE：總計
SELECT '學號'=sno, '成績'=Grade FROM SC ORDER BY sno COMPUTE SUM(Grade);

-- COMPUTE BY：分組小計
SELECT '學號'=sno, '成績'=Grade FROM SC ORDER BY Sno COMPUTE SUM(Grade) BY sno;

差異：COMPUTE 只顯示總計；COMPUTE BY 顯示每組小計 + 總計。

三、嵌套查詢

1. IN / NOT IN

-- j10011 班男生成績
SELECT SC.sno, SC.cno, SC.grade FROM SC 
WHERE sno IN (SELECT sno FROM student WHERE Sclass='j10011' AND Ssex='男');

-- 與教師不同名學生
SELECT sno 學號, sname 姓名 FROM student 
WHERE sname NOT IN (SELECT tname FROM teacher);

2. EXISTS / NOT EXISTS

SELECT SC.sno, SC.cno, SC.grade FROM SC 
WHERE EXISTS (
    SELECT * FROM student 
    WHERE SC.sno = student.sno AND student.sclass = 'j10011'
);

核心知識點總結

練習建議

1. 在 SSMS 逐題執行，觀察結果集
2. 對比 COMPUTE vs COMPUTE BY 輸出差異
3. 將 IN 改寫為 EXISTS，體會語意等價
4. 嘗試用 JOIN 替換子查詢，比較執行計畫

本實驗涵蓋查詢操作核心語法，是進階 SQL 開發的紮實基礎。