计科09数据库技术实践第二部分.docx
《计科09数据库技术实践第二部分.docx》由会员分享,可在线阅读,更多相关《计科09数据库技术实践第二部分.docx(18页珍藏版)》请在冰豆网上搜索。
计科09数据库技术实践第二部分
实验报告
课程名称数据库技术实践
实验项目高级查询与索引
实验仪器SQLServer2008
系别____计算机科学与技术系_
专业____计算机科学与技术____
班级/学号_______________________
学生姓名_______________________
实验日期__________
成绩_______________________
指导教师_________________
[在内容说明部分请总体说明在本部分实践过程中,具体都完成了哪些内容]
一.内容说明
[请按照下面练习题的要求,完成各项内容,并说明每个题目完成的情况,是否存在问题,如何解决等]
二.高级查询
根据Student、Course和SC表(可先将“学生数据.xls”文件中的数据导入到三张表中),并利用本章介绍的SQL查询功能,编写实现如下操作的SQL语句。
1.统计每个学期学分最高的两门课程的课程名、学期和学分。
selectCname,semester,creditfromcourseasc1
whereCnoin(selecttop2Cofromcourseasc2
wherec1.semester=c2.semester
orderbycreditdesc)
2.统计每门课程成绩最高的学生的姓名、所在系、课程名和该门课程成绩。
selectsname,sdept,cname,gradefromstudents
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
whereSC.Cno=oandSC.Sno=s.sno))
6.查询至少选了全部学分大于3分的课程的学生的学号、所在系和所选的课程号、课程名以及学分。
selectsc.Sno,Dept,sc.Cno,cname,creditfromstudentsjoinSCons.Sno=SC.SnojoinCourseconc.Cno=SC.Sno
wherenotexists(select*fromCoursec
wherec.Credit>3andnotexists(select*fromSC
whereSC.Cno=oandSC.Sno=s.sno))
7.查询“李勇”和“王大力”所选的相同的课程,列出课程名、开课学期和学分。
selectcname,semester,creditfromCoursejoinSConCourse.Cno=SC.CnojoinStudentonStudent.Sno=SC.Sno
wheresname='李勇'
intersect
selectcname,semester,creditfromCoursejoinSConCourse.Cno=SC.CnojoinStudentonStudent.Sno=SC.Sno
wheresname='王大力'
8.查询“李勇”选了但“王大力”没有选的课程,列出课程名、开课学期和学分。
selectCname,semester,creditfromCoursejoinSConCourse.Cno=SC.CnojoinStudentonStudent.Sno=SC.Sno
wheresname='李勇'
except
selectCname,semester,creditfromCoursejoinSConCourse.Cno=SC.CnojoinStudentonStudent.Sno=SC.Sno
wheresname='王大力'
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
fromCoursejoinSConCourse.Cno=SC.CnojoinStudentonStudent.Sno=SC.Sno
图5-3916题的查询结果样式
15.查询学生姓名、所在系、出生日期及该学生在该系中的年龄排名(按从大到小)。
该查询的部分结果形式如图5-40所示。
selectSname,Dept,birthday,
dense_RANK()over(partitionbydeptorderbybirthdayasc)asRANK
fromStudent
图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
1
NULL
C003
大学英语
3
2
C002
C004
计算机文化学
2
2
NULL
C005
VB
2
3
C004
C006
数据库基础
4
5
C007
C007
数据结构
4
4
C009
C008
计算机网络
4
4
C004
C009
Java语言
4
3
C004
查询每门课程的信息及其先修课程信息,查询结果样式如图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)
as
(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表中。
mergeNewStudentasn--目标表
usingStudentass--源表
onn.sno=s.sno
whennotmatchedthen
insert(Sno,Sname,Sex,Birthday,Dept)
values(s.Sno,s.Sname,s.Sex,s.Birthday,s.Dept);
(3)将新插入学生的出生日期改为:
1992年4月20日,并再次将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)
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;
GO
CREATENONCLUSTEREDINDEXIX_Address_PostalCode
ONPerson.Address(PostalCode)
INCLUDE(AddressLine1,AddressLine2,City,StateProvinceID);
GO
SELECTAddressLine1,AddressLine2,City,StateProvinceID,PostalCode
FROMPerson.Address
WHEREPostalCodeBETWEENN'94000'andN'95999';
GO
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
[实验总结部分需要对本部分实验的完成情况及收获做出总结认识]
四.实验总结