数据库技术实践2Word文档格式.docx
《数据库技术实践2Word文档格式.docx》由会员分享，可在线阅读，更多相关《数据库技术实践2Word文档格式.docx（25页珍藏版）》请在冰豆网上搜索。
declare@xint=1
declare@yint=5000
while@x<
=50
begin
set@y=@y-@x
set@x=@x+1
end
print@y
三．架构与基本表
1．在第3章建立的Students数据库中，创建满足如下要求的架构。
准备工作：
首先在SSMS中，以系统管理员身份执行下列脚本，创建登录账户User1和User2，并让这两个登录账户成为Students数据库中的合法用户。
CREATELOGINUser1WITHPASSWORD='
123456'
DEFAULT_DATABASE=学生数据库
go
CREATELOGINUser2WITHPASSWORD='
USE学生数据库
CREATEUSERUser1
CREATEUSERUser2
（1）为用户User1定义一个架构，架构名为Base。
createschemaBaseauthorizationUser1
（2）为用户User2定义一个架构，架构名为Inform，并在该架构中定义一个关系表Teacher，结构为：
Tnochar（8）--教师号
Tnamevarchar（10）--教师名
createschemaInformauthorizationUser2
createtableteacher（
Tnochar（8）,
Tnamevarchar（10））
（3）将Inform架构中的Teacher表传输到Base架构中。
alterschemaBasetransferInform.Teacher
（4）删除Inform架构。
dropschemaInform
2．在Students数据库中，用图形化方法创建满足下述要求的关系表。
Student
列名
说明
数据类型
约束
Sno
学号
普通编码定长字符串，长度为7
主键
Sname
姓名
普通编码定长字符串，长度为10
非空
Sex
性别
普通编码定长字符串，长度为2
取值范围为：
{男，女}
Birthdate
出生日期
日期类型
Dept
所在系
普通编码不定长字符串，长度为20
Course
Cno
课程号
Cname
课程名
Credit
学分
微整型
大于0
Semester
开课学期
SC
主键，引用Student的外键
主键，引用Course的外键
Grade
成绩
小整型
取值范围：
0～100
3．在Students数据库中，用T-SQL语句创建满足要求的表：
销售表
商品号
普通编码定长字符型，长度为10
销售时间
小日期时间型
销售价格
整型
销售数量
销售总价
等于本次销售价格*销售数量
其中（商品号，销售时间）为主键
订购表
货单号
标识列，初值为1，自动增长，每次增加1，主键
订购时间
顾客号
订购明细表
外键，引用订购表的“货单号”
订购数量
订购价格
其中（货单号，商品号）为主键。
4．创建满足如下要求的分区函数：
（1）在int列上创建右侧分区函数，该分区函数将数据分为3个区：
小于1000、1000~3000和大于3000。
createpartitionfunction右侧分区（int）
asrangeright
forvalues（1000,3000）
（2）在smalldatetime列上创建左侧分区函数，该分区函数将数据按月份分区，只针对2011年数据，每月一个区。
createpartitionfunction左侧分区（
smalldatetime）asrangeleft
forvalues（
2011-02-01,
2011-03-01,
2011-04-01,
2011-05-01,
2011-06-01,
2011-07-01,
2011-08-01,
2011-09-01,
2011-10-01,
2011-11-01,
2011-12-01）
5．在Sudents数据库中增加两个新的文件组：
MyGroup1和MyGroup2，然后利用第4题
（1）建立的分区函数，建立分区方案，使得每个分区分别存放在PRIMARY、MyGroup1和MyGroup2文件组中。
createpartitionschemefa1
aspartition右侧分区
to（Group1,Group2,Group3）
6．创建使用第4题
（2）创建的分区方案的表：
Sales_2011，结构为：
Sales_date：
小日期时间型，主键；
Sales_Total：
整型。
该表按Sales_date进行分区。
CREATETABLEdbo.Sales_2011（
GIDchar（10）NOTNULL,
Sales_datedatetimeNOTNULL,
Sales_TotalintNOTNULL）
ONfa1（Sales_Total）
四.高级查询
根据第6章给出的Student、Course和SC表，编写实现如下操作的SQL语句。
1．查询计算机系每个学生的JAVA考试情况，列出学号、姓名、成绩和成绩情况，其中成绩情况的显示规则为：
如果成绩大于等于90，则成绩情况为“好”；
如果成绩在80～89，则成绩情况为“较好”；
如果成绩在70～79，则成绩情况为“一般”；
如果成绩在60～69，则成绩情况为“较差”；
如果成绩小于60，则成绩情况为“差”。
selectsc.sno学号,student.Sname姓名,sc.Grade成绩,
case
whensc.Grade>
=90then'
好'
when（（sc.Grade>
=80）and（sc.Grade<
89））then'
较好'
=70）and（sc.Grade<
79））then'
一般'
=60）and（sc.Grade<
69））then'
较差'
else'
差'
endas'
成绩情况'
fromscjoinstudentonsc.Sno=student.Snojoincourseonsc.Cno=course.Cno
where
course.Cname='
JAVA'
2.统计每个学生的选课门数（包括没有选课的学生），列出学号、选课门数和选课情况，其中选课情况显示规则为：
如果选课门数大于等于6门，则选课情况为“多”；
如果选课门数超过在3～5门，则选课情况为“一般”；
如果选课门数在1~2门，则选课情况为“偏少”。
如果没有选课，则选课情况为“未选课”。
selectSno学号,count（Cno）选课门数,
case
when（count（sc.Cno）>
=6）then'
多'
when（（count（sc.Cno）>
=3）and（count（sc.Cno）<
5））then'
=1）and（count（sc.Cno）<
2））then'
偏少'
未选课'
end
as选课情况
fromsc
groupbysno
3.统计每个系JAVA课程的考试情况，列出系名和考试情况，其中考试情况为：
如果JAVA平均成绩大于等于90，则考试情况为“好”；
如果JAVA平均成绩在80～89，则考试情况为“良好”；
如果JAVA平均成绩在70～79，则考试情况为“一般”；
如果JAVA平均成绩低于70，则考试情况为“较差”。
selectstudent.Sdeptas系名,
whenavg（sc.Grade）>
when（（avg（sc.Grade）>
=80）and（avg（sc.Grade）<
=70）and（avg（sc.Grade）<
groupbystudent.Sdept
4.修改全部课程的学分，修改规则如下：
如果是第1～2学期开设的课程，则学分增加5分；
如果是第3～4学期开设的课程，则学分增加3分；
如果是第5～6学期开设的课程，则学分增加1分；
对其他学期开设的课程，学分不变。
查询变前
select*fromCourse
加学分
updatecourse
set
Credit=
case
when（（Semester>
=1）and（Semester<
=2））thenCredit+5
=3）and（Semester<
=4））thenCredit+3
=5）and（Semester<
=6））thenCredit+1
else0
end
查询变后
5．统计第2学期开设的课程的总学分，列出该学期开设的课程名、学分和总学分。
selectcname,credit,（selectsum（Credit）fromCoursewhereSemester=2）as总学分fromCOURSE
whereSemester=2
6．统计考试平均成绩大于等于80分的学生的姓名、考试的课程号、考试成绩和平均成绩，并将结果按平均成绩从高到低排序。
selectSname,Cno,Grade,（selectAVG（Grade）fromscjoinstudentconc.Sno=sc.Sno
wheresc.Sno=student.Sno）asAvgGrade
fromstudentjoinscc2onc2.Sno=student.Sno
where（selectAVG（Grade）fromscjoinstudentconc.Sno=sc.Sno
wheresc.Sno=student.Sno）>
=80
orderbyAvgGradedesc
7．查询计算机系年龄小于信息管理系全体学生年龄的学生的姓名和年龄。
8．查询计算机系年龄大于信息管理系某个学生年龄的学生的姓名和年龄。
9．查询哪些课程没有学生选，列出课程号和课程名。
（用EXISTS子查询实现）
10．查询计算机系哪些学生没有选课，列出学生姓名。
11．查询没有选修第2学期开设的全部课程的学生的学号、其所选的课程号和该课程的开课学期。
12．查询至少选了第4学期开设的全部课程的学生的学号和所在系。
13．查询至少选了“0831102”号学生所选的全部课程的学生的学号。
selectsnofromstudents
wherenotexists
（select*fromSCc
wherec.Sno='
0831102'
andnotexists
（select*fromSC
whereSC.Cno=oandSC.Sno=s.sno））
14．查询至少选了“张海”所选的全部课程的学生的学号、所在系和所选的课程号。
selects.Sno,sdept,cnofromStudentsjoinSCons.Sno=SC.Sno
wherenotexists（select*fromSCjoinStudentsons.Sno=SC.Sno
whereSname='
张海'
andnotexists（select*fromSC
joinStudentsonSC.sno=s.sno））
15．查询至少选了全部学分大于3分的课程的学生的学号、所在系和所选的课程号、课程名以及学分。
16．查询在第4学期开设课程中与第1学期开设的课程学分相同的课程，列出课程名和学分。
17．查询“李勇”和“王大力”所选的相同课程，列出课程名、开课学期和学分。
18．查询“李勇”选了但“王大力”没有选的课程，列出课程名、开课学期和学分。
19．查询至少同时选了“C001”和“C002”两门课程的学生的学号和所选的课程号。
20．查询学生学号、姓名、所在系及该系的学生人数。
21．查询学生姓名、年龄、所在系及该系的平均年龄、最大年龄和最小年龄。
22.查询学号、姓名、性别、所在系以及该系的学生总人数、男女生人数及男女生百分比。
查询结果样式如图7-46所示。
图7-4622题的查询结果样式
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男女生百分比fromStudentorderbydept
23.查询计算机系学生的考试情况，列出学号、姓名、考试课程名、考试成绩及成绩排名。
该查询的部分结果形式如图7-47所示。
图7-4723题的查询结果样式
24.查询学生学号、选的课程号、考试成绩及考试成绩在该门课程中的排名。
该查询的部分结果形式如图7-48所示。
图7-4824题的查询结果样式
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男女生百分比fromStudentorderbydept
25.查询学生姓名、所在系、出生日期及该学生在该系中的年龄排名（按从大到小）。
该查询的部分结果形式如图7-49所示。
图7-4925题的查询结果样式
26．定义一个统计每门课程的考试平均成绩和选课人数的CTE，并利用该CTE查询选课人数超过2人的课程。
27.（选做）查询Course表中的全部数据并将其保存到一个新表NewCourse中，然后为NewCourse表增加一个先修课程列PriorCno，该列表明了当前课程的先修课程编号，NULL表示该门课程没有先修课程。
NewCourse表的结构和数据如表7-6所示。
表7-6NewCourse表数据
PriorCno
C001
高等数学
4
1
NULL
C002
大学英语
3
C003
2
C002
C004
计算机文化学
C005
JAVA
C004
C006
数据库基础
5
C007
C007
数据结构
C009
C008
计算机网络
Java语言
用NewCourse表查询每门课程的信息及其先修课程信息，查询结果样式如图7-50所示。
图7-5027题的查询结果样式
28．（选做）利用NewCourse表，查询课程号、课程名、先修课程号及课程所在的层次。
如果该课程没有先修课，则层次为1，如果有1层先修课，则层次为2，依次类推。
查询结果样式如图7-51所示。
图7-5128题的查询结果样式
四.实验总结
升级会员 