3720500SQLServer课后实训参考答案.docx

3720500SQLServer课后实训参考答案.docx

《3720500SQLServer课后实训参考答案.docx》由会员分享，可在线阅读，更多相关《3720500SQLServer课后实训参考答案.docx（38页珍藏版）》请在冰点文库上搜索。

3720500SQLServer课后实训参考答案

课后实训参考答案

单元1（SQLServer概述）

1、使用SQL语句。

在Sale数据库中创建名为MyDataType的用户定义数据类型，数据类型为NVARCHAR,长度为20,该列允许为NULLo

USESale

GO

EXECsp_addtypeMyDataType,fNVARCHAR（20）'NULL'

GO

单元2（单表数据）

使用查询窗口或sqlcmd实施查询。

木实训使用Xk数据库。

-1.查看系部编号为“03”的系部名称。

USEXk

GO

SELECTDepartName

FROMDepartment

WHEREDepartNo=,03,

GO

-2.查看系部名称中包含有“工程”两个字的系的全名。

USEXk

GO

SELECTDepartN&me

FROMDepartment

WHEREDepartNameLIKE‘％工程％’

GO

一3・显示共有多少个系部。

USEXK

GO

SELECT'系部总数'=COUNT（*）

FROMDepartment

GO

-4・显示“01”年级共有多少个班级。

USEXK

GO

SELECT'01级班级数'=COUNT严）

FROMClass

WHEREClassNoLIKE‘2001%’

GO

SELECT'01级班级数'=COUNT（★）

FROMClass

WHEREClassNameLIKE‘01%’

GO

SELECT*01级班级数'=COUNT严）

FROMStudent

WHEREClassNoLIKE‘2001%’

GO

-5.查看在“周二晚”上课的课程名称和教师。

USEXK

GO

SELECT，课程名称^CouName,，任课教师*=Teacher

FROMCourse

WHERESchoolTime'周二晚'

GO

-6.查看姓“”、“”、“黄”同学的基本信息，要求按照降序排序查询结果。

USEXK

GO

SELECT*

FROMStudent

WHEREStuNameLIKE‘％’OR

StuNameLIKE1%1OR

StuNameLIKE'黄％'

ORDERBYStuNameDESC

GO

--方法

USEXK

GO

SELECT*

FROMStudent

WHEREStuNamelike'[…黄]%

ORDERBYStuNameDESC

GO

单元2（多表查询）

-1.按系部统计课程的平均报名人数，要求显示系部名称、平均报名人数。

SELECT，系部名称!

=DepartName,「平均报名人数

*=AVG（WillNum）

FROMCourseC,DepartmentD

WHEREC•DepartNo=D•DepartNo

GROUPBYDepartName

GO

--如果小数点后只保留位

SELECT，系部名称^DepartName,「平均报名数

1=CONVERT（DECIMAL（5,2）,AVG（WillNum））

FROMCourseCzDepartmentD

WHEREC•DepartNo=D•DepartNo

GROUPBYDepartName

GO

-2.统计各个系部的班级数，要求显示系部编号、系部名称和班级数量。

FROMClassCzDepartmentD

WHEREC•DepartNo=D•DepartNo

GROUPBYC•DepartNo『DepartName

GO

-3.查看“甘蕾”同学选修的课程名、学分、上课时间、志愿号,按志愿号（升序）排序查询结果。

SELECTCouName,Credit,SchoolTime,WillOrder

FROMStuCouSC,CourseC,StudentS

WHERESC・CouNo=C.CouNoANDSC・StuNo=S・StuNoAND

StuName=,#ffi,

ORDERBYWillOrder

GO

-4.查看“00电子商务”班的选修报名情况。

要求显示学号、、课程编号、课程名称、志愿号，并按学号（升序）、志愿号排序（升序）。

SELECTS•StuNo,StuName,C•CouNo,CouNamezWillOrder

FROMStuCouSCZCourseC,StudentS,ClassCL

WHERESC・CouNo=C.CouNoAND

SC・StuNo=S・StuNoAND

CL.ClassNo=S.ClassNoAND

ClassNameLike100电子商务1

ORDERBYS・StuNo,WillOrder

GO

-5.按系部统计各系的最少报名人数、最多报名人数、平均报名人数和报名总数，并汇总显示所有系部的报名总数。

要求平均报名人数保留两位小数位。

