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