实验14：SQL_SERVER_2005综合练习（全）

实验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中插⼊多⾏数据（专业为“电⼦”的学⽣数据）。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表中的数据进⾏输⼊。（⼋）数据库编程1．使⽤游标实现如下功能：显⽰每个学⽣的学号、姓名、各门功课的成绩与平均成绩。格式如下：学号姓名课程名称成绩

……………………

2．使⽤游标实现如下功能：修改XS表中“总学分”字段的值，使其为该学⽣所学各门功课的学分之和。

3．创建⼀存储过程proc1，显⽰指定学⽣指定课程的成绩，然后执⾏该存储过程（⽤两种⽅法执⾏）。

4．创建⼀存储过程proc2，显⽰其指定课程的成绩在指定范围内的学⽣学号、姓名、课程名和成绩，并返回其⼈数，然后执⾏该存储过程。（⽤两种⽅法执⾏）。5．创建⼀存储过程xs_insert,⽤于实现往xs表中插⼊⼀条记录，并返回执⾏状态码信息：如果插⼊成功，则返回0；否则，则返回1。6．创建⼀存储过程xs_update,⽤于对指定学号指定课程名称的学⽣成绩进⾏修改。

7．创建⼀存储过程，⽤于统计每个专业计算机基础成绩在各个分数段的学⽣⼈数。分数段划分为：低于60分，60～70分，70 ～80分，80～90分，90～100分。

（九）数据库的安全性管理1.查看所使⽤的SQL SERVER服务器的安全验证模式。

2.创建⼀SQL SERVER认证模式的登录帐号，并使该帐号成为“Database Creators”服务器⾓

⾊的成员。请问，此时该登录帐号具有什么权限？3.创建⼀Windows NT认证模式的登录帐号，并使该帐号成为STUxxxx（学⽣管理）数据库的

DB_Owner⾓⾊的成员。请问，此时该登录帐号具有什么权限？

4.⽤创建的新登录帐号登录服务器，对STUxxxx（学⽣管理）数据库进⾏访问，此时你能对数据

库进⾏什么操作？为什么？5.⽤原来的登录帐号登录服务器，为STUxxxx（学⽣管理）数据库创建⼀GUEST⽤户，并使GUEST

⽤户具有对STUxxxx（学⽣管理）数据库中XS表和KC表的SELECT权限。然后，⽤创建的新登录帐号登录服务器，对STUxxxx（学⽣管理）数据库进⾏访问，此时你能对数据库进⾏什么操作？为什么？

6.⽤原来的登录帐号登录服务器，为STUxxxx（学⽣管理）数据库添加⼀新的⽤户（对应的登录

帐号为创建的新登录帐号），授予该⽤户具有在STUxxxx（学⽣管理）数据库中创建视图的权

限和对XS、KC和XS_KC表的SELECT权限，并拒绝其创建表的权限和对XS、KC和XS_KC表的修改、删除、插⼊的权限。然后，⽤新登录帐号登录服务器，验证所设置权限的正确性。7.⽤原来的登录帐号登录服务器，把以上的权限设置⽤数据库⾓⾊来实现，并验证设置权限的