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

综合题
一、
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分者为不及格）。
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
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．写出下列课程记录添加到课程表的代码
课号 课程名称
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
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（超期信息）。
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