java面试题之sql语句_计算各科都及格学生的平均成绩

根据这一张表进行sql操作

– 分组查询总分数

select num,name,SUM(score) as scoreall from score2 GROUP BY name order by scoreall

– 计算每个人单科最高成绩

select name,MAX(score) from score2 GROUP BY name

– 计算每个人的平均成绩

select name,AVG(score) from score2 GROUP BY name

– 列出各科成绩最好的学生

select DISTINCT ‘最高分’,score2.* from score2,(select MAX(score) as score,course from score2 GROUP BY course ) b

where score2.score=b.score

– 分组统计姓名 语文、数学、英语、总分、平均分

select name as ‘姓名’,SUM(case when course=‘语文’ then score end) as ‘语文’,SUM(case when course=‘数学’ then score end)‘数学’,

SUM(case when course=‘英语’ then score end)‘英语’,SUM(score),AVG(score) FROM score2 GROUP BY name

– 列出数学成绩的排名

select name,course,score from score2 where course=‘数学’ order by score desc

– 统计课程和对应的优秀、及格、不及格个数

select course,(select COUNT(*) from score2 where course=t.course and score>=80)‘优秀’,

(select COUNT(*) from score2 where course=t.course and score>=60 and score<80)‘良好’,

(select COUNT(*) from score2 where course=t.course and score<60)‘不及格’

from score2 t GROUP BY course

– 计算各科都及格的的人的平均成绩

select name,AVG(score) from score2 GROUP BY name having MIN(score)>60