一、第一题(第六章)：数据库和表

使用T-SQL语句创建“学生库”，要求它有3个文件，其中主文件为3MB，最大值为100MB，每次增长为2MB，辅数据文件为2MB，最大值不受限制，每次增长5%；日志文件为1MB，最大值为100MB，每次增长10MB。

create database 学生库
on (
	name = 学生数据,
	filename = 'C:\SQLDATA\学生数据.ndf',
	maxsize = 100mb,
	size = 3mb,
	filegrowth = 2mb
),
filegroup 学生辅助文件 (
	name = 学生辅助文件,
	filename = 'C:\SQLDATA\学生辅助文件.ndf',
	size = 2mb,
	maxsize = unlimited,
	filegrowth = 5%
)
log on (
	name = 学生日志,
	filename = 'C:\SQLDATA\学生日志.ldf',
	maxsize = 100mb,
	size = 1mb,
	filegrowth = 10mb
)

使用T-SQL语句在数据库”学生库”中创建表”班级表“和”学生表“，表的结构自拟，要求适当使用数据类型、PRIMARY KEY 约束、FOREIGN KEY约束、CHECK约束、DEFAULT定义、NO NULL定义。

create table 班级表 (
	class_id int primary key identity(1000,10),
	class_name nvarchar(50) not null,
	class_teacher nvarchar(20) not null
)

create table 学生表 (
	student_id int primary key identity(1,1),
	student_name nvarchar(10) not null,
	student_address nvarchar(50) not null,
	phone_number int not null,
	class_id int not null,
	age int not null check(age between 6 and 18),
	entry_date datetime default getdate(),
	constraint FK_student_class foreign key (class_id) references 班级表 (class_id)
)

插入数据

insert into 班级表 values 
	('class1','lilaoshi'),
	('class2','wanglaoshi')

将lilaoshi改为zhaolaoshi

update 班级表 set class_teacher = 'zhaolaoshi' where class_id = 1000

删除class_id的信息

delete from 班级表 where class_id = 1020

修改表结构，将班级表的class_teacher的类型改为varchar(20)

alter table 班级表
alter column class_teacher varchar(10)

二、第二题(第七章)：查询视图索引和游标

已知表结构如下

• 创建数据库

create database 教学库
on (
	name = 教学库数据,
	filename = 'C:\SQLDATA\教学库数据.ndf',
	maxsize = 100mb,
	size = 3mb,
	filegrowth = 2mb
),
filegroup 教学库辅助文件 (
	name = 教学库辅助文件,
	filename = 'C:\SQLDATA\教学库辅助文件.ndf',
	size = 2mb,
	maxsize = unlimited,
	filegrowth = 5%
)
log on (
	name = 教学库日志,
	filename = 'C:\SQLDATA\教学库日志.ldf',
	maxsize = 100mb,
	size = 1mb,
	filegrowth = 10mb
)

• 创建数据表

-- 创建系部表
CREATE TABLE 系部表 (
    D_OP INT PRIMARY KEY,          -- 系部编号，主键
    D_HEAD CHAR(8)                -- 系主任，字符类型
);

-- 创建学生表
CREATE TABLE 学生表 (
    S_NO INT PRIMARY KEY,          -- 学生编号，主键
    S_NAME CHAR(10),              -- 学生姓名，字符类型
    S_AGE INT,                    -- 学生年龄，整数类型
    S_SEX CHAR(10),               -- 学生性别，字符类型
    S_DP int,                -- 系部编号，字符类型
    FOREIGN KEY (S_DP) REFERENCES 系部表(D_OP)  -- 外键约束，引用系部表的 D_OP
);

-- 创建课程表
CREATE TABLE 课程表 (
    C_NO INT PRIMARY KEY,          -- 课程编号，主键
    C_NAME CHAR(12) NOT NULL,     -- 课程名称，字符类型，非空
    C_DP int,                 -- 系部编号，字符类型
    FOREIGN KEY (C_DP) REFERENCES 系部表(D_OP)  -- 外键约束，引用系部表的 D_OP
);

-- 创建成绩表
CREATE TABLE 成绩表 (
    S_NO INT,                      -- 学生编号
    C_NO INT,                      -- 课程编号
    SC_G INT,                      -- 成绩，整数类型
    PRIMARY KEY CLUSTERED (S_NO, C_NO),  -- 以 S_NO 和 C_NO 为主键，聚集索引
    FOREIGN KEY (S_NO) REFERENCES 学生表(S_NO),   -- 外键约束，引用学生表的 S_NO
    FOREIGN KEY (C_NO) REFERENCES 课程表(C_NO)    -- 外键约束，引用课程表的 C_NO
);

• 插入数据

