sql server 经典练习题分享二

26、查询存在有85分以上成绩的课程Cno.

SELECT DISTINCT cno FROM dbo.scores WHERE degree>85

27、查询出“计算机系“教师所教课程的成绩表。

SELECT tname,prof,cname,depart,degree FROM dbo.scores 
  INNER JOIN dbo.courses ON dbo.courses.cno = dbo.scores.cno
  INNER JOIN dbo.teachers ON dbo.teachers.tno = dbo.courses.tno
      WHERE depart='计算机系'

28、查询“计算机系”与“电子工程系“不同职称的教师的Tname和Prof。

SELECT tname,prof FROM dbo.teachers 
    WHERE depart IN('计算机系','电子工程系')

29、查询选修编号为“3-105“课程且成绩至少高于任意选修编号为“3-245”的同学的成绩的Cno、Sno和Degree,并按Degree从高到低次序排序。

SELECT * from dbo.scores 
  WHERE cno='3-105' 
AND 
  degree>(SELECT Min(degree) FROM dbo.scores WHERE cno='3-245') 
      ORDER BY dbo.scores.degree DESC 

30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的Cno、Sno和Degree.

SELECT * from dbo.scores 
  WHERE cno='3-105' 
    AND 
  degree>(SELECT