数据库原理及应用期末考试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