您现在的位置：程式碼之家 >> 數據庫知識 > >> MySQL 資料庫 >> MySQL 綜合教學

SQL 練習 50 題經典案例：學生-課程-成績-教師四表聯查全解析

編輯：MySQL 綜合教學

SQL 練習 50 題 — 經典四表模型全解析

數據模型

Student(Sid, Sname, Sage, Ssex)      -- 學生表
Course(Cid, Cname, Tid)              -- 課程表
SC(Sid, Cid, score)                  -- 成績表
Teacher(Tid, Tname)                  -- 教師表

精選高頻考點題解

1. 子查詢 + 別名：查詢課程 1 成績高於課程 2 的學生

SELECT a.Sid
FROM (SELECT Sid, score FROM SC WHERE Cid = '01') a
JOIN (SELECT Sid, score FROM SC WHERE Cid = '02') b
  ON a.Sid = b.Sid
WHERE a.score > b.score;

2. GROUP BY + HAVING：平均分 > 60 的學號與平均分

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

3. LEFT JOIN + 聚合：所有學生的學號、姓名、選課數、總分

SELECT s.Sid, s.Sname, COUNT(sc.Cid) AS course_cnt, SUM(sc.score) AS total_score
FROM Student s
LEFT JOIN SC sc ON s.Sid = sc.Sid
GROUP BY s.Sid, s.Sname;

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

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

5. 多表 NOT IN / NOT EXISTS：沒學過「葉平」老師課的學生

SELECT Sid, Sname
FROM Student
WHERE Sid NOT IN (
  SELECT DISTINCT sc.Sid
  FROM SC sc
  JOIN Course c ON sc.Cid = c.Cid
  JOIN Teacher t ON c.Tid = t.Tid
  WHERE t.Tname = '葉平'
);

6. 雙重 EXISTS：同時學過「C++」和「English」的學生

SELECT s.Sid, s.Sname
FROM Student s
WHERE EXISTS (SELECT 1 FROM SC sc JOIN Course c ON sc.Cid=c.Cid WHERE sc.Sid=s.Sid AND c.Cname='C++')
  AND EXISTS (SELECT 1 FROM SC sc JOIN Course c ON sc.Cid=c.Cid WHERE sc.Sid=s.Sid AND c.Cname='English');

7. 分組 HAVING COUNT = 總課程數：學過「楊巍巍」老師所有課的學生

SELECT s.Sid, s.Sname
FROM Student s
JOIN SC sc ON s.Sid = sc.Sid
JOIN Course c ON sc.Cid = c.Cid
JOIN Teacher t ON c.Tid = t.Tid
WHERE t.Tname = '楊巍巍'
GROUP BY s.Sid, s.Sname
HAVING COUNT(*) = (
  SELECT COUNT(*) FROM Course c2 JOIN Teacher t2 ON c2.Tid=t2.Tid WHERE t2.Tname='楊巍巍'
);

8. 自關聯比較：課程 1 成績低於課程 2 的學生

SELECT a.Sid, a.Sname
FROM (
  SELECT s.Sid, s.Sname, sc.score
  FROM Student s JOIN SC sc ON s.Sid=sc.Sid WHERE sc.Cid='01'
) a
JOIN (
  SELECT s.Sid, sc.score
  FROM Student s JOIN SC sc ON s.Sid=sc.Sid WHERE sc.Cid='02'
) b ON a.Sid = b.Sid
WHERE a.score < b.score;

9. 反向 NOT IN：所有課程成績 < 60 的學生

SELECT Sid, Sname
FROM Student
WHERE Sid NOT IN (
  SELECT DISTINCT Sid FROM SC WHERE score >= 60
);

10. HAVING COUNT < 總課程數：沒學全所有課的學生

SELECT s.Sid, s.Sname
FROM Student s
JOIN SC sc ON s.Sid = sc.Sid
GROUP BY s.Sid, s.Sname
HAVING COUNT(*) < (SELECT COUNT(*) FROM Course);

11-12. IN 子查詢：至少有一門課與學號 1 相同 / 學過學號 1 所有課的其他學生

-- 11
SELECT DISTINCT s.Sid, s.Sname
FROM Student s
JOIN SC sc ON s.Sid = sc.Sid
WHERE sc.Cid IN (SELECT Cid FROM SC WHERE Sid = '1');

-- 12（關係除法）
SELECT s.Sid, s.Sname
FROM Student s
WHERE NOT EXISTS (
  SELECT Cid FROM SC WHERE Sid = '1'
  EXCEPT
  SELECT Cid FROM SC WHERE Sid = s.Sid
);

