37205-00_SQL Server 2005课后实训参考答案解析

徐人凤共1页第1页5/23/202011课后实训参考答案单元1（SQLServer概述）1、使用SQL语句。在Sale数据库中创建名为MyDataType的用户定义数据类型，数据类型为NVARCHAR，长度为20，该列允许为NULL。USESaleGOEXECsp_addtypeMyDataType,'NVARCHAR(20)','NULL'GO单元2（单表数据）使用查询窗口或sqlcmd实施查询。本实训使用Xk数据库。--1．查看系部编号为“03”的系部名称。USEXkGOSELECTDepartNameFROMDepartmentWHEREDepartNo='03'GO--2．查看系部名称中包含有“工程”两个字的系的全名。USEXk徐人凤共2页第2页5/23/202022GOSELECTDepartNameFROMDepartmentWHEREDepartNameLIKE'%工程%'GO--3．显示共有多少个系部。USEXKGOSELECT'系部总数'=COUNT(*)FROMDepartmentGO--4．显示“01”年级共有多少个班级。USEXKGOSELECT'01级班级数'=COUNT(*)FROMClassWHEREClassNoLIKE'2001%'GOSELECT'01级班级数'=COUNT(*)FROMClass徐人凤共3页第3页5/23/202033WHEREClassNameLIKE'01%'GOSELECT'01级班级数'=COUNT(*)FROMStudentWHEREClassNoLIKE'2001%'GO--5．查看在“周二晚”上课的课程名称和教师。USEXKGOSELECT'课程名称'=CouName,'任课教师'=TeacherFROMCourseWHERESchoolTime='周二晚'GO--6．查看姓“张”、“陈”、“黄”同学的基本信息，要求按照姓名降序排序查询结果。USEXKGOSELECT*FROMStudentWHEREStuNameLIKE'张%'OR徐人凤共4页第4页5/23/202044StuNameLIKE'陈%'ORStuNameLIKE'黄%'ORDERBYStuNameDESCGO--方法USEXKGOSELECT*FROMStudentWHEREStuNamelike'[张,陈,黄]%'ORDERBYStuNameDESCGO徐人凤共5页第5页5/23/202055单元2（多表查询）--1.按系部统计课程的平均报名人数，要求显示系部名称、平均报名人数。SELECT'系部名称'=DepartName,'平均报名人数'=AVG(WillNum)FROMCourseC,DepartmentDWHEREC.DepartNo=D.DepartNoGROUPBYDepartNameGO--如果小数点后只保留位SELECT'系部名称'=DepartName,'平均报名数'=CONVERT(DECIMAL(5,2),AVG(WillNum))FROMCourseC,DepartmentDWHEREC.DepartNo=D.DepartNoGROUPBYDepartNameGO--2.统计各个系部的班级数，要求显示系部编号、系部名称和班级数量。SELECTC.DepartNo,DepartName,COUNT(*)徐人凤共6页第6页5/23/202066FROMClassC,DepartmentDWHEREC.DepartNo=D.DepartNoGROUPBYC.DepartNo,DepartNameGO--3．查看“甘蕾”同学选修的课程名、学分、上课时间、志愿号，按志愿号（升序）排序查询结果。SELECTCouName,Credit,SchoolTime,WillOrderFROMStuCouSC,CourseC,StudentSWHERESC.CouNo=C.CouNoANDSC.StuNo=S.StuNoANDStuName='甘蕾'ORDERBYWillOrderGO--4．查看“00电子商务”班的选修报名情况。要求显示学号、姓名、课程编号、课程名称、志愿号，并按学号（升序）、志愿号排序（升序）。SELECTS.StuNo,StuName,C.CouNo,CouName,WillOrderFROMStuCouSC,CourseC,StudentS,ClassCLWHERESC.CouNo=C.CouNoANDSC.StuNo=S.StuNoANDCL.ClassNo=S.ClassNoAND徐人凤共7页第7页5/23/202077ClassNameLike'00电子商务'ORDERBYS.StuNo,WillOrderGO--5．按系部统计各系的最少报名人数、最多报名人数、平均报名人数和报名总数，并汇总显示所有系部的报名总数。要求平均报名人数保留两位小数位。SELECT'系部名称'=DepartName,'最少报名人数'=MIN(WillNum),'最多报名人数'=MAX(WillNum),'平均报名人数'=CONVERT(DECIMAL(5,2),AVG(WillNum)),'报名总数'=SUM(WillNum)FROMCourseC,DepartmentDWHEREC.DepartNo=D.DepartNoGROUPBYDepartNameWITHCUBEGO徐人凤共8页第8页5/23/202088单元3（维护数据）--12．学号为“00000025”的同学第一志愿报名选修“001”课程，请在数据库中进行处理。INSERTStuCou(StuNo,CouNo,WillOrder,State)VALUES('00000025','001',1,'报名')GO/*课程报名人数增加人*/UPDATECourseSETWillNum=WillNum+1WHERECouNo='001'GO--2．删除学号为“00000025”的学生的选课报名信息。DELETEStuCouWHEREStuNo='00000025'GO/*课程表'00000025'同学报名的那些课程报名人数都要减少1*/--方法：UPDATECourseSETWillNum=WillNum-1徐人凤共9页第9页5/23/202099FROMCourse,StuCouWHEREStuNo='00000025'andCourse.CouNo=StuCou.CouNoGO--方法2：（游标）DECLARE@CouNochar(3)DECLAREMYCursorcursorforSELECTCouNofromStuCouWHEREStuNo='00000025'OPENMYCursorFETCHNEXTFROMMYCursorINTO@CouNoWHILE@@FETCH_STATUS=0BEGINUPDATECourseSETWillNum=WillNum-1WHERECouNo=@CouNoFETCHNEXTFROMMYCursorINTO@CouNoENDCLOSEMYCursorDEALLOCATEMYCursorGO徐人凤共10页第10页5/23/20201010--3．需要将“00多媒体”班级“杜晓静”同学的名字修改为“杜小静”。UPDATEStudentSETStuName='杜小静'FROMStudentS,ClassCWHERES.ClassNo=C.ClassNoANDStuName='杜晓静'ANDClassName='00多媒体'GO--“00电子商务”班的“林斌”同学申请将已选修的“网络信息检索原理与技术”课程修改为“Linux操作系统”。UPDATEStuCouSETCouNo=(SELECTCouNoFROMCourseWHERECouName='Linux操作系统')FROMStuCouSC,StudentS,CourseC,ClassClWHERESC.StuNo=S.StuNoANDSC.CouNo=C.CouNoANDS.ClassNo=Cl.ClassNoANDS.StuName='林斌'ANDCl.ClassName='00电子商务'ANDCouName='网络信息检索原理与技术'徐人凤共11页第11页5/23/20201111GO徐人凤共12页第12页5/23/20201212单元4（数据库设计）某公司计划对产品的销售情况进行计算机管理。产品表具有产品编号、产品名称、单价、库存数量4个属性。客户表包括有客户编号、客户姓名、住址、联系电话4个属性。产品的入库表包括有入库日期、产品编号、产品名称、入库数量、单价属性。销售表包括有销售日期、产品编号、产品名称、客户编号、客户姓名、单价、销售数量。1．绘出产品销售的E-R图。2、将产品销售的E-R图转换为关系数据模型。产品表：客户编号选课状态产品名称自愿号产品学生客户表课程销售学生-课程mm库存数量选课密码单价班级产品名称产品编号学号联系电话系部名称住址产品编号课程名称nn客户编号客户姓名销售日期课程名称客户姓名课程名称单价课程名称销售数量课程名称库房学生属性（略）选课密码入库学生-课程单价系部名称入库数量产品编号产品名称入库日期PmQm徐人凤共13页第13页5/23/20201313产品编号产品名称单价库存数量客户表：客户编号客户名称住址联系电话入库表：入库日期产品编号产品名称入库数量单价销售表：销售日期产品编号产品名称客户编号客户姓名单价销售数量3、将产品销售关系数据模型规范为Ⅲ范式。可参考“创建和管理表”实训题。徐人凤共14页第14页5/23/202014144、请分析主键、外键、公共键。主键：产品表的产品编号，客户表的客户编号。外键：入库表的产品编号，销售表的产品编号、客户编号。5、举例说明如何保证产品销售数据的完整性。表数据完整性：给产品表、客户表创建主键。列数据的完整性：可以给指定的列创建约束（参见实施数据完整性实训题目）。参照完整性：给入库表、销售表创建外键。徐人凤共15页第15页5/23/20201515单元5（创建与管理数据库）写出实现如下功能的SQL语句。1．创建名字为Sale的销售数据库。该数据库有一个名为Sale.mdf的主数据文件和名字为Sale_log.ldf的事务日志文件。主数据文件容量为4MB，日志文件容量为10MB，数据文件和日志文件的最大容量为20MB，文件增长量为1MB。CREATEDATABASESaleONPRIMARY(NAME=Sale,FILENAME='C:\Sale.mdf',SIZE=4,MAXSIZE=10,FILEGROWTH=1)LOGON(NAME=Sale_log,FILENAME='D:\Sale_log.ldf',SIZE=2,MAXSIZE=10,FILEGROWTH=1)/*注意结尾处无逗号*/GO徐人凤共16页第16页5/23/202016162．在Sale数据库下新增名字为UserGroup的文件组。USESaleGoALTERDATABASESaleADDFILEGROUPUserGroupGo3．以增加次数据文件的方式扩充Sale数据的容量。次数据文件容量为5MB，最大容量为10MB，文件增量为1MB。要求将次数据文件保存在与事务日志文件不同的存储设备上，次数据文件作为UserGroup文件组的成员。USEMASTERGOALTERDATABASESaleADDFILE(NAME=Sale2,FILENAME='C:\Sale2.ndf',SIZE=5MB,MAXSIZE=10MB,FILEGROWTH=1MB)TOFILEGROUPUserGroupGO徐人凤共17页第17页5/23/202017174．使用存储过程显示Sale数据库的信息。SP_helpdbSaleGO徐人凤共18页第18页5/23/20201818单元6（创建与管理数据表）1．实训项目都是围绕Sale数据库展开，进销存系统通常包括客户资料、产品信息、进货记录、销售记录等