Question

一道软件测试面试题中的sql题目，求解

学生表(学生id，姓名，性别，分数)student（s_id，name,sex,score）
班级表（班级id，班级名称）class（c_id，c_name）
学生班级表（班级id，学生id）student_class(s_id,c_id)
1.查询一班得分在80分以上的学生
2.查询所有班级的名称，和所有版中女生人数和女生的平均分。

Answer 1

1.
select * from student where score>80
and s_id in
(select sid from student_class
where c_id=(select c_id from class where c_name='一班'))
2.
select c.c_name,女生人数=sum(s.s_id),平均分=avg(s.score)
from classes c
inner join student_class sc on sc.c_id=c.c_id
inner join students s on s.s_id=sc.s_id
where s.sex='女' group by c.c_name

Answer 2

1、select class.c_name,count(student.sex) as '女生人数',avg(student.score) as '女生平均分' from student,class,student_class where student.s_id=student_class.c_id and class.c_id=student_class.s_id and student.sex='女' group by class.c_name ;
2、select class.c_name,count(case when student.sex='女' then student.sex end) as '女生人数',avg(case when student.sex='女' then student.score else 0 end) as '女生平均分' from student,class,student_class where student.s_id=student_class.c_id and class.c_id=student_class.s_id group by class.c_name ;