13. 更新子查詢：將「楊巍巍」老師課程成績改為該課平均分

UPDATE SC
SET score = (
  SELECT AVG(score) FROM SC sc2
  JOIN Course c ON sc2.Cid = c.Cid
  JOIN Teacher t ON c.Tid = t.Tid
  WHERE t.Tname = '楊巍巍' AND sc2.Cid = SC.Cid
)
WHERE Cid IN (
  SELECT c.Cid FROM Course c JOIN Teacher t ON c.Tid=t.Tid WHERE t.Tname='楊巍巍'
);

14. 分組計數比較：與學號 6 選課完全相同的學生

SELECT Sid
FROM SC
WHERE Cid IN (SELECT Cid FROM SC WHERE Sid = '6')
GROUP BY Sid
HAVING COUNT(*) = (SELECT COUNT(*) FROM SC WHERE Sid = '6');

15. 多表 DELETE：刪除「李子」老師課程的成績記錄

DELETE FROM SC
WHERE Cid IN (
  SELECT c.Cid FROM Course c JOIN Teacher t ON c.Tid=t.Tid WHERE t.Tname='李子'
);

16. INSERT ... SELECT：為沒修課程 2 的學生插入該課平均分記錄

INSERT INTO SC (Sid, Cid, score)
SELECT s.Sid, '2', (SELECT AVG(score) FROM SC WHERE Cid = '2')
FROM Student s
WHERE s.Sid NOT IN (SELECT Sid FROM SC WHERE Cid = '2');

17-18. 橫向轉行 / 聚合：按平均分排序顯示三門課成績、各科最高最低分

-- 17 略（需 CASE WHEN 橫表）
-- 18
SELECT Cid, MAX(score) AS max_score, MIN(score) AS min_score
FROM SC GROUP BY Cid;

19-20. 多重排序：平均分升序 + 課程號降序、不及格課程按課程號降序

SELECT Cid, AVG(score) FROM SC GROUP BY Cid ORDER BY AVG(score), Cid DESC;
SELECT Cid, Sid FROM SC WHERE score < 60 ORDER BY Cid DESC;

26-27. 統計：每門課選修人數、只選一門課的學生

SELECT Cid, COUNT(*) FROM SC GROUP BY Cid;
SELECT s.Sid, s.Sname FROM Student s JOIN SC sc ON s.Sid=sc.Sid GROUP BY s.Sid, s.Sname HAVING COUNT(*) = 1;

32. 平均分升序、課程號降序

SELECT Cid, AVG(score) FROM SC GROUP BY Cid ORDER BY AVG(score), Cid DESC;

37-38. 簡單條件查詢

SELECT Cid, Sid FROM SC WHERE score < 60 ORDER BY Cid DESC;
SELECT s.Sid, s.Sname FROM Student s JOIN SC sc ON s.Sid=sc.Sid WHERE sc.Cid='1' AND sc.score > 60;

40. 子查詢 MAX：葉平老師課程中分最高的學生

SELECT s.Sname, sc.score
FROM Student s
JOIN SC sc ON s.Sid = sc.Sid
JOIN Course c ON sc.Cid = c.Cid
JOIN Teacher t ON c.Tid = t.Tid
WHERE t.Tname = '李子'
  AND sc.score = (
    SELECT MAX(score) FROM SC sc2 JOIN Course c2 ON sc2.Cid=c2.Cid WHERE c2.Tid = (SELECT Tid FROM Teacher WHERE Tname='李子')
  );

41. 選修人數統計

SELECT sc.Cid, COUNT(*) FROM SC sc JOIN Student s ON sc.Sid=s.Sid GROUP BY sc.Cid;

45. ROWNUM 分頁：成績第 2~4 名

SELECT * FROM (
  SELECT ROWNUM rn, t.score FROM (
    SELECT score FROM SC ORDER BY score DESC
  ) t
) WHERE rn BETWEEN 2 AND 4;

47-50. 綜合練習

-- 47 沒學過楊巍巍課的學生
SELECT DISTINCT s.Sid FROM Student s
WHERE s.Sid NOT IN (
  SELECT sc.Sid FROM SC sc JOIN Course c ON sc.Cid=c.Cid JOIN Teacher t ON c.Tid=t.Tid WHERE t.Tname='楊巍巍'
);

-- 49 Java 課 < 90 分學號降序
SELECT sc.Sid FROM SC sc JOIN Course c ON sc.Cid=c.Cid WHERE c.Cname='Java' AND sc.score < 90 ORDER BY sc.score DESC;

-- 50 刪除學號 1 的課程 1 成績
DELETE FROM SC WHERE Sid = '1' AND Cid = '1';