题目

关系代数[1]与 SQL 语句题（共 40 分）已知关系模式[2]如下：学生表 Student(Sno, Sname, Sage, Sdept)课程表 Course(Cno, Cname, Ccredit)选课表 SC(Sno, Cno, Grade)（一）关系代数题（每题 5 分，共 15 分）1. 查询所有计算机系（CS）学生的学号和姓名。2. 查询选修了课程号为 “C01” 的学生学号。3. 查询选修了课程名为 “数据库” 的学生姓名。（二）SQL 语句题（每题 5 分，共 25 分）1. 创建学生表 Student，包含字段：学号（主键）、姓名、年龄、所在系。2. 向 Course 表中插入一条记录：课程号 C10，课程名 “人工智能”，学分 3。3. 查询所有年龄大于 20 岁的学生姓名和年龄。4. 查询选修了 “数据库” 课程且成绩大于等于 90 分的学生姓名。5. 创建一个视图，显示每个学生的学号、姓名及其选修课程的平均成绩。

关系代数[1]与 SQL 语句题（共 40 分）

已知关系模式[2]如下：

学生表 Student(Sno, Sname, Sage, Sdept)

课程表 Course(Cno, Cname, Ccredit)

选课表 SC(Sno, Cno, Grade)

（一）关系代数题（每题 5 分，共 15 分）

1. 查询所有计算机系（CS）学生的学号和姓名。

2. 查询选修了课程号为 “C01” 的学生学号。

3. 查询选修了课程名为 “数据库” 的学生姓名。

（二）SQL 语句题（每题 5 分，共 25 分）

1. 创建学生表 Student，包含字段：学号（主键）、姓名、年龄、所在系。

2. 向 Course 表中插入一条记录：课程号 C10，课程名 “人工智能”，学分 3。

3. 查询所有年龄大于 20 岁的学生姓名和年龄。

4. 查询选修了 “数据库” 课程且成绩大于等于 90 分的学生姓名。

5. 创建一个视图，显示每个学生的学号、姓名及其选修课程的平均成绩。

题目解答

答案

(一) 关系代数题

  1. $\pi_{Sno, Sname}(\sigma_{Sdept = 'CS'}(Student))$
  2. $\pi_{Sno}(\sigma_{Cno = 'C01'}(SC))$
  3. $\pi_{Sname}(\sigma_{Cname = '数据库'}(Course) \bowtie SC \bowtie Student)$ 或 $\pi_{Sname}(Student \bowtie (\pi_{Sno}(\sigma_{Cname = '数据库'}(Course) \bowtie SC)))$

(二) SQL语句题 

  1. CREATE TABLE Student (
 Sno CHAR(10) PRIMARY KEY,
 Sname VARCHAR(20),
 Sage INT,
 Sdept VARCHAR(20)
);
  2. INSERT INTO Course (Cno, Cname, Ccredit) VALUES ('C10', '人工智能', 3);
  3. SELECT Sname, Sage
FROM Student
WHERE Sage > 20;
  4. SELECT Sname
FROM Student
JOIN SC ON Student.Sno = SC.Sno
JOIN Course ON SC.Cno = Course.Cno
WHERE Course.Cname = '数据库' AND SC.Grade >= 90;
  5. CREATE VIEW StudentAvgGrade AS
SELECT Student.Sno, Student.Sname, AVG(SC.Grade) AS AvgGrade
FROM Student
JOIN SC ON Student.Sno = SC.Sno
GROUP BY Student.Sno, Student.Sname;

解析

本题主要考查关系代数和 SQL 语句的运用，解题思路如下：

（一）关系代数题

  1. 查询所有计算机系（CS）学生的学号和姓名
    • 本题考查对关系代数选择和投影操作的理解。解题思路是先从学生表 Student 中选择出所在系为 “CS” 的记录，然后从这些记录中投影出学号和姓名。
    • 解析：
      • 选择操作 $\sigma_{Sdept = 'CS'}(Student)$ 用于从 Student 表中筛选出 Sdept 字段值为 “CS” 的所有记录。
      • 投影操作 $\pi_{Sno, Sname}$ 用于从筛选后的记录中提取出 Sno（学号）和 Sname（姓名）字段。
      • 所以最终的关系代数表达式为 $\pi_{Sno, Sname}(\sigma_{Sdept = 'CS'}(Student))$。
  2. 查询选修了课程号为 “C01” 的学生学号
    • 本题考查对关系代数选择和投影操作的运用。解题思路是先从选课表 SC 中选择出课程号为 “C01” 的记录，然后从这些记录中投影出学生学号。
    • 解析：
      • 选择操作 $\sigma_{Cno = 'C01'}(SC)$ 用于从 SC 表中筛选出 Cno 字段值为 “C01” 的所有记录。
      • 投影操作 $\pi_{Sno}$ 用于从筛选后的记录中提取出 Sno（学号）字段。
      • 所以最终的关系代数表达式为 $\pi_{Sno}(\sigma_{Cno = 'C01'}(SC))$。
  3. 查询选修了课程名为 “数据库” 的学生姓名
    • 本题考查对关系代数连接、选择和投影操作的综合运用。解题思路有两种，一种是先将 Course 表、SC 表和 Student 表进行连接，然后选择出课程名为 “数据库” 的记录，最后投影出学生姓名；另一种是先从 Course 表和 SC 表中找出选修 “数据库” 课程的学生学号，再与 Student 表连接并投影出学生姓名。
    • 解析：
      • 方法一：
        • 连接操作 $\sigma_{Cname = '数据库'}(Course) \bowtie SC \bowtie Student$ 先将 Course 表、SC 表和 Student 表进行连接，同时选择出 Cname 字段值为 “数据库” 的记录。
        • 投影操作 $\pi_{Sname}$ 用于从连接后的记录中提取出 Sname（姓名）字段。
        • 所以表达式为 $\pi_{Sname}(\sigma_{Cname = '数据库'}(Course) \bowtie SC \bowtie Student)$。
      • 方法二：
        • 先进行连接操作 $\sigma_{Cname = '数据库'}(Course) \bowtie SC$，从 Course 表和 SC 表中找出选修 “数据库” 课程的记录。
        • 投影操作 $\pi_{Sno}$ 用于从这些记录中提取出 Sno（学号）字段。
        • 再将得到的学号与 Student 表进行连接 `Student \bowtie (\pi{Sno}(\sigma{Cname = '数据库'}(Course) \bowtie SC))$。
        • 最后投影操作 $\pi_{Sname}$ 用于从连接后的记录中提取出 Sname（姓名）字段。
        • 所以表达式为 $\pi_{Sname}(Student \bowtie (\pi_{Sno}(\sigma_{Cname = '数据库'}(Course) \bowtie SC)))$。

（二）SQL 语句题

  1. 创建学生表 Student，包含字段：学号（主键）、姓名、年龄、所在系
    • 本题考查 SQL 中创建表的语句。解题思路是使用 CREATE TABLE 语句定义表名和各个字段的名称、数据类型，并指定主键。
    • 解析：
      • CREATE TABLE Student 用于创建名为 Student 的表。
      • Sno CHAR(10) PRIMARY KEY 定义 Sno 字段为字符类型，长度为 10，并将其设置为主键。
      • Sname VARCHAR(20) 定义 Sname 字段为可变长度字符串类型，最大长度为 20。
      • Sage INT 定义 Sage 字段为整数类型。
      • Sdept VARCHAR(20) 定义 Sdept 字段为可变长度字符串类型，最大长度为 20。
      • 所以完整的 SQL 语句为 CREATE TABLE Student ( Sno CHAR(10) PRIMARY KEY, Sname VARCHAR(20), Sage INT, Sdept VARCHAR(20) );。
  2. 向 Course 表中插入一条记录：课程号 C10，课程名 “人工智能”，学分 3
    • 本题考查 SQL 中插入数据的语句。解题思路是使用 INSERT INTO 语句指定要插入数据的表名和字段，然后使用 VALUES 关键字提供具体的插入值。
    • 解析：
      • INSERT INTO Course (Cno, Cname, Ccredit) 表示要向 Course 表的 Cno、Cname 和 Ccredit 字段插入数据。
      • VALUES ('C10', '人工智能', 3) 提供了具体的插入值，分别对应课程号、课程名和学分。
      • 所以完整的 SQL 语句为 INSERT INTO Course (Cno, Cname, Ccredit) VALUES ('C10', '人工智能', 3);。
  3. 查询所有年龄大于 20 岁的学生姓名和年龄
    • 本题考查 SQL 中查询语句的使用。解题思路是使用 SELECT 语句指定要查询的字段，从 Student 表中筛选出年龄大于 20 岁的记录。
    • 解析：
      • SELECT Sname, Sage 表示要查询 Sname（姓名）和 Sage（年龄）字段。
      • FROM Student 表示从 Student 表中查询数据。
      • WHERE Sage > 20 用于筛选出 Sage 字段值大于 20 的记录。
      • 所以完整的 SQL 语句为 SELECT Sname, Sage FROM Student WHERE Sage > 20;。
  4. 查询选修了 “数据库” 课程且成绩大于等于 90 分的学生姓名
    • 本题考查 SQL 中多表连接和条件查询的运用。解题思路是使用 JOIN 关键字将 Student 表、SC 表和 Course 表进行连接，然后根据课程名和成绩条件筛选出符合要求的记录，最后查询出学生姓名。
    • 解析：
      • SELECT Sname 表示要查询 Sname（姓名）字段。
      • FROM Student JOIN SC ON Student.Sno = SC.Sno JOIN Course ON SC.Cno = Course.Cno 用于将 Student 表、SC 表和 Course 表进行连接，连接条件分别是 Student 表和 SC 表的 Sno 字段相等，SC 表和 Course 表的 Cno 字段相等。
      • WHERE Course.Cname = '数据库' AND SC.Grade >= 90 用于筛选出课程名为 “数据库” 且成绩大于等于 90 分的记录。
      • 所以完整的 SQL 语句为 SELECT Sname FROM Student JOIN SC ON Student.Sno = SC.Sno JOIN Course ON SC.Cno = Course.Cno WHERE Course.Cname = '数据库' AND SC.Grade >= 90;。
  5. 创建一个视图，显示每个学生的学号、姓名及其选修课程的平均成绩
    • 本题考查 SQL 中创建视图的语句。解题思路是使用 CREATE VIEW 语句定义视图名，然后使用 SELECT 语句查询每个学生的学号、姓名和选修课程的平均成绩，通过 GROUP BY 子句按学生学号和姓名进行分组。
    • 解析：
      • CREATE VIEW StudentAvgGrade 用于创建名为 StudentAvgGrade 的视图。
      • SELECT Student.Sno, Student.Sname, AVG(SC.Grade) AS AvgGrade 表示要查询 Student 表的 Sno（学号）和 Sname（姓名）字段，以及 SC 表中成绩的平均值，并将其命名为 AvgGrade。
      • FROM Student JOIN SC ON Student.Sno = SC.Sno 用于将 Student 表和 SC 表进行连接，连接条件是 Student 表和 SC 表的 Sno 字段相等。
      • GROUP BY Student.Sno, Student.Sname 用于按学生学号和姓名进行分组，以便计算每个学生的平均成绩。
      • 所以完整的 SQL 语句为 CREATE VIEW StudentAvgGrade AS SELECT Student.Sno, Student.Sname, AVG(SC.Grade) AS AvgGrade FROM Student JOIN SC ON Student.Sno = SC.Sno GROUP BY Student.Sno, Student.Sname;。