SQL数据库经典题型（2）列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩

1.查询平均成绩不及格的学生姓名及平均成绩

select s1.s_id,s1.s_name,round(avg(s2.s_score))  from s_student s1,s_score s2
where s1.s_id=s2.s_id
group by s1.s_id,s1.s_name having round(avg(s2.s_score))<60;

2.--列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩
select s1.s_name,avg(s2.s_score)  from s_student s1,s_score s2
where s1.s_id=s2.s_id and s2.s_score<60
group by s1.s_name
having count(distinct c_id)>=2;

3.--查询所有同学的学号、姓名、选课数、总成绩
select s1.s_id,s1.s_name,count(distinct s2.c_id) ccourse,sum(s2.s_score) sumscore
from s_student s1,s_score s2
where s1.s_id=s2.s_id
group by s1.s_id,s1.s_name;