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

-.z.综合题一、1．使用Transact—SQL语言创建教学管理数据库，创建两个表学生信息（**，，性别，籍贯，班级编号）和成绩表（**，课程编号，成绩，是否重修）。其中，学生信息表中**为主键，其他信息都不允许为空。通过**与成绩表形成一个一对多的关系，成绩表中的成绩的默认值为0，但必须在0～100之间。注：将**设置为主键，主键名为pk_*h；为成绩添加默认值约束，约束名为df_*b,其值为"0”；为成绩添加check约束，约束名为诚恳ck_*b,在0～100之间。CREATEDATABASE教学管理use教学管理gocreatetable学生信息（**varchar(20)NOTNULLPRIMARYKEY,varchar(20)NOTNULL,性别varchar(10)NOTNULL,籍贯varchar(20)NOTNULL,班级编号varchar(20)NOTNULL)gocreatetable成绩表(**varchar(20)NOTNULLFOREIGNKEYREFERENCES学生信息（**）,课程编号varchar(20)NOTNULL,成绩numeric(5,2)DEFAULT(0)CHECK(成绩)=0and成绩<=100),是否重修varchar(8)NOTNULL)go2．写出实现各查询功能的SELECT语句。（1）统计班级编号为1002的学生人数。（2）查询课程编号为"003”的课程成绩高于该门课程平均分的学生信息。（3）在"学生信息”表中找出姓"王”的学生信息。（4）将"成绩”表中的课程编号为"001”的成绩增加10分。（5）列出每个同学的**、**及选修课程的平均成绩情况，没有选修的同学也列出。3．创建一个名为stud_view2的视图，该视图可以用来查看每个学生的选修课程的门数和平均成绩。create

view

stud_view2asselect

count(*)as课程门数,avg(grade)as平均成绩

from成绩表

group

by

**select

*

from

stud_view2

4．（1）在"教学管理”数据库中创建一个带参数的存储过程proc_score。该存储过程的作用是：当输入*门课程的课程名称时，检索该门课程成绩，并将查询结果以"**”，"”，"课程名称”，"成绩等级”输出（成绩划分成四个等级：高于或等于85分者为优秀，高于或等于75分者为良好，高于或等于60分者为及格，低于60分者为不及格）。（2）执行存储过程proc_score，查询"003”课程的成绩信息。二、假设现有数据库"学生信息”，其中包含"学生”、"课程”、"学期成绩”三个表。表结构如下："学生”表结构字段名数据类型长度是否为空约束**int4否主键char10否唯一约束性别char2是默认约束专业char20是班级char12是备注te*t是"课程”表结构课程（课号，课名，学分，先修课）"学期成绩”表结构选课（**，课号，成绩）根据各题功能要求写出相应的T-SQL语句。1．写出在"学生信息”数据库中创建"学生”表的T-SQL语句，并在建表的同时创建所需约束。约束要求如下：将**设置为主键，主键名为pk_*h；为**设置唯一约束，约束名为uk_*m；为性别添加默认约束，约束名为df_*b,其值为"男”。USE学生信息GOCREATETABLE学生(**INTCONSTRAINTpk_*hPRIMARYKEY,CHAR(10)NOTNULLCONSTRAINTuk_*mUNIQUE,性别CHAR(2)NULLCONSTRAINTdf_*bDEFAULT'男',专业CHAR(20)NULL,班级CHAR(12)

NULL,

备注TE*TNULL)2．写出实现各查询功能的SELECT语句。（1）计算机专业不及格学生的**和**。（2）选修了数据库课程的学生的**、**和成绩，查询结果按分数的降序排列。（3）在"课程”表中找出"课程名”中包含"计算机”三个字的课程。（4）将"课程”表中的课程编号为"001”的学分增加1学分；（5）列出每个同学的**、**及选修课程的平均成绩情况，没有选修的同学也列出。3．创建一个名为stud_view2的视图，该视图可以用来查看每个学生的选修课程的门数和平均成绩。4．（1）在"学生信息”数据库中创建一个带参数的存储过程proc_score。该存储过程的作用是：当输入*门课程的课程名称时，检索该门课程成绩，并将查询结果以"**”，"”，"课程名称”，"成绩等级”输出（成绩划分成四个等级：高于或等于85分者为优秀，高于或等于75分者为良好，高于或等于60分者为及格，低于60分者为不及格）。CREATEPROC[EDURE]proc_scoreKCMCCHAR(20)ASSELECT学生.**,,课程名称,成绩等级=CASEWHEN成绩>=85THEN'优秀'WHEN成绩>=75THEN'良好'WHEN成绩>=60THEN'及格'WHEN成绩<60THEN'不及格'（或：ELSE'不及格'）ENDFROM学生JOIN学期成绩ON学生.**=学期成绩.**JOIN课程注册ON课程注册.课程代码=学期成绩.课程代码WHERE课程名称=KCMCGO（2）执行存储过程proc_score，查询"计算机网络”课程的成绩信息。E*ECproc_score