SELECT，系部名称f=DepartName,，最少报名人数r=MIN（WillNum）,，最多报名人数1=MAX（WillNum）,1平均报名人数^CONVERT（DECIMAL（5,2）,AVG（WillNum））,，报名总数^SUMfWillNum）

FROMCourseC,DepartmentD

WHEREC•DepartNo=D•DepartNo

GROUPBYDepartNameWITHCUBE

GO

单元3（维护数据）

-12.学号为“00000025”的同学第一志愿报名选修“001”课程,请在数据库中进行处理。

INSERTStuCou（StuNo,CouNo,WillOrder,State）

VALUES（*00000025'z701—1,，报名*）

GO

/*课程报名人数增加人★/

UPDATECourse

SETWillNum=Wi1INum-1

WHERECouNo=*001'

GO

-2.删除学号为“00000025”的学生的选课报名信息。

DELETEStuCou

WHEREStuNo=*00000025,

GO

/*课程表'00000025-同学报名的那些课程报名人数都要减少1*/

--方法：

UPDATECourse

SETWi1lNum=Wi1INum-1

FROMCourse,StuCou

WHEREStuNo=*00000025'and

Course•CouNo=StuCou•CouNo

GO

-方法2：

（游标）

DECLARECouNochar（3）

DECLAREMYCursorcursor

for

SELECTCouNofromStuCou

WHEREStuNo=,00000025,

OPENMYCursor

FETCHNEXTFROMMYCursorINTOCouNo

WHILEFETCH_STATUS=O

BEGIN

WHERE

UPDATECourseSETWillNum=Wi1INum-1

CouNo=CouNo

FETCHNEXTFROMMYCursorINTOCouNo

END

CLOSEMYCursor

DEALLOCATEMYCursor

GO

-3・需要将“00多媒体”班级“杜晓静”同学的名字修改为“杜

小静”。

UPDATEStudent

SETStuN&me='杜小静'

FROMStudentS,ClassC

WHERES.ClassNo=C.ClcssNoAND

StuNdme=，杜晓静'ANDC]_&;ssN&me='00多媒体'

GO

-“00电子商务”班的“林斌”同学中请将己选修的“网络信息检索原理与技术”课程修改为“Linux操作系统”。

UPDATEStuCou

SETCouNo=（SELECTCouNoFROMCourseWHERE

CouName='Linux操作系统*）

FROMStuCouSC,StudentSzCourseC,ClassCl

WHERESC・StuNo=S.StuNoAND

SC.CouNo=C・CouNoAND

S・ClassNo=C1・ClassNoAND

S.StuN&mei林斌'AND

Cl_・Cl&ssN&me='00商务'AND

CouName=•网络信息检索原理与技术，

GO

单元4（数据库设计）

某公司计划对产品的销售情况进行计算机管理。

产品表具有产品编号、产品名称、单价、库存数量4个属性。

客户表包括有客户编号、客户、住址、联系4个属性。

产品的入库表包括有入库日期、产品编号、产品名称、入库数量、单价属性。

销售表包括有销售日期、产品编号、产品名称、客户编号、客户、单价、销售数量。

1.绘出产品销售的E-R图。

2、将产品销售的E-R图转换为关系数据模型。

产品表：

产品编号

产品名称

单价

库存数量

客户表:

客户编号

客户名称

住址

联系

入库表:

入库日期

产品编号

产品名称

入库数暈

单价

销售表:

销售

日期

产品

编号

产品

名称

客户

编号

客户

单价

销售

数量

3、将产品销售关系数据模型规为III式。

可参考“创建和管理表”实训题。

4、请分析主键、外键、公共键。

主键：

产品表的产品编号，客户表的客户编号。

外键：

入库表的产品编号，销售表的产品编号、客户编号。

5、举例说明如何保证产品销售数据的完整性。

表数据完整性：

给产品表、客户表创建主键。

列数据的完整性：

可以给指定的列创建约束（参见实施数据完整性

实训题目）。

参照完整性：

给入库表、销售表创建外键。

单元5（创建与管理数据库）

写出实现如下功能的SQL语句。

1.创建名字为Sale的销售数据库。

该数据库有一个名为Sale.mdf的主数据文件和名字为Salejog.ldf的事务H志文件。

主数据文件容量为4MB,日志文件容量为10MB,数据文件和H志文件的最大容量为20MB,文件增长量为1MB。

