DB Experiment

以下是一套共 30 道数据库实验 (SYSU-DCS283) 期末考核题目示例，题目均基于所提供的 school 数据库表结构 (STUDENTS, TEACHERS, COURSES, CHOICES) 进行设计。每道题目包含简要描述、标准答案及解析，覆盖了数据查询（单表与多表连接）、数据操作（INSERT、UPDATE、DELETE）、表创建及约束、触发器和事务等主要内容。希望能帮助在复习和练习中全面掌握 SQL Server 的核心技能。

1. 创建新表并设置主键与唯一性约束

题目描述
在 school 数据库中，创建一个名为 Worker 的表，字段包括：

• WID (int，主键)
• Wname (char(20) ，姓名，需要唯一性约束)
• Wage (int，年龄)
• Wgender (char(1)，性别)

要求：

1. 将 WID 作为主键。
2. 为 Wname 添加唯一性约束。
3. 限制 Wage 不超过 30。

标准答案

CREATE TABLE Worker (
    WID INT NOT NULL PRIMARY KEY,
    Wname CHAR(20) NOT NULL UNIQUE,
    Wage INT CHECK (Wage <= 30),
    Wgender CHAR(1)
);

解析

1. PRIMARY KEY 关键字可直接在定义字段时声明，或在表最后统一声明。
2. UNIQUE 关键字可为字段设置唯一性约束。
3. CHECK 约束可限制字段的取值范围。

2. 简单查询 (单表)

题目描述
查询 STUDENTS 表中所有 grade 等于 2001 的学生姓名（sname），并按照 sid 升序排列输出。

标准答案

SELECT sname
FROM STUDENTS
WHERE grade = 2001
ORDER BY sid ASC;

解析

• WHERE 子句筛选出 grade = 2001 的记录。
• ORDER BY 可控制结果集的排序，默认为升序，也可手动指定 ASC。

3. 简单查询 (单表，多字段)

题目描述
查询 TEACHERS 表中所有老师（tid、tname、email）的信息，并按照 salary 降序排列。

标准答案

SELECT tid, tname, email
FROM TEACHERS
ORDER BY salary DESC;

解析

• 仅需使用 SELECT 列出需要的字段。
• ORDER BY salary DESC 进行降序排列。

4. 条件查询与别名

题目描述
查询 COURSES 表中课时 (hour) 大于 40 的课程编号 (cid) 和课程名称 (cname)，并将查询结果中的字段起别名：课程编号 AS 课程代码、课程名称 AS 课程名称。

标准答案

SELECT cid AS 课程代码, cname AS 课程名称
FROM COURSES
WHERE hour > 40;

解析

• 使用 AS 来给查询结果字段起别名。
• WHERE hour > 40 限制显示课时大于 40 的课程。

5. 多表连接查询 (内连接)

题目描述
查询所有选课记录中（CHOICES 表）学生姓名、课程名称、成绩（score）。结果中应包含学生姓名 (STUDENTS 表的 sname) 与课程名称 (COURSES 表的 cname)，且只显示有对应关系的行。

标准答案

SELECT S.sname, C.cname, CH.score
FROM CHOICES CH
INNER JOIN STUDENTS S ON CH.sid = S.sid
INNER JOIN COURSES C ON CH.cid = C.cid;

解析

• 先将 CHOICES 与 STUDENTS 表通过 sid 关联，再通过 CHOICES 与 COURSES 的 cid 关联。
• INNER JOIN 仅返回存在匹配关系的记录。

6. 多表条件查询 (连接 + 条件)

题目描述
查询选修“C0001”课程且成绩大于 80 分的学生姓名及该课程成绩。

标准答案

SELECT S.sname, CH.score
FROM CHOICES CH
JOIN STUDENTS S ON CH.sid = S.sid
WHERE CH.cid = 'C0001'
  AND CH.score > 80;

解析

• 基于 CHOICES 和 STUDENTS 的连接，先找到 cid = ‘C0001’ 的记录，再用 AND 进一步筛选 score > 80 的记录。

