数据库原理及应用期末考试Word格式文档下载.docx
《数据库原理及应用期末考试Word格式文档下载.docx》由会员分享，可在线阅读，更多相关《数据库原理及应用期末考试Word格式文档下载.docx（14页珍藏版）》请在冰豆网上搜索。
（2）查询课程编号为“003”的课程成绩高于该门课程平均分的学生信息。
（3）在“学生信息”表中找出姓“王”的学生信息。
（4）将“成绩”表中的课程编号为“001”的成绩增加10分。
（5）列出每个同学的学号、姓名及选修课程的平均成绩情况，没有选修的同学也列出。
3．创建一个名为stud_view2的视图，该视图可以用来查看每个学生的选修课程的门数和平均成绩。
view
stud_view2
as
select
count（*）as课程门数,avg（grade）as平均成绩
from成绩表
group
by
学号
*
from
stud_view2
4．
（1）在“教学管理”数据库中创建一个带参数的存储过程proc_score。
该存储过程的作用是：
当输入某门课程的课程名称时，检索该门课程成绩，并将查询结果以“学号”，“姓名”，“课程名称”，“成绩等级”输出（成绩划分成四个等级：
高于或等于85分者为优秀，高于或等于75分者为良好，高于或等于60分者为及格，低于60分者为不及格）。
（2）执行存储过程proc_score，查询“003”课程的成绩信息。
二、
假设现有数据库“学生信息”，其中包含“学生”、“课程”、“学期成绩”三个表。
表结构如下：
“学生”表结构
字段名
数据类型
长度
是否为空
约束
int
4
否
主键
姓名
char
10
唯一约束
性别
2
是
默认约束
专业
20
班级
12
备注
text
“课程”表结构
课程（课号，课名，学分，先修课）
“学期成绩”表结构
选课（学号，课号，成绩）
根据各题功能要求写出相应的T-SQL语句。
1．写出在“学生信息”数据库中创建“学生”表的T-SQL语句，并在建表的同时创建所需约束。
约束要求如下：
将学号设置为主键，主键名为pk_xh；
为姓名设置唯一约束，约束名为uk_xm；
为性别添加默认约束，约束名为df_xb,其值为“男”。
USE
GO
TABLE
学生
（学号
INT
CONSTRAINT
pk_xh
CHAR（10）
uk_xm
UNIQUE,
CHAR
（2）
df_xb
'
男'
专业
CHAR（20）
班级
CHAR（12）
备注
TEXT
NULL）
（1）计算机专业不及格学生的学号和姓名。
（2）选修了数据库课程的学生的学号、姓名和成绩，查询结果按分数的降序排列。
（3）在“课程”表中找出“课程名”中包含“计算机”三个字的课程。
（4）将“课程”表中的课程编号为“001”的学分增加1学分；
4．
（1）在“学生信息”数据库中创建一个带参数的存储过程proc_score。
CREATEPROC[EDURE]proc_score
@KCMCCHAR（20）
ASSELECT学生.学号,姓名,课程名称,成绩等级=
CASE
WHEN
成绩>
=85
THEN
优秀'
=75
良好'
=60
及格'
60
不及格'
（或：
ELSE
）
END
FROM
JOIN
学期成绩
ON
学生.学号=学期成绩.学号
课程注册
课程注册.课程代码=学期成绩.课程代码
WHERE
课程名称=@KCMC
（2）执行存储过程proc_score，查询“计算机网络”课程的成绩信息。
EXEC
proc_score
‘计算机网络’
5．定义一事务tran_stu，在学生“学期成绩”表中，为所有成绩高于50分的同学的成绩增加10分，并提交事务。
三、
假设现有数据库“学生信息”，其中包含“学生”、“课程注册”、“学期成绩”三个表。
“学生”表结构：
┋┋┋┋┋┋┋┋┋┋┋┋┋┋┋装┋┋┋┋┋┋┋┋┋订┋┋┋┋┋┋┋┋┋┋线┋┋┋┋┋┋┋┋┋┋┋┋┋┋┋┋┋┋┋┋
“课程注册”表结构：
课程代码
课程名称
“学期成绩”表结构：
外键
成绩
float
8
根据各题功能要求写出相应的T-SQL语句。
为性别添加默认约束，约束名为df_xb,其值为“男”。
NULL）
2．完成以下查询。
（1）统计学生表中各班级的学生人数。
SELECT
班级,COUNT（*）
AS
人数
学生
GROUP
BY
班级
（2）查询“软件工程”课程成绩高于该门课程平均分的学生信息。
学号,课程名称,成绩
学期成绩.课程代码=课程注册.课程代码
课程名称='
软件工程'
AND
（SELECT
AVG（成绩）
（3）查询男同学选修的课程号。
（4）查询至少选修课程号为“C01”和“C02”的学生学号。
将所有学生的“01”课程的成绩增加10分。
3．在“学生信息”数据库中创建一个带参数的存储过程st_xsxx。
PROC[EDURE]
st_xsxx
（1分）
@KCMC
（1分）
学生.学号,姓名,课程名称,成绩等级=
GO
四、
现有关系数据库如下：
数据库名：
教师数据库
教师表（编号char（6），姓名，性别，民族，职称，身份证号）
课程表（课号char（6），名称）
任课表（ID，教师编号,课号，课时数）
用SQL语言实现下列功能的sql语句代码：
1．写出创建数据库代码，创建教师表代码；
要求使用：
编号（主键）、默认（民族“汉”）、民族和姓名（非空）、身份证号（唯一）、性别（检查约束：
只能是男或女）。
database
[教师数据库]
--（2分）
教师表
--（6分）
（[编号]
char（6）
primary
key,
[姓名]
nchar（4）
not
null,
[性别]
nchar
（1）
check（[性别]
in
（'
女'
））,
[民族]
nchar（8）
default
汉族'
[职称]
nchar（12）,
[身份证号]
char（18）
unique
课程表
--（2分）
（[课号]
[名称]
char（40）
null
任课表
--（4分）
（ID
IDENTITY（1,
1）,
[教师编号]
references
学生表（学号）,
[课号]
课程表（课号）,
[课时数]
integer
check（[课时数]
between
0
200）
2．写出下列课程记录添加到课程表的代码
课号课程名称
100001SQLServer数据库
100002数据结构
100003VB程序设计
修改：
把课号为100003的课程名称改为“VisualBasic程序设计”；
删除：
课号为100003的课程信息
insert
values（'
100001'
SQL
Server数据库'
100002'
数据结构'
100003'
VB程序设计'
update
set
名称='
Visual
Basic程序设计'
where
课号='
delete
3．创建视图：
任课表（教师编号，姓名，课号，课程名称，课时数）；
[任课表视图]
as
教师编号,姓名,课号,
课程名称,课时数
教师表,任课表
教师表.编号=任课表.教师编号
4．查询：
所有教“SQLServer数据库”这门课程的老师姓名；
function
[某门课任课教师]（@课程名
varchar（15））
returns
return
（select
课程名称,
课时数,
教师姓名=姓名
任课表视图
课程名=@课程名）
[某门课任课教师]（'
5．查询：
最大课时数、最低课时数、平均课时的；
procedure
[统计课时数]
最大课时数=max（课时）
最小课时数=min（课时）,平均课时数=avg（课时）
6．检索有一门或一门以上课程课时数大于90的所有教师的信息，包括编号、姓名；
编号,
where编号
distinct
教师编号
where课时数>
=90）
7．修改教师表结构：
教师表的职称列上，规定取值为（'
教授'
，'
副教授'
讲师'
，'
助教'
）之一。
rule
zhicheng
_rule
@zhicheng
'
sp_bindrule
zhicheng_rule,
教师表.职称'
8．计算某教师代课总课时，并将值返回的存储过程以及执行代码。
执行：
计算“郭老师”的总课时。
[统计课时]
@教师名
nchar（16）,
begin
declare
@总课时
int
@总课时=sum
（课时）
=
end
execute
郭老师'
五、
现在要为学校建立一个数据库，设学校的环境如下：
一个系有若干个专业，每个专业一年只招一个班，每个班有若干个学生，现要建立关于系、学生、班级的数据库，关系模式为：
学生表student（学号，姓名，性别，班级，生源地，高考成绩，出生日期）
班级表class（班级名，专业名，系号，入学年份）
系表department（系号，系名）
使用SQL语句完成一下功能：
1.建立数据库名为school并激活当前新建的数据库，要求主数据文件的逻辑文件名为school，实际文件名为school.mdf，事务日志文件的逻辑文件名为school,，实际文件名分别为school.ldf，上述文件的初始容量均为5MB，最大容量均为20MB，递增量均为1MB。
school
on
primary
name
school,
filename
SCHOOL.mdf'
SIZE
5MB,
MAXSIZE
20MB,
filegrowth
1MB）
log
on
name=
school_log,
‘SCHOOL_log.ldf'
2MB,
maxsize
10%）
2．建立表student（学号，姓名，性别，班级，生源地，高考成绩，出生日期）要求：
（1）表要有主键
（2）学生的性别只能取‘男’或者‘女’
（3）学生姓名不能为空
SCHOOL
student
--
学生表定义
学号char（10）
key
主键为学号
姓名varchar（20）
姓名不能为空值
性别char（4）
性别默认为男性
生源地varchar（12）
--生源地
出生日期datetime
check（（性别=
or
（性别=
））
--
性别必须为男性或女性
3.在student表中插入一条记录：
（学号：
8101，姓名:
张三，性别：
男，生源地：
山东）
into
values（8101,'
张三'
山东'
4.根据student表完成以下的查询功能
（1）通过对学生信息表的查询，查询出所有学生的学号，姓名和性别。
学号,姓名,性别
（2）通过对学生信息表的查询，查询来自福建的学生的记录。
生源地=
福建'
（3）在查询语句中使用聚合函数计算出所有学生总数（起别名为学生人数）和平均高考成绩（起别名为平均成绩）。
count（*）
学生人数'
（4）更新学号为8103的这位学生的姓名为张玲。
姓名=
张玲'
学号=
8103'
（5）查询高考成绩最高的前5条学生记录。
Select
top
5
order
高考成绩
desc
（6）在学生表中统计班级列表及各班级的人数。
班级,count（*）
（7）查询高考成绩在90到100之间的学生的学号、姓名、专业名。
学号,姓名,专业名
学生表,班级表
学生表.班级=班级表.班级名
90
100
（8）删除姓名为李飞的学生记录；
删除数据表student。
李飞'
六、
已知逻辑结构见下表，写出各题的T-SQL语句。
表readers（读者信息表）结构
属性名
宽度
小数位数
编号（主码）
读者类型
已借数量
books表（图书信息表）结构
编号（主键）
15
书名
42
作者
出版社
28
出版日期
datetime
定价（>
0）
decimal
borrowinf表（借阅信息表）结构
读者编号（外键）
图书编号（外键）
借期
还期
主键（读者编号，图书编号，借期）
视图overdue（超期信息）
列名
对应基本表的列名
读者编号
Reaers.编号
读者姓名
Readers.姓名
图书编号
Books.书名
Books.
应还日期
Borrwinf.借期+readrtype期限
1.创建基本表books。
books
图书编号
CHAR（15）
KEY,
书名
CHAR（42）,
作者
CHAR（8）,
出版社
CHAR（28）,
出版日期
DATETIME,
价格
float（8）
）;
2.创建基本表borrowinf。
borrowinf
读者编号CHAR（10）,
图书编号CHAR（15）,
借期DATETIME,
还期DATETIME,
（读者编号,图书编号）,
（读者编号）
readers（读者编号）,
（图书编号）
books（图书编号）
3.创建视图overdue（超期信息）。
CreateviewoverdueasSELECT图书编号FROMborrowinf
WHERE还期>
4.查询姓名为’王立群’的读者的借阅信息。
图书编号,借期,还期
borrowinf
读者编号
IN
readers
姓名='
王立'
5.查询书名中包含文字‘程序设计’的图书信息。
SELECT*FROMbooksWHERE书名LIKE'
%程序设计'
6.查询各出版社的馆藏书数量。
SELECTCOUNT（DISTINCT图书编号）FROMbooks
7.查询2004-1-1和2004-12-31之间作者为‘梁晓峰’的图书借阅情况。
SELECT*FROMborrowinfWHERE借期>
2007-1-1'
AND还期<
'
2007-12-31'
AND图书编号IN（SELECT图书编号FROMbooks
WHERE作者='
梁晓峰'
8.创建存储过程计算用户指定图书的价格，并将价格作为返回值。
再执行调用该存储过程的命令。
图书管理
Go
PRO
升级会员 