mysql经典练习每天10道题之三

21、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
SELECT a.s_id,a.s_name,b.avg
from student a
join (SELECT s_id,avg(s_score) as avg from score GROUP BY s_id having avg(s_score)>=85) as b
on a.s_id =b.s_id ;

SELECT a.s_id,a.s_name,avg(score.s_score)
from student a ,score
where a.s_id=score.s_id GROUP BY a.s_id having avg(score.s_score)>=85;

21、查询课程名称为”数学”，且分数低于60的学生姓名和分数
SELECT a.s_name,b.s_score
from student a,score b
where a.s_id =b.s_id and (SELECT c_id from course where c_name =‘数学’)=b.c_id
and b.s_score < 60;

SELECT a.s_name,b.s_score
from student a
join score b on a.s_id = b.s_id
and b.c_id =(SELECT c_id from course where c_name =‘数学’)
and b.s_score<60;

22、查询所有学生的课程及分数情况；
SELECT a.*,
(SELECT s_score from score where s_id =b.s_id and c_id =‘01’ )as ‘数学’,
(SELECT s_score from score where s_id =b.s_id and c_id =‘02’ )as ‘语文’,
(SELECT s_score from score where s_id =b.s_id and c_id =‘03’ )as ‘英语’,
sum(b.s_score) as sum
from student a,score b
where a.s_id = b.s_id
group by b.s_id ;

23、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
第一种
SELECT e.s_name,c.c_name,c.s_score
from (
SELECT b.c_name,a.s_score,a.s_id from score a,course b where a.c_id =b.c_id and a.s_score > 70) as c
join student e on e.s_id = c.s_id ；

第二种
SELECT a.s_name,c.c_name,b.s_score
FROM course c
left join score b on c.c_id = b.c_id
left join student a on b.s_id = a.s_id
where b.s_score > 70;
第三种
SELECT a.s_name,b.c_name,c.s_score
from (SELECT s_id ,c_id,s_score from score
where s_score >70
group by c_id,s_id) as c
join student a on a.s_id = c.s_id
join course b on c.c_id = b.c_id;

24、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
SELECT a.s_id,a.s_name,b.s_score
from student a
join score b
on b.s_id =a.s_id
and c_id =‘01’ and s_score >=80;

25、查询选修”张三”老师所授课程的学生中，成绩最高的学生信息及其成绩
第一种
SELECT b.,max(c.s_score) as max,c.c_name
from student b
join(SELECT a.c_id,t_name,a.s_score,a.s_id,e.c_name FROM
score a
inner join course e on a.c_id = e.c_id
inner join teacher d on d.t_id =e.t_id and t_name = ‘张三’) as c
on b.s_id = c.s_id;

第二种
SELECT b.,max(s_score)as max ,c.c_name
from score a
join student b on a.s_id = b.s_id and c_id=(SELECT c_id from course where t_id = (SELECT t_id from teacher where t_name =‘张三’))
join course c on c.c_id = a.c_id;

26、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
SELECT DISTINCT b.* ,a.c_id,a.s_score
FROM score a
join student b on a.s_id = b.s_id
join score c on c.c_id !=a.c_id and c.s_score=a.s_score;

27、查询每门课程成绩最好的前两名

SELECT a.s_id ,a.s_name ,c.c_id ,c.s_score
from student a
join score c on a.s_id =c.s_id and (SELECT count(1) from score where s_score >=c.s_score and c_id = c.c_id )<=2
ORDER BY c.c_id;

28、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人数相同，按课程号升序排列
SELECT c_id,count(1) AS COUNT
FROM score
GROUP BY c_id HAVING count(1)>=5
ORDER BY count desc,c_id asc;

29、检索至少选修两门课程的学生学号
SELECT s_id ,count()
from score
GROUP BY s_id
having count()>=2;

30、查询选修了全部课程的学生信息
SELECT a.*
FROM student a
where a.s_id in (SELECT s_id from score group by s_id having count(1)=(SELECT count(1) from course))