SQL SERVER 2005上机实验

SQL SERVER 2005上机操作题

一、在指定的服务器上创建数据库STUxxxx（其中xxxx为自己的学号，创建方法和数据库参数自

定）。如：CREATE DATABASE stu200800101

二、在STUxxxx数据库中使用SQL语句创建如下的表结构： 1．学生情况表（XS）结构

列名 学号 姓名 性别 出生日期 班级 总学分 备注 2．课程表（KC）结构

列名 课程号 课程名 开课学期 学时 学分 数据类型 Char Char Tinyint Tinyint Tinyint 长度 3 20 可否为空 否 否 否 否 否 默认值 无 无 1 无 0 说明 主键 只能为1~8 数据类型 Char Char Bit smalldatetime Char Tinyint Text 长度 7 8 5 可否为空 否 否 否 否 是 是 是 默认值 无 无 1 无 ‘41021’ 0 无 说明 主键 男1，女0 3．学生与课程表（XS_KC）结构

列名 学号 课程号 成绩 学分 数据类型 Char Char Tinyint Tinyint 长度 7 3 可否为空 默认值 否 否 是 是 无 无 0 0 说明 外键（参照学生表中的学号） 外键（参照课程表中的课程号） 0≤成绩≤100 三、使用语句对以上的表结构进行修改

1. 修改学生情况表（XS）的“出生日期”字段，使其数据类型改为datetime，且允许为空。 2. 删除学生与课程表（XS_KC）中的“学分”字段。

3. 修改学生情况表（XS），删除“班级”字段，增加一“专业”字段（varchar(20), 默认值

为“计算机”）

四、向数据库STUxxxx的XS表、KC表和XS_KC表中插入或修改成如下数据：

学生情况表（XS）

学号 4102101 姓名 王林 性别 1 出生日期 1983-1-23 专业 计算机 总学分 40 备注 NULL 4102102 4202103 4202110 4202221 4302101 4402130 吴荣华 张强 王向荣 李丽 刘明 叶凡 0 1 1 0 1 1 1984-3-28 1981-11-19 1980-12-9 1982-7-30 1982-10-18 1983-11-18 计算机 电子 电子 电子 自控 数学 44 NULL 50 NULL 38 46 NULL NULL NULL NULL NULL 转专业学习

课程表（KC）

课程号 101 102 103 201 202 301 课程名 计算机基础 C语言 数据库 离散数学 计算机网络 软件工程 开课学期 1 2 4 6 7 6 学时 48 80 64 64 64 64 学分 3 5 4 4 4 4

学生与课程表（XS_KC）

学号 4102101 4102101 4102101 4102102 4102102 4202103 4202110 4202110 4202221 4402130 4402130 课程号 101 102 103 101 102 103 101 102 101 101 103 成绩 80 89 78 57 67 90 85 91 69 78 74 五、以下题目均在STUxxxx（学生管理）数据库中完成。 （一） 简单查询

1. 查询XS表中各个同学的所有信息。

2. 查询XS表中各个同学的姓名、专业名和总学分。

3. 查询XS表中所有同学的学号、姓名和总学分，结果中各列的标题分别指定为num，name和mark。 4. 查询XS表中的学生数据来自哪些专业（使用DISTINCT子句消除结果集中的重复行）。 5. 查询XS表中各个同学的姓名、专业名和总学分，只返回结果集的前5行。 6. 查询XS表中每个学生的学号、姓名和年龄信息。 7. 查询XS表中专业为“计算机”的同学的情况。 8. 查询XS表中1979年出生的学生姓名和专业情况。

9. 查询XS表中专业名为“计算机”或“电子”或“数学”的学生的情况。

10. 查询XS表中姓“张”或“王”或“李”且单名的学生的情况。 11. 查询XS表中总学分尚未确定的学生情况。

12. 查询XS表中专业为“计算机”且总学分尚未确定的学生情况。

13. 从XS表中查询学生的基本信息，要求按照总学分从高到低排序，学分相同时，按学号由低到

高排序。

14. 查询年龄最小的前三个同学的姓名、专业和年龄。 （二） 数据汇总

1. 求选修了“101”课程的学生的平均成绩。 2. 求选修了“102”课程的学生的最高分和最低分。 3. 求学号为“4102101”学生的总成绩。 4. 求专业为“计算机”的学生的总人数。 5. 求选修了“101”课程的学生的人数。 6. 求选修了任意一门课程的学生的人数。 7. 统计各个专业的学生数。（按专业分组） 8. 统计各个专业的男女生人数。格式如下：

专业 ??

性别 ??

人数 ??

9. 查找平均成绩在80分以上的学生的学号和平均成绩。

10. 查找选修的课程中超过2门成绩在80分以上的学生的学号和成绩高于80分的门数。格式如

下：

学号 ??

（三） 连接查询

1. 查询每个学生的基本情况及选修的课程情况，格式如下：

学号 ??

姓名 ??

专业 ??

课程号 成绩 ??

??

成绩高于80分的门数

??

2. 查询选修了课程号为101的每个学生的姓名及成绩。

3. 查询 “计算机”专业且选修了“计算机基础”课程的学生的学号、姓名及成绩。 4. 查询选修了课程号为101的每个学生的基本情况及成绩，若学生未选修101号课程，也包括

其情况。

5. 查询生日相同的学生的姓名和出生日期。格式如下

姓名1 ??

姓名2 ??

出生日期 ??

其中：“姓名1”和“姓名2”中的姓名不能相同。 6. 显示每门课程的平均成绩并显示总平均成绩。格式如下：

课程名 ??

