数据库第三部分Word文件下载.docx

数据库第三部分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