SQL SERVER 2005上机实验

SQL SERVER 2005上机操作题

一、在指定的服务器上创建数据库STUxxxx（其中xxxx为自己的学号，创建方法和数据库参数自

定）。如：CREATE DATABASE stu200800101

二、在STUxxxx数据库中使用SQL语句创建如下的表结构： 1．学生情况表（XS）结构

列名 学号 姓名 性别 出生日期 班级 总学分 备注 2．课程表（KC）结构

列名 课程号 课程名 开课学期 学时 学分 数据类型 Char Char Tinyint Tinyint Tinyint 长度 3 20 可否为空 否 否 否 否 否 默认值 无 无 1 无 0 说明 主键 只能为1~8 数据类型 Char Char Bit smalldatetime Char Tinyint Text 长度 7 8 5 可否为空 否 否 否 否 是 是 是 默认值 无 无 1 无 ‘41021’ 0 无 说明 主键 男1，女0 3．学生与课程表（XS_KC）结构

列名 学号 课程号 成绩 学分 数据类型 Char Char Tinyint Tinyint 长度 7 3 可否为空 默认值 否 否 是 是 无 无 0 0 说明 外键（参照学生表中的学号） 外键（参照课程表中的课程号） 0≤成绩≤100 三、使用语句对以上的表结构进行修改

1. 修改学生情况表（XS）的“出生日期”字段，使其数据类型改为datetime，且允许为空。 2. 删除学生与课程表（XS_KC）中的“学分”字段。

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

为“计算机”）

四、向数据库STUxxxx的XS表、KC表和XS_KC表中插入或修改成如下数据：

学生情况表（XS）

学号 4102101 姓名 王林 性别 1 出生日期 1983-1-23 专业 计算机 总学分 40 备注 NULL 4102102 4202103 4202110 4202221 4302101 4402130 吴荣华 张强 王向荣 李丽 刘明 叶凡 0 1 1 0 1 1 1984-3-28 1981-11-19 1980-12-9 1982-7-30 1982-10-18 1983-11-18 计算机 电子 电子 电子 自控 数学 44 NULL 50 NULL 38 46 NULL NULL NULL NULL NULL 转专业学习

课程表（KC）

课程号 101 102 103 201 202 301 课程名 计算机基础 C语言 数据库 离散数学 计算机网络 软件工程 开课学期 1 2 4 6 7 6 学时 48 80 64 64 64 64 学分 3 5 4 4 4 4

学生与课程表（XS_KC）

学号 4102101 4102101 4102101 4102102 4102102 4202103 4202110 4202110 4202221 4402130 4402130 课程号 101 102 103 101 102 103 101 102 101 101 103 成绩 80 89 78 57 67 90 85 91 69 78 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分的门数。格式如

下：

学号 ??

（三） 连接查询

1. 查询每个学生的基本情况及选修的课程情况，格式如下：

学号 ??

姓名 ??

专业 ??

课程号 成绩 ??

??

成绩高于80分的门数

??

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，使其显示计算机专业学生的所有基本信息，并保证以后对

该视图的修改都要满足专业为“计算机”这个条件。