一些mysql的sql语句练习题

前言

有表格如下:

student

score

course

teacher

---

查询平均成绩大于60分的同学的id和平均成绩；

mysql> SELECT student_id, AVG(number) AS number_avg
    -> FROM score
    -> GROUP BY student_id
    -> HAVING AVG(number) > 60;

查询所有同学的id、姓名、选课的数量、总成绩；

mysql> SELECT student_id,
    ->          name,
    ->          COUNT(course_id) AS course_count,
    ->          SUM(number) AS number_sum
    -> FROM student INNER JOIN score
    -> ON student.id = score.student_id
    -> GROUP BY student_id
    -> ORDER BY student_id;

查询姓“李”的老师的个数；

mysql> SELECT COUNT(name) AS numbers
    -> FROM teacher
    -> WHERE name LIKE '李%';

查询没学过“李老师”课的同学的id、姓名；

mysql> SELECT student.id, student.name
    -> FROM student
    -> WHERE NOT (
    -> student.id IN (
    ->                  SELECT score.student_id
    ->                  FROM score, course, teacher
    ->                  WHERE score.course_id =course.id
    ->                  AND course.teacher_id = teacher.id
    ->                  AND teacher.name LIKE '李%'));

查询学过课程id为3和4的所有同学的id、姓名；

mysql> SELECT student.id, student.name
    -> FROM student, score
    -> WHERE student.id = score.student_id
    -> AND score.course_id = 3
    -> GROUP BY student.id
    -> UNION ALL
    -> SELECT student.id, student.name
    -> FROM student, score
    -> WHERE student.id = score.student_id
    -> AND score.course_id = 4
    -> GROUP BY student.id
    -> HAVING COUNT(student.id) > 1;

查询两门以上不及格的同学的id、姓名、以及不及格课程数；

mysql> SELECT student.id, student.name, COUNT(CASE WHEN score.number < 60 THEN score.number ELSE NULL END) AS nums
    -> FROM student, score
    -> WHERE student.id = score.student_id
    -> GROUP BY student.id
    -> HAVING COUNT(CASE WHEN score.number < 60 THEN score.number ELSE NULL END) >=2;