搜索
实验报告课程名称数据库技术实践实验项目SQL语言基础、架构与基本表、高级查询实验仪器SQLServer2008系别____计算机科学与技术系_专业____计算机科学与技术____班级/学号_______________________学生姓名_______________________实验日期__________成绩_______________________指导教师___张鸿斌______________[在内容说明部分请总体说明在本部分实践过程中，具体都完成了哪些内容]一．内容说明[请按照下面练习题的要求，完成各项内容，并说明每个题目完成的情况，是否存在问题，如何解决等]二.SQL语言基础1．在students数据库中创建一个用户定义的数据类型：类型名为：my_type，对应的基本数据类型为：char(10)，允许空。2．声明一个字符串型的局部变量，并对其赋初值：‘MyFirstVar’,然后在屏幕上显示此值。3．编写实现如下功能的脚本，并将编写好的脚本保存到磁盘文件中。（1）声明两个整型的局部变量：@i1和@i2，@i1的初值为10,@i2的值为：@i1乘以5，最后在屏幕上显示@i2的值。（2）用While语句实现计算5000减1、减2、减3…，一直减到50的结果，并显示最终结果。三．架构与基本表1．在第3章建立的Students数据库中，创建满足如下要求的架构。准备工作：首先在SSMS中，以系统管理员身份执行下列脚本，创建登录账户User1和User2，并让这两个登录账户成为Students数据库中的合法用户。CREATELOGINUser1WITHPASSWORD='123456',DEFAULT_DATABASE=StudentsgoCREATELOGINUser2WITHPASSWORD='123456',DEFAULT_DATABASE=studentsgoUSEStudentsgoCREATEUSERUser1goCREATEUSERUser2（1）为用户User1定义一个架构，架构名为Base。（2）为用户User2定义一个架构，架构名为Inform，并在该架构中定义一个关系表Teacher，结构为：Tnochar(8)--教师号Tnamevarchar(10)--教师名（3）将Inform架构中的Teacher表传输到Base架构中。（4）删除Inform架构。2．在Students数据库中，用图形化方法创建满足下述要求的关系表。Student列名说明数据类型约束Sno学号普通编码定长字符串，长度为7主键Sname姓名普通编码定长字符串，长度非空为10Sex性别普通编码定长字符串，长度为2取值范围为：{男，女}Birthdate出生日期日期类型Dept所在系普通编码不定长字符串，长度为20Course列名说明数据类型约束Cno课程号普通编码定长字符串，长度为10主键Cname课程名普通编码不定长字符串，长度为20非空Credit学分微整型大于0Semester开课学期微整型SC列名说明数据类型约束Sno学号普通编码定长字符串，长度为7主键，引用Student的外键Cno课程号普通编码定长字符串，长度为10主键，引用Course的外键Grade成绩小整型取值范围：0～1003．在Students数据库中，用T-SQL语句创建满足要求的表：销售表列名数据类型约束商品号普通编码定长字符型，长度为10非空销售时间小日期时间型非空销售价格整型非空销售数量小整型非空销售总价整型等于本次销售价格*销售数量其中（商品号，销售时间）为主键订购表列名数据类型约束货单号整型标识列，初值为1，自动增长，每次增加1，主键订购时间小日期时间型非空顾客号普通编码定长字符型，长度为10订购明细表列名数据类型约束货单号整型外键，引用订购表的“货单号”商品号普通编码定长字符型，长度为10非空订购数量整型订购价格整型其中（货单号，商品号）为主键。4．创建满足如下要求的分区函数：（1）在int列上创建右侧分区函数，该分区函数将数据分为3个区：小于1000、1000~3000和大于3000。（2）在smalldatetime列上创建左侧分区函数，该分区函数将数据按月份分区，只针对2011年数据，每月一个区。5．在Sudents数据库中增加两个新的文件组：MyGroup1和MyGroup2，然后利用第4题（1）建立的分区函数，建立分区方案，使得每个分区分别存放在PRIMARY、MyGroup1和MyGroup2文件组中。6．创建使用第4题（2）创建的分区方案的表：Sales_2011，结构为：Sales_date：小日期时间型，主键；Sales_Total：整型。该表按Sales_date进行分区。四.高级查询根据第6章给出的Student、Course和SC表，编写实现如下操作的SQL语句。1．查询计算机系每个学生的JAVA考试情况，列出学号、姓名、成绩和成绩情况，其中成绩情况的显示规则为：如果成绩大于等于90，则成绩情况为“好”；如果成绩在80～89，则成绩情况为“较好”；如果成绩在70～79，则成绩情况为“一般”；如果成绩在60～69，则成绩情况为“较差”；如果成绩小于60，则成绩情况为“差”。2.统计每个学生的选课门数（包括没有选课的学生），列出学号、选课门数和选课情况，其中选课情况显示规则为：如果选课门数大于等于6门，则选课情况为“多”；如果选课门数超过在3～5门，则选课情况为“一般”；如果选课门数在1~2门，则选课情况为“偏少”。如果没有选课，则选课情况为“未选课”。3.统计每个系JAVA课程的考试情况，列出系名和考试情况，其中考试情况为：如果JAVA平均成绩大于等于90，则考试情况为“好”；如果JAVA平均成绩在80～89，则考试情况为“良好”；如果JAVA平均成绩在70～79，则考试情况为“一般”；如果JAVA平均成绩低于70，则考试情况为“较差”。4.修改全部课程的学分，修改规则如下：如果是第1～2学期开设的课程，则学分增加5分；如果是第3～4学期开设的课程，则学分增加3分；如果是第5～6学期开设的课程，则学分增加1分；对其他学期开设的课程，学分不变。5．统计第2学期开设的课程的总学分，列出该学期开设的课程名、学分和总学分。6．统计考试平均成绩大于等于80分的学生的姓名、考试的课程号、考试成绩和平均成绩，并将结果按平均成绩从高到低排序。7．查询计算机系年龄小于信息管理系全体学生年龄的学生的姓名和年龄。8．查询计算机系年龄大于信息管理系某个学生年龄的学生的姓名和年龄。9．查询哪些课程没有学生选，列出课程号和课程名。(用EXISTS子查询实现)10．查询计算机系哪些学生没有选课，列出学生姓名。(用EXISTS子查询实现)11．查询没有选修第2学期开设的全部课程的学生的学号、其所选的课程号和该课程的开课学期。12．查询至少选了第4学期开设的全部课程的学生的学号和所在系。13．查询至少选了“0831102”号学生所选的全部课程的学生的学号。14．查询至少选了“张海”所选的全部课程的学生的学号、所在系和所选的课程号。15．查询至少选了全部学分大于3分的课程的学生的学号、所在系和所选的课程号、课程名以及学分。16．查询在第4学期开设课程中与第1学期开设的课程学分相同的课程，列出课程名和学分。17．查询“李勇”和“王大力”所选的相同课程，列出课程名、开课学期和学分。18．查询“李勇”选了但“王大力”没有选的课程，列出课程名、开课学期和学分。19．查询至少同时选了“C001”和“C002”两门课程的学生的学号和所选的课程号。20．查询学生学号、姓名、所在系及该系的学生人数。21．查询学生姓名、年龄、所在系及该系的平均年龄、最大年龄和最小年龄。22.查询学号、姓名、性别、所在系以及该系的学生总人数、男女生人数及男女生百分比。查询结果样式如图7-46所示。图7-4622题的查询结果样式23.查询计算机系学生的考试情况，列出学号、姓名、考试课程名、考试成绩及成绩排名。该查询的部分结果形式如图7-47所示。图7-4723题的查询结果样式24.查询学生学号、选的课程号、考试成绩及考试成绩在该门课程中的排名。该查询的部分结果形式如图7-48所示。图7-4824题的查询结果样式25.查询学生姓名、所在系、出生日期及该学生在该系中的年龄排名（按从大到小）。该查询的部分结果形式如图7-49所示。图7-4925题的查询结果样式26．定义一个统计每门课程的考试平均成绩和选课人数的CTE，并利用该CTE查询选课人数超过2人的课程。27.（选做）查询Course表中的全部数据并将其保存到一个新表NewCourse中，然后为NewCourse表增加一个先修课程列PriorCno，该列表明了当前课程的先修课程编号，NULL表示该门课程没有先修课程。NewCourse表的结构和数据如表7-6所示。表7-6NewCourse表数据CnoCnameCreditSemesterPriorCnoC001高等数学41NULLC002大学英语31NULLC003大学英语32C002C004计算机文化学22NULLC005JAVA23C004C006数据库基础45C007C007数据结构44C009C008计算机网络44C004C009Java语言43C004用NewCourse表查询每门课程的信息及其先修课程信息，查询结果样式如图7-50所示。图7-5027题的查询结果样式28．（选做）利用NewCourse表，查询课程号、课程名、先修课程号及课程所在的层次。如果该课程没有先修课，则层次为1，如果有1层先修课，则层次为2，依次类推。查询结果样式如图7-51所示。图7-5128题的查询结果样式[实验总结部分需要对本部分实验的完成情况及收获做出总结认识]四.实验总结