数据库sql实验题目14-15-1

-.z.实验一数据定义操作实验目的掌握数据库和表的根底知识掌握使用创立数据库和表的方法掌握数据库和表的修改、查看、删除等根本操作方法实验内容和要求一、数据库的创立在开场菜单中，启动SQlServer程序中的"SQLServerManagementStudio〞，在对象资源管理器窗口中，右键单击"数据库〞，选择"新建数据库〞，创立成绩管理数据库Grademanager，要求如下表所示：Grademanager数据库参数表参数参数值参数参数值存储的数据文件路径D:\db\grademanager_data.mdf存储的日志文件路径D:\db\grademanager_log.ldf数据文件初始大小5MB日志文件初始大小2MB数据文件最大值20MB日志文件最大值15MB数据文件增长量原来10%日志文件增长量2MB二、表的创立、查看、修改和删除1．表的创立在Grademanager数据库中，右键单击"表〞，选择"新建表〞命令，创立如下表所示的表：〔1〕Student表的表构造字段名称数据类型长度精度小数位数是否允许Null值说明SnoChar1000否**，主码SnameVarchar800是Sse*Char200是性别，取值：男或女SbirthdayDate800是出生日期SdeptChar1600是系名SpecialityVarchar2000是专业名特别注意：为属性Sse*设置约束，需选中属性Sse*行，然后单击菜单中的"表设计器〞，选择"CHECK约束〞命令，然后按照图1进展设置。图1设置性别的约束〔2〕Course表〔课程名称表〕的表构造字段名称数据类型长度精度小数位数是否允许Null值说明CnoChar500否课程号，主码CnameVarchar2000否课程名〔3〕SC表〔成绩表〕的表构造字段名称数据类型长度精度小数位数是否允许Null值说明SnoChar1000否**，外码CnoChar500否课程号，外码DegreeDecimal551是成绩，0～100之间特别注意：=1\*GB3①为属性Degree约束，可参照属性Sse*进展设置，"CHECK约束对话框〞中的表达式为Degree>=1AndDegree<=100=2\*GB3②为SC表设置外键Sno和Cno的方法：右键单击表SC，选择"设计〞命令，然后选择菜单"表设计器〞中的"关系〞命令，翻开"外键关系〞窗口，选择"添加〞按钮，然后单击"表和列标准〞后的按钮，按照图2进展设置，即可将SC表中的Cno属性设置为外键。按照一样的方法，将属性Sno也设置为外键。图2为SC表设置外键Cno2．向上述表中输入如下数据记录学生关系表Student〔右键单击表Student，选择"编辑前200行〞〕SnoSnameSse*SbirthdaySdeptSpeciality20050101李勇男1987-01-12计算机系计算机应用20050201刘晨女1988-06-04信息管理系电子商务20050301王敏女1989-12-23数学系数学20050202张立男1988-08-25信息管理系电子商务SnoCnoDegree20050101C019220050101C028520050101C038820050201C029020050201C038020050301C019120050301C027520050202C0187课程关系表Course成绩表SCCnoCnameC01数据库C02数学C03信息系统C04操作系统3．修改表构造〔找到操作的方法即可，不需要真正地修改表中的属性〕〔1〕向student表中增加"入学时间〞列，其数据类型为日期型〔2〕将student表中的sdept字段长度改为20〔3〕将student表中的Speciality字段删除思考题SQLServer的数据库文件有几种？扩展名分别是什么？如何实现数据库的备份和复原？2．在定义根本表语句时，NOTNULL参数的作用是什么？3．主码可以建立在"值可以为NULL〞的列上吗？实验二简单查询实验目的掌握SELECT语句的根本用法使用WHERE子句进展有条件的查询掌握使用IN和NOTIN，BETWEEN…AND和NOTBETWEEN…AND来缩小查询范围的方法掌握聚集函数的使用方法利用LIKE子句实现字符串匹配查询利用ORDERBY子句对查询结果排序利用GROUPBY子句对查询结果分组实验内容和要求一、表构造修改1．在实验一的所建立的数据库中增加Teacher表，构造如下：字段名称数据类型长度精度小数位数是否允许Null值说明TnoChar300否教师号，主码TnameVarchar800是教师名Tse*Char200是性别，取值：男或女TbirthdayDate800是出生日期TdeptChar1600是所在系2．在实验一的所建立的数据库中增加Teaching表，表构造如下：Teaching表〔授课表〕的表构造字段名称数据类型长度精度小数位数是否允许Null值说明CnoChar500否课程号，外码TnoChar300否教师号，外码CtermTinyint100是1-8之间3．向上述两表中输入如下数据记录CnoTnoCtermC011012C021021C032013C042024教师表Teacher授课表TeachingTnoTnameTse*TbirthdayTdept101李新男1977-01-12计算机系102钱军女1968-06-04计算机系201王小花女1979-12-23信息管理系202张小青男1968-08-25信息管理系二、完成下面查询1．查询所有男生信息2．查询年龄大于24岁的女生**和姓名3．查询所有教师的Tname、Tdept4．查询"电子商务〞专业的学生姓名、性别和出生日期5．查询成绩低于90分的学生**及课号，并按成绩降序排列6．查询Student表中所有的系名7．查询"C01〞课程的开课学期8．查询成绩在80分至90之间的学生**及课号9．统计有学生选修的课程门数10．查询成绩为77，88或99的记录11．计算"C02〞课程的平均成绩12．输出有成绩的学生**13．查询所有姓"〞的学生信息14．统计输出各系学生的人数15．查询选修了"C03〞课程和学生的**及其成绩，查询结果按分数的降序排列16．查询各个课程号及相应的选课人数，并为选课人数取别名为"人数〞17．统计每门课程的选课人数和最高分，并为选课人数和最高分分别取别名为"人数〞、"最高分〞18．统计每个学生的选课门数和考试总成绩，并为选课门数和总成绩分别取别名为"门数〞、"总成绩〞，并按选课门数降序排列。思考题1．聚集函数能否直接使用在：SELECT子句、HAVING子句、WHERE子句、GROUPBY子句中？2．关键字ALL和DISTINCT有什么不同的含义？3．SELECT语句中用于表示模糊查询的通配符有几种？含义分别是什么？4．利用BETWEEN…AND表示的数据范围能否用其他方法表示？怎样表示？如：scorebetween80and90还可以怎么表示？实验三复杂查询实验目的掌握连接查询和嵌套查询操作能灵活应用SELECT语句解决复杂的查询问题实验内容和要求一、连接查询查询女学生的学生**及总成绩查询李勇同学所选的课程号及成绩查询李新教师所授课程的课程名称查询女教师所授课程的课程号及课程名称查询姓"王〞的学生所学的课程名称查询选修"数据库〞课程且成绩在80到90之间的学生**及成绩查询选修"C02〞课程的学生的平均年龄查询学习课程名为"数学〞的学生**和姓名查询"钱军〞教师任课的课程号，选修其课程的学生的**和成绩查询在第3学期所开课程的课程名称，选修其课程的学生**和成绩二、嵌套查询查询至少选修两门课程的男学生姓名查询与李勇同一个系的其他同学姓名查询**比刘晨同学大，而年龄比他小的学生姓名查询年龄大于所有女同学的男同学的姓名及系别查询成绩比该课程平均成绩高的学生的**、课程号及成绩查询不讲授"C01〞课的教师姓名查询没有选修"C02〞课程的学生**及姓名查询选修了"数据库〞课程的学生**、姓名及系别查询选修了全部课程的学生姓名分别用子查询和连接查询，求"C02〞号课程在80分以上的学生信息思考题使用存在量词NOTE*ISTS的嵌套查询时，何时外层查询的WHERE条件为真，何时为假？DROP命令和DELETE命令的本质区别是什么？实验四数据更新、索引及视图操作实验目的掌握利用INSERT命令实现对表数据的插入操作掌握利用UPDATE命令实现对表数据的修改操作掌握利用DELETE命令实现对表数据的删除操作掌握索引的创立和使用掌握视图的创立和删除实验内容和要求一、数据更新向Student表中插入记录〔'20050203','张静','女','1981-3-21','CS','电子商务'〕插入**为20050302，姓名为李四学生信息把男学生记录保存到表TS中〔提示：利用selectinto命令可备份数据库中的表格式：select*into新表名from原表名where子句;)将**为20050202的学生姓名改为张华，系别改为"CS〞，专业改为"多媒体技术〞将20050201学生选修"C03〞号课程的成绩改为该课的平均成绩把女同学的成绩提高5%把选修了"数据库〞课程学生的成绩全改为空值〔NULL〕删除**为20050302的学生记录把"刘晨〞同学的选课记录全部删除删除电子商务专业所有学生的选课记录二、索引的创立与使用1．用索引管理器创立SC表的索引I*_*S_KC。〔1〕选择要创立索引的数据库文件夹，并在右边的对象窗口中选择并翻开其中的"表〞对象。〔2〕选择所要创立索引的SC表，右键单击该表，在弹出的快捷菜单中选择"设计〞命令，然后在工具栏中单击"管理索引和键〞按钮，翻开"索引和键〞窗口。〔3〕单击其中的"添加〞按钮，创立新的索引，并为其设置相应的属性。为SC表创立一个基于"课程号〞列和"成绩〞列的索引I*_*S_KC，其中课程号列按升序排列，成绩列按降序排列。设置完成后，如图3所示。图3创立索引I*_*S_KC2．强制使用刚刚创立的索引查询数据。在查询命令窗口中输入以下SQL查询命令并执行：SELECTSNO,CNO,DEGREEFROMSCWITH〔INDE*〔I*_*S_KC〕〕WHEREDEGREE>=60观察一下显示出来的数据是否有序。3．利用SQL命令实现以下操作：为Student表创立一个名为S_dept的索引，以系别排序为Teacher表创立一个名为T_name的聚集索引，以Tname降序排序。命令格式：createclusteredinde*on表名(列名1顺序，列名2顺序)删除S_dept索引。命令格式：dropinde*表名.索引名或dropinde*索引名on表名三、视图的创立与使用创立一个简单视图view1，查询"计算机系〞学生的信息创立一个简单视图view2，统计每门课程的选课人数和最高分创立一个复杂视图view3，查询与"张立〞同学同一系别的学生信息删除视图View3思考题1．利用INSERT、UPDATE和DELETE命令可以同时对多个表操作吗？2.使用索引为什么能提高查询速度？3．索引经常被建立在哪些字段上？4．聚集索引与其他索引有什么不同？实验五存储过程、函数、触发器及游标操作实验目的掌握computeby的使用掌握变量的定义和使用方法掌握存储过程的定义和执行方法掌握触发器的定义方法掌握函数的定义和调用方法掌握游标的定义和使用方法〔提示：以下操作全部基于实验一创立的Grademanager数据库〕实验内容和要求使用computeby完成以下操作显示所有学生的成绩，并汇总每个学生的总分。显示所有学生的信息，并汇总男女学生的人数。存储过程创立一个名为Query_student的存储过程，该存储过程的功能是根据**查询学生表中*一学生的**、、性别及出生日期。执行存储过程Query_student，查询**为"20050301〞的学生的**、、性别及出生日期。写出完成此功能的SQL命令。创立存储过程sm_score，用于求指定**〔输入参数〕的学生的总成绩〔输出参数〕。执行该存储过程，指定**为"20050101〞。创立并执行存储过程count_sno，用于求所有学生人数，通过参数返回学生人数。删除存储过程count_sno。创立一个向课程表中插入一门新课程的存储过程Insert_course，该存储过程需要两个参数，分别用来传递课程号、课程名；执行存储过程Insert_course，向课程表Course中插入一门新课程〔’0807011’，’Flash动画设计’〕。创立一个能向学生表中插入一条记录的存储过程Insert_student，执行存储过程Insert_student，向学生表中插入一个新同学〔'20060208','徐小花','女','1987-08-25','IS','电子商务'〕。触发器在SC表创立一个INSERT触发器，如果插入一条选课信息时，学生的**值在表S中不存在，则不执行插入操作，并提示用户。使用insert语句向SC表中插入记录〔'20020101','C01'〕,观察触发器INSERT_SC是否被执行。在Teaching表中创立一个插入触发器insert_teaching，一旦发现待插入的教师号Tno在Teacher表中不存在，就发出警告信息并制止插入。创立一个当学生表中插入一个新同学时能自动列出全部同学学生信息的触发器Display_trigger