SQLServer课后实训参考答案Word文档格式.docx
《SQLServer课后实训参考答案Word文档格式.docx》由会员分享，可在线阅读，更多相关《SQLServer课后实训参考答案Word文档格式.docx（38页珍藏版）》请在冰点文库上搜索。
USEXK
SELECT'
课程名称'
=CouName,'
任课教师'
=Teacher
FROMCourse
WHERESchoolTime='
周二晚'
--6．查看姓“张”、“陈”、“黄”同学的基本信息，要求按照姓名降序排序查询结果。
SELECT*
FROMStudent
WHEREStuNameLIKE'
张%'
OR
StuNameLIKE'
陈%'
黄%'
ORDERBYStuNameDESC
--方法
WHEREStuNamelike'
[张,陈,黄]%'
单元2（多表查询）
--1.按系部统计课程的平均报名人数，要求显示系部名称、平均报名人数。
系部名称'
=DepartName,'
平均报名人数'
=AVG（WillNum）
FROMCourseC,DepartmentD
WHEREC.DepartNo=D.DepartNo
GROUPBYDepartName
--如果小数点后只保留位
平均报名数'
=CONVERT（DECIMAL（5,2）,AVG（WillNum））
--2.统计各个系部的班级数，要求显示系部编号、系部名称和班级数量。
SELECTC.DepartNo,DepartName,COUNT（*）
FROMClassC,DepartmentD
GROUPBYC.DepartNo,DepartName
--3．查看“甘蕾”同学选修的课程名、学分、上课时间、志愿号，按志愿号（升序）排序查询结果。
SELECTCouName,Credit,SchoolTime,WillOrder
FROMStuCouSC,CourseC,StudentS
WHERESC.CouNo=C.CouNoANDSC.StuNo=S.StuNoANDStuName='
甘蕾'
ORDERBYWillOrder
--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
--5．按系部统计各系的最少报名人数、最多报名人数、平均报名人数和报名总数，并汇总显示所有系部的报名总数。
要求平均报名人数保留两位小数位。
最少报名人数'
=MIN（WillNum）,'
最多报名人数'
=MAX（WillNum）,'
=CONVERT（DECIMAL（5,2）,AVG（WillNum））,'
报名总数'
=SUM（WillNum）
GROUPBYDepartNameWITHCUBE
单元3（维护数据）
--12．学号为“”的同学第一志愿报名选修“001”课程，请在数据库中进行处理。
INSERTStuCou（StuNo,CouNo,WillOrder,State）
VALUES（'
00000025'
001'
1,'
报名'
）
/*课程报名人数增加人*/
UPDATECourse
SETWillNum=WillNum+1
WHERECouNo='
--2．删除学号为“”的学生的选课报名信息。
DELETEStuCou
WHEREStuNo='
/*课程表'
同学报名的那些课程报名人数都要减少1*/
--方法：
SETWillNum=WillNum-1
FROMCourse,StuCou
and
Course.CouNo=StuCou.CouNo
--方法2：
（游标）
DECLARE@CouNochar（3）
DECLAREMYCursorcursor
for
SELECTCouNofromStuCou
WHEREStuNo='
OPENMYCursor
FETCHNEXTFROMMYCursorINTO@CouNo
WHILE@@FETCH_STATUS=0
BEGIN
UPDATECourseSETWillNum=WillNum-1WHERECouNo=@CouNo
FETCHNEXTFROMMYCursorINTO@CouNo
END
CLOSEMYCursor
DEALLOCATEMYCursor
--3．需要将“00多媒体”班级“杜晓静”同学的名字修改为“杜小静”。
UPDATEStudent
SETStuName='
杜小静'
FROMStudentS,ClassC
WHERES.ClassNo=C.ClassNoAND
StuName='
杜晓静'
ANDClassName='
00多媒体'
--“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='
CouName='
网络信息检索原理与技术'
单元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）/*注意结尾处无逗号*/
2．在Sale数据库下新增名字为UserGroup的文件组。
Go
ALTERDATABASESale
ADDUserGroup
3．以增加次数据文件的方式扩充Sale数据的容量。
次数据文件容量为5MB，最大容量为10MB，文件增量为1MB。
要求将次数据文件保存在与事务日志文件不同的存储设备上，次数据文件作为UserGroup文件组的成员。
USEMASTER
ADDFILE
（NAME=Sale2,
\Sale2.ndf'
SIZE=5MB,
MAXSIZE=10MB,
=1MB）
TOUserGroup
4．使用存储过程显示Sale数据库的信息。
SP_helpdbSale
单元6（创建与管理数据表）
1．实训项目都是围绕Sale数据库展开，进销存系统通常包括客户资料、产品信息、进货记录、销售记录等。
所以针对Sale数据库，设计表了表1-4，数据请见表5-8，并将在后续章节逐步完善。
使用Transcact-SQL语句，在Sale数据库下创建客户表、产品表、入库表和销售表并输入数据。
请将脚本保存为.sql文件。
表1Customer（客户表）结构
列名
数据类型及长度
是否允许为空
备注
CusNo
nvarchar（3）
NOTNULL
CusName
nvarchar（10）
Address
nvarchar（20）
NULL
地址
Tel
表2Product（产品表）结构
ProNo
nvarchar（5）
ProName
产品名
Price
Decimal（8,2）
Stocks
Decimal（8,0）
表3ProIn（入库表）结构
InputDate
DateTime
Quantity
Decimal（6,0）
表4ProOut（销售表）结构
SaleDate
nvarcharr（5）
表5Customer（客户表）数据
001
杨婷
深圳
1
002
陈萍
3
003
李东
5
004
叶合
广州
005
谭新
表6Product（产品表）数据
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（入库日期）
notnull
ProNo（产品编号）
Quantity（入库数量）
2006-1-1
10
2006-1-2
2006-1-3
2006-2-1
20
2006-2-2
2006-2-3
30
2006-3-3
表8ProOut（销售表）数据
Quantity
2006-3-2
--该题创建表时同时创建主键、外键。
先创建表，再创建主键、外键的方法参见照实训5_3
UseSale
CREATETABLECustomer
（CusNonvarchar（3）Primarykey,
CusNamenvarchar（10）notnull,
Addressnvarchar（20）,
TelChar（20））
/*创建产品表*/
CREATETABLEProduct
（ProNonvarchar（5）Primarykey,
ProNamenvarchar（20）notnull,
PriceDecimal（8,2）notnull,
StocksDecimal（8,0）notnull）
/*创建入库表*/
CREATETABLEProIn
（InputDateDateTimenotnull,
ProNonvarchar（5）ReferencesProduct（ProNo）,
QuantityDecimal（6,0）notnull）
/*创建销售表*/
CREATETABLEProOut
（SaleDateDateTimenotnull,
CusNonvarchar（3）notnullReferencesCustomer（CusNo）,
ProNonvarchar（5）notnullReferencesProduct（ProNo）,
--向Customer表输入数据--
INSERTINTO
CustomerVALUES（'
杨婷'
深圳'
1'
002'
陈萍'
3'
003'
李东'
5'
004'
叶合'
广州'
'
005'
谭新'
--向Product表中插入数据--
ProductVALUES（'
00001'
电视'
3000.00,800）
00002'
空调'
2000.00,500）
00003'
床'
1000.00,300）
00004'
餐桌'
1500.00,200）
00005'
音响'
5000.00,600）
00006'
沙发'
6000.00,100）
--向ProIn表中插入数据--
ProInVALUES（'
2006-1-1'
10）
5）
2006-1-2'
2006-1-3'
2006-2-1'
20）
2006-2-2'
2006-2-3'
30）
2006-3-3'
--向ProOut表中插入数据--
INSERTINTO
ProOutVALUES（'
2006-3-2'
单元7（实施数据完整性）
1．根据你的理解，请在实训5_2的表1-4的备注栏中标出主键、外键。
客户编号主键
产品编号主键
ProN
升级会员 