Create DataBase GoodsDB
go 
Use GoodsDB
go

Create Table GoodsType(
	TypeID Int Primary Key Identity(1,1) Not Null,
	TypeName NvarChar(40) Not Null
)
Create Table GoodsInfo(
	GoodsID Int Primary key  not null ,
	GoodsNmae Nvarchar(20),
	GoodsColor Nvarchar(20),
	GoodsBrand Nvarchar(20) ,
	GoodsMoney Money Not null ,
	TypeID int References GoodsType(TypeID)
)

insert into GoodsType(TypeName)
values('服装内衣'),
       ('鞋包服饰'),
	   ('手机数码');


insert into GoodsInfo(GoodsID,GoodsName,GoodsColor,GoodsBrand,GoodsMoney,TypeID)
Values (1,'提花小西装','红色','菲曼琪',300,1)
insert into GoodsInfo(GoodsID,GoodsName,GoodsColor,GoodsBrand,GoodsMoney,TypeID)
Values (2,'百搭短裤','绿色','哥弟',100,1)
insert into GoodsInfo(GoodsID,GoodsName,GoodsColor,GoodsBrand,GoodsMoney,TypeID)
Values (3,'无袖背心','白色','阿依莲',700,1)
insert into GoodsInfo(GoodsID,GoodsName,GoodsColor,GoodsBrand,GoodsMoney,TypeID)
Values (4,'低帮休闲鞋','红色','菲曼琪',900,2)
insert into GoodsInfo(GoodsID,GoodsName,GoodsColor,GoodsBrand,GoodsMoney,TypeID)
Values (5,'中跟单鞋','绿色','哥弟',400,2)
insert into GoodsInfo(GoodsID,GoodsName,GoodsColor,GoodsBrand,GoodsMoney,TypeID)
Values (6,'平底鞋','白色','阿依莲',200,2)
insert into GoodsInfo(GoodsID,GoodsName,GoodsColor,GoodsBrand,GoodsMoney,TypeID)
Values (7,'迷你照相机','红色','尼康',500,3)
insert into GoodsInfo(GoodsID,GoodsName,GoodsColor,GoodsBrand,GoodsMoney,TypeID)
Values (8,'硬盘','黑色','希捷',600,3)
insert into GoodsInfo(GoodsID,GoodsName,GoodsColor,GoodsBrand,GoodsMoney,TypeID)
Values (9,'显卡','黑色','技嘉',800,3)

--3、查询价格最贵的商品名称，商品颜色和商品价格，要求使用别名显示列名
Select  GoodsNmae 商品名字, Max(GoodsMoney) 最贵商品, GoodsColor 商品颜色 
from GoodsInfo  
group by GoodsColor,GoodsName 
order by  Max(GoodsMoney) desc;
--4、按商品类型编号分组查询商品最高价格，最低价格和平均价格，要求使用别名显示列名
Select  TypeID 商品类型编号 ,Max(GoodsMoney) 最高价格,Min(GoodsMoney) 最低价格,AVG(GoodsMoney) 平均价格 
from GoodsInfo   group by TypeID;
--5、查询商品信息所有列，要求商品颜色为红色，价格在300~600之间
Select * from GoodsInfo where GoodsColor='红色' and GoodsMoney between 300 and 600;