数据库技术实践-第二部分-new

1、实 验 报 告课程名称 数据库技术实践 实验项目 SQL语言基础、架构与基本表、高级查询 实验仪器 SQL Server 2008 系 别_计算机科学与技术系 _专 业_计算机科学与技术_ 班级/学号_学生姓名 _ 实验日期 _ _成 绩 _ 指导教师 _ 张鸿斌_ _在内容说明部分请总体说明在本部分实践过程中，具体都完成了哪些内容一内容说明请按照下面练习题的要求，完成各项内容，并说明每个题目完成的情况，是否存在问题，如何解决等二.SQL语言基础1 在students数据库中创建一个用户定义的数据类型：类型名为：my_type，对应的基本数据类型为：char(10)，允许空。2 声明一个字符串

2、型的局部变量，并对其赋初值：My First Var,然后在屏幕上显示此值。3 编写实现如下功能的脚本，并将编写好的脚本保存到磁盘文件中。（1） 声明两个整型的局部变量：i1和i2， i1的初值为10, i2的值为：i1乘以5，最后在屏幕上显示i2的值。（2） 用While语句实现计算5000减1、减2、减3 ，一直减到50的结果，并显示最终结果。三架构与基本表1 在第3章建立的Students数据库中，创建满足如下要求的架构。准备工作：首先在SSMS中，以系统管理员身份执行下列脚本，创建登录账户User1和User2，并让这两个登录账户成为Students数据库中的合法用户。CREATE L

3、OGIN User1 WITH PASSWORD = , DEFAULT_DATABASE = StudentsgoCREATE LOGIN User2 WITH PASSWORD = , DEFAULT_DATABASE = studentsgoUSE StudentsgoCREATE USER User1goCREATE USER User2（1） 为用户User1定义一个架构，架构名为Base。（2） 为用户User2定义一个架构，架构名为Inform，并在该架构中定义一个关系表Teacher，结构为：Tno char(8) - 教师号Tname varchar(10) - 教师名（3）

4、 将Inform架构中的Teacher表传输到Base架构中。（4） 删除Inform架构。2 在Students数据库中，用图形化方法创建满足下述要求的关系表。Student列名说明数据类型约束Sno学号普通编码定长字符串，长度为7主键Sname姓名普通编码定长字符串，长度为10非空Sex性别普通编码定长字符串，长度为2取值范围为：男，女Birthdate出生日期日期类型Dept所在系普通编码不定长字符串，长度为20Course 列名说明数据类型约束Cno课程号普通编码定长字符串，长度为10主键Cname课程名普通编码不定长字符串，长度为20非空Credit学分微整型大于0Semester开

5、课学期微整型SC 列名说明数据类型约束Sno学号普通编码定长字符串，长度为7主键，引用Student的外键Cno课程号普通编码定长字符串，长度为10主键，引用Course的外键Grade成绩小整型取值范围：01003 在Students数据库中，用T-SQL语句创建满足要求的表：销售表列名数据类型约束商品号普通编码定长字符型，长度为10非空销售时间小日期时间型非空销售价格整型非空销售数量小整型非空销售总价整型等于本次销售价格*销售数量其中（商品号，销售时间）为主键订购表列名数据类型约束货单号整型标识列，初值为1，自动增长，每次增加1，主键订购时间小日期时间型非空顾客号普通编码定长字符型，长度为

6、10订购明细表列名数据类型约束货单号整型外键，引用订购表的“货单号”商品号普通编码定长字符型，长度为10非空订购数量整型订购价格整型其中（货单号，商品号）为主键。4 创建满足如下要求的分区函数：（1） 在int列上创建右侧分区函数，该分区函数将数据分为3个区：小于1000、10003000和大于3000。（2） 在smalldatetime列上创建左侧分区函数，该分区函数将数据按月份分区，只针对2011年数据，每月一个区。5 在Sudents数据库中增加两个新的文件组：MyGroup1和MyGroup2，然后利用第4题（1）建立的分区函数，建立分区方案，使得每个分区分别存放在PRIMARY、M

7、yGroup1和MyGroup2文件组中。6 创建使用第4题（2）创建的分区方案的表：Sales_2011，结构为：Sales_date：小日期时间型，主键；Sales_Total：整型。该表按Sales_date进行分区。四. 高级查询根据第6章给出的Student、Course和SC表，编写实现如下操作的SQL语句。1查询计算机系每个学生的JAVA考试情况，列出学号、姓名、成绩和成绩情况，其中成绩情况的显示规则为：如果成绩大于等于90，则成绩情况为“好”；如果成绩在8089，则成绩情况为“较好”；如果成绩在7079，则成绩情况为“一般”；如果成绩在6069，则成绩情况为“较差”；如果成绩小

