SQLServer课后实训参考答案.docx

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

2、名称中包含有“工程”两个字的系的全名。 USE Xk GO SELECT DepartName FROM Department WHERE DepartName LIKE %工程% GO-3显示共有多少个系部。 USE XK GO SELECT 系部总数=COUNT(*) FROM Department GO-4显示“01”年级共有多少个班级。 USE XK GO SELECT 01 级班级数=COUNT(*) FROM Class WHERE ClassNo LIKE 2001% GO SELECT 01 级班级数=COUNT(*) FROM Class WHERE ClassName LI

3、KE 01% GO SELECT 01 级班级数=COUNT(*) FROM Student WHERE ClassNo LIKE 2001% GO-5查看在“周二晚”上课的课程名称和教师。USE XKGOSELECT 课程名称=CouName,任课教师=TeacherFROM Course WHERE SchoolTime=周二晚GO-6查看姓“张”、“陈”、“黄”同学的基本信息，要求按照姓名降序排序查询结果。USE XKGOSELECT *FROM StudentWHERE StuName LIKE 张% OR StuName LIKE 陈% OR StuName LIKE 黄%ORDER

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

5、VERT(DECIMAL(5,2),AVG(WillNum)FROM Course C,Department DWHERE C.DepartNo=D.DepartNoGROUP BY DepartNameGO-2.统计各个系部的班级数，要求显示系部编号、系部名称和班级数量。SELECT C.DepartNo,DepartName,COUNT(*)FROM Class C,Department DWHERE C.DepartNo=D.DepartNoGROUP BY C.DepartNo,DepartNameGO-3查看“甘蕾”同学选修的课程名、学分、上课时间、志愿号，按志愿号（升序）排序查询结

6、果。SELECT CouName,Credit,SchoolTime,WillOrderFROM StuCou SC,Course C,Student SWHERE SC.CouNo=C.CouNo AND SC.StuNo=S.StuNo AND StuName=甘蕾ORDER BY WillOrderGO-4查看“00电子商务”班的选修报名情况。要求显示学号、姓名、课程编号、课程名称、志愿号，并按学号（升序）、志愿号排序（升序）。SELECT S.StuNo,StuName,C.CouNo,CouName,WillOrderFROM StuCou SC,Course C,Student S

7、,Class CLWHERE SC.CouNo=C.CouNo AND SC.StuNo=S.StuNo AND CL.ClassNo=S.ClassNo AND ClassName Like 00电子商务ORDER BY S.StuNo,WillOrderGO-5按系部统计各系的最少报名人数、最多报名人数、平均报名人数和报名总数，并汇总显示所有系部的报名总数。要求平均报名人数保留两位小数位。 SELECT 系部名称=DepartName,最少报名人数=MIN(WillNum),最多报名人数=MAX(WillNum),平均报名人数=CONVERT(DECIMAL(5,2),AVG(WillNu

8、m),报名总数=SUM(WillNum) FROM Course C,Department DWHERE C.DepartNo=D.DepartNoGROUP BY DepartName WITH CUBEGO单元3（维护数据）-12学号为“”的同学第一志愿报名选修“001”课程，请在数据库中进行处理。INSERT StuCou(StuNo,CouNo,WillOrder,State) VALUES(00000025,001,1,报名)GO/*课程报名人数增加人*/UPDATE CourseSET WillNum=WillNum+1WHERE CouNo=001GO-2删除学号为“”的学生的选

9、课报名信息。DELETE StuCouWHERE StuNo=00000025GO/*课程表00000025同学报名的那些课程报名人数都要减少1*/-方法：UPDATE CourseSET WillNum=WillNum-1FROM Course,StuCouWHERE StuNo=00000025 and Course.CouNo=StuCou.CouNoGO-方法2：（游标）DECLARE CouNo char(3)DECLARE MYCursor cursorfor SELECT CouNo from StuCou WHERE StuNo=00000025OPEN MYCursorFET

10、CH NEXT FROM MYCursor INTO CouNoWHILE FETCH_STATUS=0 BEGIN UPDATE Course SET WillNum=WillNum-1 WHERE CouNo=CouNo FETCH NEXT FROM MYCursor INTO CouNo ENDCLOSE MYCursorDEALLOCATE MYCursorGO-3需要将“00多媒体”班级“杜晓静”同学的名字修改为“杜小静”。UPDATE StudentSET StuName=杜小静FROM Student S,Class CWHERE S.ClassNo=C.ClassNo AND

11、 StuName=杜晓静 AND ClassName=00多媒体GO-“00电子商务”班的“林斌”同学申请将已选修的“网络信息检索原理与技术”课程修改为“Linux操作系统”。UPDATE StuCouSET CouNo=(SELECT CouNo FROM Course WHERE CouName=Linux操作系统)FROM StuCou SC,Student S,Course C,Class ClWHERE SC.StuNo=S.StuNo AND SC.CouNo=C.CouNo AND S.ClassNo=Cl.ClassNo AND S.StuName=林斌 AND Cl.Clas

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

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

14、志文件。主数据文件容量为4MB，日志文件容量为10MB，数据文件和日志文件的最大容量为20MB，文件增长量为1MB。CREATE DATABASE Sale ONPRIMARY (NAME=Sale, =C:Sale.mdf, SIZE =4, MAXSIZE=10, =1)LOG ON ( NAME=Sale_log, =D:Sale_log.ldf, SIZE=2, MAXSIZE= 10, =1) /*注意结尾处无逗号*/GO2在Sale数据库下新增名字为UserGroup的文件组。USE SaleGoALTER DATABASE SaleADD UserGroupGo3以增加次数据文件

15、的方式扩充Sale数据的容量。次数据文件容量为5MB，最大容量为10MB，文件增量为1MB。要求将次数据文件保存在与事务日志文件不同的存储设备上，次数据文件作为UserGroup文件组的成员。USE MASTERGOALTER DATABASE SaleADD FILE(NAME=Sale2, =C:Sale2.ndf,SIZE=5MB,MAXSIZE=10MB,=1MB)TO UserGroupGO4使用存储过程显示Sale数据库的信息。SP_helpdb SaleGO单元6（创建与管理数据表）1实训项目都是围绕Sale数据库展开，进销存系统通常包括客户资料、产品信息、进货记录、销售记录等。

16、所以针对Sale数据库，设计表了表1-4，数据请见表5-8，并将在后续章节逐步完善。使用Transcact-SQL语句，在Sale数据库下创建客户表、产品表、入库表和销售表并输入数据。请将脚本保存为.sql文件。表1 Customer（客户表）结构列名数据类型及长度是否允许为空备注CusNonvarchar（3）NOT NULL客户编号CusNamenvarchar（10）NOT NULL客户姓名Addressnvarchar（20）NULL地址Telnvarchar（20）NULL联系电话表2 Product（产品表）结构列名数据类型及长度是否允许为空备注ProNonvarchar（5）NO

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

18、ULL客户编号ProNonvarchar r（5）NOT NULL产品编号QuantityDecimal（6,0）NOT NULL销售数量表5 Customer（客户表）数据CusNoCusNameAddressTel001杨婷深圳1002陈萍深圳3003李东深圳5004叶合广州005谭新广州表6 Product（产品表）数据ProNoProNamePriceStocks00001电视3000.0080000002空调2000.0050000003床1000.0030000004餐桌1500.0020000005音响5000.0060000006沙发6000.00100表7 ProIn（入库表

19、）数据InputDate（入库日期）DateTimenot 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（销售表）数据SaleDateCusNoProNoQuantity 2006-1-100100001102006-1-2

20、0010000252006-1-30020000152006-2-100200003102006-2-200100001102006-2-300100003202006-3-200300001102006-3-200300004302006-3-30020000320-该题创建表时同时创建主键、外键。先创建表，再创建主键、外键的方法参见照实训5_3Use Sale GoCREATE TABLE Customer (CusNo nvarchar(3) Primary key, CusName nvarchar(10)not null, Address nvarchar(20),Tel Char(

21、20) Go/*创建产品表*/CREATE TABLE Product (ProNo nvarchar(5)Primary key, ProName nvarchar(20)not null, Price Decimal(8,2)not null, Stocks Decimal(8,0)not null)Go/*创建入库表*/CREATE TABLE ProIn (InputDate DateTime not null,ProNo nvarchar(5)References Product(ProNo),Quantity Decimal(6,0)not null)Go/*创建销售表*/CREA

22、TE TABLE ProOut (SaleDate DateTime not null,CusNo nvarchar(3)not null References Customer(CusNo),ProNo nvarchar(5)not null References Product(ProNo),Quantity Decimal(6,0)not null)Go-向Customer表输入数据-INSERT INTOCustomer VALUES(001,杨婷,深圳,1)INSERT INTOCustomer VALUES(002,陈萍,深圳,3)INSERT INTOCustomer VALUE

23、S(003,李东,深圳,5)INSERT INTOCustomer VALUES(004,叶合,广州,)INSERT INTOCustomer VALUES(005,谭新,广州,)Go-向Product表中插入数据-INSERT INTOProduct VALUES(00001,电视,3000.00,800)INSERT INTOProduct VALUES(00002,空调,2000.00,500)INSERT INTOProduct VALUES(00003,床,1000.00,300)INSERT INTOProduct VALUES(00004,餐桌,1500.00,200)INSER

24、T INTOProduct VALUES(00005,音响,5000.00,600)INSERT INTOProduct VALUES(00006,沙发,6000.00,100)Go-向ProIn表中插入数据-INSERT INTOProIn VALUES(2006-1-1,00001,10)INSERT INTOProIn VALUES(2006-1-1,00002,5)INSERT INTOProIn VALUES(2006-1-2,00001,5)INSERT INTOProIn VALUES(2006-1-2,00003,10)INSERT INTOProIn VALUES(2006-

25、1-3,00001,10)INSERT INTOProIn VALUES(2006-2-1,00003,20)INSERT INTOProIn VALUES(2006-2-2,00001,10)INSERT INTOProIn VALUES(2006-2-3,00004,30)INSERT INTOProIn VALUES(2006-3-3,00003,20)Go-向ProOut表中插入数据-INSERT INTO ProOut VALUES(2006-1-1,001,00001,10)INSERT INTO ProOut VALUES(2006-1-2,001,00002,5)INSERT

26、INTO ProOut VALUES(2006-1-3,002,00001,5)INSERT INTO ProOut VALUES(2006-2-1,002,00003,10)INSERT INTO ProOut VALUES(2006-2-2,001,00001,10)INSERT INTO ProOut VALUES(2006-2-3,001,00003,20)INSERT INTO ProOut VALUES(2006-3-2,003,00001,10)INSERT INTO ProOut VALUES(2006-3-2,003,00004,30)INSERT INTO ProOut V

27、ALUES(2006-3-3,002,00003,20)Go单元7（实施数据完整性）1根据你的理解，请在实训5_2的表1-4的备注栏中标出主键、外键。表1 Customer（客户表）结构列名数据类型及长度是否允许为空备注CusNonvarchar（3）NOT NULL客户编号 主键CusNamenvarchar（10）NOT NULL客户姓名Addressnvarchar（20）NULL地址Telnvarchar（20）NULL联系电话表2 Product（产品表）结构列名数据类型及长度是否允许为空备注ProNonvarchar（5）NOT NULL产品编号 主键ProNamenvarchar（20）NOT NULL产品名PriceDecimal（8,2）NOT NULL单价StocksDecimal（8,0）NOT NULL库存数量表3 ProIn（入库表）结构列名数据类型及长度是否允许为空备注InputDateDateTimeNOT NULL入库日期