SQL-SERVER上机考试题

泉州理工学院2015-2016第二学期期末考

泉州理工职业学院2015-2016学年第二学期期末考

SQL SERVER数据库管理上机操作题

班级： 学号 姓名 成绩 说明：

1、五答题的题目的答案直接写在相应题目后面，其余的内容直接保存在数据库中。

2、提交的内容包括数据库文件还有该WORD文档。统一建立一个文件夹以学号+姓名方式命名。

题目：

一、

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

二、 在JYxxxx数据库中创建如下的表结构： 1．学生情况表（StudentInfos）结构

列名 S_id S_name S_major S_sex S_ birth 说明 学号 姓名 专业 性别 出生日期 数据类型 Char Char VarChar Bit smalldatetime 长度 7 8 20 可否为空 否 否 否 否 否 默认值 无 无 无 1 无 说明 主键 男=1，女=0 2．课程表（CourseInfos）结构

列名 C_id C_name C_credit 说明 课程号 课程名 学分 数据类型 Char Char Tinyint 长度 3 20 可否为空 否 否 否 默认值 无 无 0 说明 主键 3．学生与课程表（StudentCourseInfos）结构

列名 S_id C_id Sc_ grade 说明 学号 课程号 成绩 数据类型 Char Char Tinyint 长度 7 3 可否为空 否 否 是 默认值 无 无 0 说明 外键（参照学生表中的学号） 外键（参照课程表中的课程号） 0≤成绩≤100 三、

数据完整性

1、 在StudentInfos中，要求性别只能输入0或1。 2、 在CourseInfos中，要求学分不能小于1，且不能大于10

3、 在StudentCourseInfos中，要求成绩分数在0到100之间（包含0和100）

Sqlserver数据库管理

泉州理工学院2015-2016第二学期期末考

四、 使用语句向数据库JYxxxx的StudentInfos表、CourseInfos表和

学生情况表（StudentInfos）

学号 4102101 4102102 4202103 4202110 4202221 4302101 4402130 姓名 王林 吴荣华 张强 王向荣 李丽 刘明 叶凡 性别 男 女 男 男 女 男 男 专业 计算机 计算机 电子 电子 电子 自控 数学 出生日期 1983-1-23 1984-3-28 1981-11-19 1980-12-9 1982-7-30 1982-10-18 1983-11-18 StudentCourseInfos表中插入或修改成如下数据：

课程表（CourseInfos）

课程号 101 102 103 201 202 301 课程名 计算机基础 C语言 数据库 离散数学 计算机网络 软件工程 学分 3 5 4 4 4 4

学生与课程表（StudentCourseInfos）

学号 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 五、视图操作

1．创建视图V_SCORE1，使其显示计算机专业学生的所有基本信息（性别用中文标识，例如：1则显示男），并保证以后对该视图的修改都要满足专业为“计算机”这个条件。

2．创建视图V_SCORE2，通过V_SCORE2查看各个专业所有课程的平均成绩，结果格式为：

专业 平均成绩 ??

??

六、数据库关系图

Sqlserver数据库管理

泉州理工学院2015-2016第二学期期末考

创建视图D_BASE

七、以下题目均在JYxxxx（学生管理）数据库中完成。并写出操作SQL语句 （一） 简单查询

1. 查询StudentInfos表中各个同学的姓名、生日和性别。

答：select S_name,S_ birth,S_sex from StudentInfos

2. 查询StudentInfos表中所有同学的学号、姓名，结果中各列的标题分别指定为num，name。

答：select S_id num, S_name name, C_credit mark from StudentInfos

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

行）。select DISTINCT S_major from StudentInfos 答“

4. 查询StudentInfos表中专业为“计算机”的男学生信息。

答：select

5. 查询年龄最小的前三个同学的姓名、专业和生日。

答： （二） 数据汇总

1. 求专业为“计算机”的学生的总人数。

答 select count(*) from XS where 专业='计算机' 2. 求选修了“101”课程的学生的人数。

答：select COUNT(*) from XS_KC where 课程号='101' 3. 统计各个专业的学生数。（按专业分组）

