SQL - SERVER - 2005上机考试

SQL SERVER 2005上机操作题

注意:1、除题目中有明确注明，否则可选择SSMS或SQL语句完成。操作题不要求写

步骤，最后将数据库完全备份文件上交,写SQL语句和问答第六题答案写在此文档相应题目后面。

2、SQL语句题上机调试正确才可写到题目中，否则不计分。

3、11：15考试结束，将此文档和STUxxxx数据库完全备份文件打包成XXX.rar

文件（以学号姓名班级命名），通过右下角任务栏多媒体网络教学系统?提交作业，将文件上传。 一、 二、

创建数据库STUxxxx（其中xxxx为自己的学号，创建方法和数据库参数自定）。在STUxxxx数据库中创建如下的表结构： 如：CREATE DATABASE STU200800101

1．学生情况表（XS）结构

列名 数据类型 长度 可否为空 默认值 说明 学号 Char 7 否 无 主码 姓名 Char 8 否 无 性别 Char 4 否 ‘男’ 只能为‘男’或‘女’ 出生日期 smalldatetime 否 无 班级 Char 5 是 ‘41021’ 总学分 Tinyint 是 0 备注 Text 是 无 2．课程表（KC）结构

列名 课程号 课程名 开课学期 学时 学分 数据类型 Char Char Tinyint Tinyint Tinyint 长度 3 20 可否为空 否 否 否 否 否 默认值 无 无 1 无 0 说明 主码 只能为1~8 3．学生与课程表（XS_KC）结构

列名 学号 课程号 成绩 学分 数据类型 长度 可否为空 Char 7 否 Char Tinyint Tinyint 3 否 是 是 默认值 说明 无 主码 外码（参照学生表中的学号） 无 主码 外码（参照课程表中的课程号） 0 0≤成绩≤100 0 三、 使用SQL语句对以上的表结构进行修改,将调试正确的语句写在下面每小题后

面。

1. 修改学生情况表（XS）的“出生日期”字段，使其数据类型改为datetime，且允许为空。

2. 删除学生与课程表（XS_KC）中的“学分”字段。

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

向数据库STUxxxx的XS表、KC表和XS_KC表中插入或修改成如下数据： 学号 4102101 4102102 4202103 4202110 4202221 4302101 4402130 性别 王林 1 吴荣华 0 张强 1 王向荣 1 李丽 0 刘明 1 叶凡 1 姓名 学生情况表（XS）

出生日期 专业 总学分 1983-1-23 计算机 1984-3-28 计算机 1981-11-19 电子 1980-12-9 电子 1982-7-30 电子 1982-10-18 自控 1983-11-18 数学

40 44 NULL 50 NULL 38 46 备注 NULL NULL NULL NULL NULL NULL 转专业学习 课程号 101 102 103 201 202 301 课程表（KC） 课程名 开课学期 学时 计算机基础 1 48 C语言 2 80 数据库 4 64 离散数学 6 64 计算机网络 7 64 软件工程 6 64 学生与课程表（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 学分 3 5 4 4 4 4 五、 以下题目均在STUxxxx（学生管理）数据库中完成,将调试正确的语句和查询结

果写在下面相应题目后面。每大项只做自己学号最后一位对应的题目，1号做第1题，2号做第2题，?,0号做第10题。 （一） 简单查询

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

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

3. 查询XS表中所有同学的学号、姓名和总学分，结果中各列的标题分别指定为num，

name和mark。

4. 查询XS表中的学生数据来自哪些专业（使用DISTINCT子句消除结果集中的重复

行）。

5. 查询XS表中每个学生的学号、姓名和年龄信息。 6. 查询XS表中专业为“计算机”的同学的情况。 7. 查询XS表中1979年出生的学生姓名和专业情况。

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

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

学号由低到高排序。 （二） 数据汇总

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的每个学生的基本情况及成绩。 5. 查询生日相同的学生的姓名和出生日期。格式如下

姓名1 ??

姓名2 ??

出生日期 ??

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

课程名 ??

总平均 专业 ??

平均成绩 ?? ?? 课程名 ??

平均成绩 ??

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

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

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

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

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

基础”成绩从高到低排序。

9. 在XS_KC表中查询其学号在XS表中不存在或其课程号在KC表中不存在的学生

的成绩信息。

10. 查询选修全部课程的学生的基本信息。 （五） 数据的插入、删除和修改

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表中前两个同学的基本信息。

----------------------------------------------------------------------- 六、 题。

1. 查看所使用的SQL SERVER服务器的安全验证模式。

2. 创建SQL SERVER认证模式的登录帐号AA，并使该帐号成为“Database Creators”服务器角色的成员。请问，此时该登录帐号具有什么权限？

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

4. 用创建的新登录帐号BB登录服务器，对STUxxxx（学生管理）数据库进行访问，此时能对数据库进行什么操作？为什么？

5. 用原来的登录帐号登录服务器，为STUxxxx（学生管理）数据库创建GUEST用户，并使GUEST用户具有对STUxxxx（学生管理）数据库中XS表和KC表的SELECT权限。然后，用创建的新登录帐号登录服务器，对STUxxxx（学生管理）数据库进行访问，此时能对数据库进行什么操作？为什么？

6. 用原来的登录帐号登录服务器，为STUxxxx（学生管理）数据库添加新的用户（对应的登录帐号为创建的新登录帐号），授予该用户具有在STUxxxx（学生管理）数据库中创建视图的权限和对XS、KC和XS_KC表的SELECT权限，并拒绝其创建表的权限和对XS、KC和XS_KC表的修改、删除、插入的权限。然后，用新登录帐号登录服务器，验证所设置权限的正确性。

7. 用原来的登录帐号登录服务器，把以上的权限设置用数据库角色来实现，并验证设置权限的正确性。

数据库的安全性管理 以下题目要求在SSMS中实现，并在题目后面回答相应问

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