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

1、实验一 数据定义操作l 实验目的 1. 掌握数据库和表的基础知识 2. 掌握使用创建数据库和表的方法 3. 掌握数据库和表的修改、查看、删除等基本操作方法l 实验内容和要求 一、数据库的创建 在开始菜单中，启动SQl Server 程序中的“SQL Server Management Studio”，在对象资源管理器窗口中，右键单击“数据库”，选择“新建数据库”，创建成绩管理数据库Grademanager，要求如下表所示： Grademanager数据库参数表 参数 参数值 参数 参数值 存储的数据文件路径 D:dbgrademanager_data.mdf 存储的日志文件路径 D:dbgra

2、demanager_log.ldf 数据文件初始大小 5MB 日志文件初始大小 2MB 数据文件最大值 20MB 日志文件最大值 15MB 数据文件增长量 原来10% 日志文件增长量 2MB 二、表的创建、查看、修改和删除 1表的创建 在Grademanager数据库中，右键单击“表”，选择“新建表”命令，创建如下表所示的表： （1）Student表的表结构 字段名称 数据类型 长度 精度 小数位数 是否允许Null值 说明Sno Char 10 0 0 否 学号，主码 Sname Varchar 8 0 0 是 姓名Ssex Char 2 0 0 是 性别，取值：男或女 Sbirthday

3、Date 8 0 0 是 出生日期Sdept Char 16 0 0 是 系名Speciality Varchar 20 0 0 是 专业名特别注意：为属性Ssex设置约束，需选中属性Ssex行，然后单击菜单中的“表设计器”，选择“CHECK约束”命令，然后按照图1进行设置。图1 设置性别的约束（2）Course表（课程名称表）的表结构 字段名称 数据类型 长度 精度 小数位数 是否允许Null值 说明 Cno Char 5 0 0 否 课程号，主码 Cname Varchar 20 0 0 否 课程名（3）SC表（成绩表）的表结构 字段名称 数据类型 长度 精度 小数位数 是否允许Null值

4、 说明 Sno Char 10 0 0 否 学号，外码 Cno Char 5 0 0 否 课程号，外码 Degree Decimal 5 5 1 是 成绩，0100之间 特别注意：为属性Degree 约束，可参照属性Ssex进行设置，“CHECK约束对话框”中的表达式为 Degree>=1 And Degree<=100为SC表设置外键Sno和Cno的方法：右键单击表SC，选择“设计”命令，然后选择菜单“表设计器”中的“关系”命令，打开“外键关系”窗口，选择“添加”按钮，然后单击“表和列规范”后的按钮，按照图2进行设置，即可将SC表中的Cno属性设置为外键。按照相同的方法，将属性S

5、no也设置为外键。图2为SC表设置外键Cno2向上述表中输入如下数据记录 学生关系表Student （右键单击表Student，选择“编辑前200行”）Sno Sname Ssex Sbirthday Sdept Speciality 20050101 李勇 男 1987-01-12 计算机系计算机应用 20050201 刘晨 女 1988-06-04 信息管理系电子商务 20050301 王敏 女 1989-12-23 数学系数学 20050202 张立 男 1988-08-25 信息管理系电子商务 Sno Cno Degree 20050101 C01 92 20050101 C02 85

6、 20050101 C03 88 20050201 C02 90 20050201 C03 80 20050301C019120050301C027520050202C0187课程关系表Course 成绩表SCCno Cname C01 数据库 C02 数学 C03 信息系统 C04 操作系统 3修改表结构（找到操作的方法即可，不需要真正地修改表中的属性） （1）向student表中增加“入学时间”列，其数据类型为日期型（2）将student表中的sdept字段长度改为20 （3）将student表中的Speciality字段删除l 思考题 1 SQL Server的数据库文件有几种？扩展名分

7、别是什么？ 2 如何实现数据库的备份和还原？2在定义基本表语句时，NOT NULL参数的作用是什么？ 3主码可以建立在“值可以为NULL”的列上吗？实验二 简单查询l 实验目的 1. 掌握SELECT语句的基本用法 2. 使用WHERE子句进行有条件的查询 3. 掌握使用IN和NOT IN，BETWEENAND和NOT BETWEENAND来缩小查询范围的方法 4. 掌握聚集函数的使用方法 5. 利用LIKE子句实现字符串匹配查询 6. 利用ORDER BY子句对查询结果排序 7. 利用GROUP BY子句对查询结果分组 l 实验内容和要求 一、表结构修改 1在实验一的所建立的数据库中增加Te

