萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MS SQL 資料庫

經典 50 道 SQL 查詢練習題詳解(學生-課程-成績模型)

資料模型定義

Student(S#, Sname, Sage, Ssex)      -- 學生表
Course(C#, Cname, T#)                -- 課程表
SC(S#, C#, score)                    -- 成績表
Teacher(T#, Tname)                   -- 教師表

題目與標準解法(節選重點題型)

1. 子查詢比較:查詢「001」課程比「002」成績高的學生

SELECT a.S# 
FROM (SELECT S#, score FROM SC WHERE C#='001') a
JOIN (SELECT S#, score FROM SC WHERE C#='002') b ON a.S# = b.S#
WHERE a.score > b.score;

2. 分組聚合 + HAVING:平均分 > 60 的學生

SELECT S#, AVG(score) AS avg_score
FROM SC
GROUP BY S#
HAVING AVG(score) > 60;

3. 外連結統計:所有學生的選課數與總分(含未選課者)

SELECT Student.S#, Student.Sname, 
       COUNT(SC.C#) AS course_cnt, 
       SUM(score) AS total_score
FROM Student 
LEFT JOIN SC ON Student.S# = SC.S#
GROUP BY Student.S#, Student.Sname;

4. 模糊查詢:姓「李」的老師人數

SELECT COUNT(DISTINCT Tname) FROM Teacher WHERE Tname LIKE N'李%';

5. NOT IN / NOT EXISTS:未修「葉平」老師課程的學生

SELECT S#, Sname FROM Student
WHERE S# NOT IN (
    SELECT DISTINCT SC.S# 
    FROM SC JOIN Course ON SC.C# = Course.C#
    JOIN Teacher ON Course.T# = Teacher.T#
    WHERE Teacher.Tname = N'葉平'
);

6. 多重 EXISTS:同時修「001」且「002」的學生

SELECT Student.S#, Student.Sname
FROM Student, SC
WHERE Student.S# = SC.S# 
  AND SC.C# = '001'
  AND EXISTS (SELECT 1 FROM SC SC2 WHERE SC2.S# = SC.S# AND SC2.C# = '002');

7. 關係除法:修習「葉平」老師所有課程的學生

SELECT S#, Sname FROM Student
WHERE S# IN (
    SELECT S# FROM SC 
    JOIN Course ON SC.C# = Course.C#
    JOIN Teacher ON Course.T# = Teacher.T#
    WHERE Teacher.Tname = N'葉平'
    GROUP BY S#
    HAVING COUNT(SC.C#) = (
        SELECT COUNT(C#) FROM Course 
        JOIN Teacher ON Course.T# = Teacher.T#
        WHERE Teacher.Tname = N'葉平'
    )
);

8. 行轉列(交叉表):按學生顯示三門指定課程成績

SELECT S# AS 學生ID,
       MAX(CASE WHEN C#='004' THEN score END) AS 數據庫,
       MAX(CASE WHEN C#='001' THEN score END) AS 企業管理,
       MAX(CASE WHEN C#='006' THEN score END) AS 英語,
       COUNT(*) AS 有效課程數,
       AVG(score) AS 平均成績
FROM SC
GROUP BY S#
ORDER BY AVG(score) DESC;

9. 窗口函數(2012+):各科成績前 3-6 名

WITH Ranked AS (
    SELECT S#, C#, score,
           ROW_NUMBER() OVER (PARTITION BY C# ORDER BY score DESC) AS rn
    FROM SC
    WHERE C# IN ('001','002','003','004')
)
SELECT S#, C#, score
FROM Ranked
WHERE rn BETWEEN 3 AND 6
ORDER BY C#, rn;

10. 動態插入:未修「002」的學生補錄該科平均分

INSERT INTO SC (S#, C#, score)
SELECT S#, '002', (SELECT AVG(score) FROM SC WHERE C#='002')
FROM Student
WHERE S# NOT IN (SELECT S# FROM SC WHERE C#='002');

核心技巧總結

  • 子查詢 vs JOIN:標量子查詢適合 SELECT 列表,關聯子查詢/EXISTS 適合 WHERE 條件,大數據量優先 JOIN。
  • 分組去重:COUNT(DISTINCT ...)、GROUP BY ... HAVING COUNT(*) = ... 解決「所有/任意」邏輯。
  • 條件聚合:SUM(CASE WHEN ... THEN 1 ELSE 0 END) 實現單掃描多指標統計。
  • 行轉列:MAX(CASE...) 或 PIVOT (SQL Server 2005+)。
  • 分頁/Top-N:ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) 為標準解法。

練習建議

在 tempdb 建立上述四張表並填入測試資料,逐題實作並對比執行計畫,體會邏輯等價但效能不同的寫法差異。

copyright © 萬盛學電腦網 all rights reserved