Get清风数据库第二部分.docx

Get清风数据库第二部分.docx

《Get清风数据库第二部分.docx》由会员分享，可在线阅读，更多相关《Get清风数据库第二部分.docx（38页珍藏版）》请在冰豆网上搜索。

Get清风数据库第二部分

数据库第二部分

实验报告

课程名称数据库技术实践

实验项目SQL语言基础、架构与基本表、高级查询

实验仪器SQLServer2008

系别____计算机科学与技术系_

专业____计算机科学与技术____

班级/学号__xxxxxxxxxxxxxxxxxxx

学生姓名____xxxxxxxxxxxxxxxxxxx

实验日期

成绩_______________________

指导教师___梁琦______________

一．内容说明

本次实验的内容有学习了SQL语言的基础，架构与基本表的修改，还有高级查询，涉及到相关子查询、其他形式子查询、查询结果的并，交，差运算等，其中还涉及到一些查询功能，例如：

开窗函数，公式表达等。

二.SQL语言基础

1．在students数据库中创建一个用户定义的数据类型：

类型名为：

my_type，对应的基本数据类型为：

char（10），允许空。

2．声明一个字符串型的局部变量，并对其赋初值：

‘MyFirstVar’,然后在屏幕上显示此值。

declare@straschar（100）='MyFirstVar'

print@str

3．编写实现如下功能的脚本，并将编写好的脚本保存到磁盘文件中。

（1）声明两个整型的局部变量：

@i1和@i2，@i1的初值为10,@i2的值为：

@i1乘以5，最后在屏幕上显示@i2的值。

declare@i1asint=10;

declare@i2asint=@i1*5;

print@i2

（2）用While语句实现计算5000减1、减2、减3…，一直减到50的结果，并显示最终结果。

declare@i1asint=1;

declare@sumasint=5000;

while@i1<=50

begin

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='123456',

DEFAULT_DATABASE=students

go

USEStudents

go

CREATEUSERUser1

go

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

课程号

普通编码定长字符串，长度为10

主键

Cname

课程名

普通编码不定长字符串，长度为20

非空

Credit

学分

微整型

大于0

Semester

开课学期

微整型

SC

列名

说明

数据类型

约束

Sno

学号

普通编码定长字符串，长度为7

主键，引用Student的外键

Cno

课程号

普通编码定长字符串，长度为10

主键，引用Course的外键

Grade

成绩

小整型

取值范围：

0～100

3．在Students数据库中，用T-SQL语句创建满足要求的表：

销售表

列名

数据类型

约束

商品号

普通编码定长字符型，长度为10

非空

销售时间

小日期时间型

非空

销售价格

整型

非空

销售数量

小整型

非空

销售总价

整型

等于本次销售价格*销售数量

其中（商品号，销售时间）为主键

订购表

列名

数据类型

约束

货单号

整型

标识列，初值为1，自动增长，每次增加1，主键

订购时间

小日期时间型

非空

顾客号

普通编码定长字符型，长度为10

订购明细表

列名

数据类型

约束

货单号

整型

外键，引用订购表的“货单号”

商品号

普通编码定长字符型，长度为10

非空

订购数量

整型

订购价格

整型

其中（货单号，商品号）为主键。

销售表：

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,

[商品号][char]（10）NOTNULL,

[订购数量][int]NULL,

[订购价格][int]NULL,

CONSTRAINT[PK_订购明细]PRIMARYKEYCLUSTERED

（

[货单号]ASC,

[商品号]ASC

））ON[PRIMARY]

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'较好'

when（（sc.Grade>=70）and（sc.Grade<79））then'一般'

when（（sc.Grade>=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'一般'

when（（count（sco）>=1）and（count（sco）<2））then'偏少'

else'未选课'

end

as选课情况

fromdbo.sc

groupbysc.sno

3.统计每个系JAVA课程的考试情况，列出系名和考试情况，其中考试情况为：

如果JAVA平均成绩大于等于90，则考试情况为“好”；

如果JAVA平均成绩在80～89，则考试情况为“良好”；

如果JAVA平均成绩在70～79，则考试情况为“一般”；

如果JAVA平均成绩低于70，则考试情况为“较差”。

select

student.Deptas系名,

case

whenavg（sc.Grade）>=90then'好'

when（（avg（sc.Grade）>=80）and（avg（sc.Grade）<89））then'较好'

when（（avg（sc.Grade）>=70）and（avg（sc.Grade）<79））then'一般'

else'差'

endas'成绩情况'

fromdbo.sc,dbo.student,dbo.course

where

sc.Sno=student.Snoand

sco=courseoand

courseame='VB'

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

when（（course.Semester>=3）and（course.Semester<=4））thencourse.Credit+3

when（（course.Semester>=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．查询计算机系年龄小于信息管理系全体学生年龄的学生的姓名和年龄。

select

t1.Snameas姓名,

DATEPART（yy,getdate（）-t1.Birthday）-1900as年龄

fromdbo.studentast1

where

t1.Birthday>

（

select

max（student.Birthday）

fromdbo.student

where

student.Dept='信息管理系'

）and

t1.Dept='计算机系'

8．查询计算机系年龄大于信息管理系某个学生年龄的学生的姓名和年龄。

select

t1.Snameas姓名,

DATEPART（yy,getdate（）-t1.Birthday）-1900as年龄

fromdbo.studentast1

where

t1.Birthday<

（

select

max（student.Birthday）

fromdbo.student

where

student.Dept='信息管理系'

）and

t1.Dept='计算机系'

9．查询哪些课程没有学生选，列出课程号和课程名。

（用EXISTS子查询实现）

select

courseoas课程号,

courseameas课程名

fromdbo.course

where

notexists（

select*

fromdbo.sc

wheresco=courseo

）

10.查询计算机系哪些学生没有选课，列出学生姓名。

（用EXISTS子查询实现）

select

student.Sname

fromdbo.student

where

notexists（

select*

fromdbo.sc

wheresc.Sno=student.Sno

）and

student.Dept='计算机系'

11．查询没有选修第2学期开设的全部课程的学生的学号、其所选的课程号和该课程的开课学期。

select

t1.Snoas学号,

t2oas其所选的课程号,

t3.Semesteras该课程的开课学期

from

dbo.studentast1,

dbo.scast2,

dbo.courseast3

where

notexists（

select*

fromdbo.sc,dbo.course

where

sco=courseoand

course.Semester=2and

sco=t2o

）and

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

whereSCo=coandSC.Sno=s.sno））

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

whereSCo=coandSC.Sno=s.sno））

16．查询在第4学期开设课程中与第1学期开设的课程学分相同的课程，列出课程名和学分。

selectcname,creditfromCourse

whereSemester=4andCreditin（

selectcreditfromCourse

whereSemester=1）

17．查询“李勇”和“王大力”所选的相同课程，列出课程名、开课学期和学分。

selectcname,semester,creditfromCoursejoinSConCourseo=SCojoinStudentonStudent.Sno=SC.Sno

wheresname='李勇'

intersect

selectcname,semester,creditfromCoursejoinSConCourseo=SCojoinStudentonStudent.Sno=SC.Sno

wheresname='王大力'

18．查询“李勇”选了但“王大力”没有选的课程，列出课程名、开课学期和学分。

selectCname,semester,creditfromCoursejoinSConCourseo=SCojoinStudentonStudent.Sno=SC.Sno

wheresname='李勇'

except

selectCname,semester,creditfromCoursejoinSConCourseo=SCojoinStudentonStudent.Sno=SC.Sno

wheresname='王大力'

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）该系的学