SQL SERVER 2005上机操作题
一、 在指定的服务器上创建数据库STUxxxx（其中xxxx为自己的学号，创建方法和数据库参数自定）。如：CREATE DATABASE stu200800101
二、 在STUxxxx数据库中使用SQL语句创建如下的表结构： 
1．学生情况表（XS）结构   
列名
数据类型
长度
可否为空
默认值
说明
学号
Char
7
否
无
主键
姓名
Char
8
否
无
性别
Bit
否
1
男1，女0
出生日期
smalldatetime
否
无
班级
Char
5
是
‘41021’
总学分
Tinyint
是
0
备注
Text
是
无
2．课程表（KC）结构
列名
数据类型
长度
可否为空
默认值
说明
课程号
Char
3
否
无
主键
课程名
Char
20
否
无
开课学期
Tinyint
否
1
只能为1~8
学时
Tinyint
否
无
学分
Tinyint
否
0
3．学生与课程表（XS_KC）结构
列名
数据类型
长度
可否为空
默认值
说明
学号
Char
7
否
无
外键（参照学生表中的学号）
课程号
Char
3
否
无
外键（参照课程表中的课程号）
成绩
Tinyint
是
0
0≤成绩≤100
学分
Tinyint
是
0
三、 使用语句对以上的表结构进行修改
1. 修改学生情况表（XS）的“出生日期”字段，使其数据类型改为datetime，且允许为空。
2. 删除学生与课程表（XS_KC）中的“学分”字段。
3. 修改学生情况表（XS），删除“班级”字段，增加一“专业”字段（varchar(20),  默认值为“计算机”）
四、 向数据库STUxxxx的XS表、KC表和XS_KC表中插入或修改成如下数据：
学生情况表（XS）
学号
姓名
性别
出生日期
专业
总学分
备注
4102101
王林
1
1983-1-23
计算机
40
NULL
4102102
吴荣华
0
1984-3-28
计算机
44
NULL
4202103
张强
1
1981-11-19
电子
NULL
NULL
4202110
王向荣
1
1980-12-9
电子
50
NULL
4202221
李丽
0
1982-7-30
电子
NULL
NULL
4302101
刘明
1
1982-10-18
自控
38
NULL
4402130
叶凡
1
1983-11-18
数学
46
转专业学习
课程表（KC）
课程号
课程名
开课学期
学时
学分
101
计算机基础
1
48
3
102
C语言
2
80
5
103
数据库
4
64
4
201
离散数学
6
64
4
202
计算机网络
7
64
4
301
软件工程
6
64
4
学生与课程表（XS_KC）
学号
课程号
成绩
4102101
101
80
4102101
102
89
4102101
103
78
4102102
101
57
4102102
102
67
4202103
103
90
4202110
101
85
4202110
102
91
4202221
101
69
4402130
101
78
4402130
103
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分的门数。格式如下：
学号        成绩高于80分的门数
……                ……
（三） 连接查询
1. 查询每个学生的基本情况及选修的课程情况，格式如下：
学号    姓名    专业    课程号    成绩
……    ……    ……    ……    ……
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. 查询“计算机基础”成绩高于平均分的学生的学号，姓名和成绩，并按“计算机基础”sql触发器的使用成绩从高到低排序。
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，使其显示计算机专业学生的所有基本信息，并保证以后对该视图的修改都要满足专业为“计算机”这个条件
2. 。
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表中不存在,则给出提示信息,并取消修改操作。
12. 在XS_KC表上创建一触发器，当对学生的学习成绩进行插入或修改时，根据学习成绩级
联更新该学生在XS表中的总学分（对于插入操作，如果成绩及格，该学生的总学分应为原来的总学分加上该门课程的规定学分，否则总学分不变；对于修改操作，若原成绩不及格而修改后的成绩及格，则该学生的总学分应为原来的总学分加上该门课程的规定学分，若原成绩及格而修改后的成绩不及格，则该学生的总学分应为原来的总学分减去该门课程的规定学分）。