SQL 練習題精選：學生-課程-成績經典 Schema 查詢與 DML 實戰解析

經典的「學生-課程-成績」三表結構是 SQL 入門與面試的標準練習場景。本文提供完整建表腳本、測試資料，並針對 10 題涵蓋聚合、子查詢、條件邏輯、日期處理、字串格式化、DML 操作的經典題目，給出標準解法與進階優化寫法。

Schema 定義與測試資料

-- 建立資料表
CREATE TABLE Student (
    Sno   CHAR(10) PRIMARY KEY,     -- 學號
    Sname NVARCHAR(20) NOT NULL,    -- 姓名
    Bdate DATE,                     -- 出生日期
    Grade INT                       -- 年級/班級
);

CREATE TABLE Course (
    Cno   CHAR(4) PRIMARY KEY,      -- 課程號
    Cname NVARCHAR(20) NOT NULL     -- 課程名稱
);

CREATE TABLE Score (
    Sno   CHAR(10) NOT NULL,
    Cno   CHAR(4) NOT NULL,
    Score DECIMAL(5,2),             -- 成績，NULL 表未修/缺考
    PRIMARY KEY (Sno, Cno),
    FOREIGN KEY (Sno) REFERENCES Student(Sno),
    FOREIGN KEY (Cno) REFERENCES Course(Cno)
);

-- 測試資料
INSERT INTO Student VALUES 
('S001', N'張三', '2000-01-15', 2007),
('S002', N'李四', '2000-05-22', 2007),
('S003', N'王五', '2001-03-10', 2008),
('S004', N'趙六', '2000-11-30', 2007);

INSERT INTO Course VALUES 
('C001', N'數學'), ('C002', N'英文'), ('C003', N'大學語文');

INSERT INTO Score VALUES 
('S001', 'C001', 85), ('S001', 'C002', 92), ('S001', 'C003', 78),
('S002', 'C001', 70), ('S002', 'C002', 65), ('S002', 'C003', 55),
('S003', 'C001', 95), ('S003', 'C002', 88), -- 缺考 C003
('S004', 'C001', 60), ('S004', 'C002', NULL), ('S004', 'C003', 70);

題目與標準解法

1. 統計各班級人數

SELECT Grade, COUNT(*) AS StudentCount
FROM Student
GROUP BY Grade
ORDER BY Grade;

2. 2007 級各學生平均成績 (無成績視為 0)

-- 解法 1: 相關子查詢
SELECT S.Sname, 
       ISNULL((SELECT AVG(ISNULL(Score, 0)) FROM Score WHERE Sno = S.Sno), 0) AS AvgScore
FROM Student S
WHERE S.Grade = 2007;

-- 解法 2: LEFT JOIN + 聚合 (效能較佳)
SELECT S.Sname, AVG(ISNULL(SC.Score, 0)) AS AvgScore
FROM Student S
LEFT JOIN Score SC ON S.Sno = SC.Sno
WHERE S.Grade = 2007
GROUP BY S.Sname;

3. 2007 級各科平均/最高/最低成績 (保留 2 位小數)

SELECT 
    C.Cno, C.Cname,
    ROUND(AVG(ISNULL(SC.Score, 0)), 2) AS AvgScore,
    MAX(ISNULL(SC.Score, 0)) AS MaxScore,
    MIN(ISNULL(SC.Score, 0)) AS MinScore
FROM Student S
JOIN Score SC ON S.Sno = SC.Sno
JOIN Course C ON SC.Cno = C.Cno
WHERE S.Grade = 2007
GROUP BY C.Cno, C.Cname;

4. 2007 級數學成績加 5 分

UPDATE SC
SET Score = ISNULL(Score, 0) + 5
FROM Score SC
JOIN Student S ON SC.Sno = S.Sno
JOIN Course C ON SC.Cno = C.Cno
WHERE S.Grade = 2007 AND C.Cname = N'數學';

5. 各學生平均成績等級劃分 (≥90 優秀, 60-89 及格, <60 不及格)

SELECT 
    S.Sno, S.Sname,
    AVG(ISNULL(SC.Score, 0)) AS AvgScore,
    CASE 
        WHEN AVG(ISNULL(SC.Score, 0)) >= 90 THEN N'優秀'
        WHEN AVG(ISNULL(SC.Score, 0)) >= 60 THEN N'及格'
        ELSE N'不及格'
    END AS GradeLevel
FROM Student S
LEFT JOIN Score SC ON S.Sno = SC.Sno
GROUP BY S.Sno, S.Sname
ORDER BY AvgScore DESC;

6. 統計各月出生人數

SELECT 
    MONTH(Bdate) AS BirthMonth,
    COUNT(*) AS StudentCount
FROM Student
WHERE Bdate IS NOT NULL
GROUP BY MONTH(Bdate)
ORDER BY BirthMonth;

7. 各科及格率與平均成績 (及格線 60 分)

SELECT 
    C.Cno, C.Cname,
    ROUND(AVG(ISNULL(SC.Score, 0)), 2) AS AvgScore,
    COUNT(SC.Score) AS ExamCount,           -- 實際參考人數
    ROUND(
        SUM(CASE WHEN ISNULL(SC.Score, 0) >= 60 THEN 1.0 ELSE 0 END) 
        * 100.0 / COUNT(SC.Score), 2
    ) AS PassRatePercent
FROM Course C
LEFT JOIN Score SC ON C.Cno = SC.Cno
GROUP BY C.Cno, C.Cname;

8. 刪除「張三」的「大學語文」成績

DELETE FROM Score
WHERE Sno = (SELECT Sno FROM Student WHERE Sname = N'張三')
  AND Cno = (SELECT Cno FROM Course WHERE Cname = N'大學語文');

9. 將課程名含「數學」改為「高等數學」

UPDATE Course
SET Cname = N'高等數學'
WHERE Cname LIKE N'%數學%';

10. 學號格式化：補齊前綴 'S' 與前導零至 12 碼

-- 查詢格式化
SELECT 
    N'S' + RIGHT(REPLICATE('0', 11) + Sno, 11) AS FormattedSno
FROM Student;

-- 更新資料 (謹慎！需確認無外鍵或已級聯)
UPDATE Student SET Sno = N'S' + RIGHT(REPLICATE('0', 11) + Sno, 11);
UPDATE Score   SET Sno = N'S' + RIGHT(REPLICATE('0', 11) + Sno, 11);

進階補充題

補充 1：四隊循環賽程產生 (兩兩配對，不重複)

SELECT A.TeamName AS Home, B.TeamName AS Away
FROM Team A
JOIN Team B ON A.TeamName < B.TeamName;  -- 避免重複與自我對戰

補充 2：交易控制

BEGIN TRANSACTION;
    UPDATE Score SET Score = 100 WHERE Sno = 'S001' AND Cno = 'C001';
    -- 驗證
    SELECT * FROM Score WHERE Sno = 'S001';
    -- COMMIT TRANSACTION;  -- 確認無誤提交
    -- ROLLBACK TRANSACTION; -- 有誤回滾

關鍵知識點總結

遷移至其他資料庫的語法差異

掌握此 Schema 的核心查詢模式 (多表 JOIN、聚合分組、條件邏輯、空值處理、子查詢、DML)，即能應對 80% 以上的業務報表與面試題目。建議在實際資料庫建立此結構親自執行驗證，體會執行計畫與結果差異。