CREATEDATABASESale

ON

PRIMARY（N2V4E=Sale,

FILENAME=fC:

\Sale・mdf',

SIZE=4,

MAXSIZE=10z

FILEGROWTH=1）

LOGON

（NAME=Sale_logz

FILENAME=fD:

\Sale_log・ldf1,

SIZE=2,

MAXSIZE=10,

2.在Sale数据库下新增名字为UserGroup的文件组。

USESale

Go

ALTERDATABASESale

ADDFILEGROUPUserGroup

Go

3.以增加次数据文件的方式扩充Sale数据的容量。

次数据文件容量为5MB,最大容量为10MB,文件增量为1MB。

要求将次数据文件保存在与事务日志文件不同的存储设备上，次数据文件作为UserGroup文件组的成员。

USEMASTER

GO

ALTERDATABASESale

ADDFILE

（NAME=Sde2,

FILEN2kME=lC:

\Sale2・ndf1,

SIZE=5MBz

MAXSIZE=10MBz

FILEGROWTH=1MB）

TOFILEGROUPUserGroup

4.使用存储过程显示Sale数据库的信息。

SP_helpdbSale

GO

单元6（创建与管理数据表）

1.实训项目都是围绕Sale数据库展开，进销存系统通常包括客户资料、产品信息、进货记录、销售记录等。

所以针对Sale数据库，设计表了表1-4,数据请见表5-8,并将在后续章节逐步完善。

使用Transcact-SQL语句，在Sale数据库下创建客户表、产品表、入库表和销售表并输入数据。

请将脚木保存为.sql文件。

表1Customer（客户表）结构

列名

数据类型及长度

是否允许为空

备注

CusNo

nvarchar

（3）

NOT

NULL

客户编号

CusNam

nvarchar

NOT

客户

e

（10）

NULL

Address

nvarchar

（20）

NULL

地址

Tel

nvarchar

（20）

NULL

联系

表2Product（产品表）结构

列名

数据类型及长度

是否允许为空

备注

ProNo

nvarchar

（5）

NOT

NULL

产品编号

ProNam

e

nvarchar

（20）

NOT

NULL

产品名

Price

Decimal

（8,2）

NOT

NULL

单价

Stocks

Decimal

（8,0）

NOT

NULL

库存数量

表3ProIn（入库表）结构

列名

数据类型及长度

是否允许为空

备注

InputDat

e

DateTime

NOT

NULL

入库日期

ProNo

nvarchar

（5）

NOT

NULL

产品编号

Quantity

Decimal

（6,0）

NOT

NULL

入库数量

表4ProOut（销售表）结构

列名

数据类型及长度

是否允许为空

备注

SaleDate

DateTime

NOT

NULL

销售日期

CusNo

nvarchar

（3）

NOT

NULL

客户编号

ProNo

nvarchar

r（5）

NOT

NULL

产品编号

Quantity

Decimal

（6,0）

NOT

NULL

销售数暈

表5Customer（客户表）数据

CusNo

CusName

Address

Tel

001

婷

01

002

萍

03

003

东

05

004

叶合

7

005

谭新

9

表6Product（产品表）数据

ProNo

ProName

Price

Stocks

00001

电视

3000.00

800

00002

空调

2000.00

500

00003

床

1000.00

300

00004

餐桌

1500.00

200

00005

音响

5000.00

600

00006

沙发

6000.00

100

表7ProIn（入库表）数据

InputDate（入库日期）

DateTimenotnull

ProNo（产品编号）nvarchar（5）notnull

Quantity（入库数量）

Decimal（6,0）notnull

2006-1-1

00001

10

2006-1-1

00002

5

2006-1-2

00001

5

2006-1-2

00003

10

2006-1-3

00001

10

2006-2-1

00003

20

2006-2-2

00001

10

2006-2-3

00004

30

2006-3-3

00003

20

表8ProOut（销售表）数据

SaleDate

CusNo

ProNo

Quantity

2006-1-1

001

00001

10

2006-1-2

001

00002

5

2006-1-3

002

00001

5

2006-2-1

002

00003

10

2006-2-2

001

00001

10

2006-2-3

001

00003

20

2006-3-2

003

00001

10

2006-3-2

003

00004

30

2006-3-3

002

00003

20

--该题创建表时冋时创建主键.外键。

