MySQL基础训练50题之31~40

查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩

SELECT st.s_id,st.s_name,AVG(sc.s_score)a FROM student as st INNER JOIN score as sc on st.s_id=sc.s_id GROUP BY s_id HAVING a>=85

查询课程名称为"数学"，且分数低于60的学生姓名和分数

SELECT s.s_name,s.s_id,c.c_name, sc.s_score FROM student s INNER JOIN score sc on s.s_id=sc.s_id INNER JOIN course c on sc.c_id=c.c_id WHERE c.c_name='数学' and sc.s_score<60

查询所有学生的课程及分数情况；

SELECT s.s_id,s.s_name ,MAX(CASE WHEN c.c_name='语文' then sc.s_score else NULL END) as '语文' ,MAX(CASE WHEN c.c_name='数学' then sc.s_score else NULL END) as '数学' ,MAX(CASE WHEN c.c_name='英语' then sc.s_score else NULL END) as '英语' FROM student s LEFT JOIN score sc on s.s_id=sc.s_id LEFT JOIN course c on sc.c_id=c.c_id GROUP BY s.s_id,s.s_name

查询任何一门课程成绩在70分以上的姓名、课程名称和分数

SELECT s.s_name,c.c_name,sc.s_score FROM student s INNER JOIN score sc on s.s_id=sc.s_id INNER JOIN course c on sc.c_id=c.c_id WHERE sc.s_score>70

查询不及格的课程

SELECT * FROM score WHERE s_score<60

查询课程编号为01且课程成绩在80分以上的学生的学号和姓名

SELECT s.s_id,s.s_name,sc.s_score,c.c_id,c.c_name FROM student s INNER JOIN score sc on s.s_id=sc.s_id INNER JOIN course c on sc.c_id=c.c_id WHERE c.c_id='03' AND sc.s_score>80 SELECT s.s_id,s.s_name,sc.s_score,c.c_id,c.c_name FROM student s INNER JOIN score sc on s.s_id=sc.s_id INNER JOIN course c on sc.c_id=c.c_id WHERE c.c_id='03' AND sc.s_score>80

求每门课程的学生人数

SELECT c_id,COUNT(c_id) FROM score GROUP BY c_id

查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩

SELECT s.s_name,sc.s_score FROM student s INNER JOIN score sc on s.s_id=sc.s_id INNER JOIN course c on sc.c_id=c.c_id INNER JOIN teacher t ON t.t_id=c.t_id WHERE t.t_name='张三' ORDER BY s_score DESC LIMIT 0,1

查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩

select DISTINCT s1.s_id,s1.s_score,s1.c_id from score s1,score s2 where s1.c_id !=s2.c_id and s1.s_score=s2.s_score and s1.s_id=s2.s_id

查询每门功成绩最好的前两名

SELECT s1.* FROM score s1 WHERE ( SELECT COUNT(1) FROM score s2 WHERE s1.c_id=s2.c_id AND s2.s_score>=s1.s_score )<=2 ORDER BY s1.c_id,s1.s_score DESC;