数据库原理及应用期末考试.docx
- 文档编号:3726991
- 上传时间:2022-11-24
- 格式:DOCX
- 页数:14
- 大小:22.15KB
数据库原理及应用期末考试.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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库 原理 应用 期末考试