优质SQLSERVER上机考试

1、SQL SERVER 2005上机操作题注意:1、除题目中有明确注明，否则可选择 SSMS或SQL语句完成。操作题不要求写 步骤，最后将数据库完全备份文件上交，写SQL语句和问答第六题答案写在此文 档相应题目后面。2、SQL语句题上机调试正确 才可写到题目中，否则不计分。3、11: 15考试结束，将此文档和 STUxxxx数据库完全备份文件打包成 XXX.rar 文件（以学号姓名班级命名），通过右下角任务栏多媒体网络教学系统 提交作业, 将文件上传。一、创建数据库STUxxxx （其中xxxx为自己的学号，创建方法和数据库参数自定） 女口： CREATE DATABASE STU2008001

2、01二、在STUxxxx数据库中创建如下的表结构：1 学生情况表（XS）结构列名数据类型长度可否为空默认值说明学号Char7否无主码姓名Char8否无性别Char4否男只能为男或女出生日期:smalldatetime否无班级Char5是 41021总学分1Ti nyi nt是0备注Text是无2 .课程表（KC）结构列名数据类型长度可否为空默认值说明课程号Char3否无主码课程名Char20否无开课学期Ti nyi nt否1只能为18学时Ti nyi nt否无学分Ti nyint否03. 学生与课程表（XS_KC）结构列名数据类型:长度可否为空默认值说明学号Char7否无主码外码（参照学生表

3、中的学号）课程号Char3否无主码外码（参照课程表 中的课程号）成绩Ti nyi nt是00W成绩w 100学分Ti nyint是0三、使用SQL语句对以上的表结构进行修改,将调试正确的语句写在下面每小题后 面。1. 修改学生情况表（XS）的“出生日期”字段，使其数据类型改为 datetime，且允 许为空。2. 删除学生与课程表（XS_KC）中的“学分”字段。3. 修改学生情况表（XS），删除“班级”字段，增加一“专业”字段（varchar（20）, 默认值为“计算机”）四、向数据库STUxxxx的XS表、KC表和XS_K（表中插入或修改成如下数据：学生情况表（XS学号姓名性 别出生日期专业

4、总学分备注4102101王林11983-1-23计算机40NULL4102102吴荣华01984-3-28计算机44NULL4202103张强11981-11-19电子NULLNULL :4202110王向荣11980-12-9电子50NULL4202221李丽01982-7-30电子NULLNULL4302101刘明11982-10-18自控38NULL4402130叶凡11983-11-18数学46转专业学习课程表（KC）课程号课程名开课学期学时学分101计算机基础1483102C语言2805103数据库r 4644201离散数学6644202计算机网络7644301软件工程6644学生与

5、课程表（XS KC）学号课程号成绩4102101101804102101102894102101103784102102101r 574102102102674202103103904202110101P 854202110102914202221101694402130101P 78440213010374五、以下题目均在STUxxxx（学生管理）数据库中完成,将调试正确的语句和查询结果写在下面相应题目后面。每大项只做自己学号最后一位对应的题目，1号做第1 题，2号做第2题，,0号做第10题。（一）简单查询1. 查询XS表中各个同学的所有信息。2. 查询XS表中各个同学的姓名、专业名和总学分

6、。3. 查询XS表中所有同学的学号、姓名和总学分，结果中各列的标题分别指定为num name和 mark。4. 查询XS表中的学生数据来自哪些专业（使用 DISTINCT子句消除结果集中的重复 行）。5. 查询XS表中每个学生的学号、姓名和年龄信息。6. 查询XS表中专业为“计算机”的同学的情况。7. 查询XS表中1979年出生的学生姓名和专业情况。8. 查询XS表中专业名为“计算机”或“电子”或“数学”的学生的情况。9. 查询XS表中姓“张”或“王”或“李”的学生的情况。10. 从XS表中查询学生的基本信息，要求按照总学分从高到低排序，学分相同时，按 学号由低到高排序。（二）数据汇总1. 求

7、选修了“ 101 ”课程的学生的平均成绩。2. 求选修了“ 102”课程的学生的最高分和最低分。3. 求学号为“ 4102101 ”学生的总成绩。4. 求专业为“计算机”的学生的总人数。5. 求选修了“ 101 ”课程的学生的人数。6. 求选修了任意一门课程的学生的人数。7. 统计各个专业的学生数。（按专业分组）8. 统计各个专业的男女生人数。格式如下：专业性别人数9. 查找平均成绩在80分以上的学生的学号和平均成绩。10. 查找选修的课程中超过2门成绩在80分以上的学生的学号和成绩高于 80分的门 数。格式如下：学号成绩高于80分的门数（三）连接查询1. 查询每个学生的基本情况及选修的课程情

