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

1、3720500SQLServer课后实训参考答案课后实训参考答案单元1 （SQL Server概述）1、使用SQL语句。在Sale数据库中创建名为MyDataType的用户 定义数据类型，数据类型为NVARCHAR,长度为20,该列允许为 NULLoUSE SaleGOEXEC sp_addtype MyDataType, fNVARCHAR（20）NULLGO单元2 （单表数据）使用查询窗口或sqlcmd实施查询。木实训使用Xk数据库。-1.查看系部编号为“03”的系部名称。USE XkGOSELECT DepartNameFROM DepartmentWHERE DepartNo=,03,

2、GO-2.查看系部名称中包含有“工程”两个字的系的全名。USE XkGOSELECT DepartN&meFROM DepartmentWHERE DepartName LIKE 工程GO一3显示共有多少个系部。USE XKGOSELECT 系部总数=COUNT (* )FROM DepartmentGO-4显示“01”年级共有多少个班级。USE XKGOSELECT 01 级班级数=COUNT 严)FROM ClassWHERE ClassNo LIKE 2001%GOSELECT 01 级班级数=COUNT ()FROM ClassWHERE ClassName LIKE 01%GOSEL

3、ECT *01 级班级数=COUNT 严）FROM StudentWHERE ClassNo LIKE 2001%GO-5.查看在“周二晚”上课的课程名称和教师。USE XKGOSELECT，课程名称 CouName,，任课教师 *=TeacherFROM CourseWHERE SchoolTime周二晚GO-6.查看姓“”、“”、“黄”同学的基本信息，要求按照降序排序 查询结果。USE XKGOSELECT *FROM StudentWHERE StuName LIKE ORStuName LIKE 1 % 1 ORStuName LIKE 黄 ORDER BY StuName DESCG

