create database Shopping
use Shopping


create table orders
(
orderID int primary key identity,
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,
orderId int references orders(orderID),
itemType varchar(20),
itemName nvarchar(15),
tueNumber int ,
theMoney int
)

insert into orderltem(orderid,itemType,itemName,tueNumber,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

--1.查询所有订单订购的所有物品数量总和
select sum(tueNumber)总数量 from orderltem 
--2.查询订单编号小于3的，平均单价小于10 每个订单订购的所有物品的数量和以及平均单价
select sum(tueNumber) 总数量,avg(theMoney)平均价 from orderltem where  orderId<3
group by orderId
having avg(theMoney)<10
--3.查询平均单价小于10并且总数量大于 50 每个订单订购的所有物品数量和以及平均单价
select sum(tueNumber) 总数量,avg(theMoney)平均价 from orderltem 
group by orderid
having avg(theMoney)<10 and sum(tueNumber)>50
--4.查询每种类别的产品分别订购了几次，例如：
--   文具      9
--   体育用品  3
--   日常用品  3
select itemType 类别 ,count(itemType) from orderltem order by itemType
--5.查询每种类别的产品的订购总数量在100以上的订购总数量和平均单价
select itemType 类别,sum(tueNumber) 总数,avg(tueNumber) 平均单价 from orderltem
group by itemType
having sum(tueNumber)>100 
--6.查询每种产品的订购次数，订购总数量和订购的平均单价，例如：
-- 产品名称   订购次数  总数量   平均单价 
-- 笔           3       120       2
select itemName 产品名称,sum(itemName) 订单次数,avg(tueNumber) from orderltem group by itemName