selectstudent_id,student.sname,avg(number)fromscoreleft join student on score.student_id=student.sidgroup bystudent_id having avg(number)>=85
19 查询“二年级一班”每个学生的学号、姓名、总成绩、平均成绩
selectstudent_id,student.sname,sum(number),avg(number)fromscoreleft join student on score.student_id=student.sidleft join class on student.class_id=class.cidwhereclass.caption='二年级一班'group by student_id
20查询各个班级的班级名称、总成绩、平均成绩、及格率（按平均成绩从大到小排序）
selectclass.caption,sum(number),avg(number) as av,sum(case when score.num>60 then 1 else 0 end)/count(1)*100 as JGfromscoreleft join student on score.student_id=student.sidleft join class on student.class_id=class.cidgroup byclass.captionorder byav desc
21查询学过李老师课程的同学的学号和姓名
selectstudent_id,student.snamefromscoreleft join student on score.student_id=student.sidleft join course on score.course_id=course.cidleft join teacher on course.teacher_id=teacher.tidwhereteacher.tname='李老师'
22查询没学过李老师课程的同学的学号和姓名
select * from student where sid not in( selectstudent_idfromscoreleft join student on score.student_id=student.sidleft join course on score.course_id=course.cidleft join teacher on course.teacher_id=teacher.tidwhereteacher.tname！='李老师')
23查询选修李老师所授课程的学生中，成绩最高的学生姓名及成绩（不考虑并列）
selectstudent.sname,numberfromscoreleft join student on score.student_id=student.sidleft join course on score.course_id=course.cidleft join teacher on course.teacher_id=teacher.tidwhereteacher.tname='李老师'order byscore.number desclimit 1
24查询选修李老师所授课程的学生中，成绩最高的学生姓名及成绩（考虑并列）
selectstudent.sname,numberfromscoreleft join student on score.student_id=student.sidleft join course on score.course_id=course.cidleft join teacher on course.teacher_id=teacher.tidwhereteacher.tname='李老师'and score.number={selectmax(number)fromscoreleft join course on score.course_id=course.cidleft join teacher on course.teacher_id=teacher.tidwhereteacher.tname='李老师'}
25查询只选修了一门课的全部学生的学号、姓名
selectstudent_id,student.snamefromscoreleft join student on score.student_id=student.sidgroup bystudent_idhavingcount(1)=1
26查询至少选修了2门课程的学生、学生姓名、选修课程数量
selectstudent_id,student.sname,count(1)fromscoreleft join student on score.student_id=student.sidgroup bystudent_idhavingcount(1)>=2
27查询两门及以上不及格的同学的学号、姓名、选修课数量
selectstudent_id,student.sname,count(1)fromscoreleft join student on score.student_id=student.sidwherenumber<60group bystudent_idhavingcount(1)>=2
28查询选修了所有课程的学生的学号、姓名
selectstudent_id,student.snamefromscoreleft join student on score.student_id=student.sidgroup bystudent_idhavingcount(1) = (select count(1) from course)
29查询未选修所有课程的学生的学号、姓名
selectstudent_id,student.snamefromscoreleft join student on score.student_id=student.sidgroup bystudent_idhavingcount(1)