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

课后实训参考答案

单元1（SQL Server概述）

1、使用SQL语句。在Sale数据库中创建名为MyDataType的用户定义数据类型，数据类型为NV ARCHAR，长度为20，该列允许为NULL。

USE Sale

GO

EXEC sp_addtype MyDataType,'NVARCHAR(20)','NULL' GO

单元2（单表数据）

使用查询窗口或sqlcmd实施查询。本实训使用Xk数据库。

--1．查看系部编号为“03”的系部名称。

USE Xk

GO

SELECT DepartName

FROM Department

WHERE DepartNo='03'

GO

--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 LIKE'01%'

GO

SELECT'01 级班级数'=COUNT(*)

FROM Student

WHERE ClassNo LIKE'2001%'

GO

--5．查看在“周二晚”上课的课程名称和教师。

USE XK

GO

SELECT'课程名称'=CouName,'任课教师'=Teacher

FROM Course

WHERE SchoolTime='周二晚'

GO

--6．查看姓“张”、“陈”、“黄”同学的基本信息，要求按照姓名降序排序查询结果。

USE XK

GO

SELECT*

FROM Student

WHERE StuName LIKE'张%'OR

StuName LIKE'陈%'OR

StuName LIKE'黄%'

ORDER BY StuName DESC

GO

--方法

USE XK

GO

SELECT*

FROM Student

WHERE StuName like'[张,陈,黄]%' ORDER BY StuName DESC

GO

单元2（多表查询）

--1.按系部统计课程的平均报名人数，要求显示系部名称、平均报名人数。

SELECT'系部名称'=DepartName,'平均报名人数

'=AVG(WillNum)

FROM Course C,Department D

WHERE C.DepartNo=D.DepartNo

GROUP BY DepartName

GO

--如果小数点后只保留位

SELECT'系部名称'=DepartName,'平均报名数

'=CONVERT(DECIMAL(5,2),AVG(WillNum))

FROM Course C,Department D

WHERE C.DepartNo=D.DepartNo

GROUP BY DepartName

GO

--2.统计各个系部的班级数，要求显示系部编号、系部名称和班级数量。

SELECT C.DepartNo,DepartName,COUNT(*)

FROM Class C,Department D

WHERE C.DepartNo=D.DepartNo

GROUP BY C.DepartNo,DepartName

GO

--3．查看“甘蕾”同学选修的课程名、学分、上课时间、志愿号，按志愿号（升序）排序查询结果。

SELECT CouName,Credit,SchoolTime,WillOrder

FROM StuCou SC,Course C,Student S

WHERE SC.CouNo=C.CouNo AND SC.StuNo=S.StuNo AND StuName='甘蕾'

ORDER BY WillOrder

GO

--4．查看“00电子商务”班的选修报名情况。要求显示学号、姓名、课程编号、课程名称、志愿号，并按学号（升序）、志愿号排序（升序）。

SELECT S.StuNo,StuName,C.CouNo,CouName,WillOrder FROM StuCou SC,Course C,Student S,Class CL

WHERE SC.CouNo=C.CouNo AND

SC.StuNo=S.StuNo AND

CL.ClassNo=S.ClassNo AND

ClassName Like'00电子商务'

ORDER BY S.StuNo,WillOrder

GO

--5．按系部统计各系的最少报名人数、最多报名人数、平均报名人数和报名总数，并汇总显示所有系部的报名总数。要求平均报名人数保留两位小数位。

SELECT'系部名称'=DepartName,'最少报名人数

'=MIN(WillNum),'最多报名人数'=MAX(WillNum),'平均报名人数'=CONVERT(DECIMAL(5,2),AVG(WillNum)),'报名总数'=SUM(WillNum)

FROM Course C,Department D

WHERE C.DepartNo=D.DepartNo

GROUP BY DepartName WITH CUBE

GO

单元3（维护数据）

--12．学号为“00000025”的同学第一志愿报名选修“001”课程，请在数据库中进行处理。

INSERT StuCou(StuNo,CouNo,WillOrder,State)

VALUES('00000025','001',1,'报名')

GO

/*课程报名人数增加人*/

UPDATE Course

SET WillNum=WillNum+1

WHERE CouNo='001'

GO

--2．删除学号为“00000025”的学生的选课报名信息。

DELETE StuCou

WHERE StuNo='00000025'

GO

/*课程表'00000025'同学报名的那些课程报名人数都要减少1*/

--方法：

UPDATE Course

SET WillNum=WillNum-1

FROM Course,StuCou

WHERE StuNo='00000025'and

Course.CouNo=StuCou.CouNo

GO

--方法2：（游标）

DECLARE @CouNo char(3)

DECLARE MYCursor cursor

for

