计科09数据库技术实践第三部分.docx
- 文档编号:12722547
- 上传时间:2023-06-07
- 格式:DOCX
- 页数:25
- 大小:206.01KB
计科09数据库技术实践第三部分.docx
《计科09数据库技术实践第三部分.docx》由会员分享,可在线阅读,更多相关《计科09数据库技术实践第三部分.docx(25页珍藏版)》请在冰点文库上搜索。
计科09数据库技术实践第三部分
实验报告
课程名称数据库技术实践
实验项目存储过程、触发器、用户自定义函数与游标
实验仪器SQLServer2008
系别____计算机科学与技术系_
专业____计算机科学与技术____
班级/学号_______________________
学生姓名_______________________
实验日期__________
成绩_______________________
指导教师_________________
[在内容说明部分请总体说明在本部分实践过程中,具体都完成了哪些内容]
一.内容说明
[请按照下面练习题的要求,完成各项内容,并说明每个题目完成的情况,是否存在问题,如何解决等]
二.实验步骤与内容
如无特别说明,以下各题均利用之前建立的Students数据库以及Student、Course和SC表实现。
1.创建满足下述要求的存储过程,并查看存储过程的执行结果。
(1)查询每个学生的修课总学分,要求列出学生学号及总学分。
createprocSumCredit
as
selectsno学号,sum(credit)总学分fromscjoincoursecono=o
groupbysno
go
execSumCredit
(2)查询学生的学号、姓名、修的课程号、课程名、课程学分,将学生所在系作为输入参数,默认值为“计算机系”。
执行此存储过程,并分别指定一些不同的输入参数值,查看执行结果。
createprocStudentInformation
@deptvarchar(50)='计算机系',@snamechar(50)
as
selectsc.sno学号,sname姓名,o课程号,cname课程名,credit学分
fromStudentsjoinSCons.Sno=sc.SnojoinCourseconc.Cno=SC.Cno
wheredept=@deptandSname=@sname
go
execStudentInformation'信息管理系','吴宾'
execStudentInformation@sname='李勇'
(3)查询指定系的男生人数,其中系为输入参数,人数为输出参数。
createprocMan_Num
@deptvarchar(50),@rsintoutput
as
select@rs=COUNT(*)fromStudent
whereDept=@deptandSex='男'
go
declare@rsint
execMan_Num'信息管理系',@rsoutput
select@rsas人数
(4)查询考试平均成绩超过指定分值的学生学号和平均成绩。
createprocAvgGrade
@gradeint
as
selectsno,AVG(grade)as'平均成绩'fromSC
groupbySno
havingAVG(grade)>@grade
go
execAvgGrade60
(5)查询查询指定系的学生中,选课门数最多的学生的选课门数和平均成绩,要求系为输入参数,选课门数和平均成绩用输出参数返回。
createprocChoose_Course
@deptvarchar(50),@rsintoutput,@avgintoutput
as
selecttop1@rs=COUNT(*),@avg=avg(grade)fromStudentsjoinSCons.Sno=sc.SnojoinCourseconc.Cno=SC.Cno
wheredept=@dept
groupbysc.Sno
go
declare@rsint,@avgint
execChoose_Course'信息管理系',@rsoutput,@avgoutput
select@rsas选课门数,@avgas平均成绩
(6)删除指定学生的修课记录,其中学号为输入参数。
createprocDel_Course
@snochar(50)
as
deletefromSC
whereSno=@sno
go
execDel_Course'0831102'
(7)修改指定课程的开课学期。
输入参数为:
课程号和修改后的开课学期,开课学期的默认值为2。
如果指定的开课学期不在1~8范围内,则不进行修改。
2.利用SSMS工具查看在students数据库中创建的全部存储过程。
Students=>可编程性=>存储过程
3.修改第1题
(1)的存储过程,使之能够查询指定系中,每个学生选课总门数、总学分和考试平均成绩。
alterprocSumCredit
@deptnvarchar(20)
as
selectCOUNT(SC.Cno)总门数,sum(credit)总学分,AVG(Grade)平均成绩fromstudentsleftjoinscons.sno=sc.snoleftjoincoursecono=o
wheredept=@dept
groupbysc.Sno
go
execSumCredit'信息管理系'
4.创建满足下述要求的触发器(前触发器、后触发器均可),并验证触发器执行情况。
(1)限制每个学期开设的课程总学分在20~30范围内。
altertriggerTR_SumGrade
oncourseafterinsert
as
declare@sint,@xint,@yint
set@s=(selectsum(Credit)fromcoursewheresemesterin(selectsemesterfrominserted))
if(20<@sand@s<30)
begin
print'课程总学分没有超出范围!
!
!
'
print@s
end
else
begin
print'课程总学分超出范围!
!
!
'
print's='
print@s
rollback
end
insertintocoursevalues('C010','汇编语言',200,1)
(2)限制每个学生每学期选课门数不能超过5门(设只针对插入操作)。
ALTERtriggerTR_MEN
onscafterinsert
as
declare@xint
set@x=(selectcount(*)fromscjoincoursecono=o
wheresemesterin(selectsemesterfromcoursewherecnoin(selectcnofrominserted))andsc.snoin(selectsnofrominserted))
if(@x>5)
begin
select*fromscjoincoursecono=o
select*frominserted
print@x
print'选课门数超过门'
rollback
end
5.在Students数据库建立如下所示的工作表和职工表
CREATETABLE工作表(
工作号CHAR(8)PRIMARYKEY,
最低工资SMALLINT,
最高工资SMALLINT)
CREATETABLE职工表(
职工号CHAR(7)PRIMARYKEY,
职工名CHAR(10)NOTNULL,
工作号CHAR(8)REFERENCES工作表(工作号),
基本工资SMALLINT,
浮动工资SMALLINT)
利用这两张表建立满足如下要求的触发器。
(1)限制职工的基本工资和浮动工资之和必须大于等于2000。
createtriggerTR_Salary
on职工表afterinsert,update
as
declare@xSMALLINT,@ySMALLINT,@zSMALLINT
set@x=(select基本工资from职工表where职工号in(select职工号frominserted))
set@y=(select浮动工资from职工表where职工号in(select职工号frominserted))
set@z=@x+@y
if(@z>=2000)
begin
print'操作符合要求'
end
else
begin
print@x
print@y
print@z
print'请注意,职工的基本工资和浮动工资之和小于!
!
!
!
'
select*from职工表
select*frominserted
rollback
end
insertinto工作表values('G001',10000,1000)
insertinto职工表values('Z001','张三','G001',1000,100)
(2)限制工作表中最高工资不能低于最低工资的1.5倍。
createtriggerTR_Salary1
on工作表afterinsert,update
as
declare@xSMALLINT,@ySMALLINT,@zfloat
set@x=(select最低工资from工作表where工作号in(select工作号frominserted))
set@y=(select最高工资from工作表where工作号in(select工作号frominserted))
set@z=@y/@x
if(@z>=1.5)
begin
print'操作符合要求'
end
else
begin
print@x
print@y
print@z
select*from工作表
select*frominserted
print'请注意,最高工资低于最低工资的.5倍'
rollback
end
insertinto工作表values('G002',1000,1000)
(3)限制不能删除基本工资低于1500的职工。
altertriggerTR_Salary2
on职工表afterdelete
as
ifexists(select*from职工表where基本工资<1500)
begin
print'操作符合要求'
end
else
begin
print'不能删除基本工资低于的职工'
select*from职工表
select*fromdeleted
rollback
end
6.创建满足下述要求的用户自定义标量函数。
(1)查询指定学生已经得到的修课总学分(考试及格的课程才能拿到学分),学号为输入参数,总学分为函数返回结果。
并写出利用此函数查询9512101学生的姓名、所修的课程名、课程学分、考试成绩以及拿到的总学分的SQL语句。
createfunctiondbo.Sum_Credit(@snochar(7))
returnsint
as
begin
declare@sumint
set@sum=(selectsum(credit)fromscjoincoursecono=o
wheresc.sno=@snoandgrade>=60)
return@sum
end
selectsname姓名,o课程名,credit课程学分,grade考试成绩,dbo.Sum_Credit(sc.sno)as总学分fromscjoincoursecono=o
joinstudentsons.sno=sc.sno
wheresc.sno='0811101'
(2)查询指定系在指定课程(课程号)的考试平均成绩。
createfunctiondbo.Avg_Grade(@deptnvarchar(20),@cnochar(6))
returnsint
as
begin
declare@Avgint
select@Avg=avg(grade)fromscjoinstudentsonsc.sno=s.sno
wheredept=@deptando=@cno
return@Avg
end
selectdistinctdbo.Avg_Grade(dept,cno)as平均成绩fromscjoinstudentsonsc.sno=s.sno
wheredept='计算机系'ando='C001'
(3)查询指定系的男生中选课门数超过指定门数的学生人数。
createfunctiondbo.Man(@deptnvarchar(20),@menshuint)
returnsint
as
begin
declare@Numint
select@Num=count(*)from(selectsc.sno,count(o)asbfromstudentsleftjoinscons.sno=sc.sno
wheredept=@deptandsex='男'
groupbysc.sno
havingcount(o)>@menshu)ast
return@Num
end
selectdistinctdbo.Man(dept,0)学生人数fromstudentsleftjoinscons.sno=sc.sno
wheredept='计算机系'
7.创建满足下述要求的用户自定义内联表值函数。
(1)查询选课门数在指定范围内的学生的姓名、所在系和所选的课程。
createfunctiondbo.F_7_1(@menshuint)
returnstable
as
return(
selectsname,dept,o,cnamefromStudentsjoinSCons.Sno=sc.Sno
joinCourseconc.Cno=SC.Cno
wheres.snoin(
selectsnofromsc
groupbysno
havingcount(*)=@menshu))
select*fromdbo.F_7_1(3)
(2)查询指定系的学生考试成绩大于等于90的学生的姓名、所在系、课程名和考试成绩。
并写出利用此函数查询计算机系学生考试情况的SQL语句,只列出学生姓名、课程名和考试成绩。
createfunctiondbo.F_7_2(@deptchar(20))
returnstable
as
return(selectsname,dept,cname,gradefromStudentsjoinSCons.Sno=sc.Sno
joinCourseconc.Cno=SC.Cno
wheredept=@deptandgrade>=90)
selectsname,cname,gradefromdbo.F_7_2('计算机系')
8.创建满足下述要求的用户自定义多语句表值函数。
(1)查询指定系年龄最大的前2名学生的姓名和年龄,包括并列的情况。
alterfunctiondbo.F_8_1(@deptchar(20))
returns@ret_F_8_1table(
snamechar(10),
ageint)
as
begin
insertinto@ret_F_8_1
selecttop2WITHTIESsname,year(GETDATE())-year(Birthday)agefromstudent
wheredept=@dept
orderbyageDESC
return
end
selectsname,agefromdbo.F_8_1('计算机系')
(2)查询指定学生(姓名)的考试情况,列出姓名、所在系、修的课程名和考试情况,其中考试情况列的取值为:
如果成绩大于等于90,则为“优”;如果成绩在80~89,则为“良好”;如果成绩在70~79,则为“一般”;如果成绩在60~69,则为“不太好”;如果成绩小于60,则为“很糟糕”。
并写出利用此函数查询李勇的考试情况的SQL语句。
alterfunctiondbo.F_8_2(@snamechar(10))
returns@ret_F_8_2table(
snamechar(10),
deptchar(20),
cnamechar(20),
GStyechar(6))
as
begin
insertinto@ret_F_8_2
selectsname,dept,cname,case
whengrade>=90then'优'
whengradebetween80and89then'良好'
whengradebetween70and79then'一般'
whengradebetween60and69then'不太好'
whengrade<60then'很糟糕'
end
fromStudentsjoinSCons.Sno=sc.SnojoinCourseconc.Cno=SC.Cno
wheresname=@sname
return
end
selectsname,dept,cname,gstyefromdbo.F_8_2('刘晨')
selectsname,dept,cname,gstyefromdbo.F_8_2('李勇')
9.创建满足下述要求的游标。
(1)查询VB课程的考试情况,并按如下形式显示结果数据:
选了VB课程的学生情况:
姓名所在系成绩
李勇计算机系86
刘晨计算机系78
吴宾信息系75
张海信息系68
print'选了VB课程的学生情况:
'
print''
print'姓名所在系成绩'
declare@schar(10),@dchar(20),@gint
declareC_9_1cursorfor
selectsname,dept,gradefromStudentsjoinSCons.Sno=sc.SnojoinCourseconc.Cno=SC.Cno
wherecname='VB'
openC_9_1
fetchnextfromC_9_1into@s,@d,@g
while@@fetch_status=0
begin
print''
print@s+@d+cast(@gaschar(4))
fetchnextfromC_9_1into@s,@d,@g
end
closeC_9_1
deallocateC_9_1
(2)统计每个系的男生人数和女生人数,并按如下形式显示结果数据。
系名性别人数
====================
计算机系男2
计算机系女1
数学系男1
数学系女1
信息系男2
信息系女1
print'系名性别人数'
print'===================='
declare@dchar(10),@schar
(2),@cint
declareC_9_2cursorfor
selectdept,sex,count(*)人数fromStudent
groupbydept,sex
orderbydept
openC_9_2
fetchnextfromC_9_2into@d,@s,@c
while@@fetch_status=0
begin
print''
print@d+''+@s+''+cast(@caschar(4))
fetchnextfromC_9_2into@d,@s,@c
end
closeC_9_2
deallocateC_9_2
(3)列出每个系的学生信息,要求首先列出一个系的系名,然后在该系名下列出本系学生的姓名和性别;再列出下一个系名,然后在此系名下再列出该系的学生姓名和性别;以此类推,直至列出全部系。
要求按如下形式显示结果数据:
计算机系学生:
李勇计算机系
刘晨计算机系
王敏计算机系
=====================
数学系学生:
钱小平数学系
王大力数学系
=====================
信息系学生:
张立信息系
吴宾信息系
张海信息系
=====================
declare@deptvarchar(20),@snamechar(10)
declareC_9_3cursorfor
selectdistinctdeptfromstudent
openC_9_3
fetchnextfromC_9_3into@dept
while@@fetch_status=0
begin
print@dept+':
'
declareC_3cursorfor
selectsname,deptfromstudent
wheredept=@dept
openC_3
fetchnextfromC_3in
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 09 数据库技术 实践 第三 部分