平均成绩 ??

总平均 ??

7. 查询各个专业各门课程的平均成绩。格式如下

专业 ??

课程名 ??

平均成绩 ??

8. 查询“计算机”专业平均成绩在85分以上的学生的学号、姓名和平均成绩。 （四） 子查询的使用

1. 查询平均成绩高于101号课程平均分的课程号及平均成绩。 2. 查询“计算机基础”成绩的及格率。 3. 查询101号课程及格的学生的基本情况。

4. 查找比所有计算机专业的学生年龄都大的学生的情况。 5. 查找未选修“C语言”课程的学生的情况。(嵌套子查询) 6. 查询每个专业年龄最小的学生的基本信息。（相关子查询） 7. 查找选修了“计算机基础”课程的学生的情况。

8. 查询“计算机基础”成绩高于平均分的学生的学号，姓名和成绩，并按“计算机基础”成绩

从高到低排序。

9. 查询“计算机基础”成绩及格的学生的学号、姓名、性别和专业情况。

10. 在XS_KC表中查询其学号在XS表中不存在或其课程号在KC表中不存在的学生的成绩信息。 11. 查询选修全部课程的学生的基本信息。

12. 查询至少选修了“4102102”同学选修课程的学生的基本信息。 （五） 数据的插入、删除和修改

1. 创建表XS1（包括学号、姓名、性别和出生日期字段，各个字段属性自定），然后使用SELECT

子句向表XS1中插入多行数据（专业为“电子”的学生数据）。

2. 修改XS1表的结构，增加1个“平均成绩”字段，并根据XS_KC表修改每个学生的“平均

成绩”。

3. 将XS表中计算机专业的学生的总学分增加10分。

4. 将XS表中学号为“4102101”的同学的总学分增加4分,备注改为“提前修完一门课程”。 5. 将XS_KC表中学号为“4102101”的同学的“计算机基础”课程的成绩增加10分。

6. 修改XS1表的结构，增加1个“总学分”字段，并使其值为该学生所学各门功课的学分之和。 7. 将XS1表中总学分小于10分的学生数据删除。 8. 将XS_KC表中“数据库”课程的所有成绩信息删除。

9. 将XS_KC表中其学号在XS表中不存在或其课程号在KC表中不存在的学生的成绩信息删除。 10. 删除XS1表中前两个同学的基本信息。 11. 删除XS1表中的所有数据。 （六） 视图操作

1. 使用SQL语句创建视图V_SCORE1，使其显示计算机专业学生的所有基本信息，并保证以后对

该视图的修改都要满足专业为“计算机”这个条件。

3. 使用SQL语句创建视图V_SCORE2，显示计算机专业每个学生的学号、姓名、选修的课程名称

及其成绩。

4. 使用T-SQL语句修改视图V_SCORE2，使其显示每个专业、每门课程的平均成绩，结果格式为：

专业 课程名 平均成绩 ??

??

??

5. 通过V_SCORE2查看计算机专业每门课程的平均成绩。

6. 通过V_SCORE2查看各个专业所有课程的平均成绩，结果格式为：

专业 平均成绩 ??

??

7. 使用视图V_SCORE1向其基表XS表中输入数据（数据自定），并观察什么样的数据可以插入，

什么样的数据不可以插入，并解释为什么。 8. 使用SQL语句删除视图V_SCORE1。 （七） 数据完整性

1. 用户要求：XS表的学号列、KC表的课程号列、XS_KC表的学号和课程号列必须惟一且不能为

空，请问如何解决该问题？请写出一种具体的解决方法。

2. 用户要求：XS表中的学生的专业只能是“计算机”，“数学”，“电子”中的任意一个，请问如

何解决该问题？请写出一种具体的解决方法。

3. 用户要求：XS表中的学生的年龄必须在18岁到30岁之间，请问如何解决该问题？请写出一

种具体的解决方法。

4. 用户要求：KC表中的课程号代表课程的编号，编号的每一位只能由0—9的数字组成，请问如

何解决该问题？请写出一种具体的解决方法。

5. 用户要求：在输入XS表中的数据时，若“专业”列没有提供数据，系统应自动取值“计算机”，

请问如何解决该问题？请写出一种具体的解决方法。

6. 用户要求：XS_KC表中的成绩列的取值只能为0—100之间的整数，且在用户没有输入数据时

自动取值0，请问如何解决该问题？请写出一种具体的解决方法。

7. 编写语句为XS_KC表的“学号”列添加外键约束，该约束参照XS表的主键“学号”列。该约

束用于实现何种完整性？向具有该约束的表中添加数据或修改数据时，应注意什么？ 8. 用户要求：XS_KC表中的“课程号”列的取值应依赖于KC表中“课程号”的取值，且当KC

表中某个“课程号”的取值发生改变时，XS_KC表中的对应的“课程号”也应级联更新，当KC表中某个“课程号”被删除时，XS_KC表中对应该“课程号”的数据也应级联删除，请问如何解决该问题？请写出具体的解决方法。

9. 创建一触发器，当向XS_KC表插入一记录时，检查该记录的学号在XS表中是否存在，检查该

记录的课程号在KC表中是否存在，若有一项为否，则不允许插入。

10. 创建一触发器，当在XS表中删除一个学生的基本信息时，级联删除该学生的学习信息（即该

学生在XS_KC表中的数据）。

11. 在XS_KC表上创建一触发器，若对学号列和课程号列修改,且修改后的学号或课程号在XS表

和KC表中不存在,则给出提示信息,并取消修改操作。

本文来源：https://www.bwwdw.com/article/r4z5.html