数据库SQL实验题目(14-15-1).

实验一数据定义操作实验目的1. 掌握数据库和表的基础知识2. 掌握使用创建数据库和表的方法3. 掌握数据库和表的修改、查看、删除等基本操作方法实验内容和要求一、数据库的创建在开始菜单中，启动SQl Server 程序中的“ SQL Server Management Studio ”，在对象资源管理器窗口中， 右键单击 “数据库” ，选择“新建数据库” ，创建成绩管理数据库Grademanager，要求如下表所示：Grademanager 数据库参数表参数参数值参数参数值存储的数据文件路D:dbgrademanager_data.mdf存储的日志文件路D:dbgrademanager_log.ldf径径数据文件初始大小5MB日志文件初始大小2MB数据文件最大值20MB日志文件最大值15MB数据文件增长量原来 10%日志文件增长量2MB二、表的创建、查看、修改和删除1表的创建在 Grademanager 数据库中，右键单击“表” ，选择“新建表”命令，创建如下表所示的表：（1） Student 表的表结构字段名称数据类型 长度精度小数位数是否允许说明Null 值SnoChar1000否学号，主码SnameVarchar800是姓名SsexChar200是性别，取值：男或女SbirthdayDate800是出生日期SdeptChar1600是系名SpecialityVarchar2000是专业名特别注意：为属性 Ssex 设置约束， 需选中属性Ssex行，然后单击菜单中的“表设计器” ，选择“ CHECK约束”命令，然后按照图1 进行设置。1图 1 设置性别的约束（ 2） Course 表（课程名称表）的表结构字段名称数据类型长度精度小数位数是否允许 Null 值说明CnoChar500否课程号，主码CnameVarchar2000否课程名（3） SC表（成绩表）的表结构字段名称 数据类型 长度 精度 小数位数 是否允许 Null 值 说明SnoChar1000否学号，外码CnoChar500否课程号，外码DegreeDecimal551是成绩， 0 100 之间特别注意：为属性 Degree 约束，可参照属性 Ssex 进行设置， “CHECK约束对话框”中的表达式为 Degree>=1 And Degree<=100为 SC表设置外键Sno 和 Cno 的方法：右键单击表SC，选择“设计”命令，然后选择菜单“表设计器”中的“关系”命令，打开“外键关系”窗口，选择“添加”按钮，然后单击“表和列规范”后的按钮，按照图2 进行设置，即可将SC表中的 Cno 属性设置为外键。按照相同的方法，将属性Sno 也设置为外键。图 2 为 SC表设置外键Cno22向上述表中输入如下数据记录学生关系表 Student（右键单击表 Student，选择“编辑前 200 行”）SnoSnameSsex SbirthdaySdeptSpeciality20050101李勇男1987-01-12计算机系计算机应用20050201刘晨女1988-06-04信息管理系电子商务20050301王敏女1989-12-23数学系数学20050202张立男1988-08-25信息管理系电子商务课程关系表 Course成绩表 SCCnoCnameSnoCnoDegree20050101C0192C01数据库20050101C0285C02数学20050101C0388C03信息系统20050201C0290C04操作系统20050201C038020050301C019120050301C027520050202C01873修改表结构（找到操作的方法即可，不需要真正地修改表中的属性）（ 1）向 student 表中增加“入学时间”列，其数据类型为日期型（ 2）将 student 表中的 sdept 字段长度改为 20（ 3）将 student 表中的 Speciality 字段删除思考题1 SQL Server的数据库文件有几种？扩展名分别是什么？2 如何实现数据库的备份和还原？2在定义基本表语句时，NOT NULL参数的作用是什么？3主码可以建立在“值可以为NULL”的列上吗？3实验二简单查询实验目的1. 掌握 SELECT语句的基本用法2. 使用 WHERE子句进行有条件的查询3. 掌握使用 IN 和 NOT IN， BETWEEN, AND 和 NOT BETWEEN, AND 来缩小查询范围的方法4. 掌握聚集函数的使用方法5. 利用 LIKE子句实现字符串匹配查询6. 利用 ORDER BY子句对查询结果排序7. 利用 GROUP BY子句对查询结果分组实验内容和要求一、表结构修改1在实验一的所建立的数据库中增加Teacher表，结构如下：字段名称数据类型长度精度小数位数是否允许 Null 值说明TnoChar300否教师号，主码TnameVarchar800是教师名TsexChar200是性别，取值：男或女TbirthdayDate800是出生日期TdeptChar1600是所在系2在实验一的所建立的数据库中增加Teaching表，表结构如下：Teaching 表（授课表）的表结构字段名称数据类型长度精度小数位数是否允许 Null 值说明CnoChar500否课程号，外码TnoChar300否教师号，外码CtermTinyint100是1-8 之间3向上述两表中输入如下数据记录教师表 Teacher授课表 TeachingTno TnameTsex TbirthdayTdeptCnoTnoCterm101李新男1977-01-12计算机系C011012102钱军女1968-06-04计算机系C021021201王小花女1979-12-23信息管理系C032013202张小青男1968-08-25信息管理系C042024二、完成下面查询1查询所有男生信息42查询年龄大于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子句、 GROUP BY子句中？2关键字ALL和 DISTINCT有什么不同的含义？3SELECT语句中用于表示模糊查询的通配符有几种？含义分别是什么？4利用 BETWEEN,AND 表示的数据范围能否用其他方法表示？怎样表示？如：scorebetween80and90 还可以怎么表示？5实验三复杂查询实验目的1. 掌握连接查询和嵌套查询操作2. 能灵活应用 SELECT语句解决复杂的查询问题实验内容和要求一、连接查询1. 查询女学生的学生学号及总成绩2. 查询李勇同学所选的课程号及成绩3. 查询李新老师所授课程的课程名称4. 查询女教师所授课程的课程号及课程名称5. 查询姓“王”的学生所学的课程名称6.查询选修“数据库”课程且成绩在80 到 90 之间的学生学号及成绩7. 查询选修“ C02”课程的学生的平均年龄8. 查询学习课程名为“数学”的学生学号和姓名9. 查询“钱军”教师任课的课程号，选修其课程的学生的学号和成绩10. 查询在第 3 学期所开课程的课程名称，选修其课程的学生学号和成绩二、嵌套查询1. 查询至少选修两门课程的男学生姓名2. 查询与李勇同一个系的其他同学姓名3. 查询学号比刘晨同学大，而年龄比他小的学生姓名4. 查询年龄大于所有女同学的男同学的姓名及系别5. 查询成绩比该课程平均成绩高的学生的学号、课程号及成绩6. 查询不讲授“ C01”课的教师姓名7. 查询没有选修“ C02”课程的学生学号及姓名8. 查询选修了“数据库”课程的学生学号、姓名及系别9. 查询选修了全部课程的学生姓名10. 分别用子查询和连接查询，求“C02”号课程在 80 分以上的学生信息思考题1 使用存在量词NOTEXISTS的嵌套查询时， 何时外层查询的WHERE条件为真， 何时为假？2 DROP命令和 DELETE命令的本质区别是什么？6实验四数据更新、索引及视图操作实验目的1. 掌握利用 INSERT命令实现对表数据的插入操作2. 掌握利用 UPDATE命令实现对表数据的修改操作3. 掌握利用 DELETE命令实现对表数据的删除操作4. 掌握索引的创建和使用5. 掌握视图的创建和删除实验内容和要求一、数据更新1 向 Student 表中插入记录（'20050203','张静 ','女 ','1981-3-21','CS' ,'电子商务 ' ）2 插入学号为20050302 ，姓名为李四学生信息3 把男学生记录保存到表TS中（提示：利用selectinto命令可备份数据库中的表格式： select *into 新表名from 原表名where 子句 ;)4 将学号为 20050202 的学生姓名改为张华，系别改为“CS”，专业改为“多媒体技术”5 将 20050201 学生选修“ C03”号课程的成绩改为该课的平均成绩6 把女同学的成绩提高5%7 把选修了“数据库”课程学生的成绩全改为空值（NULL）8 删除学号为20050302 的学生记录9 把“刘晨”同学的选课记录全部删除10删除电子商务专业所有学生的选课记录二、索引的创建与使用1用索引管理器创建SC表的索引IX_XS_KC。（ 1）选择要创建索引的数据库文件夹，并在右边的对象窗口中选择并打开其中的“表”对象。（ 2）选择所要创建索引的 SC表，右键单击该表，在弹出的快捷菜单中选择“设计”命令，然后在工具栏中单击“管理索引和键”按钮，打开“索引和键”窗口。（ 3）单击其中的“添加”按钮，创建新的索引，并为其设置相应的属性。为 SC 表创建一个基于“课程号”列和“成绩”列的索引IX_XS_KC，其中课程号列按升序排列，成绩列按降序排列。设置完成后，如图3 所示。7图 3 创建索引 IX_XS_KC2强制使用刚才创建的索引查询数据。在查询命令窗口中输入以下SQL查询命令并执行：SELECT SNO,CNO,DEGREEFROM SCWITH （ INDEX （ IX_XS_KC）WHERE DEGREE>=60观察一下显示出来的数据是否有序。3利用 SQL命令实现下列操作：(1) 为 Student 表创建一个名为 S_dept 的索引，以系别排序(2)为 Teacher 表创建一个名为T_name 的聚集索引，以Tname 降序排序。命令格式： createclustered indexon 表名 (列名 1顺序，列名 2顺序 )(3)删除 S_dept 索引 。命令格式： dropindex表名 .索引名或 dropindex索引名 on表名三、视图的创建与使用1. 创建一个简单视图 view1 ，查询“计算机系”学生的信息2. 创建一个简单视图 view2 ，统计每门课程的选课人数和最高分3. 创建一个复杂视图 view3 ，查询与“张立”同学同一系别的学生信息4. 删除视图 View3思考题1利用 INSERT、 UPDATE和 DELETE命令可以同时对多个表操作吗？2. 使用索引为什么能提高查询速度？3索引经常被建立在哪些字段上？4聚集索引与其他索引有什么不同？8实验五存储过程、函数、触发器及游标操作实验目的1. 掌握 compute by 的使用2. 掌握变量的定义和使用方法3. 掌握存储过程的定义和执行方法4. 掌握触发器的定义方法5. 掌握函数的定义和调用方法6. 掌握游标的定义和使用方法（提示：以下操作全部基于实验一创建的Grademanager数据库）实验内容和要求一、 使用 compute by完成下列操作1. 显示所有学生的成绩，并汇总每个学生的总分。2. 显示所有学生的信息，并汇总男女学生的人数。二、存储过程1. 创建一个名为 Query_student 的存储过程，该存储过程的功能是根据学号查询学生表中某一学生的学号、 姓名、 性别及出生日期。 执行存储过程 Query_student，查询学号为“ 20050301”的学生的学号、姓名、性别及出生日期。写出完成此功能的SQL 命令。2. 创建存储过程sm_score，用于求指定学号（输入参数）的学生的总成绩（输出参数）。执行该存储过程，指定学号为“20050101 ”。3. 创建并执行存储过程 count_sno，用于求所有学生人数，通过参数返回学生人数。4. 删除存储过程 count_sno。5.创建一个向课程表中插入一门新课程的存储过程Insert_course，该存储过程需要两个参数，分别用来传递课程号、课程名；执行存储过程Insert_course，向课程表 Course中插入一门新课程（ 0807011， Flash 动画设计 ）。6.创建一个能向学生表中插入一条记录的存储过程Insert_student ，执行存储过程Insert_student，向学生表中插入一个新同学 （ '20060208','徐小花 ','女 ', '1987-08-25', 'IS','电子商务 '）。三、触发器1. 在 SC 表创建一个INSERT 触发器，如果插入一条选课信息时，学生的学号值在表S中不存在，则不执行插入操作，并提示用户。2. 使用 insert 语句向 SC 表中插入记录（ '20020101', 'C01'）, 观察触发器 INSERT_SC是否被执行。3. 在 Teaching 表中创建一个插入触发器insert_teaching，一旦发现待插入的教师号Tno在 Teacher 表中不存在，就发出警告信息并禁止插入。4. 创建一个当学生表中插入一个新同学时能自动列出全部同学学生信息的触发器Display_trigger 。95.调用“存储过程部分”的第 5 题创建的存储过程 Insert_student，向学生表中插入一新同学，新同学信息（ '20060311','张丽丽 ','女 ', '1988-2-15', 'MA',' 数学 '），观察触发器 Display_trigger 是否被执行。6. 在成绩表 SC 中创建一个 UPDA TE 触发器 update_score，当修改成绩时，如果发现成绩小于 0 ，则不执行修改操作，并提示用户。使用下面的SQL语句试图修改'20050101' 学生的成绩，观察触发器update_score 是否被执行。update SC set Degree=-30 where Sno='20050101'7.课程表 Course 中创建一个 DELETE触发器 delete_course，如果待删除的课程在 SC表中存在对应的选课记录， 则不执行删除操作， 并提示用户。 使用下面的 SQL 语句试图删除 'C01' 课程信息，检查触发器delete_course 是否被执行。delete from Course where Cno='C01'8.禁用 update_score 触发器9.启用 update_score 触发器10.删除 update_score 触发器四、函数操作1、 创建一个标量值函数getavgscore 以获取某门课平均成绩。2、 创建内联表值函数getscore 以获取某个学生的成绩信息。五、游标操作为 “电子商务 ”专业的学生创建一个 Scroll 游标 speciality_cursor ，显示其学号、姓名、性别、出生日期，打开游标后按顺序执行以下操作：1、 提取结果集的第一行2、 提取结果集最后一行3、 提取上一行4、 提取下一行5、 提取所有行6、 关闭游标7、 删除游标10