数据库原理及应用期末考试

综合题

一、

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 char char char char text 长度 4 10 2 20 12 是否为空 否 否 是 是 是 是 约束 主键 唯一约束 默认约束 “课程”表结构 课程（课号，课名，学分，先修课）

“学期成绩”表结构

选课（学号，课号，成绩）

根据各题功能要求写出相应的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分者为不及格）。

CREATE PROC[EDURE] proc_score @KCMC CHAR(20)

AS SELECT 学生.学号,姓名,课程名称,成绩等级= 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 char char char char text 长度 4 10 2 20 12 是否为空 否 否 是 是 是 是 约束 主键 唯一约束 默认约束

课程代码 课程名称 char char 4 20 否 否 主键 “学期成绩”表结构： 字段名 学号 课程代码 成绩 数据类型 int char float 长度 4 4 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．写出下列课程记录添加到课程表的代码 课号 课程名称 100001 SQL Server数据库 100002 数据结构 100003 VB程序设计

修改：把课号为100003的课程名称改为“Visual Basic程序设计”；

删除：课号为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．查询：所有教“SQL Server数据库”这门课程的老师姓名； 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 char int int

宽度 8 8 2 2 小数位数 books表（图书信息表）结构 属性名 编号（主键） 书名 作者 出版社 出版日期 定价（>0） 数据类型 char char char char datetime decimal

宽度 15 42 8 28 8 8 borrowinf表（借阅信息表）结构 属性名 读者编号（外键） 图书编号（外键） 借期 还期 数据类型 char char datetime datetime 宽度 8 15 8 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（超期信息）。

Create view overdue as SELECT 图书编号 FROM borrowinf WHERE 还期>0

4. 查询姓名为’王立群’的读者的借阅信息。 SELECT 图书编号,借期,还期 FROM borrowinf

WHERE 读者编号 IN (SELECT 读者编号 FROM readers WHERE 姓名='王立' 5. 查询书名中包含文字‘程序设计’的图书信息。 SELECT * FROM books WHERE 书名 LIKE'%程序设计' 6. 查询各出版社的馆藏书数量。

SELECT COUNT(DISTINCT 图书编号) FROM books

7. 查询2004-1-1和2004-12-31之间作者为‘梁晓峰’的图书借阅情况。 SELECT * FROM borrowinf WHERE 借期>'2007-1-1'AND 还期< '2007-12-31' AND 图书编号 IN (SELECT 图书编号 FROM books WHERE 作者='梁晓峰')

8.创建存储过程计算用户指定图书的价格，并将价格作为返回值。再执行调用该存储过程的命令。

USE 图书管理 Go

CREATE PROCEDURE proc_BooksPrice @sm char(42), @dj float output As

SELECT @dj=定价 FROM books WHERE 书名=@sm Go

调用存储过程： Declare @price float

EXEC proc_BooksPrice '计算机文化基础',@price output SELECT @price GO

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