LabEx
登录免费加入

学生课程分析的 SQL 查询

SQLBeginner
立即练习

介绍

在本项目中，你将学习如何对一个学生课程数据库执行各种 SQL 查询。该数据库由三个表组成：学生表（S）、课程表（C）和学生课程表（SC）。你将学习如何从这些表中检索和操作数据，以完成不同的任务。

👀 预览

SQL 数据库预览图像

🎯 任务

在本项目中，你将学习：

  • 如何列出所有没有选修“Daniel”老师所授课程的学生姓名
  • 如何列出在两门或更多课程中不及格（成绩 < 60）的学生学号和姓名
  • 如何列出同时选修了课程“11”和课程“12”的所有学生姓名
  • 如何列出所有在课程“11”中的成绩高于课程“12”中成绩的学生学号，并按升序排序
  • 如何列出所有比“John Davis”年龄大的学生姓名
  • 如何列出所有选修了课程“12”的学生中成绩最高的学生学号
  • 如何列出所有学生的姓名，以及他们所选课程的课程 ID 和成绩
  • 如何列出选修了四门或更多课程的学生的学号和总成绩（别名：SG），并按总成绩降序排序

🏆 成果

完成本项目后，你将能够：

  • 理解如何编写复杂的 SQL 查询，以从关系数据库中检索和操作数据
  • 应用各种 SQL 技术，如子查询、连接、聚合和排序，以解决实际的数据分析问题
  • 培养对数据驱动的决策和问题解决至关重要的 SQL 技能

启动 MySQL 并导入数据

在本步骤中，你将学习如何启动 MySQL 服务并将必要的数据导入数据库。

  1. 使用sudo命令在不设置任何密码的情况下启动 MySQL 服务：
sudo service mysql start
  1. 使用sudo命令在不设置任何密码的情况下访问 MySQL 客户端：
sudo mysql
  1. 将提供的personnel.sql脚本中的数据导入 MySQL 数据库：
SOURCE /home/labex/project/initdb.sql

添加查询代码

在本步骤中，你将学习如何列出所有没有选修“Daniel”老师所授课程的学生姓名。请按照以下步骤完成本步骤：

  1. 打开 /home/labex/project 目录下的 answer.sql 文件。

  2. 在注释下方添加 SQL 语句：

  3. 找到注释 -- 列出所有没有选修 “Daniel” 老师所授课程的学生姓名。

    SELECT s.sname
FROM S s
WHERE NOT EXISTS (
  SELECT 1
  FROM SC sc
  JOIN C c ON sc.cno = c.cno
  WHERE c.tname = 'Daniel' AND sc.sno = s.sno
);

    这条 SQL 语句首先从 S 表中选择 sname 列。然后使用 NOT EXISTS 子句检查 SC 表中是否存在任何记录，这些记录的课程 ID（cno）与“Daniel”老师所授课程匹配，并且学生 ID（sno）与当前学生匹配。如果不存在这样的记录，学生的姓名将包含在结果中。

  4. 找到注释 -- 列出在两门或更多课程中不及格（成绩 < 60）的学生学号和姓名。

    SELECT s.sno, s.sname
FROM S s
JOIN SC sc ON s.sno = sc.sno
WHERE sc.grade < 60
GROUP BY s.sno, s.sname
HAVING COUNT(sc.sno) >= 2;

    这条 SQL 语句首先将 S 表和 SC 表进行连接，以获取学生信息及其课程成绩。然后过滤出成绩小于 60 的记录，按学生学号和姓名进行分组，最后使用 HAVING 子句仅选择在两门或更多课程中不及格的学生。

  5. 找到注释 -- 列出同时选修了课程 “11” 和课程 “12” 的所有学生姓名。

     SELECT s.sname
 FROM S s
 JOIN SC sc ON s.sno = sc.sno
 WHERE sc.cno IN (11, 12)
 GROUP BY s.sno
 HAVING COUNT(DISTINCT sc.cno) = 2
 ORDER BY s.sno;

    这条 SQL 语句首先将 S 表和 SC 表进行连接，以获取学生信息及其课程 ID。然后过滤出课程 ID 为 11 或 12 的记录，按学生学号进行分组，并使用 HAVING 子句仅选择同时选修了这两门课程的学生。最后，按学生学号对结果进行排序。

  6. 找到注释 -- 列出所有在课程 “11” 中的成绩高于课程 “12” 中成绩的学生学号，并按升序排序。

    SELECT sc11.sno
FROM SC sc11
JOIN SC sc12 ON sc11.sno = sc12.sno AND sc12.cno = 12
WHERE sc11.cno = 11 AND sc11.grade > sc12.grade
ORDER BY sc11.sno;

    这条 SQL 语句首先将 SC 表与自身进行连接，以获取课程 11 和课程 12 的成绩信息。然后过滤出课程 ID 为 11 且成绩高于课程 12 成绩的记录，并选择学生学号。最后，按学生学号对结果进行排序。

  7. 找到注释 -- 列出所有比 “John Davis” 年龄大的学生姓名。

    SELECT s.sname