8、于60，则成绩情况为“差”。2. 统计每个学生的选课门数（包括没有选课的学生），列出学号、选课门数和选课情况，其中选课情况显示规则为：如果选课门数大于等于6门，则选课情况为“多”；如果选课门数超过在35门，则选课情况为“一般”；如果选课门数在12门，则选课情况为“偏少”。如果没有选课，则选课情况为“未选课”。3. 统计每个系JAVA课程的考试情况，列出系名和考试情况，其中考试情况为：如果JAVA平均成绩大于等于90，则考试情况为“好”；如果JAVA平均成绩在8089，则考试情况为“良好”；如果JAVA平均成绩在7079，则考试情况为“一般”；如果JAVA平均成绩低于70，则考试情况为“较差”。

9、4. 修改全部课程的学分，修改规则如下：如果是第12学期开设的课程，则学分增加5分；如果是第34学期开设的课程，则学分增加3分；如果是第56学期开设的课程，则学分增加1分；对其他学期开设的课程，学分不变。5统计第2学期开设的课程的总学分，列出该学期开设的课程名、学分和总学分。6统计考试平均成绩大于等于80分的学生的姓名、考试的课程号、考试成绩和平均成绩，并将结果按平均成绩从高到低排序。7查询计算机系年龄小于信息管理系全体学生年龄的学生的姓名和年龄。8查询计算机系年龄大于信息管理系某个学生年龄的学生的姓名和年龄。9查询哪些课程没有学生选，列出课程号和课程名。(用EXISTS子查询实现)10查询计

10、算机系哪些学生没有选课，列出学生姓名。(用EXISTS子查询实现)11查询没有选修第2学期开设的全部课程的学生的学号、其所选的课程号和该课程的开课学期。12查询至少选了第4学期开设的全部课程的学生的学号和所在系。13查询至少选了“”号学生所选的全部课程的学生的学号。14查询至少选了“张海”所选的全部课程的学生的学号、所在系和所选的课程号。15查询至少选了全部学分大于3分的课程的学生的学号、所在系和所选的课程号、课程名以及学分。16查询在第4学期开设课程中与第1学期开设的课程学分相同的课程，列出课程名和学分。17查询“李勇”和“王大力”所选的相同课程，列出课程名、开课学期和学分。18查询“李勇”

11、选了但“王大力”没有选的课程，列出课程名、开课学期和学分。19查询至少同时选了“C001”和“C002”两门课程的学生的学号和所选的课程号。20查询学生学号、姓名、所在系及该系的学生人数。21查询学生姓名、年龄、所在系及该系的平均年龄、最大年龄和最小年龄。22. 查询学号、姓名、性别、所在系以及该系的学生总人数、男女生人数及男女生百分比。查询结果样式如图7-46所示。图7-46 22题的查询结果样式23. 查询计算机系学生的考试情况，列出学号、姓名、考试课程名、考试成绩及成绩排名。该查询的部分结果形式如图7-47所示。图7-47 23题的查询结果样式24. 查询学生学号、选的课程号、考试成绩及

12、考试成绩在该门课程中的排名。该查询的部分结果形式如图7-48所示。图7-48 24题的查询结果样式25. 查询学生姓名、所在系、出生日期及该学生在该系中的年龄排名（按从大到小）。该查询的部分结果形式如图7-49所示。图7-49 25题的查询结果样式26定义一个统计每门课程的考试平均成绩和选课人数的CTE，并利用该CTE查询选课人数超过2人的课程。27. （选做）查询Course表中的全部数据并将其保存到一个新表NewCourse中，然后为NewCourse表增加一个先修课程列PriorCno，该列表明了当前课程的先修课程编号，NULL表示该门课程没有先修课程。NewCourse表的结构和数据如表7-6所示。表7-6 NewCourse表数据CnoCnameCreditSemesterPriorCnoC001 高等数学41NULLC002 大学英语31NULLC003 大学英语32C002C004 计算机文化学22NULLC005 JAVA23C004C006 数据库基础45C007C007 数据结构44C009C008 计算机网络44C004C009Java语言43C004 用NewCou