实验14：SQL_SERVER_2005综合练习（全）SQL SERVER 2005综合练习题
⼀、创建数据库STUxxxx（其中xxxx为⾃⼰的姓名，创建⽅法和数据库参数⾃定）。
⼆、在STUxxxx数据库中使⽤SQL语句创建如下的表结构：
1．学⽣情况表（XS）结构
2．课程表（KC）结构
3．学⽣与课程表（XS_KC）结构
三、使⽤语句对以上的表结构进⾏修改
1.修改学⽣情况表（XS）的“出⽣⽇期”字段，使其数据类型改为datetime，且允许为空。
2.删除学⽣与课程表（XS_KC）中的“学分”字段。
3.修改学⽣情况表（XS），删除“班级”字段，增加⼀“专业”字段（varchar(20), 默认值
为“计算机”）
四、向数据库STUxxxx的XS表、KC表和XS_KC表中插⼊或修改成如下数据：
学⽣情况表（XS）
课程表（KC）
学⽣与课程表（XS_KC）
五、以下题⽬均在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.查询“计算机基础”成绩⾼于平均分的学⽣的学号，姓名和成绩，并按“计算机基础”成绩
从⾼到低排序。
9.查询“计算机基础”成绩及格的学⽣的学号、姓名、性别和专业情况。
10.在XS_KC表中查询其学号在XS表中不存在或其课程号在KC表中不存在的学⽣的成绩信息。
11.查询选修全部课程的学⽣的基本信息。
12.查询⾄少选修了“4102102”同学选修课程的学⽣的基本信息。
（五）数据的插⼊、删除和修改
1.创建表XS1（包括学号、姓名、性别和出⽣⽇期字段，各个字段属性⾃定），然后使⽤SELECT ⼦句向表XS1中插⼊多⾏数据（专业为“电⼦”的学⽣数据）。
distinct查询
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表中不存在，则给出提⽰信息，并取消修改操作。
12.在XS_KC表上创建⼀触发器，当对学⽣的学习成绩进⾏插⼊或修改时，根据学习成绩级联更
新该学⽣在XS表中的总学分（对于插⼊操作，如果成绩及格，该学⽣的总学分应为原来的总学分加上该门课程的规定学分，否则总学分不变；对于修改操作，若原成绩不及格⽽修改后
的成绩及格，则该学⽣的总学分应为原来的总学分加上该门课程的规定学分，若原成绩及格⽽修改后的成绩不及格，则该学⽣的总学分应为原来的总学分减去该门课程的规定学分）。13.创建⼀视图，该视图包括学⽣的学号、姓名、性别、出⽣⽇期、专业、课程号和成绩，现在
该视图上创建INSTEAD OF触发器，使通过该视图可以对XS表和XS_KC表中的数据进⾏输⼊。（⼋）数据库编程

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。