Sql Server 一则训练题

（1） 在图上补齐Customers、Addresses、Items、Categorys 这几个表的各字段的类型。

（2） 列出订单编号（OrderID）为80007的订单所销售的商品的名称(Name)、定购数量（Quantity）、单价（UnitPrice）。

--注意UnitPrice在OrderItems和Items表都有，如果能理解这些表结构的话，可以发现历史订单上的价格应该是在OrderItems的,Items表记录的是当前的价格。

select [Name],Quantity,OrderItems.UnitPrice from Items,OrderItems

where Items.ItemID = OrderItems.ItemID and OrderItems.OrderID = 80007

（3） 查询出客户编号（CustomerID）为1001的客户在2008年上半年所有定购过的订单的编号（OrderID）、日期（OrderDate）、该订单总金额，按日期从小到大排列。

--注意限制时间的写法，如果要用2008-01-01这样的日期也可以，最好转换为日期型。

select Orders.OrderID,OrderDate,SUM(UnitPrice*Quantity) as OrderTotalAmount 

from Orders,OrderItems

where Orders.OrderID=OrderItems.OrderID

and CustomerID = 1001

and month(OrderDate) <= 6 and year(OrderDate) = 2008

group by Orders.OrderID,OrderDate

order by OrderDate

（4） 查询商品名称(Name)为’BENQ EL71’的商品在去年的销售汇总情况。

请按月份汇总，列出月份、销售数量2个字段。

select Month(Orders.OrderDate) as [Month],SUM(Quantity) as TotalQty 

from Items,Orders,OrderItems

where Items.ItemID = OrderItems.ItemID 

and Orders.OrderID=OrderItems.OrderID

and Items.[Name] = 'BENQ EL71'

and Year(OrderDate)= Year(getdate())- 1

group by Month(Orders.OrderDate)

order by Month(Orders.OrderDate)

（5） 删除订单日期（OrderDate）在2000年及之前的所有订单（包括Orders表和OrderItems表中的资料）

--注意删除的顺序，因为Orders表和OrderItems表是有主外键关系的，所以如果先删Orders会不成功。

先运行：

delete OrderItems where OrderID in 

(select OrderID from Orders where Year(OrderDate)<=2000) 

再运行：

delete Orders where Year(OrderDate)<=2000

（6） 更新所有商品的名称(Name)及描述(Description)，把中间含有‘BENQ’的字符串替换为‘明基’

--注意 “Where [name] like '%BENQ%' or description like '%BENQ%'”可有可无。

update items set [name] = Replace([name],'BENQ','明基'), 

description = replace(description,'BENQ','明基')

（7） 写一个Function：udf_getAddressDetailByOrderID(OrderID)，用来返回某张订单对应的详细送货地址。

送货地址显示格式为：Country + ’，’ + Address + ’，电话：’+ PhoneNumber。

例如： “中国，河南省郑州市金水区纬五路9号楼东单元，电话：1301588975”

--注意 Orders表和Addresses表的串连：“Orders.ShipToAddressID=Addresses.AddressID”。

CREATE FUNCTION udf_getAddressDetailByOrderID(@OrderID int)

RETURNS nvarchar(300)

AS

BEGIN

DECLARE @AddressDetail nvarchar(300)

SELECT @AddressDetail = Country + '，' + Address + '，电话：'+ PhoneNumber

FROM Orders,Addresses

WHERE Orders.ShipToAddressID = Addresses.AddressID and OrderID = @OrderID

RETURN @AddressDetail

END

调用：Select dbo.udf_getAddressDetailByOrderID(XXX)

（8） 写一个Store Procedure：udp_modifyUnitPriceOfTop3Item，用来把每个类别（Category）下上个月销售数量居前3位的商品的单价（Item.UnitPrice）上涨2%（不考虑Category的递归，若销售数量相同，则随机取）

--注意“上个月”不要写死是2008年9月，要用函数来动态取得，写法有很多种。

CREATE PROCEDURE udp_modifyUnitPriceOfTop3Item

    @CategoryID int

AS

BEGIN

update Items set UnitPrice = UnitPrice * 1.02 

where ItemID in (

select top 3 Items.ItemID

from Items,Orders,OrderItems

where Items.ItemID=OrderItems.ItemID

and Orders.OrderID=OrderItems.OrderID

and OrderDate>=DATEADD(mm,DATEDIFF(mm,0,getdate())-1,0) -- >=上月第一天

and OrderDate< DATEADD(mm,DATEDIFF(mm,0,getdate()),0)  -- <上月第一天

and CategoryID = @CategoryID

group by Items.ItemID

order by SUM(OrderItems.quantity) desc

) 

END

调用：exec dbo.udp_modifyUnitPriceOfTop3Item XXX