MySQL练习题

取出价格里面最大的前三个值
SELECT price FROM t_book ORDER BY price DESC LIMIT 3;

查询两门及两门以上不及格的学生的平均分。
SELECT name,AVG(chengji) FROM student1 GROUP BY name HAVING SUM(chengji < 60) >= 2;
select student_id,avg(number) from score where
student_id in (select student_id from score where number < 60 group by student_id having count(corse_id) >=1) group by student_id;

求选了课程的学生人数
select count(A.student_id) from (select student_id from score group by corse_id) as A;

按各科平均成绩从低到高和及格率的百分数从高到低顺序；
select corse_id,avg(number), 100*sum(case when number>=60 then 1 else 0 end)/count(*) as '及格率' from score group by corse_id  order by avg(number) asc ,'及格率' desc;
select corse_id,avg(number),sum(case when score.number>=60 then 1 else 0 end)/count(1)*100 as jgl from score group by corse_id;