SQLServer课后实训参考答案.docx
《SQLServer课后实训参考答案.docx》由会员分享，可在线阅读，更多相关《SQLServer课后实训参考答案.docx（32页珍藏版）》请在冰豆网上搜索。
SQLServer课后实训参考答案
课后实训参考答案
单元1（SQLServer概述）
1、使用SQL语句。
在Sale数据库中创建名为MyDataType的用户定义数据类型，数据类型为NVARCHAR，长度为20，该列允许为NULL。
USESale
GO
EXECsp_addtypeMyDataType,'NVARCHAR（20）','NULL'
GO
单元2（单表数据）
使用查询窗口或sqlcmd实施查询。
本实训使用Xk数据库。
--1．查看系部编号为“03”的系部名称。
USEXk
GO
SELECTDepartName
FROMDepartment
WHEREDepartNo='03'
GO
--2．查看系部名称中包含有“工程”两个字的系的全名。
USEXk
GO
SELECTDepartName
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
StuNameLIKE'陈%'OR
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,'平均报名数'=CONVERT（DECIMAL（5,2）,AVG（WillNum））
FROMCourseC,DepartmentD
WHEREC.DepartNo=D.DepartNo
GROUPBYDepartName
GO
--2.统计各个系部的班级数，要求显示系部编号、系部名称和班级数量。
SELECTC.DepartNo,DepartName,COUNT（*）
FROMClassC,DepartmentD
WHEREC.DepartNo=D.DepartNo
GROUPBYC.DepartNo,DepartName
GO
--3．查看“甘蕾”同学选修的课程名、学分、上课时间、志愿号，按志愿号（升序）排序查询结果。
SELECTCouName,Credit,SchoolTime,WillOrder
FROMStuCouSC,CourseC,StudentS
WHERESC.CouNo=C.CouNoANDSC.StuNo=S.StuNoANDStuName='甘蕾'
ORDERBYWillOrder
GO
--4．查看“00电子商务”班的选修报名情况。
要求显示学号、姓名、课程编号、课程名称、志愿号，并按学号（升序）、志愿号排序（升序）。
SELECTS.StuNo,StuName,C.CouNo,CouName,WillOrder
FROMStuCouSC,CourseC,StudentS,ClassCL
WHERESC.CouNo=C.CouNoAND
SC.StuNo=S.StuNoAND
CL.ClassNo=S.ClassNoAND
ClassNameLike'00电子商务'
ORDERBYS.StuNo,WillOrder
GO
--5．按系部统计各系的最少报名人数、最多报名人数、平均报名人数和报名总数，并汇总显示所有系部的报名总数。
要求平均报名人数保留两位小数位。
SELECT'系部名称'=DepartName,'最少报名人数'=MIN（WillNum）,'最多报名人数'=MAX（WillNum）,'平均报名人数'=CONVERT（DECIMAL（5,2）,AVG（WillNum））,'报名总数'=SUM（WillNum）
FROMCourseC,DepartmentD
WHEREC.DepartNo=D.DepartNo
GROUPBYDepartNameWITHCUBE
GO
单元3（维护数据）
--12．学号为“”的同学第一志愿报名选修“001”课程，请在数据库中进行处理。
INSERTStuCou（StuNo,CouNo,WillOrder,State）
VALUES（'00000025','001',1,'报名'）
GO
/*课程报名人数增加人*/
UPDATECourse
SETWillNum=WillNum+1
WHERECouNo='001'
GO
--2．删除学号为“”的学生的选课报名信息。
DELETEStuCou
WHEREStuNo='00000025'
GO
/*课程表'00000025'同学报名的那些课程报名人数都要减少1*/
--方法：
UPDATECourse
SETWillNum=WillNum-1
FROMCourse,StuCou
WHEREStuNo='00000025'and
Course.CouNo=StuCou.CouNo
GO
--方法2：
（游标）
DECLARE@CouNochar（3）
DECLAREMYCursorcursor
for
SELECTCouNofromStuCou
WHEREStuNo='00000025'
OPENMYCursor
FETCHNEXTFROMMYCursorINTO@CouNo
WHILE@@FETCH_STATUS=0
BEGIN
UPDATECourseSETWillNum=WillNum-1WHERECouNo=@CouNo
FETCHNEXTFROMMYCursorINTO@CouNo
END
CLOSEMYCursor
DEALLOCATEMYCursor
GO
--3．需要将“00多媒体”班级“杜晓静”同学的名字修改为“杜小静”。
UPDATEStudent
SETStuName='杜小静'
FROMStudentS,ClassC
WHERES.ClassNo=C.ClassNoAND
StuName='杜晓静'ANDClassName='00多媒体'
GO
--“00电子商务”班的“林斌”同学申请将已选修的“网络信息检索原理与技术”课程修改为“Linux操作系统”。
UPDATEStuCou
SETCouNo=（SELECTCouNoFROMCourseWHERECouName='Linux操作系统'）
FROMStuCouSC,StudentS,CourseC,ClassCl
WHERESC.StuNo=S.StuNoAND
SC.CouNo=C.CouNoAND
S.ClassNo=Cl.ClassNoAND
S.StuName='林斌'AND
Cl.ClassName='00电子商务'AND
CouName='网络信息检索原理与技术'
GO
单元4（数据库设计）
某公司计划对产品的销售情况进行计算机管理。
产品表具有产品编号、产品名称、单价、库存数量4个属性。
客户表包括有客户编号、客户姓名、住址、联系电话4个属性。
产品的入库表包括有入库日期、产品编号、产品名称、入库数量、单价属性。
销售表包括有销售日期、产品编号、产品名称、客户编号、客户姓名、单价、销售数量。
1．绘出产品销售的E-R图。
2、将产品销售的E-R图转换为关系数据模型。
产品表：
产品编号
产品名称
单价
库存数量
客户表：
客户编号
客户名称
住址
联系电话
入库表：
入库日期
产品编号
产品名称
入库数量
单价
销售表：
销售日期
产品编号
产品名称
客户编号
客户姓名
单价
销售数量
3、将产品销售关系数据模型规范为Ⅲ范式。
可参考“创建和管理表”实训题。
4、请分析主键、外键、公共键。
主键：
产品表的产品编号，客户表的客户编号。
外键：
入库表的产品编号，销售表的产品编号、客户编号。
5、举例说明如何保证产品销售数据的完整性。
表数据完整性：
给产品表、客户表创建主键。
列数据的完整性：
可以给指定的列创建约束（参见实施数据完整性实训题目）。
参照完整性：
给入库表、销售表创建外键。
单元5（创建与管理数据库）
写出实现如下功能的SQL语句。
1．创建名字为Sale的销售数据库。
该数据库有一个名为Sale.mdf的主数据文件和名字为Sale_log.ldf的事务日志文件。
主数据文件容量为4MB，日志文件容量为10MB，数据文件和日志文件的最大容量为20MB，文件增长量为1MB。
CREATEDATABASESale
ON
PRIMARY（NAME=Sale,
='C:
\Sale.mdf',
SIZE=4,
MAXSIZE=10,
=1）
LOGON
（NAME=Sale_log,
='D:
\Sale_log.ldf',
SIZE=2,
MAXSIZE=10,
=1）/*注意结尾处无逗号*/
GO
2．在Sale数据库下新增名字为UserGroup的文件组。
USESale
Go
ALTERDATABASESale
ADDUserGroup
Go
3．以增加次数据文件的方式扩充Sale数据的容量。
次数据文件容量为5MB，最大容量为10MB，文件增量为1MB。
要求将次数据文件保存在与事务日志文件不同的存储设备上，次数据文件作为UserGroup文件组的成员。
USEMASTER
GO
ALTERDATABASESale
ADDFILE
（NAME=Sale2,
='C:
\Sale2.ndf',
SIZE=5MB,
MAXSIZE=10MB,
=1MB）
TOUserGroup
GO
4．使用存储过程显示Sale数据库的信息。
SP_helpdbSale
GO
单元6（创建与管理数据表）
1．实训项目都是围绕Sale数据库展开，进销存系统通常包括客户资料、产品信息、进货记录、销售记录等。
所以针对Sale数据库，设计表了表1-4，数据请见表5-8，并将在后续章节逐步完善。
使用Transcact-SQL语句，在Sale数据库下创建客户表、产品表、入库表和销售表并输入数据。
请将脚本保存为.sql文件。
表1Customer（客户表）结构
列名
数据类型及长度
是否允许为空
备注
CusNo
nvarchar（3）
NOTNULL
客户编号
CusName
nvarchar（10）
NOTNULL
客户姓名
Address
nvarchar（20）
NULL
地址
Tel
nvarchar（20）
NULL
联系电话
表2Product（产品表）结构
列名
数据类型及长度
是否允许为空
备注
ProNo
nvarchar（5）
NOTNULL
产品编号
ProName
nvarchar（20）
NOTNULL
产品名
Price
Decimal（8,2）
NOTNULL
单价
Stocks
Decimal（8,0）
NOTNULL
库存数量
表3ProIn（入库表）结构
列名
数据类型及长度
是否允许为空
备注
InputDate
DateTime
NOTNULL
入库日期
ProNo
nvarchar（5）
NOTNULL
产品编号
Quantity
Decimal（6,0）
NOTNULL
入库数量
表4ProOut（销售表）结构
列名
数据类型及长度
是否允许为空
备注
SaleDate
DateTime
NOTNULL
销售日期
CusNo
nvarchar（3）
NOTNULL
客户编号
ProNo
nvarcharr（5）
NOTNULL
产品编号
Quantity
Decimal（6,0）
NOTNULL
销售数量
表5Customer（客户表）数据
CusNo
CusName
Address
Tel
001
杨婷
深圳
1
002
陈萍
深圳
3
003
李东
深圳
5
004
叶合
广州
005
谭新
广州
表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（入库日期）
DateTime
notnull
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）notnull,
Addressnvarchar（20）,
TelChar（20））
Go
/*创建产品表*/
CREATETABLEProduct
（ProNonvarchar（5）Primarykey,
ProNamenvarchar（20）notnull,
PriceDecimal（8,2）notnull,
StocksDecimal（8,0）notnull）
Go
/*创建入库表*/
CREATETABLEProIn
（InputDateDateTimenotnull,
ProNonvarchar（5）ReferencesProduct（ProNo）,
QuantityDecimal（6,0）notnull）
Go
/*创建销售表*/
CREATETABLEProOut
（SaleDateDateTimenotnull,
CusNonvarchar（3）notnullReferencesCustomer（CusNo）,
ProNonvarchar（5）notnullReferencesProduct（ProNo）,
QuantityDecimal（6,0）notnull）
Go
--向Customer表输入数据--
INSERTINTO
CustomerVALUES（'001','杨婷','深圳','1'）
INSERTINTO
CustomerVALUES（'002','陈萍','深圳','3'）
INSERTINTO
CustomerVALUES（'003','李东','深圳','5'）
INSERTINTO
CustomerVALUES（'004','叶合','广州',''）
INSERTINTO
CustomerVALUES（'005','谭新','广州',''）
Go
--向Product表中插入数据--
INSERTINTO
ProductVALUES（'00001','电视',3000.00,800）
INSERTINTO
ProductVALUES（'00002','空调',2000.00,500）
INSERTINTO
ProductVALUES（'00003','床',1000.00,300）
INSERTINTO
ProductVALUES（'00004','餐桌',1500.00,200）
INSERTINTO
ProductVALUES（'00005','音响',5000.00,600）
INSERTINTO
ProductVALUES（'00006','沙发',6000.00,100）
Go
--向ProIn表中插入数据--
INSERTINTO
ProInVALUES（'2006-1-1','00001',10）
INSERTINTO
ProInVALUES（'2006-1-1','00002',5）
INSERTINTO
ProInVALUES（'2006-1-2','00001',5）
INSERTINTO
ProInVALUES（'2006-1-2','00003',10）
INSERTINTO
ProInVALUES（'2006-1-3','00001',10）
INSERTINTO
ProInVALUES（'2006-2-1','00003',20）
INSERTINTO
ProInVALUES（'2006-2-2','00001',10）
INSERTINTO
ProInVALUES（'2006-2-3','00004',30）
INSERTINTO
ProInVALUES（'2006-3-3','00003',20）
Go
--向ProOut表中插入数据--
INSERTINTO
ProOutVALUES（'2006-1-1','001','00001',10）
INSERTINTO
ProOutVALUES（'2006-1-2','001','00002',5）
INSERTINTO
ProOutVALUES（'2006-1-3','002','00001',5）
INSERTINTO
ProOutVALUES（'2006-2-1','002','00003',10）
INSERTINTO
ProOutVALUES（'2006-2-2','001','00001',10）
INSERTINTO
ProOutVALUES（'2006-2-3','001','00003',20）
INSERTINTO
ProOutVALUES（'2006-3-2','003','00001',10）
INSERTINTO
ProOutVALUES（'2006-3-2','003','00004',30）
INSERTINTO
ProOutVALUES（'2006-3-3','002','00003',20）
Go
单元7（实施数据完整性）
1．根据你的理解，请在实训5_2的表1-4的备注栏中标出主键、外键。
表1Customer（客户表）结构
列名
数据类型及长度
是否允许为空
备注
CusNo
nvarchar（3）
NOTNULL
客户编号主键
CusName
nvarchar（10）
NOTNULL
客户姓名
Address
nvarchar（20）
NULL
地址
Tel
nvarchar（20）
NULL
联系电话
表2Product（产品表）结构
列名
数据类型及长度
是否允许为空
备注
ProNo
nvarchar（5）
NOTNULL
产品编号主键
ProName
nvarchar（20）
NOTNULL
产品名
Price
Decimal（8,2）
NOTNULL
单价
Stocks
Decimal（8,0）
NOTNULL
库存数量
表3ProIn（入库表）结构
列名
数据类型及长度
是否允许为空
备注
InputDate
DateTime
NOTNULL
入库日期
升级会员 