INSERT INTO 系部表 (D_OP, D_HEAD) VALUES
(1, '计算机系'),
(2, '物理系'),
(3, '化学系'),
(4, '外语系'),
(5, '数学系'),
(6, '生物系');

-- 插入学生表数据
INSERT INTO 学生表 (S_NO, S_NAME, S_AGE, S_SEX, S_DP) VALUES
(1001, '陈雪', 20, '女', 1),
(1002, '李雷', 21, '男', 1),
(1003, '王芳', 22, '女', 2),
(1004, '杨洋', 23, '男', 2),
(1005, '郑敏', 20, '女', 3),
(1006, '刘强', 22, '男', 4),
(1007, '张杰', 21, '男', 5),
(1008, '刘涛', 22, '女', 6),
(1009, '赵丽', 20, '女', 1),
(1010, '孙强', 23, '男', 3),
(1011, '李华', 24, '女', 5),
(1012, '王明', 21, '男', 2);

-- 插入课程表数据
INSERT INTO 课程表 (C_NO, C_NAME, C_DP) VALUES
(101, '数据结构', 1),
(102, '操作系统', 1),
(103, '量子物理', 2),
(104, '英语口语', 4),
(105, '微积分', 3),
(106, '数据库原理', 1),
(107, '电磁学', 2),
(108, '有机化学', 3),
(109, '法语', 4),
(110, '线性代数', 5),
(111, '生态学', 6),
(112, '统计学', 5);

-- 插入成绩表数据
INSERT INTO 成绩表 (S_NO, C_NO, SC_G) VALUES
(1001, 101, 85),
(1001, 102, 90),
(1002, 101, 78),
(1002, 103, 88),
(1003, 104, 92),
(1004, 105, 75),
(1005, 102, 80),
(1006, 105, 89),
(1003, 101, 95),
(1004, 103, 82),
(1007, 106, 88),
(1008, 107, 90),
(1009, 108, 87),
(1010, 109, 76),
(1011, 110, 94),
(1012, 111, 85),
(1001, 106, 93),
(1002, 104, 79),
(1003, 107, 88),
(1004, 110, 81),
(1005, 109, 95),
(1006, 108, 86),
(1007, 102, 80),
(1008, 105, 78),
(1009, 111, 91),
(1010, 112, 82),
(1011, 106, 94),
(1012, 109, 88);

使用 select语句和聚合函数，统计教学库数据库内各系部的男、女学生人数和男、女学生的平均年龄。

select 系部表.D_HEAD as '系部',学生表.S_SEX as '性别',
count(学生表.S_NAME) as '数量',avg(学生表.S_Age) as '平均年龄'  
from 学生表 
join 系部表 on 系部表.D_OP = 学生表.S_DP
group by 学生表.S_SEX,系部表.D_HEAD

根据给出的学生学号，从教学库查询该学生的姓名和各门功课的课程名、成绩

select 学生表.S_Name,课程表.C_NAME,成绩表.SC_G
from 学生表 join 成绩表 on 学生表.S_NO = 成绩表.S_NO
join 课程表 on 课程表.C_NO = 成绩表.C_NO 
where 学生表.S_NO = '1005'

设计一个视图，根据给定的课程名，从教学库查询所有学生修读该课程的成绩，要求按照系部升序输出学生学号，姓名，课程名，成绩

CREATE VIEW 学生成绩视图 AS
SELECT 
    学生表.S_NO AS '学生学号',
    学生表.S_NAME AS '学生姓名',
    课程表.C_NAME AS '课程名',
    成绩表.SC_G AS '成绩'
FROM 
    学生表 
JOIN 
    成绩表 ON 学生表.S_NO = 成绩表.S_NO
JOIN 
    课程表 ON 成绩表.C_NO = 课程表.C_NO
JOIN 
    系部表 ON 学生表.S_DP = 系部表.D_OP;

查询所有女生的学号、姓名和所修读课程的平均成绩，要求按学号升序输出

select 学生表.S_NO,学生表.S_NAME,avg(成绩表.SC_G) as '所修读课程的平均成绩' from 成绩表
join 学生表 on 学生表.S_NO = 成绩表.S_NO where 学生表.S_SEX = '女'
group by 学生表.S_NO,学生表.S_NAME
order by 学生表.S_NO 

查询各系部的课程数量，并按照课程数量降序排列

select 系部表.D_HEAD,count(课程表.C_NAME) from 系部表
join 课程表 on 系部表.D_OP =  课程表.C_DP
group by 系部表.D_HEAD
order by count(课程表.C_NAME) Desc

查询成绩在90分以上的学生姓名及其对应课程名，并按学生姓名升序排列

select 学生表.S_NAME,课程表.C_NAME,成绩表.SC_G from 学生表
join 成绩表 on  学生表.S_NO = 成绩表.S_NO
join 课程表 on 课程表.C_NO = 成绩表.C_NO 
where 成绩表.SC_G > 90
group by 学生表.S_NAME,课程表.C_NAME,成绩表.SC_G
order by 学生表.S_NAME

查询所有学生的姓名、性别、所修课程的成绩，并计算每个课程的通过率（成绩大于等于60分的学生数量占总学生数量的比例），要求按照课程名升序输出

SELECT 
    课程表.C_NAME AS '课程名',
    COUNT(CASE WHEN 成绩表.SC_G >= 60 THEN 1 END) * 1.0 / COUNT(*) AS '通过率'
FROM 
    成绩表 
JOIN 
    课程表 ON 成绩表.C_NO = 课程表.C_NO
JOIN 
    学生表 ON 成绩表.S_NO = 学生表.S_NO
GROUP BY 
    课程表.C_NAME
ORDER BY 
    课程表.C_NAME;

三、第三题(第二章)：关系运算

设有3个关系:①S(学号,姓名,年龄,性别);②SC(学号,课程号,成绩);③C(课程号,课程名，教师)。试用关系代数表达式表示下列查询语句。

(1)检索李老师所投课程的课程号、课程名。

(2)检索学号为S173100311学生所学课程的课程名与任课教师名。

(3)检索至少选修了李老师所授课程中1门课程的女学生的姓名。

(4)检索年龄大于20岁的男学生的学号与姓名。

(5)检索张同学不学的课程的课程号。

(6)检索至少选修了两门课程的学生学号。

(7)检索全部学生都选修的课程的课程号与课程名。

(8)检索选修课程包含李老师所授课程的学生的学号。

(9)在教学数据库S、SC、C中，用户用一查询语句检索女同学修读课程的课程名和任课教师名。

---

1. 检索李老师所投课程的课程号、课程名

查询要求: 需要找到所有李老师所投课程的课程号和课程名。

关系:

• C(课程号, 课程名, 教师)

步骤:

1. 筛选: 筛选出教师是“李老师”的课程。使用选择操作 ​\sigma 来实现。

   \sigma_{\text{教师} = '李老师'}(\text{C})

2. 投影: 然后，我们需要从筛选结果中提取课程号和课程名。使用投影操作 ​\pi 来实现。

   \pi_{\text{课程号}, \text{课程名}}(\sigma_{\text{教师} = '李老师'}(\text{C}))

   最终表达式:

2. 检索学号为 S173100311 学生所学课程的课程名与任课教师名

查询要求: 需要找到学号为 S173100311 的学生所学课程的课程名和任课教师名。

关系:

• S(学号, 姓名, 年龄, 性别)
• SC(学号, 课程号, 成绩)
• C(课程号, 课程名, 教师)

步骤:

1. 筛选: 筛选出学号为 S173100311 的学生的选课记录。使用选择操作 ​\sigma。

   \sigma_{\text{学号} = 'S173100311'}(\text{SC})

2. 连接: 将选课表 SC 与课程表 C 进行自然连接（通过课程号）。连接后，我们就可以获取课程的课程名和教师名。

   \sigma_{\text{学号} = 'S173100311'}(\text{SC}) \bowtie \text{C}

3. 投影: 从连接后的结果中提取课程名和教师名。使用投影操作 ​\pi。

   \pi_{\text{课程名}, \text{教师}}(\sigma_{\text{学号} = 'S173100311'}(\text{SC}) \bowtie \text{C})

最终表达式:

3. 检索至少选修了李老师所授课程中 1 门课程的女学生的姓名

查询要求: 需要找到至少选修了李老师所授课程中 1 门课程的所有女学生的姓名。

关系:

• S(学号, 姓名, 年龄, 性别)
• SC(学号, 课程号, 成绩)
• C(课程号, 课程名, 教师)

步骤:

1. 筛选: 先筛选出所有女学生的信息。使用选择操作 ​\sigma 来筛选性别为 "女" 的学生。

   \sigma_{\text{性别} = '女'}(\text{S})

2. 筛选李老师授课的课程: 从课程表 C 中筛选出李老师授课的课程。

   \sigma_{\text{教师} = '李老师'}(\text{C})

3. 连接: 连接 SC 和 C，找出选修了李老师授课的课程的学生。

   \sigma_{\text{教师} = '李老师'}(\text{C}) \bowtie \text{SC}

4. 获取学号: 获取选修了李老师课程的学生的学号。

   \pi_{\text{学号}}(\sigma_{\text{教师} = '李老师'}(\text{C}) \bowtie \text{SC})

5. 连接与筛选: 将女学生表 S 与选修李老师课程的学生学号进行连接，得到最终结果。

   \sigma_{\text{性别} = '女'}(\text{S}) \bowtie (\pi_{\text{学号}}(\sigma_{\text{教师} = '李老师'}(\text{C}) \bowtie \text{SC}))

6. 投影: 最后，提取女学生的姓名。

   \pi_{\text{姓名}}(\sigma_{\text{性别} = '女'}(\text{S}) \bowtie (\pi_{\text{学号}}(\sigma_{\text{教师} = '李老师'}(\text{C}) \bowtie \text{SC})))

最终表达式:

4. 检索年龄大于 20 岁的男学生的学号与姓名

查询要求: 找出年龄大于 20 岁且性别为男的学生的学号和姓名。

关系:

• S(学号, 姓名, 年龄, 性别)

步骤:

1. 筛选: 筛选出年龄大于 20 且性别为男的学生。

   \sigma_{\text{年龄} > 20 \land \text{性别} = '男'}(\text{S})

2. 投影: 从筛选结果中提取学号和姓名。

   \pi_{\text{学号}, \text{姓名}}(\sigma_{\text{年龄} > 20 \land \text{性别} = '男'}(\text{S}))

最终表达式:

5. 检索张同学不学的课程的课程号

查询要求: 找出张同学不选修的课程的课程号。

关系:

• SC(学号, 课程号, 成绩)
• C(课程号, 课程名, 教师)

步骤:

1. 所有课程号: 获取所有课程的课程号。

   \pi_{\text{课程号}}(\text{C})

2. 张同学选修的课程号: 获取张同学选修的所有课程号。

   \pi_{\text{课程号}}(\sigma_{\text{学号} = 'S173100300'}(\text{SC}))

3. 集合差: 使用差集操作（-）找到张同学没有选修的课程。

   \pi_{\text{课程号}}(\text{C}) - \pi_{\text{课程号}}(\sigma_{\text{学号} = 'S173100300'}(\text{SC}))

最终表达式:

6. 检索至少选修了两门课程的学生学号

查询要求: 找出选修了至少两门课程的学生的学号。

关系:

• SC(学号, 课程号, 成绩)

步骤:

1. 分组: 根据学号分组，计算每个学生选修的课程数。这个操作在关系代数中较难直接表达，通常需要通过合适的扩展操作来完成。
2. 筛选: 选择选修课程数大于等于 2 的学生学号。

最终表达式（假设我们能够进行分组操作）：

7. 检索全部学生都选修的课程的课程号与课程名

查询要求: 找出所有学生都选修的课程。

关系:

• SC(学号, 课程号, 成绩)
• S(学号, 姓名, 年龄, 性别)

步骤:

1. 除法操作: 使用除法操作来查找所有学生都选修的课程。

   (\pi_{\text{课程号}}(\text{SC})) \div (\pi_{\text{学号}}(\text{S}))

2. 投影: 提取课程号和课程名。

   \pi_{\text{课程号}, \text{课程名}}((\pi_{\text{课程号}}(\text{SC})) \div (\pi_{\text{学号}}(\text{S})))

最终表达式:

8. 检索选修课程包含李老师所授课程的学生的学号

查询要求: 找出选修了李老师所授课程的学生的学号。

关系:

• SC(学号, 课程号, 成绩)
• C(课程号, 课程名, 教师)

步骤:

1. 筛选李老师授课的课程:

   \sigma_{\text{教师} = '李老师'}(\text{C})

2. 连接: 与选课表 SC 连接，找出选修李老师课程的学生。

   (\sigma_{\text{教师} = '李老师'}(\text{C})) \bowtie \text{SC}

3. 投影: 从连接结果中提取学号。

   \pi_{\text{学号}}((\sigma_{\text{教师} = '李老师'}(\text{C})) \bowtie \text{SC})

最终表达式:

9. 检索女同学修读课程的课程名和任课教师名

查询要求: 找出所有女学生修读课程的课程名和教师名。

关系:

• S(学号, 姓名, 年龄, 性别)
• SC(学号, 课程号, 成绩)
• C(课程号, 课程名, 教师)

步骤:

1. 筛选女学生:

   \sigma_{\text{性别} = '女'}(\text{S})

2. 连接选课表和课程表:

   \text{SC} \bowtie \text{C}

3. 连接女学生与选课结果:

   \sigma_{\text{性别} = '女'}(\text{S}) \bowtie (\text{SC} \bowtie \text{C})

4. 投影: 提取课程名和教师名。

   \pi_{\text{课程名}, \text{教师}}(\sigma_{\text{性别} = '女'}(\text{S}) \bowtie (\text{SC} \bowtie \text{C}))

最终表达式: