Home Archives About

【题】MySQL数据库的一个例题（下）

  1. 查询每门课程被选修的学生数
    1
    2
    3
    4
    5
    6
    SELECT t2.cname,
           COUNT(t1.sid) AS sum_stu
    FROM sc t1
    INNER JOIN course t2
      ON t1.cid = t2.cid
    GROUP BY t2.cname
  2. 查询出只选修了一门课程的全部学生的学号和姓名 
    1
    2
    3
    4
    5
    6
    7
    8
    /* GROUP BY 语句中选择出来的只有备份组的字段和聚合函数 */
    SELECT t1.sid,
           t1.sname
    FROM student t1
    INNER JOIN sc t2
      ON t1.sid = t2.sid
    GROUP BY t1.sid,t1.sname
    HAVING COUNT(t2.cid) = 1
  3. 查询男生、女生人数
    1
    2
    3
    4
    SELECT t1.ssex,
           COUNT(t1.ssex) AS sex_num  
    FROM student t1
    GROUP BY t1.ssex
  4. 查询名字中有’黑’的学生名单
    1
    2
    3
    4
    SELECT t1.sid,
           t1.sname
    FROM student t1
    WHERE t1.sname LIKE '%黑%'
  5. 查询同名同性学生名单，并统计同名人数
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    /* 写了个错的 
       原因：student表中有两个小粉，on条件只是县限定了sname相等，所以导致了2X2的笛卡尔乘积*/
    SELECT t1.sname,
           COUNT(t1.sid) AS same_name_count
    FROM student t1
    INNER JOIN student t2
      ON t1.sname = t2.sname
    GROUP BY t1.sname,t2.sname
    HAVING COUNT(t1.sname) > 1
    /* 正确的写法 */
    SELECT t1.sname,
           COUNT(t1.sname) AS same_name_count
    FROM student t1
    GROUP BY t1.sname
    HAVING COUNT(t1.sname) > 1
  6. 查询每门课程的平均成绩，结果按平均成绩升序排列，平均成绩相同时，按课程号降序排列
    1
    2
    3
    4
    5
    6
    7
    8
    SELECT t2.cname,
           t2.cid,
           AVG(t1.score) AS avg_score       
    FROM sc t1
    INNER JOIN course t2
      ON t1.cid = t2.cid
    GROUP BY t2.cname,t2.cid
    ORDER BY avg_score ASC,t2.cid DESC
  7. 查询平均成绩大于85的所有学生的学号、姓名和平均成绩
    1
    2
    3
    4
    5
    6
    7
    8
    SELECT t1.sid,
           t1.sname,
           AVG(t2.score) AS avg_score
    FROM student t1
    INNER JOIN sc t2
      ON t1.sid = t2.sid
    GROUP BY t1.sid,t1.sname
    HAVING AVG(t2.score) > 85
  8. 查询课程名称为’语文1’，且分数低于60的学生姓名和分数
    1
    2
    3
    4
    5
    6
    7
    8
    9
    SELECT t1.sname,
           t2.score
    FROM student t1
    INNER JOIN sc t2
      ON t1.sid = t2.sid
    INNER JOIN course t3
      ON t2.cid = t3.cid
    WHERE t3.cname = '语文1'
      AND t2.score < 60
  9. 查询所有学生的选课情况
    1
    2
    3
    4
    5
    6
    7
    8
    9
    /* 可能不是这样写，有更好的写法 */
    SELECT t1.sid,
           t1.sname,
           t3.cname
    FROM student t1
    INNER JOIN sc t2
      ON t1.sid = t2.sid
    INNER JOIN course t3
      ON t2.cid = t3.cid
  10. 查询任何一门课程成绩在70分以上的姓名、课程名称和分数
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    SELECT t1.sid,
           t1.sname,
           t3.cname,
           t2.score
    FROM student t1
    INNER JOIN sc t2
      ON t1.sid = t2.sid
    INNER JOIN course t3
      ON t2.cid = t3.cid
    WHERE t2.score > 70
  11. 查询不及格的课程，并按课程号从大到小排列
    1
    2
    3
    4
    5
    6
    7
    8
    SELECT t1.cid,
           t2.cname,
           t1.score
    FROM sc t1
    INNER JOIN course t2
      ON t1.cid = t2.cid
    WHERE t1.score < 60
    ORDER BY t1.cid DESC
  12. 查询课程编号为003且课程成绩在80分以上的学生的学号和姓名
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    SELECT t1.sid,
           t1.sname,
           t2.score
    FROM student t1
    INNER JOIN sc t2
      ON t1.sid = t2.sid
    INNER JOIN course t3
      ON t2.cid = t3.cid
    WHERE t2.cid = 113
      AND t2.score > 80
  13. 求选了课程的学生人数
    1
    2
    3
    4
    5
    6
    SELECT SUM(1)
    FROM (
          SELECT 1
          FROM sc t1
          GROUP BY t1.s#
          )
  14. 查询选修“叶平”老师所授课程的学生中，成绩最高的学生姓名及其成绩
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    SELECT t4.sid,
           t4.sname,
           t5.score,
           t6.cname
    FROM student t4
    INNER JOIN sc t5
      ON t4.sid = t5.sid
    INNER JOIN course t6
      ON t5.cid = t6.cid
    WHERE (t5.score,t6.cname) IN (
                                  SELECT MAX(t1.score),
                                         t2.cname
                                  FROM sc t1
                                  INNER JOIN course t2
                                    ON t1.cid = t2.cid
                                  INNER JOIN teacher t3
                                    ON t2.tid = t3.tid
                                  WHERE t3.tname = '叶平'
                                    GROUP BY t2.cname
                                 )
  15. 查询各个课程及相应的选修人数
    1
    2
    3
    4
    5
    6
    7
    SELECT t1.cid,
           t2.cname,
           COUNT(t1.sid)
    FROM sc t1
    INNER JOIN course t2
      ON t1.cid = t2.cid
    GROUP BY t1.cid,t2.cname
  16. 查询不同课程成绩相同的学生的学号、课程号、学生成绩
    1
    2
    3
    4
    5
    6
    7
    8
    SELECT t1.sid,
           t1.cid,
           t1.score
    FROM sc t1
    INNER JOIN sc t2
      ON t1.score = t2.score
      AND t1.cid <> t2.cid
      AND t1.sid = t2.sid
  17. 查询每门功成绩最好的前两名
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    SELECT sub_query.sid,
           sub_query.sname,
           sub_query.cname,
           sub_query.score,
           sub_query.rank
    FROM (
          SELECT t1.sid,
                 t3.sname,
                 t2.cname,
                 t1.score,
                 dense_rank() over(PARTITION BY t1.cid ORDER BY t1.score DESC) AS rank
          FROM sc t1
          INNER JOIN course t2
            ON t1.cid = t2.cid
          INNER JOIN student t3
            ON t1.sid = t3.sid
          ) sub_query
    WHERE sub_query.rank < 3
  18. 统计每门课程的学生选修人数（超过10人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，查询结果按人数降序排列，若人数相同，按课程号升序排列
    1
    2
    3
    4
    5
    6
    SELECT t1.cid,
           COUNT(t1.sid) AS stu_num
    FROM sc t1
    GROUP BY t1.cid
      HAVING COUNT(t1.sid) > 10
    ORDER BY stu_num DESC,t1.cid ASC
  19. 检索至少选修两门课程的学生学号
    1
    2
    3
    4
    SELECT t1.sid
    FROM sc t1
    GROUP BY t1.sid
      HAVING COUNT(t1.cid) > 1
  20. 查询全部学生都选修的课程的课程号和课程名
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    SELECT t1.cid,
           t2.cname,
           COUNT(t1.sid)
    FROM sc t1
    INNER JOIN course t2
      ON t1.cid = t2.cid
    GROUP BY t1.cid,t2.cname
      HAVING COUNT(t1.sid) = (
                              SELECT COUNT(t3.sid)
                              FROM student t3
                            )
  21. 查询没学过’陈奕迅’老师讲授的任一门课程的学生姓名
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    SELECT t5.s#, 
           t5.sname
    FROM student t5
    WHERE t5.s# NOT IN (
                         SELECT DISTINCT t1.s#
                         FROM student t1
                         INNER JOIN sc t2
                           ON t1.s# = t2.s#
                         INNER JOIN course t3
                           ON t2.c# = t3.c#
                         INNER JOIN teacher t4
                           ON t3.t# = t4.t#
                         WHERE t4.tname = '陈奕迅'
                        )
  22. 查询两门以上不及格课程的同学的学号及其平均成绩
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    /* 不能直接用下面的方式，因为有where条件过滤了一部分的成绩
    SELECT t1.sid,
           AVG(t1.score)
    FROM sc t1
    WHERE t1.score < 60
    GROUP BY t1.sid
      HAVING COUNT(t1.cid) > 2 */                  
    SELECT t2.sid,
           AVG(t2.score)
    FROM sc t2
    WHERE t2.sid IN (
                    SELECT t1.sid
                    FROM sc t1
                    WHERE t1.score < 60
                    GROUP BY t1.sid
                      HAVING COUNT(t1.cid) > 2
                   )
    GROUP BY t2.sid
  23. 检索’114’课程分数小于60，按分数降序排列的同学学号
    1
    2
    3
    4
    5
    SELECT t1.sid
    FROM sc t1
    WHERE t1.cid = 114
      AND t1.score < 60
    ORDER BY t1.sid DESC
  24. 删除’2’同学的’111’课程的成绩
    1
    2
    3
    DELETE FROM sc t1
    WHERE t1.sid = 2
      AND t1.cid = 111

last update time 2022-03-03

© 云息未来 2021 - 2022
Powered by Hexo & iLiKE Theme