Student(S#, Sname, Sage, Ssex) -- 學生表
Course(C#, Cname, T#) -- 課程表
SC(S#, C#, score) -- 成績表
Teacher(T#, Tname) -- 教師表
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;
SELECT S#, AVG(score) AS avg_score
FROM SC
GROUP BY S#
HAVING AVG(score) > 60;
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;
SELECT COUNT(DISTINCT Tname) FROM Teacher WHERE Tname LIKE N'李%';
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'葉平'
);
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');
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'葉平'
)
);
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;
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;
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');
COUNT(DISTINCT ...)、GROUP BY ... HAVING COUNT(*) = ... 解決「所有/任意」邏輯。SUM(CASE WHEN ... THEN 1 ELSE 0 END) 實現單掃描多指標統計。MAX(CASE...) 或 PIVOT (SQL Server 2005+)。ROW_NUMBER() OVER (PARTITION BY ... ORDER BY ...) 為標準解法。在 tempdb 建立上述四張表並填入測試資料,逐題實作並對比執行計畫,體會邏輯等價但效能不同的寫法差異。