(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