create database ordeInfo;




create table orders(
orderId int primary key identity(1,1),	
orderDate datetime	
);
go


create table orderltem(
ltemiD int primary key identity(1,1),	
orderID int references orders(orderId), 
itemType nvarchar(10),
itemName nvarchar(10),
theNumber int,
theMoney int 
);
go


insert into orders(orderDate) values('2008-01-12');
insert into orders(orderDate) values('2008-02-10');
insert into orders(orderDate) values('2008-02-15');
insert into orders(orderDate) values('2008-03-10')
go



insert into orderltem(orderID,itemType,itemName,theNumber,theMoney) values(1,'文具','笔',72,2);
insert into orderltem(orderID,itemType,itemName,theNumber,theMoney) values(1,'文具','尺',10,1);
insert into orderltem(orderID,itemType,itemName,theNumber,theMoney) values(1,'体育用品','篮球',1,56);
insert into orderltem(orderID,itemType,itemName,theNumber,theMoney) values(2,'文具','笔',36,2);
insert into orderltem(orderID,itemType,itemName,theNumber,theMoney) values(2,'文具','固体胶',20,3);
insert into orderltem(orderID,itemType,itemName,theNumber,theMoney) values(2,'日常用品','透明胶',2,1);
insert into orderltem(orderID,itemType,itemName,theNumber,theMoney) values(2,'体育用品','羽毛球',20,3);
insert into orderltem(orderID,itemType,itemName,theNumber,theMoney) values(3,'文具','订书机',20,3);
insert into orderltem(orderID,itemType,itemName,theNumber,theMoney) values(3,'文具','订书针',10,3);
insert into orderltem(orderID,itemType,itemName,theNumber,theMoney) values(3,'文具','裁纸刀',5,5);
insert into orderltem(orderID,itemType,itemName,theNumber,theMoney) values(4,'文具','笔',20,2);
insert into orderltem(orderID,itemType,itemName,theNumber,theMoney) values(4,'文具','信纸',50,1);
insert into orderltem(orderID,itemType,itemName,theNumber,theMoney) values(4,'日常用品','毛巾',4,5);
insert into orderltem(orderID,itemType,itemName,theNumber,theMoney) values(4,'日常用品','透明胶',30,1);
insert into orderltem(orderID,itemType,itemName,theNumber,theMoney) values(4,'体育用品','羽毛球',20,3);
go

select sum(thenumber)数量总和 from orderltem

select sum(thenumber)数量总和,avg(theMoney)平均单价 from orderltem
where orderID<3
group by orderId
having avg(theMoney)<10

select sum(thenumber)数量总和,avg(themoney)平均单价 from orderltem
group by orderID
having avg(theMoney)<10 and sum(thenumber)>50

select itemType,count(*) from orderltem 
group by itemType

select sum(thenumber)订购总量,avg(themoney)平均单价 from orderltem
group by itemType
having sum(thenumber)>100

select itemname 产品名称,count(itemname)订购次数,sum(thenumber)订购数量,avg(themoney)平均单价 from orderltem
group by itemname