FROM S s, (SELECT sage FROM S WHERE sname = 'John Davis') AS zs
WHERE s.sage > zs.sage;

    这条 SQL 语句首先选择姓名为“John Davis”的学生的年龄值，并将其存储在子查询 zs 中。然后从 S 表中选择年龄（sage）大于“John Davis”年龄的学生的姓名。

  8. 找到注释 -- 列出所有选修了课程 “12” 的学生中成绩最高的学生学号。

    SELECT sc.sno
FROM SC sc
WHERE sc.cno = 12 AND sc.grade = (
  SELECT MAX(grade)
  FROM SC
  WHERE cno = 12
);

    这条 SQL 语句首先从 SC 表中选择课程 ID 为 12 且成绩等于选修课程 12 的所有学生中的最高成绩的学生学号。这将给出课程 12 中成绩最高的学生的学号。

  9. 找到注释 -- 列出所有学生的姓名，以及他们所选课程的课程ID和成绩。

    SELECT s.sname, sc.cno, sc.grade
FROM S s
LEFT JOIN SC sc ON s.sno = sc.sno;

    这条 SQL 语句在 S 表和 SC 表之间执行左连接，这将包括 S 表中的所有学生，即使他们在 SC 表中没有记录。这将确保所有学生姓名都包含在结果中，以及他们的课程 ID 和成绩（如果有的话）。

  10. 找到注释 -- 列出选修了四门或更多课程的学生的学号和总成绩（别名：SG），并按总成绩降序排序。

    SELECT sc.sno, SUM(sc.grade) AS SG
FROM SC sc
GROUP BY sc.sno
HAVING COUNT(sc.cno) >= 4
ORDER BY SG DESC;

    这条SQL语句首先按学生学号（sno）对 SC 表进行分组，并计算每个学生的成绩总和（别名 SG）。然后使用 HAVING 子句过滤结果，仅包括选修了四门或更多课程的学生。最后，按总成绩降序对结果进行排序。

运行 SQL 脚本

在这最后一步中，你将运行在前几步中创建的 SQL 脚本。

  1. 在 MySQL 客户端中，运行以下命令来执行 answer.sql 脚本：
SOURCE /home/labex/project/answer.sql

这将执行 answer.sql 文件中的 SQL 查询并显示结果。

恭喜你！你已经完成了这个项目。你应该会看到以下输出：

MariaDB [student]> SOURCE /home/labex/project/answer.sql
+----------------+
| sname          |
+----------------+
| Michael Brown  |
| William Wilson |
| Richard Taylor |
| Charles Thomas |
+----------------+
4行记录（0.013秒）

+-----+---------------+
| sno | sname         |
+-----+---------------+
|   1 | James Johnson |
|   6 | David Moore   |
+-----+---------------+
2行记录（0.000秒）

+----------------+
| sname          |
+----------------+
| James Johnson  |
| Michael Brown  |
| John Davis     |
| Robert Miller  |
| William Wilson |
| David Moore    |
| Richard Taylor |
+----------------+
7行记录（0.000秒）

+-----+
| sno |
+-----+
|   2 |
|   3 |
|   4 |
|   7 |
+-----+
4行记录（0.001秒）

+----------------+
| sname          |
+----------------+
| William Wilson |
| Richard Taylor |
+----------------+
2行记录（0.013秒）

+-----+
| sno |
+-----+
|   5 |
|   8 |
+-----+
2行记录（0.000秒）

+-----------------+------+-------+
| sname           | cno  | grade |
+-----------------+------+-------+
| James Johnson   |   11 |    50 |
| James Johnson   |   12 |    80 |
| James Johnson   |   13 |    50 |
| James Johnson   |   14 |    50 |
| James Johnson   |   15 |    50 |
| Michael Brown   |   11 |    70 |
| Michael Brown   |   12 |    65 |
| Michael Brown   |   15 |    70 |
| John Davis      |   11 |    90 |
| John Davis      |   12 |    70 |
| John Davis      |   14 |    80 |
| John Davis      |   15 |    90 |
| Robert Miller   |   11 |    80 |
| Robert Miller   |   12 |    50 |
| Robert Miller   |   13 |    70 |
| Robert Miller   |   14 |    62 |
| Robert Miller   |   15 |    80 |
| William Wilson  |   11 |    40 |
| William Wilson  |   12 |    90 |
| William Wilson  |   13 |    60 |
| William Wilson  |   15 |    61 |
| David Moore     |   11 |    30 |
| David Moore     |   12 |    50 |
| David Moore     |   13 |    40 |
| David Moore     |   14 |    30 |
| David Moore     |   15 |    35 |
| Richard Taylor  |   11 |    90 |
| Richard Taylor  |   12 |    80 |
| Richard Taylor  |   13 |    80 |
| Richard Taylor  |   15 |    80 |
| Joseph Anderson |   12 |    90 |
| Joseph Anderson |   14 |    70 |
| Joseph Anderson |   15 |    95 |
| Charles Thomas  | NULL |  NULL |
+-----------------+------+-------+
34行记录（0.001秒）

+-----+------+
| sno | SG   |
+-----+------+
|   4 |  342 |
|   7 |  330 |
|   3 |  330 |
|   1 |  280 |
|   5 |  251 |
|   6 |  185 |
+-----+------+
6行记录（0.000秒）

总结

恭喜你！你已经完成了这个项目。你可以在 LabEx 中练习更多实验来提升你的技能。

✨ 查看解决方案并练习✨ 查看解决方案并练习✨ 查看解决方案并练习