MySQL基础训练50题之11~20

MySQL基础训练50题之11~20

1. 查询和"01"号的同学学习的课程完全相同的其他同学的信息

   select * from student where s_id in 
   (select s_id from score where s_id not in
   (select s_id from score where s_id not in (select c_id from score where s_id='01'))
   group by s_id
   having count(*)=(select count(*) from score where s_id='01') and s_id != '01');
   

2. 查询没学过"张三"老师讲授的任一门课程的学生姓名

   SELECT s.s_name FROM student s
   WHERE s.s_id not in (SELECT sc.s_id FROM score sc
   WHERE c_id in (SELECT c.c_id from course c
   LEFT JOIN teacher t
   on t.t_id=c.t_id
   WHERE t.t_name='张三'))
   

3. 查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩

   SELECT s.s_id,s.s_name,a.平均数 FROM student s
   right JOIN
   (
   SELECT sc.s_id,COUNT(*) 不及格门数 ,round(avg(sc.s_score),1) "平均数" from score sc
   WHERE sc.s_score<60
   GROUP BY sc.s_id
   ) a ON a.s_id=s.s_id
   

4. 检索"01"课程分数小于60，按分数降序排列的学生信息

   select s.*,sc.s_score from student s
   inner join score sc on sc.s_id=s.s_id
   where s.s_id in(select s_id from score where s_score<60 and  c_id=01) and c_id=01
   order by sc.s_score desc
   

5. 按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩

   SELECT sc1.s_id,a.`平均分`
   ,sum(case sc1.c_id when 01 then sc1.s_score end) "语文"
   ,sum(case sc1.c_id when 02 then sc1.s_score end) "数学",sum(case sc1.c_id when 03 then sc1.s_score end) "英语"
   FROM score sc1
   right JOIN (SELECT s_id,AVG(s_score) 平均分 FROM score
               GROUP BY s_id
               ORDER BY 平均分 DESC) a ON a.s_id=sc1.s_id
   GROUP BY sc1.s_id
   
   

6. 查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
   – 及格为>=60，中等为：70-80，优良为：80-90，优秀为：>=90

   select c.c_id "课程ID",c.c_name "课程名称",max(s.s_score) "最高分",min(s.s_score) "最低分",
   round(avg(s.s_score)) "平均分",
   round((
   	select count(1) from score  where s_score>=60 and c_id=c.c_id
   )/(
   	select count(1) from score where c_id=c.c_id
   ),2) "及格率",
   round((
   	select count(1) from score  where s_score between 70 and 80 and c_id=c.c_id
   )/(
   	select count(1) from score where c_id=c.c_id
   ),2) "中等",
   round((
   	select count(1) from score  where s_score between 80 and 90 and c_id=c.c_id
   )/(
   	select count(1) from score where c_id=c.c_id
   ),2) "优良",
   round((
   	select count(1) from score  where s_score>=90 and c_id=c.c_id
   )/(
   	select count(1) from score where c_id=c.c_id
   ),2) "优秀"
   from course c
   left join score s on s.c_id=c.c_id
   group by c.c_id,c.c_name
   

   select c.c_id "课程ID",c.c_name "课程名称",max(s.s_score) "最高分",min(s.s_score) "最低分",
   round(avg(s.s_score)) "平均分",
   round((
   	select count(1) from score  where s_score>=60 and c_id=c.c_id
   )/(
   	select count(1) from score where c_id=c.c_id
   ),2) "及格率",
   round((
   	select count(1) from score  where s_score between 70 and 80 and c_id=c.c_id
   )/(
   	select count(1) from score where c_id=c.c_id
   ),2) "中等",
   round((
   	select count(1) from score  where s_score between 80 and 90 and c_id=c.c_id
   )/(
   	select count(1) from score where c_id=c.c_id
   ),2) "优良",
   round((
   	select count(1) from score  where s_score>=90 and c_id=c.c_id
   )/(
   	select count(1) from score where c_id=c.c_id
   ),2) "优秀"
   from course c
   left join score s on s.c_id=c.c_id
   group by c.c_id,c.c_name
   

7. 按各科成绩进行排序，并显示排名(实现不完全)

   select s1.c_id,s1.s_id,s1.s_score,count(s2.s_score)+1 "rank" from score s1
   left join score s2 on s1.s_score<s2.s_score and s1.c_id=s2.c_id
   group by s1.c_id,s1.s_id,s1.s_score
   order by s1.c_id asc,s1.s_score desc
   

8. 查询学生的总成绩并进行排名

   select t.s_id,t.总成绩,@rank:=@rank+1 "排名" from 
   (SELECT sc.s_id,SUM(sc.s_score) 总成绩 from score sc
   GROUP BY sc.s_id
   ORDER BY SUM(sc.s_score) DESC) t,(select @rank:=0) r
   

9. 查询不同老师所教不同课程平均分从高到低显示

   SELECT c_id,ROUND(AVG(s_score),2) 平均分 FROM score
   GROUP BY c_id
   ORDER BY AVG(s_score)
   

10. 查询所有课程的成绩第2名到第3名的学生信息及该课程成绩

    SELECT b.t_id,b.t_name,a.c_name,a.c_id,AVG(c.s_score)'mean' FROM
    course as a
    INNER JOIN 
    teacher as b ON a.t_id=b.t_id
    INNER JOIN
    score as c ON c.c_id=a.c_id
    GROUP BY c.c_id  
    ORDER BY mean DESC