计科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
硬件测试
用游标实现对此表数据的如下修改：
将工作级别相同的工作只保留工作编号较小的一项工作，同时，将这些工作的工作描述拼接为一个
升级会员 