SELECT CouNo from StuCou

WHERE StuNo='00000025'

OPEN MYCursor

FETCH NEXT FROM MYCursor INTO @CouNo

WHILE@@FETCH_STATUS=0

BEGIN

UPDATE Course SET WillNum=WillNum-1 WHERE CouNo=@CouNo

FETCH NEXT FROM MYCursor INTO @CouNo

END

CLOSE MYCursor

DEALLOCATE MYCursor

GO

--3．需要将“00多媒体”班级“杜晓静”同学的名字修改为“杜小静”。

UPDATE Student

SET StuName='杜小静'

FROM Student S,Class C

WHERE S.ClassNo=C.ClassNo AND

StuName='杜晓静'AND ClassName='00多媒体' GO

--“00电子商务”班的“林斌”同学申请将已选修的“网络信息检索原理与技术”课程修改为“Linux操作系统”。

UPDATE StuCou

SET CouNo=(SELECT CouNo FROM Course WHERE CouName='Linux操作系统')

FROM StuCou SC,Student S,Course C,Class Cl

WHERE SC.StuNo=S.StuNo AND

SC.CouNo=C.CouNo AND

S.ClassNo=Cl.ClassNo AND

S.StuName='林斌'AND

Cl.ClassName='00电子商务'AND

CouName='网络信息检索原理与技术'

GO

单元4（数据库设计）

某公司计划对产品的销售情况进行计算机管理。产品表具有产品编号、产品名称、单价、库存数量4个属性。客户表包括有客户编号、客户姓名、住址、联系电话4个属性。产品的入库表包括有入库日期、产品编号、产品名称、入库数量、单价属性。销售表包括有销售日期、产品编号、产品名称、客户编号、客户姓名、单价、销售数量。

1．绘出产品销售的E-R图。

2、将产品销售的E-R图转换为关系数据模型。

产品表：

客户表：

入库表：

销售表：

3、将产品销售关系数据模型规范为Ⅲ范式。可参考“创建和管理表”实训题。

4、请分析主键、外键、公共键。

主键：产品表的产品编号，客户表的客户编号。

外键：入库表的产品编号，销售表的产品编号、客户编号。

5、举例说明如何保证产品销售数据的完整性。

表数据完整性：给产品表、客户表创建主键。

列数据的完整性：可以给指定的列创建约束（参见实施数据完整性实训题目）。

参照完整性：给入库表、销售表创建外键。

单元5（创建与管理数据库）Array写出实现如下功能的SQL语句。

1．创建名字为Sale的销售数据库。该数据库有一个名为Sale.mdf 的主数据文件和名字为Sale_log.ldf的事务日志文件。主数据文件容量为4MB，日志文件容量为10MB，数据文件和日志文件的最大容量为20MB，文件增长量为1MB。

CREATE DATABASE Sale

ON

PRIMARY(NAME=Sale,

FILENAME='C:\Sale.mdf',

SIZE=4,

MAXSIZE=10,

FILEGROWTH=1)

LOG ON

(NAME=Sale_log,

FILENAME='D:\Sale_log.ldf',

SIZE=2,

MAXSIZE= 10,

FILEGROWTH=1)/*注意结尾处无逗号*/

GO

2．在Sale数据库下新增名字为UserGroup的文件组。

USE Sale

Go

ALTER DATABASE Sale

ADD FILEGROUP UserGroup

Go

3．以增加次数据文件的方式扩充Sale数据的容量。次数据文件容量为5MB，最大容量为10MB，文件增量为1MB。要求将次数据文件保存在与事务日志文件不同的存储设备上，次数据文件作为UserGroup文件组的成员。

USE MASTER

GO

ALTER DATABASE Sale

ADD FILE

(NAME=Sale2,

FILENAME='C:\Sale2.ndf',

SIZE=5MB,

MAXSIZE=10MB,

FILEGROWTH=1MB)

TO FILEGROUP UserGroup

GO

4．使用存储过程显示Sale数据库的信息。SP_helpdb Sale

GO

单元6（创建与管理数据表）

1．实训项目都是围绕Sale数据库展开，进销存系统通常包括客户资料、产品信息、进货记录、销售记录等。所以针对Sale数据库，设计表了表1-4，数据请见表5-8，并将在后续章节逐步完善。使用Transcact-SQL语句，在Sale数据库下创建客户表、产品表、入库表和销售表并输入数据。请将脚本保存为.sql文件。

表1 Customer（客户表）结构

表2 Product（产品表）结构

表3 ProIn（入库表）结构

表4 ProOut（销售表）结构

表5 Customer（客户表）数据

表6 Product（产品表）数据

表7 ProIn（入库表）数据

表8 ProOut（销售表）数据