‘计算机网络’5．定义一事务tran_stu，在学生"学期成绩”表中，为所有成绩高于50分的同学的成绩增加10分，并提交事务。三、假设现有数据库"学生信息”，其中包含"学生”、"课程注册”、"学期成绩”三个表。表结构如下："学生”表结构：字段名数据类型长度是否为空约束**int4否主键char10否唯一约束性别char2是默认约束专业char20是班级char12是备注te*t是┋┋┋┋┋┋┋┋┋┋┋┋┋┋┋装┋┋┋┋┋┋┋┋┋订┋┋┋┋┋┋┋┋┋┋┋┋┋┋┋装┋┋┋┋┋┋┋┋┋订┋┋┋┋┋┋┋┋┋┋线┋┋┋┋┋┋┋┋┋┋┋┋┋┋┋┋┋┋┋┋字段名数据类型长度是否为空约束课程代码char4否主键课程名称char20否"学期成绩”表结构：字段名数据类型长度是否为空约束**int4否外键课程代码char4否外键成绩float8是根据各题功能要求写出相应的T-SQL语句。1．写出在"学生信息”数据库中创建"学生”表的T-SQL语句，并在建表的同时创建所需约束。约束要求如下：将**设置为主键，主键名为pk_*h；为**设置唯一约束，约束名为uk_*m；为性别添加默认约束，约束名为df_*b,其值为"男”。USE学生信息GOCREATETABLE学生(**INTCONSTRAINTpk_*hPRIMARYKEY,CHAR(10)NOTNULLCONSTRAINTuk_*mUNIQUE,性别CHAR(2)NULLCONSTRAINTdf_*bDEFAULT'男',专业CHAR(20)NULL,班级CHAR(12)

NULL,

备注TE*TNULL)2．完成以下查询。(1)统计学生表中各班级的学生人数。SELECT班级,COUNT(*)AS人数FROM学生GROUPBY班级查询"软件工程”课程成绩高于该门课程平均分的学生信息。SELECT**,课程名称,成绩FROM学期成绩JOIN课程注册ON学期成绩.课程代码=课程注册.课程代码WHERE课程名称='软件工程'AND成绩>(SELECTAVG(成绩)FROM学期成绩JOIN课程注册ON学期成绩.课程代码=课程注册.课程代码WHERE课程名称='软件工程')(3)查询男同学选修的课程号。(4)查询至少选修课程号为"C01”和"C02”的学生**。将所有学生的"3．在"学生信息”数据库中创建一个带参数的存储过程st_*s**。该存储过程的作用是：当输入*门课程的课程名称时，检索该门课程成绩，并将查询结果以"**”，"”，"课程名称”，"成绩等级”输出（成绩划分成四个等级：高于或等于85分者为优秀，高于或等于75分者为良好，高于或等于60分者为及格，低于60分者为不及格）。CREATEPROC[EDURE]st_*s**（1分）KCMCCHAR(20)（1分）ASSELECT学生.**,,课程名称,成绩等级=CASEWHEN成绩>=85THEN'优秀'WHEN成绩>=75THEN'良好'WHEN成绩>=60THEN'及格'WHEN成绩<60THEN'不及格'（或：ELSE'不及格'）ENDFROM学生JOIN学期成绩ON学生.**=学期成绩.**JOIN课程注册ON课程注册.课程代码=学期成绩.课程代码WHERE课程名称=KCMC（1分）GO四、现有关系数据库如下：数据库名：教师数据库教师表(编号char(6)，，性别，民族，职称，**号)课程表(课号char(6)，名称)任课表(ID，教师编号,课号，课时数)用SQL语言实现下列功能的sql语句代码：1．写出创建数据库代码，创建教师表代码；要求使用：编号(主键)、默认(民族"汉”)、民族和**(非空)、**号(唯一)、性别(检查约束：只能是男或女)。createdatabase[教师数据库]--（2分）use[教师数据库]gocreatetable教师表--（6分）([编号]char(6)primarykey,[]nchar(4)notnull,[性别]nchar(1)check([性别]in('男','女')),[民族]nchar(8)default'汉族'notnull,[职称]nchar(12),[**号]char(18)unique)createtable课程表--（2分）([课号]char(6)primarykey,[名称]char(40)notnull)createtable任课表--（4分）(IDIDENTITY(1,1),[教师编号]char(6)references学生表(**),[课号]char(6)references课程表(课号),[课时数]integercheck([课时数]between0and200))2．写出下列课程记录添加到课程表的代码课号课程名称100001SQLServer数据库100002数据结构100003VB程序设计修改：把课号为100003的课程名称改为"VisualBasic程序设计”；删除：课号为100003的课程信息insert课程表values('100001','SQLServer数据库')insert课程表values('100002','数据结构')insert课程表values('100003','VB程序设计')update课程表set名称='VisualBasic程序设计'where课号='100003'delete课程表where课号='100003'3．创建视图：任课表(教师编号，，课号，课程名称，课时数)；createview[任课表视图]asselect教师编号,,课号,课程名称,课时数from教师表,任课表where教师表.编号=任课表.教师编号4．查询：所有教"SQLServer数据库”这门课程的老师**；createfunction[*门课任课教师](课程名varchar(15))returnstableasreturn(select课程名称,课时数,教师**=from任课表视图where课程名=课程名)goselect*from[*门课任课教师]('SQLServer数据库')5．查询：最大课时数、最低课时数、平均课时的；createprocedure[统计课时数]as

