sql server 实验9 连接查询
1、① 查询不同课程成绩相同的学生的学号、课程号、学生成绩（自身连接）
select a.* from sc a ,sc b where a.score=b.score and a.cno<>b.cno
2、① 查询课程编号为c001 且课程成绩在80 分以上的学生的学号和姓名
select student.sno,sname
from student,sc
where student.sno=sc.sno and sc.cno='c001' and score>=80
3、① 查询不及格的课程，并按课程成绩降序排列
select sc.sno,cname,score
from sc,course
where sc.cno=course.cno and sc.score<60
order by score desc
4、① 查询课程成绩在70 分以上的姓名、课程名称和分数；
select sname,cname,score
from sc,student,course
where sc.sno=student.sno and sc.cno=course.cno and sc.score>70
5、① 查询课程名称为“Oracle”，且分数低于60 的学生姓名和分数
select sname,score
from student,sc,course
where 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 student join sc on student.sno=sc.sno
group by student.sno,sname
having avg(score)>85
7、① 查询出只选修了一门课程的全部学生的学号和姓名
select student.sno,sname
from student left join sc on student.sno=sc.sno
group by student.sno,sname
having count(cno)=1
注意：having count(cno)不能写成having count(*)，因为没有成绩的学生信息也有一行数据。
也可以：
select student.sno,sname
from student,sc
where student.sno=sc.sno
group by student.sno,sname
having 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,course
where sc.cno=course.cno
group by course.cno,cname
9、① 查询不同老师所教不同课程平均分从高到低显示，平均分最多保留两位小数。
select sc.cno,teacher.tname,cname,round(avg(score),2) as 平均成绩
from course,sc,teacher
where course.cno=sc.cno and course.tno=teacher.tno
group by teacher.tname,sc.cno,cname
order by avg(score) desc
10、若不想把cname放在group by 里，也可以放在一些聚集函数，但不影响它的值，如max(cname)
① 查询所有课程成绩小于60 分的同学的学号、姓名
select student.sno,sname
from student,sc
where student.sno=sc.sno
group by student.sno,sname
having sum(case when score<60 then 1 else 0 end)=count(*)
