一起做面试题--统计学院及格率

需求就是给你两张表

student_info表

course_info表

统计各个学院英语成绩的通过率百分比，只保留及格率大于10%的，并按照百分比排序

这个题目很弱鸡，面试时居然告诉我由于此题目较难应试者可以选做。。。

不多说了给答案吧

select s.major,count(1)/(select count(1) from student_info s2 where s2.major=s.major) as pass from student_info s join course_info c on s.id=c.student_id where c.score>=60  and c.course_name='英语' group by s.major having pass>0.1 order by pass asc