萬盛學電腦網

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

SQL 進階查詢 21 題實戰:學生-課程-成績-教師經典模型

模型定義

T_Student(SID, SName, Sage, SSex, SDept)
T_Course(CID, CName, TID)
T_Score(SCID, SID, CID, Grade)
T_Teacher(TID, TName)

精選高頻題目與標準解法

  1. 課程 001 成績高於 002 的學生
    SELECT T1.SID
        FROM (SELECT SID, Grade FROM T_Score WHERE CID='001') T1
        JOIN (SELECT SID, Grade FROM T_Score WHERE CID='002') T2 ON T1.SID=T2.SID
        WHERE T1.Grade > T2.Grade;
  2. 平均分 > 60 的學號與平均分
    SELECT SID, AVG(Grade) FROM T_Score GROUP BY SID HAVING AVG(Grade) > 60;
  3. 所有同學學號、姓名、選課數、總分
    SELECT S.SID, S.SName, COUNT(SC.CID), SUM(SC.Grade)
        FROM T_Student S LEFT JOIN T_Score SC ON S.SID=SC.SID
        GROUP BY S.SID, S.SName;
  4. 姓「李」的老師人數
    SELECT COUNT(DISTINCT TName) FROM T_Teacher WHERE TName LIKE '李%';
  5. 沒學過「葉平」老師課的同學
    SELECT SID, SName FROM T_Student
        WHERE SID NOT IN (
            SELECT DISTINCT SC.SID FROM T_Score SC
            JOIN T_Course C ON SC.CID=C.CID
            JOIN T_Teacher T ON C.TID=T.TID
            WHERE T.TName='葉平');
  6. 同時學過 001 和 002 的同學
    SELECT S.SID, S.SName FROM T_Student S
        JOIN T_Score SC1 ON S.SID=SC1.SID AND SC1.CID='001'
        WHERE EXISTS (SELECT 1 FROM T_Score SC2 WHERE SC2.SID=S.SID AND SC2.CID='002');
  7. 學過「葉平」所有課的同學(關係除法)
    SELECT SID, SName FROM T_Student
        WHERE SID IN (
            SELECT SC.SID FROM T_Score SC
            JOIN T_Course C ON SC.CID=C.CID
            JOIN T_Teacher T ON C.TID=T.TID
            WHERE T.TName='葉平'
            GROUP BY SC.SID
            HAVING COUNT(SC.CID) = (SELECT COUNT(*) FROM T_Course C JOIN T_Teacher T ON C.TID=T.TID WHERE T.TName='葉平'));
  8. 全科不及格(所有課程 < 60)的同學
    SELECT SID, SName FROM T_Student
        WHERE SID NOT IN (SELECT SID FROM T_Score WHERE Grade >= 60);
  9. 未學全所有課的同學
    SELECT S.SID, S.SName FROM T_Student S
        JOIN T_Score SC ON S.SID=SC.SID
        GROUP BY S.SID, S.SName
        HAVING COUNT(SC.CID) < (SELECT COUNT(*) FROM T_Course);
  10. 行轉列:三門課成績橫向展示 + 統計
    SELECT SID AS 學生ID,
        (SELECT Grade FROM T_Score WHERE SID=T.SID AND CID='004') AS 數據庫,
        (SELECT Grade FROM T_Score WHERE SID=T.SID AND CID='001') AS 企業管理,
        (SELECT Grade FROM T_Score WHERE SID=T.SID AND CID='006') AS 英語,
        COUNT(*) AS 有效課程數, AVG(Grade) AS 平均分
        FROM T_Score T GROUP BY SID ORDER BY AVG(Grade) DESC;
  11. 各科最高/最低分
    SELECT CID, MAX(Grade) AS 最高分, MIN(Grade) AS 最低分 FROM T_Score GROUP BY CID;
  12. 課程平均分升序 + 及格率降序
    SELECT CID, AVG(Grade) AS 平均分,
        100.0 * SUM(CASE WHEN Grade>=60 THEN 1 ELSE 0 END)/COUNT(*) AS 及格率
        FROM T_Score GROUP BY CID ORDER BY 及格率 DESC, 平均分 ASC;

其餘題目(13-21)涉及更新、插入、刪除、複雜條件分組,遵循相同模式:CTE 分步構建 → 聚合/窗口函數 → 條件過濾 → 輸出。

copyright © 萬盛學電腦網 all rights reserved