7. INSERT 语句 (单条插入)

题目描述
往 STUDENTS 表中插入一条新学生信息，学号 sid = ‘S1009’，姓名 sname = ‘LinX’, email = ‘linx@school.com‘, grade = 2002。

标准答案

INSERT INTO STUDENTS (sid, sname, email, grade)
VALUES ('S1009', 'LinX', 'linx@school.com', 2002);

解析

• 指定字段列表并与 VALUES 的值一一对应。
• 注意如果 sid 是主键，需保证该值无重复。

8. INSERT 语句 (多条插入)

题目描述
在 TEACHERS 表中一次性插入两条新教师数据：

1. tid = ‘T2001’，tname = ‘Wang Wu’，email = ‘wangwu@xxx.com‘，salary = 4500
2. tid = ‘T2002’，tname = ‘Zhao Liu’，email = ‘zhaoliu@xxx.com‘，salary = 2000

标准答案

INSERT INTO TEACHERS (tid, tname, email, salary)
VALUES
('T2001', 'Wang Wu', 'wangwu@xxx.com', 4500),
('T2002', 'Zhao Liu', 'zhaoliu@xxx.com', 2000);

解析

• SQL Server 支持在单条 INSERT 语句中使用多个 VALUES 子句进行批量数据插入。
• 注意字段与 VALUES 的顺序需一致。

9. UPDATE 语句 (单表)

题目描述
将 STUDENTS 表中学号 sid = ‘S1002’ 的学生邮箱 (email) 改为 ‘new_email@school.com‘。

标准答案

UPDATE STUDENTS
SET email = 'new_email@school.com'
WHERE sid = 'S1002';

解析

• 使用 UPDATE 表名 SET 字段 = 值， WHERE 子句确定要更新哪条记录。
• 不加 WHERE 会导致该表所有记录都被更新。

10. DELETE 语句 (单表)

题目描述
删除 TEACHERS 表中工资 (salary) 小于 2500 的所有教师记录。

标准答案

DELETE FROM TEACHERS
WHERE salary < 2500;

解析

• DELETE 语句配合 WHERE 子句定位要删除的行。
• 不带 WHERE 子句则会删除表的所有记录，需谨慎使用。

11. 多表连接查询 (左外连接)

题目描述
查询所有 STUDENTS 表中的学生姓名以及他们在 CHOICES 表中是否有选课记录的成绩信息（score）。即使学生没有选课记录，也需要显示其姓名，score 字段可以显示为 NULL。

标准答案

SELECT S.sname, CH.score
FROM STUDENTS S
LEFT JOIN CHOICES CH ON S.sid = CH.sid;

解析

• LEFT JOIN 会保证左表 (STUDENTS) 的所有记录都被返回，即使右表 (CHOICES) 中无匹配行，右表字段以 NULL 填充。

12. 多表连接查询 (内连接 + 多条件)

题目描述
查询 CHOICES 表中成绩大于 90 分并且对应的教师 salary 大于 5000 的学生姓名、课程名称、教师姓名。

标准答案

SELECT S.sname, C.cname, T.tname
FROM CHOICES CH
JOIN STUDENTS S ON CH.sid = S.sid
JOIN COURSES C ON CH.cid = C.cid
JOIN TEACHERS T ON CH.tid = T.tid
WHERE CH.score > 90
  AND T.salary > 5000;

解析

• 通过 STUDENTS、COURSES、TEACHERS 三表与 CHOICES 连接获取需要的信息。
• WHERE 条件可并列多个限制。

13. 使用别名与表达式

题目描述
查询 TEACHERS 表中所有教师姓名 (tname) 及其工资的 12 个月总额 (命名为 AnnualSalary)。AnnualSalary = salary * 12。

标准答案

SELECT tname,
       salary * 12 AS AnnualSalary
FROM TEACHERS;

解析

• 可在 SELECT 字段时直接使用算术表达式。
• AS AnnualSalary 用来设置输出字段别名。

14. 使用聚合函数 (COUNT)

