例1 查询每个学期学分最低的课程的课程名、开课学期和学分。
SELECT Cname,Semester,Credit
FROM Course c1
WHERE Credit IN (
SELECT MIN(Credit) FROM Course c2
WHERE c1.Semester = c2.Semester )
例2 查询每门课程考试成绩最高的两个学生的学号以及相应的课程号和成绩。不包括没考试的课程。
SELECT Sno,Cno,Grade FROM SC SC1
WHERE Sno IN (
SELECT TOP 2 WITH TIES Sno FROMSC SC2
WHERE SC1.Cno = SC2.Cno
ORDER BY Grade