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

1、综合题一、1使用TransactSQL语言创建教学管理数据库，创建两个表学生信息（学号，姓名，性别，籍贯，班级编号）和成绩表（学号，课程编号，成绩，是否重修）。其中，学生信息表中学号为主键，其他信息都不允许为空。通过学号与成绩表形成一个一对多的关系，成绩表中的成绩的默认值为0，但必须在0100之间。注：将学号设置为主键，主键名为pk_xh；为成绩添加默认值约束，约束名为df_xb,其值为“0”；为成绩添加check约束，约束名为诚恳ck_xb, 在0100之间。CREATE  DATABASE   教学管理 use 教学管理

2、 go  create  table  学生信息 （     学号  varchar(20)  NOT  NULL  PRIMARY  KEY,    姓名  varchar(20)  NOT  NULL ,    性别 

3、0;varchar(10)  NOT  NULL,    籍贯  varchar(20)  NOT  NULL,    班级编号  varchar(20)  NOT  NULL ) go  create  table  成绩表 (     

4、  学号  varchar(20)  NOT  NULL  FOREIGN   KEY  REFERENCES  学生信息（学号）,     课程编号 varchar(20)  NOT  NULL,     成绩  numeric(5,2)  DEFAULT&

5、#160;(0)  CHECK(成绩)=0  and  成绩<=100) ,是否重修 varchar(8)  NOT  NULL ) go2写出实现各查询功能的SELECT语句。（1）统计班级编号为1002的学生人数。（2）查询课程编号为“003”的课程成绩高于该门课程平均分的学生信息。（3）在“学生信息”表中找出姓“王”的学生信息。（4）将“成绩”表中的课程编号为“001”的成绩增加10分。（5）列出每个同学的学号、姓名及选修课程的平均成绩情况，没

6、有选修的同学也列出。3创建一个名为stud_view2的视图，该视图可以用来查看每个学生的选修课程的门数和平均成绩。create view stud_ view2 asselect count(*) as 课程门数,avg(grade) as 平均成绩 from成绩表 group by 学号select * from stud_view2 4（1）在“教学管理”数据库中创建一个带参数的存储过程proc_score。该存储过程的作用是：当输入某门课程的课程名称时，检索该门课程成绩，并将查

7、询结果以“学号” ，“姓名” ，“课程名称” ，“成绩等级”输出（成绩划分成四个等级：高于或等于85分者为优秀，高于或等于75分者为良好，高于或等于60分者为及格，低于60分者为不及格）。（2）执行存储过程proc_score，查询“003”课程的成绩信息。二、 假设现有数据库“学生信息”，其中包含“学生”、“课程”、“学期成绩”三个表。表结构如下：“学生”表结构字段名数据类型长度是否为空约束学号int4否主键姓名char10否唯一约束性别char2是默认约束专业char20是班级char12是备注text是“课程”表结构课程（课号，课名，学分，先修课）“学期成绩”表结构选课（学号，课号，成绩

8、）根据各题功能要求写出相应的T-SQL语句。1写出在“学生信息”数据库中创建“学生”表的T-SQL语句，并在建表的同时创建所需约束。约束要求如下： 将学号设置为主键，主键名为pk_xh；为姓名设置唯一约束，约束名为uk_xm；为性别添加默认约束，约束名为df_xb,其值为“男” 。USE 学生信息                        &#

9、160;             GO CREATE TABLE 学生                               

10、;  (学号 INT CONSTRAINT pk_xh PRIMARY KEY,               姓名 CHAR(10) NOT NULL CONSTRAINT uk_xm UNIQUE,        性别 CHAR

11、(2) NULL CONSTRAINT df_xb DEFAULT '男',       专业 CHAR(20) NULL,      班级 CHAR(12) NULL, 备注 TEXT NULL)2写出实现各查询功能的SELECT语句。（1）计算机专业不及格学生的学号和姓名。（2）选修了数据库课程的学生的学号、姓名和成绩，查询结果

12、按分数的降序排列。（3）在“课程”表中找出“课程名”中包含“计算机”三个字的课程。（4）将“课程”表中的课程编号为“001”的学分增加1学分；（5）列出每个同学的学号、姓名及选修课程的平均成绩情况，没有选修的同学也列出。3创建一个名为stud_view2的视图，该视图可以用来查看每个学生的选修课程的门数和平均成绩。4（1）在“学生信息”数据库中创建一个带参数的存储过程proc_score。该存储过程的作用是：当输入某门课程的课程名称时，检索该门课程成绩，并将查询结果以“学号” ，“姓名” ，“课程名称” ，“成绩等级”输出（成绩划分成四个等级：高于或等于85分者为优秀，高于或等于75分者为良好

13、，高于或等于60分者为及格，低于60分者为不及格）。CREATE PROCEDURE proc_score KCMC CHAR(20) AS SELECT 学生.学号,姓名,课程名称,成绩等级=CASE   WHEN 成绩>=85 THEN '优秀' WHEN 成绩>=75 THEN '良好' WHEN 成绩>=60 THEN '及格' WHEN 成绩<60

14、0; THEN '不及格' （或：ELSE '不及格'） END FROM 学生 JOIN 学期成绩 ON 学生.学号=学期成绩.学号  JOIN 课程注册 ON 课程注册.课程代码=学期成绩.课程代码 WHERE 课程名称=KCMC  GO （2）执行存储过程proc_score，查询“计算机网络”课程的成绩信息。EXEC  proc_scor

15、e 计算机网络5定义一事务tran_stu，在学生“学期成绩”表中，为所有成绩高于50分的同学的成绩增加10分，并提交事务。三、假设现有数据库“学生信息”，其中包含“学生”、“课程注册”、“学期成绩”三个表。表结构如下：“学生”表结构：字段名数据类型长度是否为空约束学号int4否主键姓名char10否唯一约束性别char2是默认约束专业char20是班级char12是备注text是装订线“课程注册”表结构：字段名数据类型长度是否为空约束课程代码char4否主键课程名称char20否“学期成绩”表结构：字段名数据类型长度是否为空约束学号int4否外键课程代码char4否外键成绩floa

16、t8是 根据各题功能要求写出相应的T-SQL语句。1写出在“学生信息”数据库中创建“学生”表的T-SQL语句，并在建表的同时创建所需约束。约束要求如下：将学号设置为主键，主键名为pk_xh；为姓名设置唯一约束，约束名为uk_xm；为性别添加默认约束，约束名为df_xb,其值为“男”。USE 学生信息                        &

17、#160;             GO CREATE TABLE 学生                              

18、0;  (学号 INT CONSTRAINT pk_xh PRIMARY KEY,           姓名 CHAR(10) NOT NULL CONSTRAINT uk_xm UNIQUE,        性别 CHAR(2) NULL CONS

19、TRAINT df_xb DEFAULT '男',       专业 CHAR(20) NULL,      班级 CHAR(12) NULL, 备注 TEXT NULL) 2完成以下查询。(1)统计学生表中各班级的学生人数。SELECT 班级,COUNT(*) AS 人数  FROM

20、0; 学生  GROUP  BY  班级 (2) 查询“软件工程”课程成绩高于该门课程平均分的学生信息。SELECT 学号,课程名称,成绩    FROM 学期成绩 JOIN 课程注册  ON 学期成绩.课程代码=课程注册.课程代码 WHERE 课程名称='软件工程' AND 成绩>  (SELECT AVG(成绩)   &#

21、160; FROM 学期成绩 JOIN 课程注册  ON 学期成绩.课程代码=课程注册.课程代码      WHERE 课程名称='软件工程')(3)查询男同学选修的课程号。(4)查询至少选修课程号为“C01”和“C02”的学生学号。将所有学生的“01”课程的成绩增加10分。3在“学生信息”数据库中创建一个带参数的存储过程st_xsxx。该存储过程的作用是：当输入某门课程的课程名称时，检索该门课程成绩，并将查询结果以“学号” ，“姓名” ，

22、“课程名称” ，“成绩等级”输出（成绩划分成四个等级：高于或等于85分者为优秀，高于或等于75分者为良好，高于或等于60分者为及格，低于60分者为不及格）。CREATE PROCEDURE st_xsxx  （1分） KCMC CHAR(20)   （1分） AS SELECT 学生.学号,姓名,课程名称,成绩等级=CASE     WHEN 成绩>=85 THEN '优秀'

23、 WHEN 成绩>=75 THEN '良好' WHEN 成绩>=60 THEN '及格' WHEN 成绩<60  THEN '不及格' （或：ELSE '不及格'） END FROM 学生 JOIN 学期成绩 ON 学生.学号=学期成绩.学号  JOIN 课程注册&#

24、160;ON 课程注册.课程代码=学期成绩.课程代码 WHERE 课程名称=KCMC （1分） GO四、现有关系数据库如下： 数据库名：教师数据库教师表(编号 char(6)，姓名，性别，民族，职称，身份证号) 课程表(课号 char(6)，名称) 任课表(ID，教师编号,课号，课时数) 用SQL语言实现下列功能的sql语句代码：1 写出创建数据库代码，创建教师表代码； 要求使用：编号(主键)、默认(民族“汉”)、民族和姓名 (非空)、身份证号 (唯一)、性别(检查约束：只能是男或女)。create database 教师数

25、据库                 -（2分） use 教师数据库 go create table 教师表                      

26、;    -（6分） (编号 char(6) primary key, 姓名 nchar(4) not null, 性别 nchar(1) check(性别 in ('男', '女'), 民族 nchar(8) default '汉族' not null, 职称 nchar(12),

27、0;身份证号 char(18) unique )  create table 课程表                          -（2分） (课号 char(6) primary key, 名称 cha

28、r(40) not null )  create table 任课表                          -（4分） (ID IDENTITY(1, 1),  教师编号 char(6) 

29、references 学生表(学号), 课号 char(6) references 课程表(课号), 课时数 integer check(课时数 between 0 and 200) ) 2写出下列课程记录添加到课程表的代码 课号 课程名称 100001 SQL Server数据库 100002 数据结构 100003 VB程序设计 修改：把课号为100003的课程名称改为“Visual Basic程序设计”；删除：课号为100003的课程信息 inser

30、t 课程表 values('100001', 'SQL Server数据库')  insert 课程表 values('100002', '数据结构') insert 课程表 values('100003', 'VB程序设计') update 课程表 set 名称='Visual Basic程序设计'

31、60;where 课号='100003' delete 课程表 where 课号='100003'3创建视图：任课表 (教师编号，姓名，课号，课程名称，课时数)；create view 任课表视图 as select 教师编号,姓名,课号, 课程名称,课时数 from 教师表,任课表 where 教师表.编号=任课表.教师编号 4查询：所有教“SQL Server数据库”这门课程的老师姓名；create&

32、#160;function 某门课任课教师 (课程名 varchar(15) returns table as return (select 课程名称, 课时数, 教师姓名=姓名 from 任课表视图 where 课程名=课程名) go select * from 某门课任课教师('SQL Server数据库')5查询：最大课时数、最低课时数、平均课时的；create p

33、rocedure 统计课时数 as select  最大课时数=max(课时) ,最小课时数=min(课时),平均课时数=avg(课时) from 任课表 go 6检索有一门或一门以上课程课时数大于90的所有教师的信息，包括编号、姓名；select 编号, 姓名 from 教师表 where编号 in (select distinct 教师编号 from 任课表 where课时

34、数>=90) 7修改教师表结构：教师表的职称列上，规定取值为（'教授'，'副教授'，'讲师'， '助教'）之一。create rule zhicheng _rule as zhicheng  in ('教授','副教授','讲师', '助教') go sp_bindrule zhicheng_rule, '教师表.职

35、称' 8计算某教师代课总课时，并将值返回的存储过程以及执行代码。执行：计算“郭老师”的总课时。create procedure 统计课时  教师名 nchar(16), as begin    declare 总课时 int     select 总课时=sum (课时) from 任课表视图    where 

36、;姓名 = 教师名  end go execute 统计课时 '郭老师'五、现在要为学校建立一个数据库，设学校的环境如下：一个系有若干个专业，每个专业一年只招一个班，每个班有若干个学生，现要建立关于系、学生、班级的数据库，关系模式为：学生表student（学号，姓名，性别，班级，生源地，高考成绩，出生日期）班级表class（班级名，专业名，系号，入学年份）系表 department（系号，系名）使用SQL语句完成一下功能：1. 建立数据库名为school并激活当前新建的数据库，要求主数据文件的逻辑文

37、件名为school，实际文件名为school.mdf，事务日志文件的逻辑文件名为school,，实际文件名分别为school.ldf，上述文件的初始容量均为5MB，最大容量均为20MB，递增量均为1MB。create database school on primary ( name = school, filename = 'SCHOOL.mdf', SIZE = 5MB, MAXSIZE = 20MB,&#

38、160;filegrowth = 1MB) log on( name= school_log, filename =  SCHOOL_log.ldf', SIZE = 2MB, maxsize = 20MB, filegrowth = 10%) go2建立表student（学号，姓名，性别，班级，生源地，高考成绩，出生日期）要求：（1）表要有主键（2）学生的性别只能取男或者女 （3）学生姓名不能为空

39、use SCHOOL create table student - 学生表定义 ( 学号char(10) primary key , - 主键为学号 姓名varchar(20) not null , - 姓名不能为空值 性别char(4) default '男' ,   - 性别默认为男性 生源地var

40、char(12) , -生源地出生日期datetime , check(性别= '男') or (性别= '女')  - 性别必须为男性或女性 )3.在student表中插入一条记录：（学号：8101，姓名:张三，性别：男，生源地：山东）insert into student values(8101,'张三','男','山东')4.根据student表完成以下的查询功能（1）通

41、过对学生信息表的查询，查询出所有学生的学号，姓名和性别。select 学号,姓名,性别 from student （2）通过对学生信息表的查询，查询来自福建的学生的记录。select * from student where 生源地= '福建'（3）在查询语句中使用聚合函数计算出所有学生总数（起别名为学生人数）和平均高考成绩（起别名为平均成绩）。select count(*) as '学生人数' from stud

42、ent （4）更新学号为8103的这位学生的姓名为张玲。update student set 姓名= '张玲'  where 学号= '8103'（5）查询高考成绩最高的前5条学生记录。Select top 5 * from student order by 高考成绩 desc（6）在学生表中统计班级列表及各班级的人数。Select 班级,count(*) from

43、 student group by 班级（7）查询高考成绩在90到100之间的学生的学号、姓名、专业名。Select 学号,姓名,专业名 from 学生表,班级表 where 学生表.班级=班级表.班级名 and 高考成绩 between 90 and 100（8）删除姓名为李飞的学生记录；删除数据表student。delete from student where 姓名= '李飞'

44、;六、已知逻辑结构见下表，写出各题的T-SQL语句。表readers（读者信息表）结构属性名数据类型宽度小数位数编号（主码）char8姓名char8读者类型int2已借数量int2books表（图书信息表）结构属性名数据类型宽度编号（主键）char15书名char42作者char8出版社char28出版日期datetime8定价（>0）decimal8borrowinf表（借阅信息表）结构属性名数据类型宽度读者编号（外键）char8图书编号（外键）char15借期datetime8还期datetime8主键（读者编号，图书编号，借期）视图overdue（超期信息）列名对应基本表的列名读者编号Reaers.编号读者姓名Readers.姓名图书编号Books.书名书名Books.应还日期Borrwinf.借期+readrtype期限1. 创建基本表books。CREATE TABLE books (  图书编号 CHA