题目描述
查询 STUDENTS 表中共有多少位学生 (统计行数)，并将结果命名为 StudentCount。

标准答案

SELECT COUNT(*) AS StudentCount
FROM STUDENTS;

解析

• COUNT(*) 会计数表中的所有行。
• 可以使用别名让结果列更具可读性。

15. 使用聚合函数 (MAX、MIN、AVG)

题目描述
查询 TEACHERS 表中最大工资、最小工资以及平均工资，分别命名为 MaxSalary、MinSalary 和 AvgSalary。

标准答案

SELECT 
    MAX(salary) AS MaxSalary,
    MIN(salary) AS MinSalary,
    AVG(salary) AS AvgSalary
FROM TEACHERS;

解析

• 聚合函数可直接应用于一个列上。
• 结果会在一行返回多个聚合值。

16. GROUP BY 与 HAVING

题目描述
查询每门课程的选课人数 (COUNT(sid))，仅显示选课人数大于 3 的课程编号和对应人数。

标准答案

SELECT cid, COUNT(sid) AS StudentCount
FROM CHOICES
GROUP BY cid
HAVING COUNT(sid) > 3;

解析

• GROUP BY cid 先按课程分组，再使用 COUNT(sid) 聚合统计。
• HAVING 相当于对分组后的结果做进一步筛选。

17. DISTINCT 去重

题目描述
查询所有已选课 (存在于 CHOICES 表中) 的学生年级 (grade) 信息，不可重复显示。

标准答案

SELECT DISTINCT S.grade
FROM CHOICES C
JOIN STUDENTS S ON C.sid = S.sid;

解析

• DISTINCT 可去除重复记录。
• 需要和 STUDENTS 表关联获取 grade 字段。

18. 在现有表上增加列

题目描述
在 STUDENTS 表中增加一个新列 phone，类型为 char(11)，允许 NULL 值。

标准答案

ALTER TABLE STUDENTS
ADD phone CHAR(11) NULL;

解析

• 使用 ALTER TABLE … ADD 语句为已存在的表添加列。
• 如果不指定 NULL/NOT NULL，默认为允许 NULL。

19. 修改列类型与名称

题目描述
将 TEACHERS 表中的 salary 列改名为 baseSalary，并将数据类型修改为 decimal(8,2)。

标准答案

EXEC sp_rename 'TEACHERS.salary', 'baseSalary', 'COLUMN';
ALTER TABLE TEACHERS
ALTER COLUMN baseSalary DECIMAL(8,2);

解析

• sp_rename 用于修改列名，参数中需指定原表名.原列名、新列名以及 ‘COLUMN’ 关键字。
• ALTER TABLE … ALTER COLUMN 修改字段数据类型。
• 注意某些情况下需要保证原字段的数据可兼容新的数据类型。

20. 创建视图 (VIEW)

题目描述
创建一个名为 vHighScore 的视图，用于查询所有成绩大于等于 90 分的学生姓名、课程名称、成绩。

标准答案

CREATE VIEW vHighScore
AS
SELECT S.sname, C.cname, CH.score
FROM CHOICES CH
JOIN STUDENTS S ON CH.sid = S.sid
JOIN COURSES C ON CH.cid = C.cid
WHERE CH.score >= 90;

解析

• 视图 (VIEW) 是一个“虚拟表”，可简化查询操作。
• 在 SSMS 中使用 CREATE VIEW 语句，并在 AS 后跟上具体的 SELECT 查询。

21. 删除视图

题目描述
删除上一步创建的视图 vHighScore。

标准答案

DROP VIEW vHighScore;

解析

• DROP VIEW 视图名 用于删除已存在的视图。
• 注意删除后无法直接引用该视图。

22. 创建触发器 (INSERT 或 UPDATE)

题目描述
为 TEACHERS 表创建触发器 T1，当向该表插入或更新记录时，如果 baseSalary（或 salary）低于 3000，则将其自动修改为 3000。

标准答案
下面的示例假设列名已经改为 baseSalary；如果仍是 salary，请相应调整列名。