8、况，格式如下: 学号 姓名 专业 课程号成绩2. 查询选修了课程号为101的每个学生的姓名及成绩。3. 查询“计算机”专业且选修了 “计算机基础”课程的学生的学号、姓名及成绩4. 查询选修了课程号为101的每个学生的基本情况及成绩。5. 查询生日相同的学生的姓名和出生日期。格式如下姓名1 姓名2 出生日期其中：“姓名T和“姓名2”中的姓名不能相同。6. 显示每门课程的平均成绩并显示总平均成绩。格式如下: 课程名平均成绩总平均7. 查询各个专业各门课程的平均成绩。格式如下专业课程名平均成绩8. 查询“计算机”专业平均成绩在 85分以上的学生的学号、姓名和平均成绩。9. 查询成绩在90分以上的学生

9、的学号、姓名和成绩。10. 查询成绩在60分以下的学生的姓名和成绩。（四）子查询的使用1. 查询平均成绩高于101号课程平均分的课程号及平均成绩。2. 查询“计算机基础”成绩的及格率。3. 查询101号课程及格的学生的基本情况。4. 查找比所有计算机专业的学生年龄都大的学生的情况。5. 查找未选修“ C语言”课程的学生的情况。（嵌套子查询）6. 查询每个专业年龄最小的学生的基本信息。（相关子查询）7. 查找选修了“计算机基础”课程的学生的情况。8. 查询“计算机基础”成绩高于平均分的学生的学号，姓名和成绩，并按“计算机 基础”成绩从高到低排序。9. 在XS_KC表中查询其学号在XS表中不存在或

10、其课程号在 KC表中不存在的学生 的成绩信息。10. 查询选修全部课程的学生的基本信息。（五）数据的插入、删除和修改1. 创建表XS1 （包括学号、姓名、性别和出生日期字段，各个字段属性自定），然后使用SELECT?句向表XS1中插入多行数据（专业为“电子”的学生数据）。2. 修改XS1表的结构，增加1个“平均成绩”字段，并根据 XS_KC表修改每个学 生的“平均成绩”。3. 将XS表中计算机专业的学生的总学分增加 10分。4. 将XS表中学号为“ 4102101 ”的同学的总学分增加4分,备注改为“提前修完一门 课程”。5. 将XS_K（表中学号为“ 4102101 ”的同学的“计算机基础”

11、课程的成绩增加 10分6. 修改XS1表的结构，增加1个“总学分”字段，并使其值为该学生所学各门功课 的学分之和。7. 将XS1表中总学分小于10分的学生数据删除。8. 将XS_K（表中“数据库”课程的所有成绩信息删除。9. 将XS_K（表中其学号在XS表中不存在或其课程号在 KC表中不存在的学生的成绩 信息删除。10. 删除XS1表中前两个同学的基本信息。六、数据库的安全性管理 以下题目要求在SSMS实现，并在题目后面回答相应问题。1. 查看所使用的SQL SERVE服务器的安全验证模式。2. 创建SQLSERVER证模式的登录帐号 AA并使该帐号成为“Database Creators ”

12、 服务器角色的成员。请问，此时该登录帐号具有什么权限？3. 创建 Windows认证模式的登录帐号 BB,并使该帐号成为STUxxxx （学生管理）数 据库的DB_Owne角色的成员。请问，此时该登录帐号具有什么权限？4. 用创建的新登录帐号BB登录服务器，对STUxxxx （学生管理）数据库进行访问， 此时能对数据库进行什么操作？为什么？5. 用原来的登录帐号登录服务器，为 STUxxxx （学生管理）数据库创建GUES用户， 并使GUES用户具有对STUxxxx （学生管理）数据库中XS表和KC表的SELECT权 限。然后，用创建的新登录帐号登录服务器，对 STUxxxx （学生管理）数据库进行 访问，此时能对数据库进行什么操作？为什么？6. 用原来的登录帐号登录服务器，为STUxxxx （学生管理）数据库添加新的用户（