8、acher表，结构如下： 字段名称 数据类型 长度 精度 小数位数 是否允许Null值 说明 Tno Char 3 0 0 否 教师号，主码 Tname Varchar 8 0 0 是 教师名Tsex Char 2 0 0 是 性别，取值：男或女 Tbirthday Date 8 0 0 是 出生日期Tdept Char 16 0 0 是 所在系2在实验一的所建立的数据库中增加Teaching表，表结构如下： Teaching表（授课表）的表结构 字段名称 数据类型 长度 精度 小数位数 是否允许Null值 说明 Cno Char 5 0 0 否 课程号，外码 Tno Char 3 0 0 否

9、 教师号，外码 Cterm Tinyint 1 0 0 是 1-8之间 3向上述两表中输入如下数据记录 Cno Tno Cterm C01 101 2 C02 102 1 C03 201 3 C04 202 4 教师表Teacher 授课表TeachingTno Tname Tsex Tbirthday Tdept 101 李新 男 1977-01-12 计算机系102 钱军 女 1968-06-04 计算机系201 王小花 女 1979-12-23 信息管理系202 张小青 男 1968-08-25 信息管理系二、完成下面查询 1查询所有男生信息2查询年龄大于24岁的女生学号和姓名3查询所有

10、教师的Tname、Tdept 4查询“电子商务”专业的学生姓名、性别和出生日期5查询成绩低于90分的学生学号及课号，并按成绩降序排列6查询Student表中所有的系名7查询“C01”课程的开课学期 8查询成绩在80分至90之间的学生学号及课号9统计有学生选修的课程门数10查询成绩为77，88或99的记录11计算“C02”课程的平均成绩12输出有成绩的学生学号 13查询所有姓“刘”的学生信息14统计输出各系学生的人数15查询选修了“C03”课程和学生的学号及其成绩，查询结果按分数的降序排列16查询各个课程号及相应的选课人数，并为选课人数取别名为“人数”17统计每门课程的选课人数和最高分，并为选课

11、人数和最高分分别取别名为“人数”、“最高分”18统计每个学生的选课门数和考试总成绩，并为选课门数和总成绩分别取别名为“门数”、“总成绩”，并按选课门数降序排列。 l 思考题 1聚集函数能否直接使用在：SELECT子句、HAVING子句、WHERE子句、GROUP BY子句中？ 2关键字ALL和DISTINCT有什么不同的含义？ 3SELECT语句中用于表示模糊查询的通配符有几种？含义分别是什么？ 4利用BETWEENAND表示的数据范围能否用其他方法表示？怎样表示？如：score between 80 and 90 还可以怎么表示？实验三 复杂查询l 实验目的 1. 掌握连接查询和嵌套查询操作

12、2. 能灵活应用SELECT语句解决复杂的查询问题l 实验内容和要求 一、连接查询1. 查询女学生的学生学号及总成绩 2. 查询李勇同学所选的课程号及成绩3. 查询李新老师所授课程的课程名称4. 查询女教师所授课程的课程号及课程名称5. 查询姓“王”的学生所学的课程名称 6. 查询选修“数据库”课程且成绩在80到90之间的学生学号及成绩7. 查询选修“C02”课程的学生的平均年龄8. 查询学习课程名为“数学”的学生学号和姓名9. 查询“钱军”教师任课的课程号，选修其课程的学生的学号和成绩10. 查询在第3学期所开课程的课程名称，选修其课程的学生学号和成绩二、嵌套查询1. 查询至少选修两门课程的

13、男学生姓名2. 查询与李勇同一个系的其他同学姓名3. 查询学号比刘晨同学大，而年龄比他小的学生姓名4. 查询年龄大于所有女同学的男同学的姓名及系别5. 查询成绩比该课程平均成绩高的学生的学号、课程号及成绩6. 查询不讲授“C01”课的教师姓名7. 查询没有选修“C02”课程的学生学号及姓名8. 查询选修了“数据库”课程的学生学号、姓名及系别 9. 查询选修了全部课程的学生姓名10. 分别用子查询和连接查询，求“C02”号课程在80分以上的学生信息l 思考题 1 使用存在量词NOT EXISTS的嵌套查询时，何时外层查询的WHERE条件为真，何时为假？ 2 DROP命令和DELETE命令的本质区

14、别是什么？ 实验四 数据更新、索引及视图操作l 实验目的 1. 掌握利用INSERT命令实现对表数据的插入操作2. 掌握利用UPDATE命令实现对表数据的修改操作3. 掌握利用DELETE命令实现对表数据的删除操作4. 掌握索引的创建和使用5. 掌握视图的创建和删除l 实验内容和要求 一、数据更新1 向Student表中插入记录（'20050203','张静','女','1981-3-21','CS' ,'电子商务'） 2 插入学号为20050302，姓名为李四学生信息3 把男学生记录保存到表TS中