CREATE TRIGGER T1
ON TEACHERS
FOR INSERT, UPDATE
AS
BEGIN
    -- 将新插入或更新的记录里 baseSalary < 3000 的部分自动修正为 3000
    UPDATE TEACHERS
    SET TEACHERS.baseSalary = 3000
    FROM TEACHERS
    JOIN Inserted I ON TEACHERS.tid = I.tid
    WHERE I.baseSalary < 3000;
END;

解析

• 触发器 (TRIGGER) 在某些指定事件 (INSERT、UPDATE、DELETE) 发生时自动执行。
• Inserted 表是 SQL Server 触发器内的虚拟表，包含新插入或更新的记录。
• 在触发器中可以对 Inserted 中不符合条件的数据做二次处理或拒绝等操作。

23. 创建触发器 (DELETE)

题目描述
在 STUDENTS 表上创建触发器 T_StuDel，当删除学生记录时，需要先将此学生在 CHOICES 中的学习记录删除，然后才允许删除 STUDENTS 中对应记录。

题目提示
可以在触发器中手动先 DELETE CHOICES 表，再 DELETE STUDENTS，也可以使用其他方式保证清理。

标准答案
(一种可能实现方式——先删除 CHOICES 中相关记录，再删除 STUDENTS。)

CREATE TRIGGER T_StuDel
ON STUDENTS
INSTEAD OF DELETE
AS
BEGIN
    DELETE FROM CHOICES
    WHERE sid IN (SELECT sid FROM DELETED);

    DELETE FROM STUDENTS
    WHERE sid IN (SELECT sid FROM DELETED);
END;

解析

• INSTEAD OF DELETE 触发器可拦截原始 DELETE 操作，改为在触发器主体中执行自定义逻辑。
• DELETED 虚拟表中包含被删除的记录信息，可用 sid 匹配引用。

24. 事务处理 (简单示例)

题目描述
往 COURSES 表中插入两条记录 (cid、cname、hour)。如果任意一条插入失败，则回滚事务，保证数据一致性。

标准答案

BEGIN TRANSACTION;

BEGIN TRY
    INSERT INTO COURSES (cid, cname, hour)
    VALUES ('C1005', 'Physics', 60);

    INSERT INTO COURSES (cid, cname, hour)
    VALUES ('C1006', 'Chemistry', 55);

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT '插入失败，事务已回滚。';
END CATCH;

解析

• BEGIN TRANSACTION、COMMIT TRANSACTION、ROLLBACK TRANSACTION 结合使用，对操作进行事务保护。
• BEGIN TRY … BEGIN CATCH 结构便于捕获异常，并在发生异常时回滚。

25. 事务处理 (多表操作)

题目描述
需要同时往 STUDENTS 表和 CHOICES 表各插入一条相关记录。如果插入 STUDENTS 表成功但插入 CHOICES 表失败，则需回滚所有操作。

标准答案

BEGIN TRANSACTION;

BEGIN TRY
    -- 假设待插入学生
    INSERT INTO STUDENTS (sid, sname, email, grade)
    VALUES ('S1010', 'Sun Qi', 'sunqi@school.com', 2003);

    -- 再往 CHOICES 表插入对应选课信息
    INSERT INTO CHOICES (no, sid, tid, cid, score)
    VALUES (1010, 'S1010', 'T1001', 'C0001', 85);

    COMMIT TRANSACTION;
END TRY
BEGIN CATCH
    ROLLBACK TRANSACTION;
    PRINT '出现错误，事务已回滚';
END CATCH;

解析

• 同一事务 (BEGIN TRANSACTION … COMMIT/ROLLBACK) 内的多表操作如果有一处失败，需要回滚所有操作。

26. CHECK 约束

题目描述
在 COURSES 表中新增一个 CHECK 约束，保证课程课时 (hour) 大于 0。

标准答案

ALTER TABLE COURSES
ADD CONSTRAINT CK_COURSES_HOUR CHECK (hour > 0);

解析

