Get清风数据库第二部分Word文档格式.docx
《Get清风数据库第二部分Word文档格式.docx》由会员分享，可在线阅读，更多相关《Get清风数据库第二部分Word文档格式.docx（38页珍藏版）》请在冰豆网上搜索。
set@sum=@sum-@i1;
set@i1=@i1+1;
end
print@sum;
三．架构与基本表
1．在第3章建立的Students数据库中，创建满足如下要求的架构。
准备工作：
首先在SSMS中，以系统管理员身份执行下列脚本，创建登录账户User1和User2，并让这两个登录账户成为Students数据库中的合法用户。
CREATELOGINUser1WITHPASSWORD='
123456'
DEFAULT_DATABASE=Students
go
CREATELOGINUser2WITHPASSWORD='
DEFAULT_DATABASE=students
USEStudents
CREATEUSERUser1
CREATEUSERUser2
（1）为用户User1定义一个架构，架构名为Base。
（2）为用户User2定义一个架构，架构名为Inform，并在该架构中定义一个关系表Teacher，结构为：
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，主键
订购时间
顾客号
订购明细表
外键，引用订购表的“货单号”
订购数量
订购价格
其中（货单号，商品号）为主键。
销售表：
CREATETABLE[dbo].[销售]（
[商品号][char]（10）NOTNULL,
[销售时间][smalldatetime]NOTNULL,
[销售价格][int]NOTNULL,
[销售数量][smallint]NOTNULL,
[销售总价][int]NOTNULL,
CONSTRAINT[PK_销售]PRIMARYKEYCLUSTERED
（
[商品号]ASC,
[销售时间]ASC
））ON[PRIMARY]
ALTERTABLE[dbo].[销售]WITHCHECKADDCONSTRAINT[CK_销售]CHECK（（[销售总价]=[销售价格]*[销售数量]））
GO
订购表：
CREATETABLE[dbo].[订购]（
[货单号][int]IDENTITY（1,1）NOTNULL,
[订购时间][smalldatetime]NOTNULL,
[顾客号][char]（10）NULL,
CONSTRAINT[PK_订购]PRIMARYKEYCLUSTERED
[货单号]ASC
）
）ON[PRIMARY]
订购明细表：
CREATETABLE[dbo].[订购明细]（
[货单号][int]NOTNULL,
[订购数量][int]NULL,
[订购价格][int]NULL,
CONSTRAINT[PK_订购明细]PRIMARYKEYCLUSTERED
[货单号]ASC,
[商品号]ASC
ALTERTABLE[dbo].[订购明细]WITHCHECKADDCONSTRAINT[FK_订购明细_订购]FOREIGNKEY（[货单号]）
REFERENCES[dbo].[订购]（[货单号]）
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
（3）
5．在Sudents数据库中增加两个新的文件组：
MyGroup1和MyGroup2，然后利用第4题
（1）建立的分区函数，建立分区方案，使得每个分区分别存放在PRIMARY、MyGroup1和MyGroup2文件组中
createpartitionschemefa1
aspartition右侧分区
to（Group1,Group2,Group3）
6．创建使用第4题
（2）创建的分区方案的表：
Sales_2011，结构为：
GID：
普通编码定长字符型，长度为10，主键；
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，则成绩情况为“差”。
select
sc.snoas学号,
student.Snameas姓名,
sc.Gradeas成绩,
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'
成绩情况'
fromdbo.sc,dbo.student,dbo.course
where
sc.Sno=student.Snoand
sco=courseoand
courseame='
VB'
2.统计每个学生的选课门数（包括没有选课的学生），列出学号、选课门数和选课情况，其中选课情况显示规则为：
如果选课门数大于等于6门，则选课情况为“多”；
如果选课门数超过在3～5门，则选课情况为“一般”；
如果选课门数在1~2门，则选课情况为“偏少”。
如果没有选课，则选课情况为“未选课”。
select
sc.Snoas学号,
count（sco）选课门数,
case
when（count（sco）>
=6）then'
多'
when（（count（sco）>
=3）and（count（sco）<
5））then'
=1）and（count（sco）<
2））then'
偏少'
未选课'
as选课情况
fromdbo.sc
groupbysc.sno
3.统计每个系JAVA课程的考试情况，列出系名和考试情况，其中考试情况为：
如果JAVA平均成绩大于等于90，则考试情况为“好”；
如果JAVA平均成绩在80～89，则考试情况为“良好”；
如果JAVA平均成绩在70～79，则考试情况为“一般”；
如果JAVA平均成绩低于70，则考试情况为“较差”。
student.Deptas系名,
whenavg（sc.Grade）>
when（（avg（sc.Grade）>
=80）and（avg（sc.Grade）<
=70）and（avg（sc.Grade）<
groupbystudent.Dept
4.修改全部课程的学分，修改规则如下：
如果是第1～2学期开设的课程，则学分增加5分；
如果是第3～4学期开设的课程，则学分增加3分；
如果是第5～6学期开设的课程，则学分增加1分；
对其他学期开设的课程，学分不变。
updatedbo.course
set
course.Credit=
case
when（（course.Semester>
=1）and（course.Semester<
=2））thencourse.Credit+5
=3）and（course.Semester<
=4））thencourse.Credit+3
=5）and（course.Semester<
=6））thencourse.Credit+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）asAVGGradfromstudentjoinscc2onc2.Sno=student.Sno
where（selectAVG（Grade）fromscjoinstudentconc.Sno=sc.Sno
wheresc.Sno=student.Sno）>
=80
orderbyAVGGraddesc
7．查询计算机系年龄小于信息管理系全体学生年龄的学生的姓名和年龄。
t1.Snameas姓名,
DATEPART（yy,getdate（）-t1.Birthday）-1900as年龄
fromdbo.studentast1
where
t1.Birthday>
max（student.Birthday）
fromdbo.student
student.Dept='
信息管理系'
）and
t1.Dept='
计算机系'
8．查询计算机系年龄大于信息管理系某个学生年龄的学生的姓名和年龄。
t1.Birthday<
9．查询哪些课程没有学生选，列出课程号和课程名。
（用EXISTS子查询实现）
courseoas课程号,
courseameas课程名
fromdbo.course
notexists（
select*
wheresco=courseo
）
10.查询计算机系哪些学生没有选课，列出学生姓名。
student.Sname
wheresc.Sno=student.Sno
student.Dept='
11．查询没有选修第2学期开设的全部课程的学生的学号、其所选的课程号和该课程的开课学期。
t1.Snoas学号,
t2oas其所选的课程号,
t3.Semesteras该课程的开课学期
from
dbo.studentast1,
dbo.scast2,
dbo.courseast3
fromdbo.sc,dbo.course
where
sco=courseoand
course.Semester=2and
sco=t2o
t1.Sno=t2.Snoand
t2o=t3o
12．查询至少选了第4学期开设的全部课程的学生的学号和所在系。
selectsno,deptfromstudents
wherenotexists（select*fromCoursec
wheresemester=4andnotexists（select*fromSC
whereSCo=coandSC.Sno=s.sno））
13．查询至少选了“0831102”号学生所选的全部课程的学生的学号。
selectsnofromstudents
wherenotexists（select*fromSCc
wherec.Sno='
0831102'
andnotexists（select*fromSC
14．查询至少选了“张海”所选的全部课程的学生的学号、所在系和所选的课程号。
selects.Sno,dept,cnofromStudentsjoinSCon
s.Sno=SC.Sno
wherenotexists（
select*fromSCjoinStudents
ons.Sno=SC.Sno
whereSname='
张海'
andnotexists（
select*fromSC
joinStudentsonSC.sno=s.sno
15．查询至少选了全部学分大于3分的课程的学生的学号、所在系和所选的课程号、课程名以及学分。
selectsc.Sno,Dept,sco,cname,creditfromstudentsjoinSCons.Sno=SC.SnojoinCourseconco=SC.Sno
wherenotexists（select*fromCoursec
wherec.Credit>
3andnotexists（select*fromSC
16．查询在第4学期开设课程中与第1学期开设的课程学分相同的课程，列出课程名和学分。
selectcname,creditfromCourse
whereSemester=4andCreditin（
selectcreditfromCourse
whereSemester=1）
17．查询“李勇”和“王大力”所选的相同课程，列出课程名、开课学期和学分。
selectcname,semester,creditfromCoursejoinSConCourseo=SCojoinStudentonStudent.Sno=SC.Sno
wheresname='
李勇'
intersect
王大力'
18．查询“李勇”选了但“王大力”没有选的课程，列出课程名、开课学期和学分。
selectCname,semester,creditfromCoursejoinSConCourseo=SCojoinStudentonStudent.Sno=SC.Sno
except
19．查询至少同时选了“C001”和“C002”两门课程的学生的学号和所选的课程号。
selectSC.Sno,SCofromCoursejoinSConCourseo=SCojoinStudentonStudent.Sno=SC.Sno
whereSC.Snoin（
selectt1.snofrom（select*fromSCwhereCno='
C001'
）ast1
join（select*fromSCwhereCno='
C002'
）ast2
ont1.Sno=t2.Sno）
20．查询学生学号、姓名、所在系及该系的学生人数。
selectsno,sname,dept,COUNT（*）over（partitionbydept）该系的学
升级会员 