use master

go

create database DD  --建立数据库
on primary
(
name='D:\home',
filename='D:\home.mdf',
size=5MB,
filegrowth=1MB,
maxsize=5MB
)
log on
(
name='D:\home_log',
filename='D:\home_log.ldf',
size=5MB,
filegrowth=1MB,
maxsize=5MB
)
go

use DD 
create table orders --建立订单表
(
orderId int primary key,
orderDate datetime
)
go

create table orderltem
(
ltemiD int primary key,
orderId int,
itemType varchar(15),
itemName varchar(20),
theNumber int,
theMoney int
)
go 

insert orders(orderId,orderDate)
select 1,'2008-01-12 00:00:00.000' union
select 2,'2008-02-10 00:00:00.000' union
select 3,'2008-02-15 00:00:00.000' union
select 4,'2008-03-10 00:00:00.000'

select * from orders

insert into orderltem(ltemiD,orderId,itemType,itemName,theNumber,theMoney)
select 1,1,'文具','笔',72,2 union
select 2,1,'文具','尺',10,1 union
select 3,1,'体育用品','篮球',1,56 union
select 4,2,'文具','笔',36,2 union
select 5,2,'文具','固体胶',20,3 union
select 6,2,'日常用品','透明胶',2,1 union
select 7,2,'体育用品','羽毛球',20,3 union
select 8,3,'文具','订书机',20,3 union
select 9,3,'文具','丁书针',10,3 union
select 10,3,'文具','裁纸刀',5,5 union
select 11,4,'文具','笔',20,2 union
select 12,4,'文具','信纸',50,1 union
select 13,4,'日常用品','毛巾',4,5 union
select 14,4,'日常用品','透明胶',30,1 union 
select 15,5,'体育用品','羽毛球',20,3

select * from orderltem


----1.查询所有订单订购的所有物品数量总和

select sum(theNumber)数量总和 from orderltem 

--2.查询订单编号小于3的，平均单价小于10 每个订单订购的所有物品的数量和以及平均单价

select (orderId)订单编号,avg(theMoney)平均单价,count(theNumber)数量 from orderltem group by orderId having orderId<3 and avg(theMoney)<10

--3.查询平均单价小于10并且总数量大于 50 每个订单订购的所有物品数量和以及平均单价

select (orderId)订单编号,count(theNumber)数量总数,avg(theMoney)平均单价 from orderltem group by orderId having orderId>50 and avg(theMoney)<10

--4.查询每种类别的产品分别订购了几次，例如：
--					  文具      9
--                    体育用品  3
--                    日常用品  3

select (itemType)类别,count(*)订购次数 from orderltem group by itemType

--5.查询每种类别的产品的订购总数量在100以上的订购总数量和平均单价

select (itemType)类别,sum(theNumber)订购总数量,avg(theMoney)平均单价 from orderltem group by itemType having sum(theNumber)>100


--6.查询每种产品的订购次数，订购总数量和订购的平均单价，例如：
 
select (itemType)类别,count(itemType)订购次数,sum(theNumber)总数量,avg(theMoney)平均单价 from orderltem group by itemType

--  产品名称   订购次数  总数量   平均单价 
--    笔           3       120       2