先创建表，再创建主键.

外键的方法参见照实训5_3

UseSale

Go

CREATETABLECustomer

（CusNonvarchar（3）Primarykey,

CusNamenvarchar（10）notnu丄丄孑

Addressnvarchar（20）『

TelChar（20））

Go

/★创建产品表★/

CREATETABLEProduct

（ProNonvarchar（5）Primarykey,

ProNamenvarchar（20）notnull

PriceDecimal（8A2）notnullA

StocksDecimal（8,0）notnull）

Go

/★创建入库表★/

CREATETABLEProIn

（InputDateD&teTimenotnull,

ProNonvarchar（5）ReferencesProduct（ProNo）,

QuantityDecimal（6,0）notnull）

Go

/★创建销售表★/

CREATETABLEProOut

（SaleDateD&teTimenotnullz

CusNonvarchar（3）notnullReferences

Customer（CusNo）,

ProNonvarchar（5）notnullReferences

Product（ProNo）,

QuantityDecimal（6,0）notnull）

Go

--向Customer表输入数据--

INSERTINTO

CustomerVALUES（1001\'婷；；0「）

INSERTINTO

CustomerVALUES（1002；萍；；03'）

INSERTINTO

CustomerVALUES（'003'J东；；05'）

INSERTINTO

INSERTINTO

CustomerVALUES（'0051,*谭新；；9'）

Go

--向Product表中插入数据--

INSERTINTO

ProductVALUES（!

00001*,'电视',3000・00,800）

INSERTINTO

ProductVALUES（100002*,'空调J2000・00,500）

INSERTINTO

ProductVALUES（'00003','丿耒‘,丄000.00,300）

INSERTINTO

ProductVALUES（*00004'z'餐桌',1500・00,200）

INSERTINTO

ProductVALUES「00005’，'音响'，5000.00,600）

INSERTINTO

ProductVALUES（‘00006’,'沙发',6000・00,100）

Go

--向ProIn表中插入数据--

INSERTINTO

INSERTINTO

ProInVALUES（12006-1-11,‘00002J5）

INSERTINTO

ProInVALUES（*2006-1-2*z‘00001J5）

INSERTINTO

ProInVALUES（*2006-1-2*z‘00003—10）

INSERTINTO

ProInVALUES（'2006-1-3J,00001f,10）

INSERTINTO

ProInVALUES（12006-2-11z‘00003—20）

INSERTINTO

ProInVALUES（*2006-2-2*z‘00001—10）

INSERTINTO

ProInVALUES「2006-2-3J‘00004—30）

INSERTINTO

ProInVALUES（‘2006-3-3J,00003\20）

Go

--向ProOut表中插入数据--

INSERTINTO

INSERTINTO

ProOut

VALUES（*2006-1-21,

f00002\5）

INSERT

INTO

ProOut

VALUES（'2006-1-3*,

002,,

^OOOOl*

5）

INSERT

INTO

ProOut

VALUES（'2006-2-1\

*002*z

!

00003*

10）

INSERT

INTO

ProOut

VALUES（,2006-2-2l,

^OOl1,

9000—

10）

INSERT

INTO

ProOut

VALUES（*2006-2-3*,

!

0011,

P0003,

20）

INSERT

INTO

ProOut

VALUES（12006-3-21,

P03J

9000U

zlO）

INSERT

INTO

ProOut

VALUES（'2006-3-21,

703J

!

00004*

30）

INSERT

INTO

ProOut

VALUES（'2006-3-31,

f002*z

‘00003’

z20）

Go

单元7（实施数据完整性）

1.根据你的理解，请在实训5_2的表1-4的备注栏中标出主键、

外键。

表1Customer（客户表）结构

列名

数据类型及长度

是否允许为空

备注

CusNo

nvarchar

（3）

NOT

NULL

客户编号主键

CusNam

nvarchar

NOT

客户

e

（10）

NULL

Address

nvarchar

（20）

NULL

地址

Tel

nvarchar

（20）

NULL

联系

表2Product（产品表）结构

列名

数据类型及长度

是否允许为空

备注

ProNo

nvarchar

（5）

NOT

NULL

产品编号主键

ProNam

nvarchar

NOT

产品名

e

（20）

NULL

Price

Decimal

（8,2）

NOT

NULL

单价

Stocks

Decimal

（8,0）

NOT

NULL

库