附录C 上机实验 C.1 第4章上机实验
下列实验均使用SQL Server 的SSMS工具实现..
1．用图形化方法创建符合如下条件的数据库创建数据库的方法可参见本
书附录A：
数据库名为：学生数据库
主要数据文件的逻辑文件名为：Students_data;存放在D:\\Data文件夹下若D:盘中无此文件夹;请先建立此文件夹;然后再创建数据库..;初始大小为：5MB;增长方式为自动增长;每次增加1MB..
日志文件的逻辑文件名字为：Students_log;也存放在D:\\Data文件夹下;初始大小为：2MB;增长方式为自动增长;每次增加10%..
2．选用已建立的“学生数据库”;写出创建满足表C-1到4-4条件的表的SQL
语句;并执行所写代码..注：“说明”部分不作为表定义内容
表C-1 Student表结构 说明 数据类型 学号 普通编码定长字符串;长度为7 姓名 普通编码定长字符串;长度为10 性别 普通编码定长字符串;长度为2 年龄 微整型tinyint 所在系 普通编码不定长字符串;长度为20 身份证普通编码定长字符串;长号 度为10 列名 Sno Sname Ssex Sage Sdept Sid 约束 主键 非空 取值范围：{男;女} 取值范围：15-45 默认值为“计算机系” 取值不重
Sdate 入学日日期 期 说明 课程号 课程名 默认为系统当前日期 列名 Cno Cname Credit 学时数 Semester 学分 列名 Sno Cno Grade 列名 Tno Tname Salary 说明 学号 课程号 成绩 表C-2 Course表结构 数据类型 约束 普通编码定长字符串;长主键 度为10 普通编码不定长字符串;非空 长度为20 整型 取值大于0 小整型 表C-3 SC表结构
数据类型 普通编码定长字符串;长度为7 普通编码定长字符串;长度为10 小整型 约束 主键;引用Student的外键 主键;引用Course的外键 取值范围为0-100 表C-4 Teacher表结构 说明 数据类型 约束 教师号 普通编码定长字符串;长度为8 非空 教师名 普通编码定长字符串;长度为10 非空 定点小数;小数点前4位;小数工资 点后2位 3. 写出实现如下功能的SQL语句;并执行所写代码;查看执行结果..
（1） 在（2） 为
Teacher表中添加一个职称列;列名为：Title;类型为nchar4.. Teacher表中的Title列增加取值范围约束;取值范围为：{教授;
副教授;讲师}..
（3） 将
Course表中Credit列的类型改为：tinyint.. Student表中的Sid和Sdate列..
（4） 删除（5） 为
Teacher表添加主键约束;其主键为：Tno..
C.2 第5章上机实验
本实验均在SQL Server 的SSMS工具中实现..首先在已创建的“学生数据库”中创建本章表5-1~5-3所示的Student、Course和SC表;并插入表5-4~5-6所示数据;然后编写实现如下操作的SQL语句;执行所写的语句;并查看执行结果..
1. 2. 3. 4. 5. 6. 7. 8. 9.
查询SC表中的全部数据..
查询计算机系学生的姓名和年龄..
查询成绩在70～80分的学生的学号、课程号和成绩.. 查询计算机系年龄在18～20岁的男生姓名和年龄.. 查询C001课程的最高分..
查询计算机系学生的最大年龄和最小年龄.. 统计每个系的学生人数..
统计每门课程的选课人数和最高成绩..
统计每个学生的选课门数和考试总成绩;并按选课门数升序显示结果..
10. 列出总成绩超过11. 查询选了
200的学生的学号和总成绩..
C002课程的学生姓名和所在系..
80分以上的学生姓名、课程号和成绩;并按成绩降序排
12. 查询考试成绩
列结果..
13. 查询与
VB在同一学期开设的课程的课程名和开课学期..
14. 查询与李勇年龄相同的学生的姓名、所在系和年龄..
15. 查询哪些课程没有学生选修;列出课程号和课程名..
16. 查询每个学生的选课情况;包括未选课的学生;列出学生的学号、姓名、
选的课程号..
17. 查询计算机系哪些学生没有选课;列出学生姓名.. 18. 查询计算机系年龄最大的三个学生的姓名和年龄..
19. 列出“VB”课程考试成绩前三名的学生的学号、姓名、所在系和
VB成
绩..
20. 查询选课门数最多的前
2位学生;列出学号和选课门数..
21. 查询计算机系学生姓名、年龄和年龄情况;其中年龄情况为：如果年龄
小于18;则显示“偏小”；如果年龄在18-22;则显示“合适”；如果年龄大于22;则显示“偏大”..
22. 统计每门课程的选课人数;包括有人选的课程和没有人选的课程;列出
课程号;选课人数及选课情况;其中选课情况为：如果此门课程的选课人数超过100人;则显示“人多”；如果此门课程的选课人数在40～100;则显示“一般”；如果此门课程的选课人数在1～40;则显示“人少”；如果此门课程没有人选;则显示“无人选”..
23. 查询计算机系选了
VB课程的学生姓名、所在系和考试成绩;并将结果
保存到新表VB_Grade中..
24. 统计每个系的女生人数;并将结果保存到新表25. 用子查询实现如下查询：
Girls中..
1查询选了“C001”课程的学生姓名和所在系..
2查询通信工程系成绩80分以上的学生的学号和姓名..
3查询计算机系考试成绩最高的学生的姓名.. 4查询年龄最大的男生的姓名、所在系和年龄..
26. 查询C001课程的考试成绩高于该课程平均成绩的学生的学号和成绩.. 27. 查询计算机系学生考试成绩高于计算机系学生平均成绩的学生的姓名、
考试的课程名和考试成绩..
28. 查询
VB课程考试成绩高于VB平均成绩的学生姓名和VB成绩..
VB的学生姓名和所在系..
29. 查询没选
30. 查询每个学期学分最高的课程信息;列出开课学期、课程名和学分.. 31. 查询每门课程考试成绩最高的学生信息;列出课程号、学生姓名和最高
成绩;结果按课程号升序排序;不包括没考试的课程..
32. 创建一个新表;表名为
test;其结构为COL1; COL 2; COL 3;其中;
COL1：整型;允许空值..
COL2：普通编码定长字符型;长度为10;不允许空值.. COL3：普通编码定长字符型;长度为10;允许空值.. 试写出按行插入如下数据的语句空白处表示是空值..
COL1 COL2 B1 COL3 C2 1 2 B2 B3 33. 利用23题建立的VB_Grade表;将信息管理系选了VB课程的学生姓名、
所在系和考试成绩插入到VB_Grade表中..
34. 将所有选修
C001课程的学生的成绩加10分..
10分..
35. 将计算机系所有学生的“计算机文化学”的考试成绩加
36. 修改“VB”课程的考试成绩;如果是通信工程系的学生;则增加10分；
如果是信息管理系的学生则增加5分;其他系的学生不加分..
37. 删除成绩小于38. 删除计算机系
50分的学生的选课记录..
VB考试成绩不及格学生的VB选课记录..
VB修课记录..
39. 删除“VB”考试成绩最低的学生的40. 删除没人选的课程的基本信息..
C.3 第6章上机实验
下列实验均使用SQL Server 的SSMS工具实现..利用第4章上机实验创建的“学生数据库”中Student、Course和SC表;完成下列实验..
1.
写出实现下列操作的SQL语句;并执行所写代码..
1在Student表上为Sname列建立一个聚集索引;索引名为：IdxSno..
提示：若执行创建索引的代码;请先删除该表的主键约束
2在Course表上为Cname列建立一个唯一的非聚集索引;索引名为：
IdxCN
3在SC表上为Sno和Cno建立一个组合的聚集索引;索引名为：
IdxSnoCno..提示：若执行创建索引的代码;请先删除该表的主键约束
4删除Sname列上建立的IdxSno索引..
2.
写出创建满足下述要求的视图的SQL语句;并执行所写代码.. 1查询学生的学号、姓名、所在系、课程号、课程名、课程学分.. 2查询学生的学号、姓名、选修的课程名和考试成绩..
3统计每个学生的选课门数;要求列出学生学号和选课门数..
4统计每个学生的修课总学分;要求列出学生学号和总学分说明：考试成
绩大于等于60才可获得此门课程的学分..
3.
利用第2题建立的视图;完成如下查询..
1查询考试成绩大于等于90分的学生的姓名、课程名和成绩.. 2查询选课门数超过3门的学生的学号和选课门数..
3查询计算机系选课门数超过3门的学生的姓名和选课门数..
4查询修课总学分超过10分的学生的学号、姓名、所在系和修课总学
分..
5查询年龄大于等于20岁的学生中;修课总学分超过10分的学生的姓名、
年龄、所在系和修课总学分..
4.
修改第3题4定义的视图;使其查询每个学生的学号、总学分以及总的选课门数..
C.4 第7章上机实验
利用第5章建立的学生数据库以及Student、Coures和SC表;完成下
列操作..
1.
创建满足如下要求的后触发型触发器.. 1学生的考试成绩必须在0-100之间.. 2不能删除成绩不及格的考试记录.. 3每个学期开设的课程总学分不能超过20.. 4每个学生每学期选的课程不能超过5门..
2.
创建满足如下要求的存储过程..
1查询每个学生的修课总学分;要求列出学生学号及总学分..
2查询学生的学号、姓名、修的课程号、课程名、课程学分;将学生所在
的系作为输入参数;执行此存储过程;并分别指定一些不同的输入参数值..
3查询指定系的男生人数;其中系为输入参数;人数为输出参数.. 4删除指定学生的修课记录;其中学号为输入参数..
5修改指定课程的开课学期..输入参数为：课程号和修改后的开课学
期..
C.5 第11章上机实验
利用第4、5章建立的学生数据库和其中的Student、Course、SC表;并利用SSMS工具完成下列操作..
1.
用SSMS工具建立SQL Server身份验证模式的登录名：log1、log2和log3..
2.
用log1建立一个新的数据库引擎查询;在“可用数据库”下列列表框中是否能选中学生数据库 为什么
3.
用系统管理员身份建立一个新的数据库引擎查询;将log1、log2和log3映射为学生数据库中的用户;用户名同登录名..
4.
在log1建立的数据库引擎查询中;现在在“可用数据库”下列列表框中是否能选中学生数据库 为什么
5.
在log1建立的数据库引擎查询中;选中学生数据库;执行下述语句;能
否成功 为什么
SELECT FROM Course
6.
在系统管理员的数据库引擎查询中;执行合适的授权语句;授予log1具有对Course表的查询权限;授予log2具有对Course表的插入权限..
7.
用log2建立一个新的数据库引擎查询;执行下述语句;能否成功 为什么
INSERT INTO Course VALUES'C1001';'数据库基础';4;5 再执行下述语句;能否成功 为什么
SELECT FROM Course
8.
在log1建立的数据库引擎查询中;再次执行下述语句：
SELECT FROM Course
这次能否成功 但如果执行下述语句：
INSERT INTO Course VALUES'C103'; '软件工程'; 4; 5 能否成功 为什么
9.
log3建立一个新的数据库引擎查询;执行下述语句;能否成功 为什么 CREATE TABLE NewTable C1 int; C2 char4
10. 授予log3在学生数据库中具有创建表的权限..
11. 在系统管理员的数据库引擎查询中;执行下述语句：
GRANT CREATE TABLE TO log3 GO
CREATE SCHEMA log3 AUTHORIZATION log3 GO
ALTER USER log3 WITH DEFAULT_SCHEMA = log3
12. 在
log3建立一个新的数据库引擎查询中;再次执行第9题的语句;能否
成功 为什么
如果执行下述语句：
SELECT NewTable 能否成功 为什么