• ALTER TABLE … ADD CONSTRAINT 来增加约束。
• CHECK(hour > 0) 可防止插入或更新到非正值课时。

27. FOREIGN KEY 约束的创建

题目描述
尝试在 CHOICES 表上添加一个外键约束 FK_CHOICES_TEACHERS2，要求 tid 必须是 TEACHERS 表中已存在的教师编号 (tid)，一旦在 TEACHERS 中删除记录时，自动删除 CHOICES 中相应的记录 (ON DELETE CASCADE)。

注意：可能与现有外键 FK_CHOICES_TEACHERS 冲突，需先删除或更改已有外键以作演示。

标准答案
(仅作示例，若已存在外键，需要先 DROP 再 CREATE。)

ALTER TABLE CHOICES
ADD CONSTRAINT FK_CHOICES_TEACHERS2
FOREIGN KEY (tid) REFERENCES TEACHERS(tid)
ON DELETE CASCADE;

解析

• 在 CHOICES 中通过外键引用 TEACHERS 的主键，指定 ON DELETE CASCADE 表示当父表记录删除时，子表对应记录也一并删除。
• 需留意与已有外键冲突的问题。

28. UNIQUE 约束的添加

题目描述
在 TEACHERS 表中为 email 字段添加唯一性约束，保证同一老师邮箱不重复。若 email 字段已有重复值，需要先行修改或删除。

标准答案

ALTER TABLE TEACHERS
ADD CONSTRAINT UQ_TEACHERS_EMAIL UNIQUE(email);

解析

• ALTER TABLE … ADD CONSTRAINT … UNIQUE(字段) 用于添加唯一性约束。
• 注意使用前需确保该字段中不存在重复值，否则会报错。

29. 复杂多表查询 (三表连接 + 聚合)

题目描述
查询各个教师 (tid) 所授课程 (名下 CHOICES 记录) 的平均成绩，结果包含 tid、教师姓名、平均成绩(别名 AvgScore)，并按 AvgScore 降序排序。

标准答案

SELECT T.tid, T.tname, AVG(CH.score) AS AvgScore
FROM TEACHERS T
JOIN CHOICES CH ON T.tid = CH.tid
GROUP BY T.tid, T.tname
ORDER BY AvgScore DESC;

解析

• 首先使用 TEACHERS 与 CHOICES 进行关联，根据教师id 分组计算平均成绩。
• GROUP BY T.tid, T.tname 后才能使用聚合函数 AVG(CH.score)。
• ORDER BY AvgScore DESC 降序显示平均成绩。

30. 子查询

题目描述
查询成绩高于 TEACHERS 表平均工资的学生选课记录信息 (sname、课程名称 cname、score)，要求只显示满足“score > 平均 salary”的记录。

(注意：这里将教师的平均工资与学生的分数做对比仅是题目示例，并不一定具有实际业务逻辑。)

标准答案

SELECT S.sname, C.cname, CH.score
FROM CHOICES CH
JOIN STUDENTS S ON CH.sid = S.sid
JOIN COURSES C ON CH.cid = C.cid
WHERE CH.score > (
    SELECT AVG(T.salary) FROM TEACHERS T
);

解析

• 在 WHERE 子句中使用子查询，先求 TEACHERS 表平均工资，再与 CHOICES.score 做比较。
• 子查询返回一个标量值，可直接参与比较运算。

小结

1. 本套题目共 30 道，覆盖了 SQL Server Management Studio (SSMS) 中常见且重要的操作场景，包括数据库对象创建（表、视图、触发器）、数据操作（增删改）、查询（单表、多表、聚合、子查询）、约束（PRIMARY KEY、FOREIGN KEY、CHECK、UNIQUE）以及事务处理等关键内容。
2. 题目设置难度适中，既包含基础语法 (简单增删改查) 又涉及多表连接、外键级联、触发器、事务等稍具复杂性或需要理解机制的考核点。
3. 每道题目的“解析”部分对常见的使用场景、语法形式及注意事项进行了简要说明。学生在完成这些题目后，可有效复习并掌握 SQL Server 操作的核心内容。