MySQL基础训练50题之31~40

MySQL基础训练50题之31~40

1. 查询平均成绩大于等于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 
   

2. 查询课程名称为"数学"，且分数低于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
   

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

   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
   

4. 查询任何一门课程成绩在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
   

5. 查询不及格的课程

   SELECT * FROM score
   WHERE s_score<60
   
   

6. 查询课程编号为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
   

7. 求每门课程的学生人数

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

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

   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
   

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

   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
   

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

    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;