计科09数据库技术实践第二部分文档格式.docx
《计科09数据库技术实践第二部分文档格式.docx》由会员分享，可在线阅读，更多相关《计科09数据库技术实践第二部分文档格式.docx（18页珍藏版）》请在冰豆网上搜索。
joinSCsc1onsc1.Sno=s.Sno
joinCOURSEconsc1.Cno=c.Cno
wheresc1.gradein（selectMAX（Grade）fromscsc2
wheresc1.Cno=sc2.Cno）
3．查询第2学期开设的课程的总学分，列出该学期开设的课程名、学分和总学分。
selectcname,credit,（selectsum（Credit）fromCOURSEwhereSemester=2）as总学分fromCOURSE
whereSemester=2
selectcname,credit,sum（Credit）as总学分fromCOURSE
whereSemester=2
groupbycname,credit
统计考试平均成绩大于等于80分的学生的姓名、考试的课程号、考试成绩和平均成绩，并将结果按平均成绩从高到低排序。
createVIEWIS_New
as
selectStudent.sname,SC.cno,grade,
AVG（grade）over（partitionbysc.sno）asavgg
fromCoursejoinSConCourse.Cno=SC.CnojoinStudentonStudent.Sno=SC.Sno
select*fromIS_New
whereavgg>
=80
方法：
首先我建立了个视图，然后通过对视图的操作，找到平均成绩大于等于80的学生。
4．查询至少选了第4学期开设的全部课程的学生的学号和所在系。
selectsno,deptfromstudents
wherenotexists（select*fromCoursec
wheresemester=4andnotexists（select*fromSC
whereSC.Cno=oandSC.Sno=s.sno））
5．查询至少选了“0831102”号学生所选的全部课程的学生的学号。
selectsnofromstudents
wherenotexists（select*fromSCc
wherec.Sno='
0831102'
andnotexists（select*fromSC
6．查询至少选了全部学分大于3分的课程的学生的学号、所在系和所选的课程号、课程名以及学分。
selectsc.Sno,Dept,sc.Cno,cname,creditfromstudentsjoinSCons.Sno=SC.SnojoinCourseconc.Cno=SC.Sno
wherenotexists（select*fromCoursec
wherec.Credit>
3andnotexists（select*fromSC
7．查询“李勇”和“王大力”所选的相同的课程，列出课程名、开课学期和学分。
selectcname,semester,creditfromCoursejoinSConCourse.Cno=SC.CnojoinStudentonStudent.Sno=SC.Sno
wheresname='
李勇'
intersect
王大力'
8．查询“李勇”选了但“王大力”没有选的课程，列出课程名、开课学期和学分。
selectCname,semester,creditfromCoursejoinSConCourse.Cno=SC.CnojoinStudentonStudent.Sno=SC.Sno
except
9．查询至少同时选了“C001”和“C002”这两门课程的学生的学号和所选的课程号。
selectSC.Sno,SC.CnofromCoursejoinSConCourse.Cno=SC.CnojoinStudentonStudent.Sno=SC.Sno
whereSC.Snoin（
selectt1.snofrom（select*fromSCwhereCno='
C001'
）ast1
join（select*fromSCwhereCno='
C002'
）ast2
ont1.Sno=t2.Sno）
10．查询学生学号、姓名、所在系及该系的学生人数。
selectsno,sname,dept,COUNT（*）over（partitionbydept）该系的学生人数fromStudent
11．查询学生姓名、年龄、所在系及该系的平均年龄、最大年龄和最小年龄。
createVIEWIS_student
as
selectsname,dept,year（GETDATE（））-year（Birthday）age
fromstudent
selectsname,dept,
AVG（age）over（）as平均年龄,
MAX（age）over（）as最大年龄,
MIN（age）over（）as最小年龄
fromIS_student
12．查询学号、姓名、性别、所在系以及该系的学生总人数、男女生人数及男女生百分比。
查询结果样式如图5-37所示。
selectsno,sname,sex,dept,
COUNT（*）over（partitionbydept）as系总人数,
COUNT（Sex）over（partitionbysex,dept）as男女生人数,
cast（1.0*count（sex）over（partitionbysex,dept）/COUNT（*）over（partitionbydept）asdecimal（5,2））as男女生百分比
fromStudent
orderbydept
图5-3714题的查询结果样式
13．查询计算机系学生的考试情况，列出学号、姓名、考试课程名、考试成绩及成绩排名。
该查询的部分结果形式如图5-38所示。
selectsc.Sno,sname,Cname,grade,
dense_RANK（）over（orderbygradedesc）as成绩排名
fromCoursejoinSConCourse.Cno=SC.CnojoinStudentonStudent.Sno=SC.Sno
图5-3815题的查询结果样式
14．查询学生学号、选的课程号、考试成绩及考试成绩在该门课程中的排名。
该查询的部分结果形式如图5-39所示。
selectsc.Sno,sc.Cno,grade,
dense_RANK（）over（partitionbyoorderbygradedesc）asRANK
图5-3916题的查询结果样式
15．查询学生姓名、所在系、出生日期及该学生在该系中的年龄排名（按从大到小）。
该查询的部分结果形式如图5-40所示。
selectSname,Dept,birthday,
dense_RANK（）over（partitionbydeptorderbybirthdayasc）asRANK
图5-4017题的查询结果样式
16．定义一个统计每门课程的考试平均成绩和选课人数的CTE，并利用该CTE查询选课人数超过2人的课程。
withavg_count（avgs,counts）as
（
selectAVG（grade）over（partitionbycno）asavgs,
COUNT（Sno）over（partitionbycno）ascounts
fromSC
）
selectDISTINCTavgs,countsfromavg_count
wherecounts>
2
注：
avg_count——这个是公用表达式的有效标识符
（avgs,counts）——括号内是公用表达式中指定的列名
17．为Course表增加一个先修课程列PriorCno，该列表明了当前课程的先修课程编号，NULL表示该门课程没有先修课程。
修改Course表，使其有如表5-7所示的数据。
表5-7Course表数据
Cno
Cname
Credit
Semester
PriorCno
C001
高等数学
4
1
NULL
C002
大学英语
3
C003
C002
C004
计算机文化学
C005
VB
C004
C006
数据库基础
5
C007
C007
数据结构
C009
C008
计算机网络
Java语言
查询每门课程的信息及其先修课程信息，查询结果样式如图5-41所示。
图5-4119题的查询结果样式
selectc1.Cno课程名,c1.Cname课程名,c1.Semester开课学期,c2.Cno先修课程名,c2.Cname先修课程名,c2.Semester先修开课学期fromCoursec1
join（select*fromCourse）asc2
onc1.PriorCno=c2.Cno
withNEW_Courseas
（select*fromCourse）
select*fromNEW_CourseN1joinNEW_CourseN2
onN1.PriorCno=N2.Cno
18．（选做）利用18题修改后的Course表，查询课程号、课程名、先修课程号及课程所在的层次。
如果该课程没有先修课，则层次为1，如果有1门先修课，则层次为2，依次类推。
查询结果样式如图5-42所示。
图5-4220题的查询结果样式
withNEW_Course（Cno,Cname,PriorCno,Level）
（selectCno,Cname,PriorCno,1asLevel
fromCourse
wherePriorCnoisnull
unionall
selectN1.Cno,N1.Cname,N1.PriorCno,N2.level+1fromCourseN1INNERjoinNEW_CourseN2
onN1.PriorCno=N2.Cno
selectCno,Cname,PriorCno,LevelfromNEW_Course
19．创建一个新的学生表：
NewStudent，该表结构同Student。
利用Merge语句完成下列操作。
（1）将Student表中的数据同步到NewStudent表中；
mergeNewStudentasn--目标表
usingStudentass--源表
onn.sno=s.sno
whennotmatchedthen
insert（Sno,Sname,Sex,Birthday,Dept）
values（s.Sno,s.Sname,s.Sex,s.Birthday,s.Dept）;
（2）在Student表中插入数据：
（0811105，周萍，女，1992-4-10，计算机系），再将Student表的数据同步到NewStudent表中。
（3）将新插入学生的出生日期改为：
1992年4月20日，并再次将Student表的数据同步到NewStudent表中。
mergeNewStudentasn
usingStudentass
values（s.Sno,s.Sname,s.Sex,s.Birthday,s.Dept）
whenmatchedthen
updatesetn.Birthday=s.Birthday;
（4）从NewStudent表中新插入的学生记录，再将Student表中的数据同步到NewStudent表中。
（5）用一个Merge语句表达步骤
（1）~（4）的全部同步数据操作。
三．索引
本章上机练习均利用AdventureWorks数据库实现。
写出实现创建满足如下要求的索引的SQL语句，并执行这些语句。
1．请为下列查询设计一个最合适的索引，并查看建立索引前后该语句的执行计划，比较执行效率。
SELECTUserName,Password,RealName,Mobile,Age
FROMbw_Users
WHEREUserName=XXXANDAge=XX
2．Production.ProductReview表包含的列有：
ProductID（int）、ReviewerName（nvarchar（50））和Comments（nvarchar（3850））。
假设经常执行下列这种形式的查询，试为该类查询创建合适的索引，以最大程度地提高查询效率，并查看建立索引前后该语句的执行计划，比较执行效率。
SELECTComments,ReviewerName
FROMProduction.ProductReview
WHEREProductID>
=937andReviewerNamelike'
[a-d]%'
;
3．在Person.Address表上创建具有一个键列（PostalCode）和四个非键列（AddressLine1、AddressLine2、City、StateProvinceID）的非聚集包含列索引。
查看索引建立前后下列查询语句的执行计划，观察索引对效率的提高情况。
SELECTAddressLine1,AddressLine2,City,StateProvinceID,PostalCode
FROMPerson.Address
WHEREPostalCodeBETWEENN'
94000'
andN'
95999'
USEAdventureWorks_Simple;
GO
IFEXISTS（SELECTnameFROMsys.indexes
WHEREname=N'
IX_Address_PostalCode'
DROPINDEXIX_Address_PostalCodeONPerson.Address;
CREATENONCLUSTEREDINDEXIX_Address_PostalCode
ONPerson.Address（PostalCode）
INCLUDE（AddressLine1,AddressLine2,City,StateProvinceID）;
4．设经常需要执行下列类型的查询，即只统计2003年某一段时间各产品的销售总量。
为尽可能提高该类查询的效率，请分别为Sales.SalesOrderHeader和Sales.SalesOrderDetail表建立最合适的索引，并简单说明理由。
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
[实验总结部分需要对本部分实验的完成情况及收获做出总结认识]
四.实验总结
升级会员 