create database MENT


go
create table orders		
(
	orderid int primary key identity(1,1),	
	orderdate datetime, 
)
go
	insert into orders(orderdate)
	values ('2008-01-12'),('2008-02-10'),('2008-02-15'),('2008-03-10')
go
create table OrderItem
(
	ItemiD int primary key identity,	
	Orderid int	references orders(orderid), 
	ItemType nvarchar(10), 
	Itemname nvarchar(10), 
	Thenumber int,	
	Themoney int, 
)
go
	insert into OrderItem(Orderid,ItemType,Itemname,Thenumber,Themoney)
	values (1,'文具','笔',72,2),
			(1,'文具','尺',10,1),(1,'体育用品','篮球',1,56),
			(2,'文具','笔',36,2),(2,'文具','固体胶',20,3),
			(2,'日常用品','透明胶',2,1),(2,'体育用品','羽毛球',20,3),
			(3,'文具','订书机',20,3),(3,'文具','订书针',10,3),
			(3,'文具','裁纸刀',5,5),(4,'文具','笔',20,2),(4,'文具','信纸',50,1),
			(4,'日常用品','毛巾',4,5),(4,'日常用品','透明胶',30,1),(4,'体育用品','羽毛球',20,3)
go
	select sum(thenumber) 数量总和 from OrderItem	
go
	select orderid 订单编号,sum(thenumber) 所有数量,avg(themoney) 平均单价 from OrderItem where orderid<3 group by orderid having avg(themoney)<10
go
	select orderid 订单,avg(themoney) 平均单价,sum(thenumber) 总数量 from OrderItem group by orderid having avg(themoney)<10 and sum(thenumber)>50
go
	select ItemType 类别,count(itemtype) 次数 from oederltem group by itemtype
go
	select ItemType类别,sum(thenumber) 订购总数,avg(thenumber) 平均单价 from OrderItem group by itemtype having sum(thenumber)>100
go
	select itemname 产品名称,count(itemname) 订购次数,sum(thenumber) 总数量,avg(thenumber) 平均单价 from OrderItem group by itemname