SQL语句练习及答案

student.no=score.no inner join course on score.cno=course.cno inner join Teacher on course.tno=Teacher.no where teacher.depart='计算机系'

24. 列出所有可能的“计算机系”与“电子工程系”不同职称的教师配对信息，要求输出每

个老师的姓名（name）和（职称）

命令：select ex1.name,ex1.prof,ex2.name,ex2.prof from Teacher ex1,Teacher ex2 where ex1.depart='计算机系' and ex2.depart='电子工程系' and ex1.prof!=ex2.prof

25. 列出所有处于不同班级中，但具有相同生日的学生，要求输出每个学生的学

号和姓名。（提示：使用datediff函数，具体用法可以参考：http://hcmfys.javaeye.com/blog/588844） 命令：

select ex1.no,ex1.name,ex2.no,ex2.name from Student ex1 inner join Student ex2 on ex1.birthday=ex2.birthday where ex1.class!=ex2.class

select ex1.no,ex1.name,ex2.no,ex2.name from Student ex1 ,Student ex2 where ex1.class!=ex2.class

and datediff(day,ex1.birthday,ex2.birthday )=0

26. 显示‘张三’教师任课的学生姓名，课程名，成绩

命令：select student.name,cname,DEGREE from Student inner join Score on

student.no=score.no inner join course on score.cno=course.cno inner join Teacher on course.tno=teacher.no where teacher.name='张三'

27. 列出所讲课已被选修的教师的姓名和系别

命令：select distinct teacher.name,depart from Score inner join course on score.cno=course.cno inner join Teacher on course.tno=Teacher.no

28. 输出所有学生的name、no和degree。（degree为空的不输出和为空的输出两种

情况）。

命令：select student.name,student.no,DEGREE from Student inner join Score on

student.no=score.no

select student.name,student.no,DEGREE from Student left join Score on student.no=score.no

29. 列出所有任课教师的name和depart。（从课程选修和任课两个角度考虑）

命令：

(课程选修)

select distinct teacher.name,depart from score

left join course on score.cno=course.cno left join teacher on course.tno=teacher.no

（任课）

select distinct teacher.name,depart from teacher inner join course on Teacher.no=course.tno

30. 输出男教师所上课程名称。

命令：

select cname from Teacher inner join course on Teacher.no=course.tno where teacher.sex='男'

31. 出与“李军”同性别的所有同学的name。

命令：select name from Student where sex=(select sex from Student where name='李军')

32. 输出选修“数据结构”课程的男同学的成绩。

命令：select DEGREE from Student inner join Score on student.no=score.no where sex='男'

33. 列出选修编号为‘3-105’课程并且该门课程成绩比课程 ‘3-111’的最高分

要高的cno,no和degree。

命令：select cno,student.no,DEGREE from Student inner join Score on student.no=score.no where cno='3-105' and degree>(select MAX(degree) from Score where cno='3-111')

子查询

34. 输出score中成绩最高的学号和课程号

命令：select no,cno from Score where degree=(select MAX(degree) from Score)

35. 输出选修3-105课程，其成绩高于109号同学在此课程所得成绩的所有同学的学号，姓

名

命令：select student.no,name from Student inner join Score on student.no=score.no where

cno='3-105' and degree>(select degree from Student inner join Score on student.no=score.no where student.no=109 and cno='3-105')

36. 列出成绩比该课程平均成绩低的同学的学号，成绩和该门课的平均成绩

命令：

select no,DEGREE,temp.avgdegree from Score inner join

(select cno,AVG(degree) avgdegree from Score group by cno) as temp on score.cno=temp.cno where degree

37. 列出没有实际授课的教师的姓名和系别

命令：

select distinct name,depart from Teacher