MySQL基础训练50题之1~10

MySQL基础训练50题之1~10

1. 查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩

   select s.s_id "学号",s.s_name "姓名",round(avg(sc.s_score),1) "平均分" from student s
   inner join score sc on sc.s_id=s.s_id
   group by s.s_id
   HAVING
   平均分>=60
   1
   2
   3
   4
   5

2. 查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩(包括有成绩的和无成绩的)

   SELECT s.s_id "学号",s.s_name "姓名",round(avg(sc.s_score),1) "平均分" FROM student s
   RIGHT JOIN score sc on sc.s_id=s.s_id
   GROUP BY s.s_id
   HAVING
   平均分<60
   1
   2
   3
   4
   5

3. 查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩

   SELECT s.s_id 学号,s.s_name 姓名,COUNT(sc.c_id) 选课总数, SUM(sc.s_score)  FROM student s 
   INNER JOIN score sc ON sc.s_id=s.s_id
   GROUP BY s.s_id
   1
   2
   3

4. 查询"李"姓老师的数量

   SELECT COUNT(t_name) 李姓老师的数量 FROM teacher
   WHERE t_name LIKE "李%" 
   1
   2

5. 查询学过"张三"老师授课的同学的信息

   SELECT * FROM student s
   INNER JOIN score sc ON sc.s_id=s.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='张三';
   
   1
   2
   3
   4
   5
   6

6. 查询没学过"张三"老师授课的同学的信息

   SELECT * FROM student s
   INNER JOIN score sc ON sc.s_id=s.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!='张三';
   1
   2
   3
   4
   5

7. 查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息

   SELECT s.* from student s
   WHERE s.s_id in (
   SELECT sc1.s_id from score sc1 
   INNER JOIN score sc2
   ON sc1.s_id=sc2.s_id
   WHERE sc1.c_id=01 AND sc2.c_id=02)
   
   1
   2
   3
   4
   5
   6
   7

8. 查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息

   SELECT s.* FROM student s
   INNER JOIN score sc
   ON s.s_id=sc.s_id
   WHERE sc.c_id=01 and sc.c_id!=02
   1
   2
   3
   4

9. 查询没有学全所有课程的同学的信息

   select s.* from student s
   right join 
   (
   select s_id,count(1) "所学门数" from score
   group by s_id
   having 所学门数<3
   )c1 on c1.s_id=s.s_id
   1
   2
   3
   4
   5
   6
   7

10. 查询至少有一门课与学号为"01"的同学所学相同的同学的信息

    SELECT s.* from student s INNER JOIN score sc
    on s.s_id=sc.s_id
    WHERE sc.c_id IN (SELECT sc.c_id from score sc
                      where sc.s_id=01) AND s.s_id!=01
                      GROUP BY s.s_id
    1
    2
    3
    4
    5