数据库第三部分Word文档下载推荐.docx
《数据库第三部分Word文档下载推荐.docx》由会员分享，可在线阅读，更多相关《数据库第三部分Word文档下载推荐.docx（28页珍藏版）》请在冰点文库上搜索。
ONPerson.Address（PostalCode）
INCLUDE（AddressLine1,AddressLine2,City,StateProvinceID）;
索引使该查询速度变快了
建立索引后：
4.设经常需要执行下列类型的查询，以统计2003年某一段时间各产品的销售总量。
SELECTProductID,SUM（sod.OrderQty）ASQtySold
FROMSales.SalesOrderHeadersoh
JOINSales.SalesOrderDetailsod
ONsoh.SalesOrderID=sod.SalesOrderID
WHEREsoh.OrderDate>
='
2003-08-02'
ANDsoh.OrderDate<
'
2003-08-31'
GROUPBYsod.ProductID
为尽可能提高该类查询的执行效率，请分别为Sales.SalesOrderHeader和Sales.SalesOrderDetail表建立合适的索引，并简单说明理由。
查看索引建立前后上述查询语句的执行计划，观察索引对该查询的效率提高情况。
索引使该查询速度变快了
三．存储过程和触发器
如无特别说明，以下各题均利用第6章建立的Students数据库以及Student、Course和SC表实现。
1．创建满足下述要求的存储过程，并查看存储过程的执行结果。
（1）查询每个学生的修课总学分，要求列出学生学号及总学分。
CREATEPROCEDURESELECT_STUDENT
AS
SELECTSno,sum（Credit）as总学分
FROMscjoincoursecono=o
GroupbySno
EXECSELECT_STUDENT
（2）查询学生的学号、姓名、修的课程号、课程名、课程学分，将学生所在系作为输入参数，默认值为“计算机系”。
执行此存储过程，并分别指定一些不同的输入参数值，查看执行结果。
CREATEPROCinformation
@deptCHAR（20）='
计算机系'
AS
SELECTsc$.sno,sname,sc$.cno,cname,creditFROMstudent$sinnerjoinsc$ON
s.sno=sc$.snoinnerjoincourse$cONo=sc$.cno
WHEREdept=@dept
EXECinformation'
信息管理系'
EXECSELECT_STUDENT1'
通信工程系'
null
（3）查询指定系的男生人数，其中系为输入参数，人数为输出参数。
CREATEPROCEDURESELECT_STUDENT2
@t_Deptnvarchar（20）,@total_manintOUTPUT
SELECT@total_man=COUNT（*）FROMstudent
WHEREDept=@t_DeptANDSex='
男'
PRINT@total_man
DECLARE@RE_manint--声明变量
EXECSELECT_STUDENT2'
@RE_manOUTPUT
--PRINT@RE_man
（4）查询指定学生（姓名）在指定学期的选课门数和考试平均成绩，要求姓名和学期为输入参数，选课门数和平均成绩用输出参数返回，平均成绩保留到小时点后2位。
createprocedureti_4
@namechar（10）,@semesint,
@countCnointoutput,
@avgGnumeric（4,2）output
as
select@countCno=COUNT（*）,@avgG=avg（Grade*1.00）from
SCjoinCourseconSC.Cno=c.Cno
joinStudentsons.Sno=SC.Sno
whereSemester=@semes
andSname=@name
groupbySC.Cno
declare@cint,@anumeric（4,2）
execti_4"
李勇"
2,@coutput,@aoutput
print@c
print@a
（5）查询指定学生（学号）的选课门数。
如果指定学生不存在，则返回代码1；
如果指定的学生没有选课，则返回代码2；
如果指定学生有选课，则返回代码0，并用输出参数返回该学生的选课门数。
createprocedureti_5
@snochar（7）,
@Countintoutput
ifexists（select*fromStudentwhereSno=@sno）
begin
ifexists（select*fromscwheresno=@sno）
begin
select@Count=count（Cno）
fromStudentleftjoinsconStudent.Sno=sc.Sno
wherestudent.Sno=@sno
return0
end
else
return2
end
else
return1
（6）删除指定学生（学号）的修课记录，如果指定的学生不存在，则显示提示信息“没有指定学生”；
如果指定的学生没有选课，则显示提示信息“该学生没有选课”。
学号为输入参数。
createprocedureti_6
@snochar（7）
as
if@snoisnotnullandexists（select*fromStudentwhereSno=@sno）
begin
ifexists（select*fromSCwhereSno=@sno）
deletefromSCwhereSno=@sno
else
print'
该学生没有选课'
end
else
print'
没有指定学生'
（7）修改指定课程的开课学期。
输入参数为：
课程号和修改后的开课学期。
createprocedureti_7
@cnochar（6）,
@semint
updateCourseset
Semester=@sem
whereCno=@cno
（8）在Course表中插入一行数据，课程号、课程名、学分、开课学期均为输入参数。
课程号为C100、课程名为操作系统、学分为4、开课学期为4，开课学期的默认值为3。
如果学分大于10或者小于1，则不插入数据，并显示提示信息“学分为1~10间的整数”。
createprocedureti_8
@cnamevarchar（20）,
@credittinyint,
@semtinyint
if@creditbetween1and10
insertintoCoursevalues（@cno,@cname,@credit,@sem）
学分为1~10间的整数'
2．创建满足下述要求的DML触发器（前触发器、后触发器均可），并验证触发器执行情况。
（1）限制学生所在系的取值范围为{计算机系，信息管理系，数学系，通信工程系}
createtriggertri_dept
onStudentafterinsert,update
ifexists（select*frominserted
whereSdeptnotin（'
'
数学系'
））
rollback
go
（2）限制每个学期开设的课程总学分在20~30范围内。
createTRIGGEReve_total_credit
ONcourseAFTERINSERT
declare@tint
SELECT@t=SUM（C.Credit）FROMCoursec
JOININSERTEDIONI.Semester=c.Semester
IF（@t<
20or@t>
30）
BEGIN
PRINT'
本学期课程学分限制在--30之间！
'
ROLLBACK
END
插入正常的数据：
插入超限的数据提醒：
（3）限制每个学生每学期选课门数不能超过6门（设只针对单行插入操作）。
CREATETRIGGEReve_total_cno
SELECT@t=COUNT（*）FROMCoursec
JOINSCSONS.cno=C.cno
0or@t>
6）
本学期选课门数不能超过6门！
（4）限制不能删除有人选的课程。
createtriggertri_delCno
oncourseafterdelete
ifexists（select*fromdeleteddwhered.Cnoin（selectCnofromsc））
rollback
（5）利用10.2.2例6建立的Teachers表和Depts表，编写实现如下要求的触发器：
每当在Teachers表中修改了某个教师的职称时，自动维护Depts表中职称人数统计的一致性。
（考虑同时修改多名教师职称的情况）
（6）利用10.2.2例6建立的Teachers表和Depts表，首先为Depts表增加一个记录部门教师人数的列，列名为：
DeptCount，类型为整型。
然后编写实现如下要求的触发器：
每当在Teachers表中插入一行数据或者是删除一行数据时，自动维护Depts表中的相关信息。
四．函数和游标
如无特别说明，以下各题均利用第6章建立的Student、Course和SC表实现。
3．创建满足下述要求的用户自定义标量函数。
（1）查询指定学生已经得到的修课总学分（考试及格的课程才能拿到学分），学号为输入参数，总学分为函数返回结果。
并写出利用此函数查询0811101学生的姓名、所修的课程名、课程学分、考试成绩以及拿到的总学分的SQL语句。
CREATEFUNCTIONDBO.FIND_XF（@SNOCHAR（7））
RETURNSINT
DECLARE@XINT
SELECT@X=SUM（CREDIT）FROMSTUDENTSJOINSC
ONS.SNO=SC.SNOJOINCOURSECONSC.CNO=C.CNO
wheregrade>
=60ands.sno=@SNO
RETURN@X
END
SELECTsnameAS姓名,cnameAS课程名,
creditAS课程学分,gradeAS考试成绩,
dbo.find_xf（s.sno）as总学分
fromSTUDENTSJOINSCON
S.SNO=SC.SNOJOINCOURSECONSC.CNO=C.CNO
wheres.sno=0811101
（2）查询指定系在指定课程（课程号）的考试平均成绩。
CREATEFUNCTIONDBO.FIND_AVG（@DEPTCHAR（20）,@CNOCHAR（6））
RETURNSnumeric（4,2）
DECLARE@AVGnumeric（4,2）
SELECT@AVG=AVG（GRADE）FROMSCJOIN
studentsonsc.sno=s.sno
joincoursecono=o
whereo=@cnoanddept=@dept
return@AVG
End
selectS.dept,So,
dbo.FIND_AVG（S.dept,o）ASavg_GRADE
fromscjoinstudentsonsc.sno=s.sno
wheredept='
（3）查询指定系的男生中选课门数超过指定门数的学生人数。
createFUNCTIONDBO.FIND_man（@DEPTCHAR（20）,@CNOint）
returnsint
BEGIN
DECLARE@cumint
select@cum=COUNT（*）fromstudentsjoin
scons.sno=sc.sno
wheredept=@deptandsex='
groupbys.SnohavingCOUNT（*）>
@cno
return@cum
selectsname,dept,cname,DBO.FIND_man（dept,1）as学生人数
fromstudentsjoinscons.sno=sc.sno
wheredept='
4．创建满足下述要求的用户自定义内联表值函数。
（1）查询选课门数在指定范围内的学生的姓名、所在系和所选的课程名。
CREATEfunctionfind_1（@xint）
returnstable
return（selectsname,dept,cname
wheres.snoin（
selectsnofromSCgroupbySnohavingCOUNT（*）>
@x））
--利用函数查询选课门数超过门的情况
select*fromfind_1（3）
（2）查询指定系的学生考试成绩大于等于90的学生的姓名、所在系、课程名和考试成绩。
并写出利用此函数查询计算机系学生考试情况的SQL语句，只列出学生姓名、课程名和考试成绩。
createfunctionfind_2（@xchar（20））
return（SELECTsname,dept,cname,gradeFROMstudentASs
LEFTJOINscONs.sno=sc.sno
LEFTJOINcourseAScONo=o
WHEREgrade>
=90ands.dept=@x）
selectsname,cname,gradefromfind_2（'
）
5．创建满足下述要求的用户自定义多语句表值函数。
（1）查询指定系年龄最大的前2名学生的姓名和年龄，包括并列的情况。
alterFUNCTIONDBO.FIND_AGE（@DEPTCHAR（20））
RETURNS@RET_FIND_AGETABLE（
SNAMECHAR（10）,
AGEINT）
INSERTINTO@RET_FIND_AGE
SELECTTOP2WITHtiessname,YEAR（GETDATE（））-YEAR（BirTHDAY）AGE
FROMSTUDENTWHEREDEPT=@DEPT
ORDERBYAGEDESC
RETURN
SELECTSNAME,AGEfromFIND_AGE（'
）
（2）查询指定学生（姓名）的考试情况，列出姓名、所在系、修的课程名和考试情况，其中考试情况列的取值为：
如果成绩大于等于90，则为“优”；
如果成绩在80~89，则为“良好”；
如果成绩在70~79，则为“一般”；
如果成绩在60~69，则为“不太好”；
如果成绩小于60，则为“很糟糕”。
并写出利用此函数查询李勇的考试情况的SQL语句。
createfunctionfind_3（@xnchar（5））
return（SELECTsname,dept,cname,grade,
case
whengrade>
=90then'
优'
whengradebetween80and89then'
良'
whengradebetween70and79then'
一般'
whengradebetween60and69then'
不太好'
whengrade<
60then'
很糟糕'
ENDAS'
考试情况'
FROMstudentASs
WHERESNAME=@x）
SELECT*FROMFIND_3（'
李勇'
6．创建满足下述要求的游标。
（1）查询Java课程的考试情况，并按图11-18所示样式显示结果数据。
declare@cnamevarchar（20）,@cnochar（8）,@snamenchar（5）,@sexnchar
（1）,
@deptnvarchar（20）,@gradesmallint
--（a）声明游标
declarecur_cnocursorfor
selectdistinctcnamefromCourse
wherecnoin（selectcnofromscwheregradeisnotnull
andcname='
VB'
opencur_cno
--（3）取数据
fetchnextfromcur_cnointo@cname
while@@FETCH_STATUS=0
课程名:
+@cname
姓名性别所在系成绩'
-----------------------------
--
（2）对每一门课程，查询选了这门课程的学生（高等数学）:
用游标实现
--（a）声明游标
declarecur_scursorfor
selectsname,sex,dept,gradefromstudentsjoinSCons.Sno=SC.Sno
joincoursecono=o
wherecname=@cname
--（b）打开游标
opencur_s
--取数据处理
fetchnextfromcur_sinto@sname,@sex,@dept,@grade
while@@FETCH_STATUS=0
if@gradeisnotnull
print@sname+@sex+'
+@dept+'
+cast（@gradeaschar（4））
null'
fetchnextfromcur_sinto@sname,@sex,@dept,@grade
closecur_s
deallocatecur_s
FETCHNEXTFROMcur_cnoINTO@cname
closecur_cno
deallocatecur_cno
（2）
统计每个系的男生人数和女生人数，并按图11-19所示样式显示结果数据。
DECLARE@countsINT,@deptNVARCHAR（14）,@sexCHAR（10）
DECLAREc2CURSORFORSELECTdept,sex,count（*）FROMstudent$
GROUPBYs
升级会员 