《数据库系统》上机题实验四TSQL查询存储过程触发器完整性.docx
- 文档编号:3729078
- 上传时间:2022-11-25
- 格式:DOCX
- 页数:11
- 大小:334.46KB
《数据库系统》上机题实验四TSQL查询存储过程触发器完整性.docx
《《数据库系统》上机题实验四TSQL查询存储过程触发器完整性.docx》由会员分享,可在线阅读,更多相关《《数据库系统》上机题实验四TSQL查询存储过程触发器完整性.docx(11页珍藏版)》请在冰豆网上搜索。
《数据库系统》上机题实验四TSQL查询存储过程触发器完整性
实验四:
T-SQL查询、存储过程、触发器、完整性
姓名:
张浩学号:
专业:
信息管理与信息系统
班级:
1
课程名称:
数据库系统概论课程代码:
实验时间:
2010年12月24日,2010年12月18日
实验地点:
12号楼406室
1.实验特点:
类型:
验证类别:
专业基础计划学时:
4学时每组人数:
1人
2.实验目的与要求:
实验目的:
1)熟练掌握局部变量、游标的定义和使用
2)熟练掌握存储过程、触发器的创建和使用
3)掌握数据库完整性约束的定义和使用
基本要求:
1)掌握上述过程定义的一般格式
3.主要仪器设备:
计算机+SQLServer2000
第一部分:
T-SQL程序设计
(1).如果3号课程的平均成绩在80分以上,则输出“3号课程成绩良好”,否则输出“3号成绩一般”if(selectavg(grade)fromscwherecno='3')>80
begin
print'3号成绩良好'
end
else
begin
print'3号成绩一般'
end
(2)计算并输出95003号学生的平均成绩,若无该生信息,则显示“该生未选课”,提示信息.declare@messagevarchar(200)
ifexists(selectsnofromscwheresc.sno='95003')
beginselectavg(grade)fromscwheresc.sno='95003'end
elsebeginset@message='该生未选课'
print@messageend
(3).如果有成绩在90分以上的学生,则显示他的学号,课程和成绩,否则显示“没有学生的课程成绩在90分以上”提示信息declare@msgvarchar(200)
ifexists(selectgradefromscwheresc.grade>=90)
selectsno,cno,gradefromscwheresc.grade>=90
else
set@msg='没有学生的成绩在90以上'
print@msg
(4).利用游标逐行显示student表中的记录。
declare@cursorvarcursor
set@cursorvar=cursorscrolldynamicfor
select*fromstudent
open@cursorvar
fetchnextfrom@cursorvar
while@@fetch_status=0
beginfetchnextfrom@cursorvarendclose@cursorvardeallocate@cursorvar
(5).用自定义函数计算全体男生的平均年龄
CREATEFUNCTIONaverage(@cnumchar
(2))RETURNSintas
begindeclare@averint
select@aver=(selectavg(sage)fromstudentwheressex=@cnumgroupbyssex)
return@aver
end
go
declare@aver1int,@cnum1char
(2)
set@cnum1='男'
select@aver1=dbo.average(@cnum1)
select@aver1as'全体男生的平均年龄'
(6).显示course表中课程名的前2个字符。
selectleft(cname,2)
fromcourse
orderbycno
(7).在一列中显示student中各元组的学号中的年级,列名显示为“年级”;另一列中显示学号中的学生序列号,列名显示为“序号”。
SELECTSUBSTRING(sno,1,2)'年级',SUBSTRING(sno,3,LEN(sno)-1)'序号'
FROMstudent
ORDERBYsno
(8).在选课表中显示学号、课程号,并根据成绩:
0-59显示“不合格”;60-79显示“合格”;80-89显示“良好”;90-100显示“优秀。
”selectsno,cno,grade=
case
whengrade>=0andgrade<60then'不合格'
whengrade>=60andgrade<80then'合格'
whengrade>=80andgrade<90then'良好'
whengrade>=90andgrade<=100then'优秀'
end
fromsc
第二部分:
存储过程
(1)创建一个为worker表添加职工记录的存储过程Addworker
CREATEprocAddworker@职工号char(4),
@名姓char(8),
@性别char
(2),
@出生日期datetime,
@党员否char
(2),
@参加工作datetime,
@部门号char(4)
asinsertintoworker
values(@职工号,@名姓,@性别,@出生日期,@党员否,@参加工作,@部门号);
(2)创建一个存储过程Delworker删除worker表中指定职工号的记录
createprocDelworker@zhigonghaochar(4)asdeletefromworkerwhere职工号=@zhigonghao;
go
(3)显示存储过程Delworker的定义信息。
(4)删除存储过程Addworker和Delworker。
不要截图
dropprocedureAddworker
dropprocedureDelworker
(5)创建并执行以下存储过程:
a.从数据库表中查询,返回学生学号、姓名、课程名、成绩
createprocs_back
as
selectstudent.sno,sname,cname,grade
fromstudent,sc,course
wherestudent.sno=sc.snoando=o
go
execs_back
b.从数据库表中查询指定学号的学生学号,姓名、班级,该存储过程接受与传递参数,精确匹配的值
createprocr_back
@achar(10)
asselectsno,snamefromstudentwheresno=@a
go
execr_back'95003'
第三部分:
触发器
(1)在表depart上创建一个触发器depart_update,当更改部门号时同步更改worker表中对应的部门号createtriggerdepart_update
ondepart
forupdate
asupdateworker
set部门号=(select部门号frominserted)
whereworker.部门号=(select部门号fromdeleted)
。
(2)在表worker上创建一个触发器worker_delete,当删除职工记录时同步删除salary表中对应的职工记录。
createtriggerworker_delete
onworkerfordelete
asdeletefromsalary
where职工号=(select职工号fromdeleted)
(3)删除触发器depart_update
droptriggerdepart_update
(4)删除触发器worker_delete
droptriggerworker_delete
(5)在数据库中创建一个触发器,向选课表添加一条纪录时,检查该纪录的学号在学生表中是否存在,检查该纪录的课程号在课程表中是否存在,若其中有一项为否,则拒绝添加操作,并显示“违反数据一致性”提示信息。
例如:
向选课表中添加一条记录('95007','2',99),说明95009不在student表中
createtriggersc_insertonscforinsertas
declare@xuehaochar(10),@kechenghaochar(10),@chengjismallint,@a,@b
select@xuehao=sno,@kechenghao=cnofrominserted
set@a=selectsnofromstudentwheresno=@xuehao
set@b=selectcnofromcoursewherecno=@kechenghao
ifexists(@aand@b)
beign
insertintoscvalues(@xuehao,@kechenghao,@chengji)
end
rollbacktransactionprint'违反数据一致性'
insertintoscvalues('95007','2',99)
第四部分:
数据库完整性
1、实施worker表的“性别”字段默认值为“男”的约束
altertableworker
addconstraintcon1default'男'for性别
go
例如:
insertworker(职工号)values(111)
select*fromworker
2实施salary表的“工资”字段值在0~9999的约束
createtablesalary
(职工号char(4),
姓名char(8),
日期datetime,
工资decimalnotnullcheck(工资>=0and工资<=9999),
primarykey(职工号,日期))
3实施depart表的“部门号”字段值唯一的非聚集索引的约束
createtabledepart
(部门号char(4)unique,
部门名char(10))
Go
4、为worker表建立外键“部门号”,参考表depart的“部门号”列。
Createtableworker
(职工号char(4)primarykey,
姓名char(8),
性别char
(2),
出生日期datetime,
党员否char
(2),
参加工作datetime,
部门号char(4),
Foreignkey(部门号)referencesdepart(部门号))
5、建立一个规则sex:
@性别=’男’OR@性别=’女’,将其绑定到worker表的“性别”列上。
createruleworker_rule
as@性别like'男'or@性别like'女'
go
execsp_bindrule'worker_rule','worker.性别'
Go
6、删除1小题所建立的约束
Altertableworkerdropconstraintcon1
7、删除2小题所建立的约束。
Altertablesalarydrop工资check
8、删除3小题所建立的约束
Altertabledepartdropunique
9、删除4小题所建立的约束
Altertableworkerdropforeignkey
10.解除5小题所建立的绑定并删除规则sex
ifexists(selectnamefromsysobjects
wherename='worker_rule'andtype='R')
begin
execsp_unbindrule'worker.性别'
dropruleworker_rule
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 数据库系统 数据库 系统 上机 实验 TSQL 查询 存储 过程 触发器 完整性