select最大课时数=ma*(课时),最小课时数=min(课时),平均课时数=avg(课时)from任课表go6．检索有一门或一门以上课程课时数大于90的所有教师的信息，包括编号、；select编号,from教师表where编号in(selectdistinct教师编号from任课表where课时数>=90)7．修改教师表结构：教师表的职称列上，规定取值为（'教授'，'副教授'，'讲师'，'助教'）之一。create

rule

zhicheng

_rule

aszhichengin('教授','副教授','讲师','助教')gosp_bindrulezhicheng_rule,'教师表.职称'8．计算*教师代课总课时，并将值返回的存储过程以及执行代码。执行：计算"郭老师”的总课时。createprocedure[统计课时]教师名nchar(16),asbegindeclare总课时intselect总课时=sum(课时)from任课表视图where=教师名endgoe*ecute[统计课时]'郭老师'五、现在要为学校建立一个数据库，设学校的环境如下：一个系有若干个专业，每个专业一年只招一个班，每个班有若干个学生，现要建立关于系、学生、班级的数据库，关系模式为：学生表student（**，，性别，班级，生源地，高考成绩，出生日期）班级表class（班级名，专业名，系号，入学年份）系表department（系号，系名）使用SQL语句完成一下功能：1.建立数据库名为school并激活当前新建的数据库，要求主数据文件的逻辑文件名为school，实际文件名为school.mdf，事务日志文件的逻辑文件名为school,，实际文件名分别为school.ldf，上述文件的初始容量均为5MB，最大容量均为20MB，递增量均为1MB。createdatabaseschoolonprimary

(

name

=

school,

filename='SCHOOL.mdf',SIZE=5MB,MA*SIZE=20MB,filegrowth=1MB)logon(

name=

school_log,

filename=

‘SCHOOL_log.ldf',SIZE=2MB,ma*size=20MB,filegrowth=10%)go2．建立表student（**，，性别，班级，生源地，高考成绩，出生日期）要求：（1）表要有主键（2）学生的性别只能取‘男’或者‘女’（3）学生**不能为空use

SCHOOL

createtablestudent--学生表定义(**char(10)primarykey,--主键为****varchar(20)notnull,--**不能为空值性别char(4)default'男',--性别默认为男性生源地varchar(12),--生源地出生日期datetime,check((性别='男')or(性别='女'))--性别必须为男性或女性)3.在student表中插入一条记录：（**：8101，:*三，性别：男，生源地：）insertintostudentvalues(8101,'*三','男','')4.根据student表完成以下的查询功能（1）通过对学生信息表的查询，查询出所有学生的**，**和性别。select**,,性别fromstudent（2）通过对学生信息表的查询，查询来自**的学生的记录。select*fromstudentwhere生源地=''（3）在查询语句中使用聚合函数计算出所有学生总数（起别名为学生人数）和平均高考成绩（起别名为平均成绩）。selectcount(*)as'学生人数'fromstudent（4）更新**为8103的这位学生的**为*玲。updatestudentset='*玲'where**='8103'（5）查询高考成绩最高的前5条学生记录。Selecttop5*fromstudentorderby高考成绩desc（6）在学生表中统计班级列表及各班级的人数。Select班级,count(*)fromstudentgroupby班级（7）查询高考成绩在90到100之间的学生的**、、专业名。Select**,,专业名from学生表,班级表where学生表.班级=班级表.班级名and高考成绩between90and100（8）删除**为李飞的学生记录；删除数据表student。deletefromstudentwhere='李飞'六、已知逻辑结构见下表，写出各题的T-SQL语句。表readers（读者信息表）结构属性名数据类型宽度小数位数编号（主码）char8char8读者类型int2已借数量int2books表（图书信息表）结构属性名数据类型宽度编号（主键）char15书名char42作者char8char28出版日期datetime8定价（>0）decimal8borrowinf表（借阅信息表）结构属性名数据类型宽度读者编号（外键）char8图书编号（外键）char15借期datetime8还期datetime8主键（读者编号，图书编号，借期）视图overdue（超期信息）列名对应基本表的列名读者编号Reaers.编号读者**Readers.图书编号Books.书名书名Books.应还日期Borrwinf.借期+readrtype期限创建基本表books。CREATETABLEbooks(图书编号CHAR(15)PRIMARYKEY,书名CHAR(42),作者CHAR(8),CHAR(28),出版日期DATETIME,价格float(8));创建基本表borrowinf。CREATE

TABLE

borrowinf(

读者编号CHAR