mysql练习题进阶版

student表

1 查询"01"课程比"02"课程成绩高的学生的信息及课程分数  

select student.*,s_score from student,score where c_id = 01 and score.s_id = student.s_id and score.s_id in
(select s1.s_id from score s1,score s2 where s1.c_id = 01 and s2.c_id = 02 and s1.s_score >s2.s_score and s1.s_id = s2.s_id)

2 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

select sc.s_id,st.s_name,avg(s_score) from score sc left join student st on sc.s_id = st.s_id group by sc.s_id having avg(s_score)>60 

3 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

select st.s_id,s_name,count(c_id) sum_course,sum(s_score) sum_score from student st 
left join score sc on sc.s_id = st.s_id group by st.s_id 

 4 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩 

select st.s_id,s_name,avg(s_score) from student st LEFT OUTER JOIN score sc on st.s_id=sc.s_id 
where st.s_id in (select s_id from score where s_score < 60 group by s_id having count(*)>=2) GROUP BY st.s_id

18