T_Student(SID, SName, Sage, SSex, SDept)
T_Course(CID, CName, TID)
T_Score(SCID, SID, CID, Grade)
T_Teacher(TID, TName)
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;
SELECT SID, AVG(Grade) FROM T_Score GROUP BY SID HAVING AVG(Grade) > 60;
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;
SELECT COUNT(DISTINCT TName) FROM T_Teacher WHERE TName LIKE '李%';
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='葉平');
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');
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='葉平'));
SELECT SID, SName FROM T_Student
WHERE SID NOT IN (SELECT SID FROM T_Score WHERE Grade >= 60);
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);
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;
SELECT CID, MAX(Grade) AS 最高分, MIN(Grade) AS 最低分 FROM T_Score GROUP BY CID;
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 分步構建 → 聚合/窗口函數 → 條件過濾 → 輸出。