计科09数据库技术实践第三部分Word文档格式.docx
- 文档编号:17086726
- 上传时间:2022-11-28
- 格式:DOCX
- 页数:22
- 大小:205.38KB
计科09数据库技术实践第三部分Word文档格式.docx
《计科09数据库技术实践第三部分Word文档格式.docx》由会员分享,可在线阅读,更多相关《计科09数据库技术实践第三部分Word文档格式.docx(22页珍藏版)》请在冰豆网上搜索。
execStudentInformation@sname='
李勇'
(3)查询指定系的男生人数,其中系为输入参数,人数为输出参数。
createprocMan_Num
@deptvarchar(50),@rsintoutput
select@rs=COUNT(*)fromStudent
whereDept=@deptandSex='
男'
declare@rsint
execMan_Num'
@rsoutput
select@rsas人数
(4)查询考试平均成绩超过指定分值的学生学号和平均成绩。
createprocAvgGrade
@gradeint
selectsno,AVG(grade)as'
平均成绩'
fromSC
groupbySno
havingAVG(grade)>
@grade
execAvgGrade60
(5)查询查询指定系的学生中,选课门数最多的学生的选课门数和平均成绩,要求系为输入参数,选课门数和平均成绩用输出参数返回。
createprocChoose_Course
@deptvarchar(50),@rsintoutput,@avgintoutput
selecttop1@rs=COUNT(*),@avg=avg(grade)fromStudentsjoinSCon=joinCoursecon=
wheredept=@dept
groupby
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)
selectCOUNT总门数,sum(credit)总学分,AVG(Grade)平均成绩fromstudentsleftjoinscon=leftjoincoursecon=
wheredept=@dept
execSumCredit'
4.创建满足下述要求的触发器(前触发器、后触发器均可),并验证触发器执行情况。
(1)限制每个学期开设的课程总学分在20~30范围内。
altertriggerTR_SumGrade
oncourseafterinsert
declare@sint,@xint,@yint
set@s=(selectsum(Credit)fromcoursewheresemesterin(selectsemesterfrominserted))
if(20<
@sand@s<
30)
begin
print'
课程总学分没有超出范围!
!
'
print@s
end
else
begin
print'
课程总学分超出范围!
s='
print@s
rollback
insertintocoursevalues('
C010'
汇编语言'
200,1)
(2)限制每个学生每学期选课门数不能超过5门(设只针对插入操作)。
ALTERtriggerTR_MEN
onscafterinsert
declare@xint
set@x=(selectcount(*)fromscjoincoursecon=
wheresemesterin(selectsemesterfromcoursewherecnoin(selectcnofrominserted))andin(selectsnofrominserted))
if(@x>
5)
select*fromscjoincoursecon=
select*frominserted
print@x
选课门数超过门'
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
print@x
print@y
print@z
请注意,职工的基本工资和浮动工资之和小于!
!
select*from职工表
select*frominserted
rollback
insertinto工作表values('
G001'
10000,1000)
insertinto职工表values('
Z001'
张三'
1000,100)
(2)限制工作表中最高工资不能低于最低工资的倍。
createtriggerTR_Salary1
on工作表afterinsert,update
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>
=
print@y
print@z
select*from工作表
select*frominserted
请注意,最高工资低于最低工资的.5倍'
insertinto工作表values('
G002'
1000,1000)
(3)限制不能删除基本工资低于1500的职工。
altertriggerTR_Salary2
on职工表afterdelete
as
ifexists(select*from职工表where基本工资<
1500)
不能删除基本工资低于的职工'
select*from职工表
select*fromdeleted
6.创建满足下述要求的用户自定义标量函数。
(1)查询指定学生已经得到的修课总学分(考试及格的课程才能拿到学分),学号为输入参数,总学分为函数返回结果。
并写出利用此函数查询9512101学生的姓名、所修的课程名、课程学分、考试成绩以及拿到的总学分的SQL语句。
createfunction(@snochar(7))
returnsint
declare@sumint
set@sum=(selectsum(credit)fromscjoincoursecon=
where=@snoandgrade>
=60)
return@sum
selectsname姓名,课程名,credit课程学分,grade考试成绩,as总学分fromscjoincoursecon=
joinstudentson=
where='
0811101'
(2)查询指定系在指定课程(课程号)的考试平均成绩。
createfunction(@deptnvarchar(20),@cnochar(6))
declare@Avgint
select@Avg=avg(grade)fromscjoinstudentson=
wheredept=@deptand=@cno
return@Avg
selectdistinct(dept,cno)as平均成绩fromscjoinstudentson=
wheredept='
and='
C001'
(3)查询指定系的男生中选课门数超过指定门数的学生人数。
createfunction(@deptnvarchar(20),@menshuint)
declare@Numint
select@Num=count(*)from(select,countasbfromstudentsleftjoinscon=
wheredept=@deptandsex='
groupby
havingcount>
@menshu)ast
return@Num
selectdistinct(dept,0)学生人数fromstudentsleftjoinscon=
7.创建满足下述要求的用户自定义内联表值函数。
(1)查询选课门数在指定范围内的学生的姓名、所在系和所选的课程。
createfunction(@menshuint)
returnstable
return(
selectsname,dept,,cnamefromStudentsjoinSCon=
joinCoursecon=
wherein(
selectsnofromsc
groupbysno
havingcount(*)=@menshu))
select*from(3)
(2)查询指定系的学生考试成绩大于等于90的学生的姓名、所在系、课程名和考试成绩。
并写出利用此函数查询计算机系学生考试情况的SQL语句,只列出学生姓名、课程名和考试成绩。
createfunction(@deptchar(20))
return(selectsname,dept,cname,gradefromStudentsjoinSCon=
wheredept=@deptandgrade>
=90)
selectsname,cname,gradefrom('
)
8.创建满足下述要求的用户自定义多语句表值函数。
(1)查询指定系年龄最大的前2名学生的姓名和年龄,包括并列的情况。
alterfunction(@deptchar(20))
returns@ret_F_8_1table(
snamechar(10),
ageint)
insertinto@ret_F_8_1
selecttop2WITHTIESsname,year(GETDATE())-year(Birthday)agefromstudent
wheredept=@dept
orderbyageDESC
return
selectsname,agefrom('
(2)查询指定学生(姓名)的考试情况,列出姓名、所在系、修的课程名和考试情况,其中考试情况列的取值为:
如果成绩大于等于90,则为“优”;
如果成绩在80~89,则为“良好”;
如果成绩在70~79,则为“一般”;
如果成绩在60~69,则为“不太好”;
如果成绩小于60,则为“很糟糕”。
并写出利用此函数查询李勇的考试情况的SQL语句。
alterfunction(@snamechar(10))
returns@ret_F_8_2table(
deptchar(20),
cnamechar(20),
GStyechar(6))
insertinto@ret_F_8_2
selectsname,dept,cname,case
whengrade>
=90then'
优'
whengradebetween80and89then'
良好'
whengradebetween70and79then'
一般'
whengradebetween60and69then'
不太好'
whengrade<
60then'
很糟糕'
fromStudentsjoinSCon=joinCoursecon=
wheresname=@sname
selectsname,dept,cname,gstyefrom('
刘晨'
9.创建满足下述要求的游标。
(1)查询VB课程的考试情况,并按如下形式显示结果数据:
选了VB课程的学生情况:
姓名所在系成绩
李勇计算机系86
刘晨计算机系78
吴宾信息系75
张海信息系68
'
姓名所在系成绩'
declare@schar(10),@dchar(20),@gint
declareC_9_1cursorfor
selectsname,dept,gradefromStudentsjoinSCon=joinCoursecon=
wherecname='
VB'
openC_9_1
fetchnextfromC_9_1into@s,@d,@g
while@@fetch_status=0
print@s+@d+cast(@gaschar(4))
closeC_9_1
deallocateC_9_1
(2)统计每个系的男生人数和女生人数,并按如下形式显示结果数据。
系名性别人数
====================
计算机系男2
计算机系女1
数学系男1
数学系女1
信息系男2
信息系女1
系名性别人数'
===================='
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
print@d+'
+@s+'
+cast(@caschar(4))
closeC_9_2
deallocateC_9_2
(3)列出每个系的学生信息,要求首先列出一个系的系名,然后在该系名下列出本系学生的姓名和性别;
再列出下一个系名,然后在此系名下再列出该系的学生姓名和性别;
以此类推,直至列出全部系。
要求按如下形式显示结果数据:
计算机系学生:
李勇计算机系
刘晨计算机系
王敏计算机系
=====================
数学系学生:
钱小平数学系
王大力数学系
信息系学生:
张立信息系
吴宾信息系
张海信息系
=====================
declare@deptvarchar(20),@snamechar(10)
declareC_9_3cursorfor
selectdistinctdeptfromstudent
openC_9_3
fetchnextfromC_9_3into@dept
print@dept+'
:
declareC_3cursorfor
selectsname,deptfromstudent
wheredept=@dept
openC_3
fetchnextfromC_3into@sname,@dept
print@sname+@dept
closeC_3
deallocateC_3
======================'
closeC_9_3
deallocateC_9_3
(4)设有工作表,结构如下:
Job(
Jobidchar(4)primarykey,--工作编号
descvarchar(40),--工作描述
lvltinyint)--工作级别
设此表包含的数据如表7-2所示。
表7-2Job表的数据
Jobid
desc
lvl
J01
软件开发
10
J02
硬件开发
12
J03
软件测试
J04
硬件维护
8
J05
硬件测试
用游标实现对此表数据的如下修改:
将工作级别相同的工作只保留工作编号较小的一项工作,同时,将这些工作的工作描述拼接为一个
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 09 数据库技术 实践 第三 部分