数据库上机实验.doc

附录C 上机实验C.1 第4章上机实验下列实验均使用SQL Server 的SSMS工具实现。1用图形化方法创建符合如下条件的数据库（创建数据库的方法可参见本书附录A）：l 数据库名为：学生数据库l 主要数据文件的逻辑文件名为：Students_data，存放在D:Data文件夹下（若D:盘中无此文件夹，请先建立此文件夹，然后再创建数据库。），初始大小为：5MB，增长方式为自动增长，每次增加1MB。l 日志文件的逻辑文件名字为：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默认值为“计算机系”Sid身份证号普通编码定长字符串，长度为10取值不重Sdate入学日期日期默认为系统当前日期表C-2 Course表结构列名说明数据类型约束Cno课程号普通编码定长字符串，长度为10主键Cname课程名普通编码不定长字符串，长度为20非空Credit学时数整型取值大于0Semester学分小整型表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. 查询成绩在7080分的学生的学号、课程号和成绩。4. 查询计算机系年龄在1820岁的男生姓名和年龄。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人，则显示“人多”；如果此门课程的选课人数在40100，则显示“一般”；如果此门课程的选课人数在140，则显示“人少”；如果此门课程没有人选，则显示“无人选”。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在学生数据库中具有创建表的权限。11. 在系统管理员的数据库引擎查询中，执行下述语句：GRANT CREATE TABLE TO log3GOCREATE SCHEMA log3 AUTHORIZATION log3GOALTER USER log3 WITH DEFAULT_SCHEMA = log312. 在log3建立一个新的数据库引擎查询中，再次执行第9题的语句，能否成功？为什么？如果执行下述语句：SELECT * NewTable能否成功？为什么？