答：select 专业,性别,count(*) as 人数 from XS group by 专业,性别 4. 统计各个专业的男女生人数。格式如下：

专业 ??

答：

性别 ??

人数 ??

（三） 连接查询

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

学号 ??

答：

2. 查询 “计算机”专业且选修了“计算机基础”课程的学生的学号、姓名及成绩。

答：select xs.学号,xs.姓名,xs_kc.成绩 from xs join xs_kc on xs.学号=xs_kc.

学号 join kc on xs_kc.课程号=kc.课程号 and xs.专业='计算机' and kc.课程名='计算机基础'

姓名 ??

专业 ??

课程号 成绩 成绩等级(>=90A；>=80B;>=70C；>=60D;<60D) ??

?? ??

3. 显示每门课程的平均成绩并显示总平均成绩。格式如下：

课程名 ??

平均成绩 ??

Sqlserver数据库管理

泉州理工学院2015-2016第二学期期末考

总平均 ??

答：select CASE WHEN (GROUPING(a.课程名) = 1) THEN '总平均' ELSE a.课程名 END

AS 课程名,avg(b.成绩) as '平均成绩' from kc a join xs_kc b on a.课程号=b.课程号 group by a.课程名 WITH rollup

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

专业 ??

课程名 ??

平均成绩 ??

答：select a.专业,c.课程名,avg(b.成绩) as '平均成绩' from xs a join xs_kc b

on a.学号=b.学号 join kc c on b.课程号=c.课程号 group by a.专业,c.课程名

5. 查询“计算机”专业平均成绩在85分以上的学生的学号、姓名和平均成绩。

答：select b.学号,a.姓名,avg(成绩) as '平均成绩' from xs a join xs_kc b on ( a.

学号=b.学号) and (a.专业='计算机') group by b.学号,a.姓名 having avg(成绩)>85

6. 查找平均成绩在80分以上的学生的学号和平均成绩。

答： select 学号,AVG(成绩) 平均成绩 from XS_KC group by 学号 having AVG(成

绩)>80

（四） 子查询的使用

1. 查询平均成绩高于101号课程平均分的课程号及平均成绩。

答：select 课程号,avg(成绩) '平均成绩' from xs_kc group by 课程号 having avg(成

绩)>(select avg(成绩) from xs_kc group by 课程号 having 课程号='101' )

2. 查找未选修“C语言”课程的学生的情况。(嵌套子查询)

答：select * from xs where 学号 not in( select 学号 from xs_kc where 课程

号 in (select 课程号 from kc where 课程名='C语言' ))

3. 查找选修了“计算机基础”课程的学生的情况。

答：select * from xs where 学号 in (select 学号 from xs_kc where 课程号

=(select 课程号 from kc where 课程名='计算机基础'))

4. 查询“计算机基础”成绩及格的学生的学号、姓名、性别和专业情况。

答：select 学号,姓名,性别,专业 from xs where 学号 in (select 学号 from xs_kc

where 成绩>=60 and 课程号=(select 课程号 from kc where 课程名='计算机基础'))

5. 查询至少选修了“4102102”同学选修课程的学生的基本信息。

答：select * from xs where 学号 in (select 学号 from xs_kc where 课程号

='4102102')

（五） 数据的插入、删除和修改

1. 在StudentInfos添加一条自己的信息，并选择数据库课程，以及给自己打分。

答：

2. 将StudentCourseInfos表中学号为“4102101”的同学的“计算机基础”课程的成绩增加10

分。

Sqlserver数据库管理

泉州理工学院2015-2016第二学期期末考

答：update StudentCourseInfos set 成绩=成绩+10 where 学号='4102101' 3. 将StudentCourseInfos表中“软件工程”课程的成绩不及格的信息删除。

答：

（六） SQL基础语法

随机求班上的一个学生的学号。 要求：

1、 最终显示学生的学号格式是：152011XX

2、 随机出来的学号，必须是班上存在的学生。排除休学的学生。 答：

Sqlserver数据库管理

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