1、① 查询不同课程成绩相同的学生的学号、课程号、学生成绩（自身连接）select a.* from sc a ,sc b where a.score=b.score and a.cno<>b.cno
2、① 查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名select student.sno,snamefrom student,scwhere student.sno=sc.sno and sc.cno='c001' and score>=80
3、① 查询不及格的课程，并按课程成绩降序排列select sc.sno,cname,scorefrom sc,coursewhere sc.cno=course.cno and sc.score<60order by score desc
4、① 查询课程成绩在70 分以上的姓名、课程名称和分数；select sname,cname,scorefrom sc,student,coursewhere sc.sno=student.sno and sc.cno=course.cno and sc.score>70
5、① 查询课程名称为“Oracle”，且分数低于60 的学生姓名和分数select sname,scorefrom student,sc,coursewhere sc.sno=student.sno and sc.cno=course.cno and course.cname='Oracle' and scourse.score<60
6、① 查询平均成绩大于85 的所有学生的学号、姓名和平均成绩select student.sno,sname,avg(score) as 平均成绩from studentjoin sc on student.sno=sc.snogroup by student.sno,snamehaving avg(score)>85
7、① 查询出只选修了一门课程的全部学生的学号和姓名select student.sno,snamefrom studentleft join sc on student.sno=sc.snogroup by student.sno,snamehaving count(cno)=1注意：having count(cno)不能写成having count(*)，因为没有成绩的学生信息也有一行数据。也可以：select student.sno,snamefrom student,scwhere student.sno=sc.snogroup by student.sno,snamehaving count(cno)=1
8、① 统计列印各科成绩,各分数段人数:课程编号,课程名称,[100-85],[85-70], [70幻腾寂埒-60],[ <60]select course.cno,cname,sum(case when score<60 then 1 else 0 end) as '[<60]',sum(case when score>=60 and score<70 then 1 else 0 end) as '[70-60]',sum(case when score>=70 and score<85 then 1 else 0 end) as '[85-70]',sum(case when score>=85 and score<100 then 1 else 0 end) as '[100-85]'from sc,coursewhere sc.cno=course.cnogroup by course.cno,cname
9、① 查询不同老师所教不同课程平均分从高到低显示，平均分最多保留两位小数。select sc.cno荑樊综鲶,teacher.tname,cname,round(avg(score),2) as 平均成绩from course,sc,teacherwhere course.cno=sc.cno and course.tno=teacher.tnogroup by teacher.tname,sc.cno,cnameorder by avg(score) desc
10、若不想把cname放在group by 里，也可以放在一些聚集函数，但不影响它的值，如max(cname)① 查询所有课程成绩小于60 分的同学的学号、姓名select student.sno,snamefrom student,scwhere student.sno=sc.snogroup by student.sno,snamehaving sum(case when score<60 then 1 else 0 end)=count(*)
