学生——成绩表查询
一、表
学生表:student
课程表:course
成绩表:score
注:s_id不是主键,而是外键。
教师表:teacher
二、查询
1.查询所有平均分80分以上的学生
SQL:
select sname from student where s_id in( select s_id from score x where (select avg(score) from score y where x.s_id=y.s_id)>80)
分析:此查询的关键在于从成绩表score中查询平均分在80分以上的学生学号,即第2层嵌套的查询,可以看出其对成绩表score 查询了2次,最里面是从成绩表查询平均分avg(score) ,将avg(score) >80作为条件,再查对应的学号s_id,得到这个s_id,就可以利用in从学生表查询结果了。
结果:
改进:查询平均成绩大于80分的学生姓名以及平均成绩。1.使用join连接包含聚合函数的结果;2.使用having作为筛选聚合函数的条件
注:对于筛选聚合函数用having,不能用where
SQL:
select sname,avg(score) as average from student join score on student.s_id=score.s_id group by sname having avg(score)>80
2.查询各科的最高分、最低分及平均分
SQL:
SELECT c.Cname, MAX(s.Score) AS Max, MIN(s.Score) AS Min, AVG(s.Score) AS Average FROM Course c JOIN score s ON c.C_id = s.C_id GROUP BY c.Cname
结果:
2.查询各科成绩的最高分,及对应的学生姓名,课程名称: