数据库上机实验

附录 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表结构列名说明数据类型约束sno学号普通编码定长字符串，长度为7主键sname姓名普通编码定长字符串，长度为10非空ssex性别普通编码定长字符串，长度为2取值范围： 男，女 sage年龄微整型（ tinyint ）取值范围： 15-45sdept所在系普通编码不定长字符串，长度为20默认值为“计算机系”sidsdate身份证号入学日期普通编码定长字符串，长度为日期10取值不重默认为系统当前日期表 c-2 course表结构列名cno说明课程号数据类型普通编码定长字符串，长度为10约束主键cname课程名普通编码不定长字符串，长度为20非空credit学时数整型取值大于0精品资料semester学分小整型表 c-3 sc 表结构列名sno说明学号数据类型普通编码定长字符串，长度为7主键，引用约束student的外键cno课程号普通编码定长字符串，长度为10主键，引用course的外键grade成绩小整型取值范围为0-100表 c-4 teacher表结构列名说明数据类型约束tno教师号普通编码定长字符串，长度为8非空tname教师名普通编码定长字符串，长度为10非空salary工资定点小数，小数点前4 位，小数点后2 位3. 写出实现如下功能的sql 语句，并执行所写代码，查看执行结果。（1） ） 在 teacher表中添加一个职称列，列名为：title ，类型为 nchar(4) 。（2） ） 为 teacher表中的 title 列增加取值范围约束，取值范围为：教授，副教授，讲师。（3） ） 将 course表中 credit 列的类型改为：tinyint 。（4） ） 删除 student表中的 sid 和 sdate 列。（5） ） 为 teacher表添加主键约束，其主键为：tno 。c.2 第 5 章上机实验本实验均在 sql server 的 ssms 工具中实现。 首先在已创建的“学生数据库”中创建本章表 5-15-3 所示的 student 、course 和 sc 表，并插入表 5-45-6 所示数据， 然后编写实现如下操作的 sql 语句，执行所写的语句，并查看执行结果。1. 查询 sc 表中的全部数据。2. 查询计算机系学生的姓名和年龄。3. 查询成绩在70 80 分的学生的学号、课程号和成绩。4. 查询计算机系年龄在18 20 岁的男生姓名和年龄。5. 查询 c001 课程的最高分。6. 查询计算机系学生的最大年龄和最小年龄。7. 统计每个系的学生人数。8. 统计每门课程的选课人数和最高成绩。9. 统计每个学生的选课门数和考试总成绩，并按选课门数升序显示结果。10. 列出总成绩超过200 的学生的学号和总成绩。11. 查询选了 c002课程的学生姓名和所在系。12. 查询考试成绩80 分以上的学生姓名、课程号和成绩，并按成绩降序排列结果。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. 统计每个系的女生人数，并将结果保存到新表girls 中。25. 用子查询实现如下查询：（ 1 ）查询选了“c001 ”课程的学生姓名和所在系。（ 2 ）查询通信工程系成绩80 分以上的学生的学号和姓名。（ 3 ）查询计算机系考试成绩最高的学生的姓名。（ 4 ）查询年龄最大的男生的姓名、所在系和年龄。26. 查询 c001 课程的考试成绩高于该课程平均成绩的学生的学号和成绩。27. 查询计算机系学生考试成绩高于计算机系学生平均成绩的学生的姓名、考试的课程名和考试成绩。28. 查询 vb 课程考试成绩高于vb 平均成绩的学生姓名和vb 成绩。29. 查询没选 vb 的学生姓名和所在系。30. 查询每个学期学分最高的课程信息，列出开课学期、课程名和学分。31. 查询每门课程考试成绩最高的学生信息，列出课程号、 学生姓名和最高成绩，结果按课程号升序排序，不包括没考试的课程。32. 创建一个新表，表名为test ，其结构为（col1, col 2, col 3），其中，col1 ：整型，允许空值。col2 ：普通编码定长字符型，长度为10，不允许空值。col3 ：普通编码定长字符型，长度为10，允许空值。 试写出按行插入如下数据的语句（空白处表示是空值）。col1col2col3b11b2c22b333. 利用 23 题建立的vb_grade表，将信息管理系选了vb 课程的学生姓名、所在系和考试成绩插入到vb_grade表中。34. 将所有选修c001 课程的学生的成绩加10 分。35. 将计算机系所有学生的“计算机文化学”的考试成绩加10 分。36. 修改“vb ”课程的考试成绩，如果是通信工程系的学生，则增加10 分；如果是信息管理系的学生则增加5 分，其他系的学生不加分。37. 删除成绩小于50 分的学生的选课记录。38. 删除计算机系vb 考试成绩不及格学生的vb 选课记录。39. 删除“vb ”考试成绩最低的学生的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 course6. 在系统管理员的数据库引擎查询中，执行合适的授权语句，授予 log1 具有对 course表的查询权限，授予log2 具有对course表的插入权限。7. 用 log2 建立一个新的数据库引擎查询，执行下述语句，能否成功？为什么？insert into course values(c1001 ， 数据库基础 ， 4 ， 5)再执行下述语句，能否成功？为什么？select * from course8. 在 log1 建立的数据库引擎查询中，再次执行下述语句：select * from course这次能否成功？但如果执行下述语句：insert into course values(c103 , 软件工程 , 4, 5)能否成功？为什么？9. log3 建立一个新的数据库引擎查询，执行下述语句，能否成功？为什么？create table newtable( c1 int,c2 char(4)10. 授予 log3 在学生数据库