15、（提示：利用select into 命令可备份数据库中的表格式：select * into 新表名 from 原表名 where 子句;)4 将学号为20050202的学生姓名改为张华，系别改为“CS”，专业改为“多媒体技术”5 将20050201学生选修“C03”号课程的成绩改为该课的平均成绩6 把女同学的成绩提高5% 7 把选修了“数据库”课程学生的成绩全改为空值（NULL）8 删除学号为20050302的学生记录9 把“刘晨”同学的选课记录全部删除10 删除电子商务专业所有学生的选课记录二、索引的创建与使用 1用索引管理器创建SC表的索引IX_XS_KC。 （1）选择要创建索引的数据库文

16、件夹，并在右边的对象窗口中选择并打开其中的“表”对象。 （2）选择所要创建索引的SC表，右键单击该表，在弹出的快捷菜单中选择“设计”命令，然后在工具栏中单击“管理索引和键”按钮，打开“索引和键”窗口。 （3）单击其中的“添加”按钮，创建新的索引，并为其设置相应的属性。 为SC表创建一个基于“课程号”列和“成绩”列的索引IX_XS_KC，其中课程号列按升序排列，成绩列按降序排列。 设置完成后，如图3所示。图3 创建索引IX_XS_KC2强制使用刚才创建的索引查询数据。 在查询命令窗口中输入以下SQL查询命令并执行： SELECT SNO,CNO,DEGREE FROM SC WITH （INDE

17、X （IX_XS_KC） WHERE DEGREE>=60 观察一下显示出来的数据是否有序。 3利用SQL命令实现下列操作： (1) 为Student表创建一个名为S_dept的索引，以系别排序 (2) 为Teacher表创建一个名为T_name的聚集索引，以Tname降序排序。命令格式：create clustered index on 表名(列名1 顺序，列名2 顺序)(3) 删除S_dept索引 。命令格式：drop index 表名.索引名 或 drop index 索引名 on 表名三、视图的创建与使用 1. 创建一个简单视图view1，查询“计算机系”学生的信息2. 创建一个

18、简单视图view2，统计每门课程的选课人数和最高分3. 创建一个复杂视图view3，查询与“张立”同学同一系别的学生信息4. 删除视图View3l 思考题 1利用INSERT、UPDATE和DELETE命令可以同时对多个表操作吗？ 2. 使用索引为什么能提高查询速度？ 3索引经常被建立在哪些字段上？ 4聚集索引与其他索引有什么不同？实验五 存储过程、函数、触发器及游标操作l 实验目的 1. 掌握compute by的使用2. 掌握变量的定义和使用方法3. 掌握存储过程的定义和执行方法4. 掌握触发器的定义方法5. 掌握函数的定义和调用方法6. 掌握游标的定义和使用方法（提示：以下操作全部基于实

19、验一创建的Grademanager数据库）l 实验内容和要求 一、 使用compute by完成下列操作1. 显示所有学生的成绩，并汇总每个学生的总分。2. 显示所有学生的信息，并汇总男女学生的人数。二、 存储过程1. 创建一个名为Query_student的存储过程，该存储过程的功能是根据学号查询学生表中某一学生的学号、姓名、性别及出生日期。执行存储过程Query_student，查询学号为“20050301”的学生的学号、姓名、性别及出生日期。写出完成此功能的SQL命令。2. 创建存储过程sm_score，用于求指定学号（输入参数）的学生的总成绩（输出参数）。执行该存储过程，指定学号为“2

20、0050101 ”。3. 创建并执行存储过程count_sno，用于求所有学生人数，通过参数返回学生人数。4. 删除存储过程count_sno。5. 创建一个向课程表中插入一门新课程的存储过程Insert_course，该存储过程需要两个参数，分别用来传递课程号、课程名；执行存储过程Insert_course，向课程表Course中插入一门新课程（0807011，Flash动画设计）。6. 创建一个能向学生表中插入一条记录的存储过程Insert_student，执行存储过程Insert_student，向学生表中插入一个新同学（'20060208','徐小花',

21、'女', '1987-08-25', 'IS','电子商务'）。三、 触发器1. 在SC表创建一个INSERT触发器，如果插入一条选课信息时，学生的学号值在表S中不存在，则不执行插入操作，并提示用户。2. 使用insert语句向SC表中插入记录（'20020101', 'C01'）, 观察触发器INSERT_SC是否被执行。3. 在Teaching表中创建一个插入触发器insert_teaching，一旦发现待插入的教师号Tno在Teacher表中不存在，就发出警告信息并禁止插入。4. 创建一个当学生表中插入一个新同学时能自动列出全部同学学生信息的触发器Display_trigger。5. 调用“存储过程部分”的第5题创建的存储过程Insert_student，向学生表中插入一新同学，新同学信息（'20060311','张丽丽','女'