37205-00 - SQL Server 2005课后实训参考答案(8)

单元10（保证数据安全性_视图） 本实训使用Sale数据库。

1．创建视图V_Sale1，显示销售日期、客户编号、客户姓名、产品编号、产品名称、单价、销售数量和销售金额。 CREATE VIEW V_Sale1 AS SELECT

SaleDate,CusName,ProName,Price,Quantity,SaleMoney=(Price*Quantity)

FROM ProOut PO,Customer C,Product P

WHERE P.ProNo=PO.ProNo AND C.CusNo=PO.CusNo GO

2．创建视图V_Sale2，统计每种产品的销售数量和销售金额。统计结果包括：产品编号、产品名称、单价、销售数量和销售金额。 CREATE VIEW V_Sale2 AS SELECT

P.ProNo,P.ProName,SaleQuantity=SUM(Quantity),SaleMoney=SUM(Price*Quantity) FROM ProOut PO,Product P

徐人凤

共 36 页 第 36 页

5/19/2015 36

36

WHERE P.ProNo=PO.ProNo GROUP BY P.ProNo,P.ProName GO

3．创建视图V_Sale3，统计销售金额在10万以下的产品信息。

CREATE VIEW V_Sale3 AS

SELECT P.ProNo,P.ProName FROM ProOut PO,Product P WHERE P.ProNo=PO.ProNo GROUP BY P.ProNo,P.ProName

HAVING SUM(Price*Quantity)<100000 GO

徐人凤 共 37 页 第 37 页 5/19/2015 37 37

单元11（保证数据安全性_存储过程） 本实训使用Sale数据库。

1．创建存储过P_Sale1，统计每种产品的销售数量和销售金额。 CREATE PROCEDURE P_Sale1 AS SELECT

Product.ProNo,ProName,Price,Sl=SUM(Quantity),Je=SUM(Price*Quantity) FROM ProOut,Product

WHERE ProOut.ProNo=Product.ProNo GROUP BY Product.ProNo,ProName,Price GO --调用 EXEC P_Sale1

2．创建存储过程P_Sale2，能够根据指定的客户统计汇总该客户购买每种产品的数量和花费金额。 CREATE PROCEDURE P_Sale2 @CusNo nvarchar(3)

徐人凤

共 38 页 第 38 页

5/19/2015 38

38

AS SELECT

Product.ProNo,ProName,Price,Sl=SUM(Quantity),Je=SUM(Price*Quantity) FROM ProOut,Product

WHERE ProOut.ProNo=Product.ProNo AND CusNo=@CusNo

GROUP BY Product.ProNo,ProName,Price GO --调用

EXEC P_Sale2 '001' EXEC P_Sale2 '002' GO

3．创建存储过程P_Sale3，能够根据指定的产品编号和日期，以输出参数的形式得到该产品的销售金额。 USE SALE GO

CREATE PROCEDURE P_Sale3

@ProNo nvarchar(5),@SaleDate DateTime,@Je Decimal(8,2) OUTPUT

徐人凤

共 39 页 第 39 页

5/19/2015 39

39

AS SET @Je=

(SELECT SUM(Price*Quantity) FROM ProOut,Product

WHERE ProOut.ProNo=Product.ProNo AND Product.ProNo=@ProNo AND SaleDate=@SaleDate GROUP BY Product.ProNo,ProName,Price )

print @je GO --调用

DECLARE @Je Decimal(8,2)

EXEC P_Sale3 '00001','2006-1-1',@Je GO

徐人凤 共 40 页 第 40 页 5/19/2015 40 40