create database ABC


 create table orders
 (
 orderld int identity primary key,
 orderDate datetime 
 )
 
 insert into orders(orderDate)
 values ('2008-01-12'),
        ('2008-02-10'),
        ('2008-02-15'),
        ('2008-03-10')

 select * from orders
 create table orderltem
 (
itemID int identity ,
orderld int references orders(orderld),
itemType nvarchar(20),
itemName nvarchar(20),
theNumber int ,
theMoney int 
 )
insert into orderltem(orderld,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)-
select * from orderltem
go
select sum(theNumber)总数量 from orderltem
go
--2.查询订单编号小于3的，平均单价小于10 每个订单订购的所有物品的数量和以及平均单价
select sum(theNumber)总数量,avg(theMoney)订购单价 from orderltem 
group by orderld
having orderld<3 and avg(theMoney)<10
go
--3.查询平均单价小于10并且总数量大于 50 每个订单订购的所有物品数量和以及平均单价
    select orderld 订单,avg(theMoney) 平均单价,sum(thenumber) 总数量 from orderltem
	group by orderld having avg(themoney)<10 and sum(thenumber)>50
go
	select itemtype 类别,count(itemtype) 次数 from orderltem group by itemtype
go
	select itemtype 类别,sum(thenumber) 订购总数,avg(thenumber) 平均单价 from orderltem group by itemtype having sum(thenumber)>100
go
	select itemname 产品名称,count(itemname) 订购次数,sum(thenumber) 总数量,avg(thenumber) 平均单价 from orderltem group by itemname