4、O-方法USE XKGOSELECT *FROM StudentWHERE StuName like 黄%ORDER BY StuName DESCGO单元2 (多表查询)-1.按系部统计课程的平均报名人数，要求显示系部名称、平均报 名人数。SELECT，系部名称!=DepartName,平均报名人数*=AVG(WillNum)FROM Course C,Department DWHERE CDepartNo=DDepartNoGROUP BY DepartNameGO-如果小数点后只保留位SELECT，系部名称DepartName,平均报名数1=CONVERT(DECIMAL(5,2),AV

5、G(WillNum)FROM Course Cz Department DWHERE CDepartNo=DDepartNoGROUP BY DepartNameGO-2.统计各个系部的班级数，要求显示系部编号、系部名称和班级 数量。FROM Class Cz Department DWHERE CDepartNo=DDepartNoGROUP BY CDepartNoDepartNameGO-3.查看“甘蕾”同学选修的课程名、学分、上课时间、志愿号, 按志愿号（升序）排序查询结果。SELECT CouName,Credit,SchoolTime,WillOrderFROM StuCou SC

6、,Course C,Student SWHERE SCCouNo=C.CouNo AND SCStuNo=SStuNo ANDStuName=,#ffi,ORDER BY WillOrderGO-4.查看“00电子商务”班的选修报名情况。要求显示学号、课 程编号、课程名称、志愿号，并按学号（升序）、志愿号排序（升序）。SELECT SStuNo,StuName,CCouNo,CouNamez WillOrderFROM StuCou SCZ Course C,Student S,Class CLWHERE SCCouNo=C.CouNo ANDSCStuNo=SStuNo ANDCL.Clas

7、sNo=S.ClassNo ANDClassName Like 1 00电子商务 1ORDER BY SStuNo,WillOrderGO-5.按系部统计各系的最少报名人数、最多报名人数、平均报名 人数和报名总数，并汇总显示所有系部的报名总数。要求平均报名人 数保留两位小数位。SELECT，系部名称f =DepartName,，最少报名人数 r=MIN(WillNum),，最多报名人数 1 =MAX (WillNum) , 1 平均报名 人数 CONVERT (DECIMAL (5,2) , AVG (WillNum),，报名总数 SUMfWillNum)FROM Course C,Depar

8、tment DWHERE CDepartNo=DDepartNoGROUP BY DepartName WITH CUBEGO单元3 （维护数据）-12.学号为“00000025”的同学第一志愿报名选修“001”课程, 请在数据库中进行处理。INSERT StuCou（StuNo,CouNo,WillOrder,State）VALUES （ *00000025 z 701 1,，报名 *）GO/*课程报名人数增加人/UPDATE CourseSET WillNum=Wi1INum-1WHERE CouNo=* 001GO-2.删除学号为“00000025”的学生的选课报名信息。DELETE S

9、tuCouWHERE StuNo=*00000025,GO/*课程表00000025-同学报名的那些课程报名人数都要减少1*/-方法：UPDATE CourseSET Wi1lNum=Wi1INum-1FROM Course,StuCouWHERE StuNo=*00000025 andCourseCouNo=StuCouCouNoGO-方法2：(游标)DECLARE CouNo char(3)DECLARE MYCursor cursorforSELECT CouNo from StuCouWHERE StuNo=,00000025,OPEN MYCursorFETCH NEXT FROM

10、MYCursor INTO CouNoWHILE FETCH_STATUS=OBEGINWHEREUPDATE Course SET WillNum=Wi1INum-1CouNo=CouNoFETCH NEXT FROM MYCursor INTO CouNoENDCLOSE MYCursorDEALLOCATE MYCursorGO-3需要将“00多媒体”班级“杜晓静”同学的名字修改为“杜小静”。UPDATE StudentSET StuN&me=杜小静FROM Student S,Class CWHERE S.ClassNo=C.ClcssNo ANDStuNdme=，杜晓静AND C_&

11、;ssN&me= 00多媒体GO-“00电子商务”班的“林斌”同学中请将己选修的“网络信息检 索原理与技术”课程修改为“Linux操作系统”。UPDATE StuCouSET CouNo=(SELECT CouNo FROM Course WHERECouName= Linux操作系统 * )FROM StuCou SC,Student Sz Course C,Class ClWHERE SCStuNo=S.StuNo ANDSC.CouNo=CCouNo ANDSClassNo=C1ClassNo ANDS.StuN&mei 林斌ANDCl_ Cl&ssN&me= 00商务ANDCouNam

12、e= 网络信息检索原理与技术，GO单元4 （数据库设计）某公司计划对产品的销售情况进行计算机管理。产品表具有产 品编号、产品名称、单价、库存数量4个属性。客户表包括有客户编 号、客户、住址、联系4个属性。产品的入库表包括有入库日期、产 品编号、产品名称、入库数量、单价属性。销售表包括有销售日期、 产品编号、产品名称、客户编号、客户、单价、销售数量。1.绘出产品销售的E-R图。2、将产品销售的E-R图转换为关系数据模型。产品表：产品编号产品名称单价库存数量客户表:客户编号客户名称住址联系入库表:入库日期产品编号产品名称入库数暈单价销售表:销售日期产品编号产品名称客户编号客户单价销售数量3、将产品

13、销售关系数据模型规为III式。可参考“创建和管理表”实训题。4、 请分析主键、外键、公共键。主键：产品表的产品编号，客户表的客户编号。外键：入库表的产品编号，销售表的产品编号、客户编号。5、 举例说明如何保证产品销售数据的完整性。表数据完整性：给产品表、客户表创建主键。列数据的完整性：可以给指定的列创建约束（参见实施数据完整性实训题目）。参照完整性：给入库表、销售表创建外键。单元5 (创建与管理数据库)写出实现如下功能的SQL语句。1.创建名字为Sale的销售数据库。该数据库有一个名为Sale.mdf 的主数据文件和名字为Salejog.ldf的事务H志文件。主数据文件容 量为4MB,日志文件

14、容量为10MB,数据文件和H志文件的最大容量 为20MB,文件增长量为1MB。CREATE DATABASE SaleONPRIMARY (N2V4E=Sale,FILENAME=fC:Salemdf,SIZE =4,MAXSIZE=10zFILEGROWTH=1)LOG ON(NAME=Sale_logzFILENAME=fD:Sale_logldf1,SIZE=2,MAXSIZE= 10,2.在Sale数据库下新增名字为UserGroup的文件组。USE SaleGoALTER DATABASE SaleADD FILEGROUP UserGroupGo3.以增加次数据文件的方式扩充Sal

15、e数据的容量。次数据文件容 量为5MB,最大容量为10MB,文件增量为1MB。要求将次数据文 件保存在与事务日志文件不同的存储设备上，次数据文件作为 UserGroup文件组的成员。USE MASTERGOALTER DATABASE SaleADD FILE(NAME=Sde2,FILEN2kME=lC: Sale2 ndf 1 ,SIZE=5MBzMAXSIZE=10MBzFILEGROWTH=1MB)TO FILEGROUP UserGroup4.使用存储过程显示Sale数据库的信息。SP_helpdb SaleGO单元6 （创建与管理数据表）1.实训项目都是围绕Sale数据库展开，进销

16、存系统通常包括客户 资料、产品信息、进货记录、销售记录等。所以针对Sale数据库， 设计表了表1-4,数据请见表5-8,并将在后续章节逐步完善。使用 Transcact-SQL语句，在Sale数据库下创建客户表、产品表、入库表 和销售表并输入数据。请将脚木保存为.sql文件。表1 Customer （客户表）结构列名数据类 型及长度是否允 许为空备注CusNonvarchar(3)NOTNULL客户编号CusNamnvarcharNOT客户e(10)NULLAddressnvarchar(20)NULL地址Telnvarchar(20)NULL联系表2 Product （产品表）结构列名数据类

17、 型及长度是否允 许为空备注ProNonvarchar(5)NOTNULL产品编号ProNamenvarchar(20)NOTNULL产品名PriceDecimal(8,2)NOTNULL单价StocksDecimal(8,0)NOTNULL库存数量表3 ProIn （入库表）结构列名数据类 型及长度是否允 许为空备注InputDateDateTimeNOTNULL入库日期ProNonvarchar(5)NOTNULL产品编号QuantityDecimal(6,0)NOTNULL入库数量表4 ProOut （销售表）结构列名数据类 型及长度是否允 许为空备注SaleDateDateTimeNO

18、TNULL销售日期CusNonvarchar(3)NOTNULL客户编号ProNonvarcharr (5)NOTNULL产品编号QuantityDecimal(6,0)NOTNULL销售数暈表5 Customer （客户表）数据CusNoCusNameAddressTel001婷01002萍03003东05004叶合7005谭新9表6 Product （产品表）数据ProNoProNamePriceStocks00001电视3000. 0080000002空调2000. 0050000003床1000. 0030000004餐桌1500. 0020000005音响5000. 00600000

19、06沙发6000. 00100表7 ProIn （入库表）数据InputDate （入 库日期）DateTime not nullProNo （产品编号） nvarchar （5） not nullQuantity （入库数 量）Decimal （6,0） not null2006-1-100001102006-1-10000252006-1-20000152006-1-200003102006-1-300001102006-2-100003202006-2-200001102006-2-300004302006-3-30000320表8 ProOut （销售表）数据SaleDateCusNo

20、ProNoQuantity2006-1-100100001102006-1-20010000252006-1-30020000152006-2-100200003102006-2-200100001102006-2-300100003202006-3-200300001102006-3-200300004302006-3-30020000320-该题创建表时冋时创建主键.外键。先创建表，再创建主键.外键的方法参见照实训5_3Use SaleGoCREATE TABLE Customer(CusNo nvarchar(3) Primary key,CusName nvarchar(10)not

21、nu丄丄孑Address nvarchar (20)Tel Char(20)Go/创建产品表/CREATE TABLE Product(ProNo nvarchar(5)Primary key,ProName nvarchar(20)not nullPrice Decimal(8A 2)not nullAStocks Decimal(8,0)not null)Go/创建入库表/CREATE TABLE ProIn(InputDate D&teTime not null,ProNo nvarchar(5)References Product(ProNo),Quantity Decimal(6,0

22、)not null)Go/创建销售表/CREATE TABLE ProOut(SaleDate D&teTime not nullzCusNo nvarchar(3)not null ReferencesCustomer(CusNo),ProNo nvarchar(5)not null ReferencesProduct(ProNo),Quantity Decimal(6,0)not null)Go-向Customer表输入数据-INSERT INTOCustomer VALUES ( 1 001 婷；0)INSERT INTOCustomer VALUES ( 1 002 ；萍；03 )IN

23、SERT INTOCustomer VALUES ( 003 J 东；05 )INSERT INTOINSERT INTOCustomer VALUES ( 005 1 , * 谭新；9 )Go-向Product表中插入数据-INSERT INTOProduct VALUES (! 00001* ,电视, 30 00 0 0,8 00)INSERT INTOProduct VALUES ( 1 00002 * ,空调 J 2000 00,5 00)INSERT INTOProduct VALUES(0 0003,丿耒,丄000.00,300)INSERT INTOProduct VALUES(

24、*00004z 餐桌,150000,2 00)INSERT INTOProduct VALUES00005 ，音响，5000.00,600)INSERT INTOProduct VALUES(00006 ,沙发,6000 00,100)Go-向ProIn表中插入数据-INSERT INTOINSERT INTOProIn VALUES(12006-1-11 , 00002 J 5)INSERT INTOProIn VALUES(*2006-1-2 * z 00001 J 5)INSERT INTOProIn VALUES ( *2006-1-2 * z 00003 10)INSERT INTO

25、ProIn VALUES(200 6-1-3 J ,00001f,10)INSERT INTOProIn VALUES ( 1 2006-2-1 1 z 00003 20)INSERT INTOProIn VALUES ( *2006-2-2 * z 00001 10)INSERT INTOProIn VALUES200 6-2-3 J 00004 30)INSERT INTOProIn VALUES ( 2006-3-3 J ,0000320)Go-向ProOut表中插入数据-INSERT INTOINSERT INTOProOutVALUES(*2006-1-21,f00002 5)INS

26、ERTINTOProOutVALUES(2006-1-3*,002,OOOOl*,5)INSERTINTOProOutVALUES(2006-2-1 *002 * z!00003*,10)INSERTINTOProOutVALUES(,2006-2-2l,OOl1,9000 ,10)INSERTINTOProOutVALUES(*2006-2-3*,!001 1 ,P0003,20)INSERTINTOProOutVALUES(12006-3-21,P03 J9000UzlO)INSERTINTOProOutVALUES(2006-3-21,703 J!00004*,30)INSERTINTO

27、ProOutVALUES(2006-3-31,f002 * z00003z20)Go单元7 （实施数据完整性）1.根据你的理解，请在实训5_2的表1-4的备注栏中标出主键、外键。表1 Customer （客户表）结构列名数据类 型及长度是否允 许为空备注CusNonvarchar(3)NOTNULL客户编号 主键CusNamnvarcharNOT客户e(10)NULLAddressnvarchar(20)NULL地址Telnvarchar(20)NULL联系表2 Product （产品表）结构列名数据类 型及长度是否允 许为空备注ProNonvarchar(5)NOTNULL产品编号主键ProNamnvarcharNOT产品名e(20)NULLPriceDecimal(8,2)NOTNULL单价StocksDecimal(8,0)NOTNULL库