数据库sql
join Course on Score.cno=Course.cno where sex='男' and cname='数据结构';
33. 列出选修编号为‘3-105’课程并且该门课程成绩比课程 ‘3-111’的最高分
要高的cno,no和degree。
命令：select Course.cno,Score.no,DEGREE from Course join Score on
Course.cno=Score.cno join Student on Score.no=Student.no where Course.cno='3-105' and Score.degree>(select max(degree) from Score where Score.cno='3-111');
子查询
34. 输出score中成绩最高的学号和课程号
命令：select no,cno from Score where degree in(select MAX(degree) from Score);
35. 输出选修3-105课程，其成绩高于109号同学在此课程所得成绩的所有同学的学号，姓
名
命令：select Student.no,name from Student join Score on Student.no
=Score.no where Score.cno='3-105' and degree>(select degree from Score where no='109' and cno='3-105');
36. 列出成绩比该课程平均成绩低的同学的学号，成绩和该门课的平均成绩
命令：select no,DEGREE,a.avg_degree from Score join (select
cno,AVG(cast(degree as float)) from Score group by cno) a(cno,avg_degree)
on
Score.cno=a.cno
where
Score.degree
- 11 -
数据库sql
37. 列出没有实际授课的教师的姓名和系别
命令：select name,depart from Teacher except select name,depart from
Teacher join Course on Teacher.no=Course.tno join Score on Course.cno=Score.cno;
38. 列出选修了编号为‘3-105’课程且其成绩高于‘4-109’课程最高成绩的同学的 课程
编号，学号和成绩
命令：select cno,no,DEGREE from Score where cno='3-105' and degree>(select MAX(degree) from Score where cno='4-109');
39. **列出符合下述条件的所有可能的同学配对（sno1,sname1,sno2,sname2,difference）。
其中要求学号为sno1的sname1同学的所学课程的平均分大于学号为sno2的sname2同学的所学课程平均分，两个同学的课程平均分的差值difference为（sno1同学平均分-sno2同学平均分）
命令：select a.no,a.name,b.no,b.name,a.avg_degree-b.avg_degree as
difference from (select Student.no,name,avg(degree) from Student join Score
on
Student.no=Score.no
group
by
Student.no,name)
a(no,name,avg_degree) join (select Student.no,name,avg(degree) from Student join Score on Student.no=Score.no group by Student.no,name) b(no,name,avg_degree) on a.avg_degree>b.avg_degree;
- 12 -
数据库sql
- 13 -