create database orderitem

use orderitem
go

create table Order1
(
 orderID int identity ,
 orderDate nvarchar(max)
);
go

set identity_insert Order1 on

insert into Order1(orderID,orderDate)
values(1,'2008-01-10 00:00:00.000'),
       (2,'2008-02-10 00:00:00.000'),
	   (3,'2008-02-15 00:00:00.000'),
	   (4,'2008-03-10 00:00:00.000');
	   go


create table item
(
 itemID int identity ,
 orderid int,
 itemType nvarchar(5),
 itemName nvarchar(5),
 theNumber  int,
 theMoney int 
);
go



insert into item(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

--订单表（orders）列为：订单编号（orderId 主键）  订购日期（orderDate）

--订购项目表（orderItem），列为：
--项目编号（ItemiD）订单编号（orderId）产品类别（itemType）
--产品名称（itemName） 订购数量（theNumber）  订购单价（theMoney）



--1.查询所有订单订购的所有物品数量总和
select sum(theNumber) 物品数量总和 from item 


--2.查询订单编号小于3的，平均单价小于10 每个订单订购的所有物品的数量和以及平均单价
select sum(theNumber) 所有物品量 , avg(theMoney) 平局单价 from item  
group by orderid 
having orderid<3 and avg(theMoney)<10


--3.查询平均单价小于10并且总数量大于 50 每个订单订购的所有物品数量和以及平均单价
select sum(theNumber)  所有物品量 , avg(theMoney) 平均单价 from item 
group by orderid 
having avg(theMoney)<10 and sum(theNumber)>50

--4.查询每种类别的产品分别订购了几次，例如：
--		          文具      9
--                                          体育用品  3
--                                          日常用品  3
select itemName 产品名,count(*) 分别被订购量  from item group by itemType,itemName



--5.查询每种类别的产品的订购总数量在100以上的订购总数量和平均单价
  select sum(theNumber) 订购总数量 , avg(theMoney) 平均单价 from item
  group by itemType 
  having sum(theNumber)>100


--6.查询每种产品的订购次数，订购总数量和订购的平均单价，例如：

--  产品名称   订购次数  总数量   平均单价 
--    笔           3       120       2
  select itemName 产品名称,count(*) 订购次数, sum(theNumber ) 订购总量, avg(theMoney) 平均单价 from item 
  group by itemName,theNumber,theMoney