SQL sever 数据库统计查询和组合查询

商品（编号，品名，进价，库存，售价，厂商编号） 
顾客（卡号，姓名，电话，积分）
厂商（编号，厂址，名称、电话）
销售（顾客卡号，商品编号，数量，日期）
根据上面基本表的信息完成下列查询。
1 查询积分最高的顾客姓名
2 查询销售数量1000以上的商品的厂商电话。
3 查询生产商品5种以上的厂商信息。
4 查询没有顾客买的商品信息。

建表语句

create table [dbo].[Guke]
(Gno char(10) primary key,
Gname char(20),
G_tel smallint,
jifen smallint
);
create table [dbo].[Changshang]
(Cno char(10)primary key,
addre char(40),
Cname char(20),
C_tel smallint
);

create table [dbo].[Shangpin]
(Sno char(10) primary key,
Sname char(10),
jin_jia smallint,
kucun smallint,
shou_jia smallint,
Cno char(10),
foreign key(Cno)references Changshang(Cno)


);


create table [dbo].[Xiaoshou]
(Gno char(10),
Sno char(10),
shuliang smallint,
X_time char(20),
primary key(Gno,Sno),
foreign key(Sno)references Shangpin(Sno),
foreign key(Gno)references Guke(Gno)
);

--查询积分最高的客户姓名

--方法1
SELECT Gname
FROM Guke
WHERE jifen = ( SELECT MAX(jifen)
FROM Guke
)
------------------------ 
--方法2 
SELECT TOP 1
Gname
FROM Guke
ORDER BY jifen DESC 
--------------------------------------------------
--查询销售数量1000以上的商品的厂商电话
SELECT DISTINCT
C.C_tel
FROM ( SELECT Sno ,
SUM(shuliang) AS shuliang
FROM Xiaoshou
GROUP BY Sno
) A
LEFT JOIN Shangpin B ON A.Sno = B.Sno
LEFT JOIN Changshang C ON B.Cno = C.Cno
WHERE A.shuliang > 1000
--------------------------------------------------
--查询生产商品5种以上的厂商信息
SELECT B.*
FROM Shangpin A
LEFT JOIN Changshang B ON A.Cno = B.Cno
GROUP BY B.Cno ,
B.addre ,
B.Cname ,
B.C_tel
HAVING COUNT(B.Cno) > 5
--------------------------------------------------
--查询没有顾客买的商品和购买数量少于3个的商品
--方法1
SELECT DISTINCT
A.*
FROM Shangpin A
LEFT JOIN ( SELECT Sno ,
SUM(shuliang) AS shuliang
FROM Xiaoshou
GROUP BY Sno
) B ON A.Sno = B.Sno
WHERE  B.shuliang IS NULL
-------------------------
--方法2
SELECT A.*
FROM Shangpin A
WHERE NOT EXISTS (
SELECT B.Sno
FROM Xiaoshou B
WHERE B.Sno = A.Sno
GROUP BY B.Sno
HAVING SUM(shuliang) !=0 
)