- 掌握多表连接查询、子查询、游标、大对象类型数据的基本概念。
- 掌握多表连接的各种方法,包括内连接、外链接和交叉连接等。
- 掌握子查询的方法,包括相关子查询和不相关子查询。
- 掌握游标处理结果集的基本过程。
1.查询所有班级的期末成绩平均分,并按照平均分降序排序。
select classno,avg(final) from student,score where student.studentno=score.studentno group by classno order by avg(final) desc
2.查询教师基本信息和教授课程信息,其中包括未分配课程的教师信息。
select * from teacher left join teach_class on teacher.teachno=teach_class.teacherno
3.查询160501班级中选修了“韩晋升”老师讲授的课程的学生学号、姓名、课程号和期末成绩。
select student.studentno,sname,courseno,final from student,score
where student.studentno=score.studentno and classno=160501 and
courseno=(select courseno from teach_class where teacherno=(select teachno from teacher where tname='韩晋升'))
4.查询每门课程的课程号、课程名和选修该课程的学生人数,并按所选人数升序排序。
select score.courseno,COUNT(*) as 选课人数 from score inner join course on course.courseno=score.courseno group by score.courseno order by COUNT(studentno) desc
5.查询两门及以上课程的期末成绩超过80分的学生姓名及平均成绩。
select student.sname,AVG(final) as '平均成绩' from student, score where final>80 and student.studentno=score.studentno group by student.studentno,student.sname having COUNT(*)>=2
6.查询入学考试成绩最高的学生学号、姓名和入学成绩。
select top 1 sname,studentno,point from student order by point desc
7.查询同时教授c05127号和c05109号课程的教师信息。
select * from teacher left join teach_class on teacher.teachno=teach_class.teacherno where courseno='c05127' and courseno='c05109'
8.查询至少