SQLsever数据库实验（二）

--16．查找每个采购员和每个供应商签订的合同的总金额，要求显示采购员姓名、供应商名称、和签订合同的总金额
select a.ename, c.sname, sum(b.cgmoney)
from employee a, stock b, supplier c
where a.etype=2 and a.eid=b.eid and b.sid=c.sid
group by a.ename, c.sname

--15．请使用左连接完成以下查询：查找每一个销售人员销售的商品的详细信息，要求显示销售人员姓名、销售单ID、客户姓名、商品名称、销售数量、和销售单价
select a.ename, b.said, d.cuname, e.cname, c.sdnumber, c.sdprice
from employee a
left outer join sale b on a.eid=b.eid
left outer join saledetail c on b.said=c.said
left outer join customer d on b.cuid=d.cuid
left outer join commodity e on e.cid=c.cid
where a.etype=3
order by b.said

--14．查找在2006年各个客户购买商品的总金额，要求结果按照购买商品的总金额降序排序
select b.cuid,sum(samoney)
from sale a,customer b
where a.cuid=b.cuid and year(sadate)=2006
group by b.cuid
order by sum(samoney)desc

--13．查找没有供应任何一类商品的供应商的名字
select sname
from supplier a
where not exists
( select a.sid from supplying b
where a.sid=b.sid)

--12．汇总由姓名为‘刘明’的采购员在2005年采购的各类商品的数量
select ename,sdnumber
from employee a,stockdetail b,stock c
where a.eid=c.eid and b.cgid=c.cgid
and year(cgdate)=2005
and ename='刘明'
group by sdnumber,ename

--11.汇总由姓名为‘刘明’的采购员在2004年采购的‘数码相机’的总金额
select b.ename,d.cname,sum(cgmoney)
from stock a,employee b,stockdetail c,commodity d
where a.eid=b.eid and c.cid=d.cid and a.cgid=c.cgid
and year(cgdate)=2005 and ename='刘明' and cname='数码相机'
group by b.ename,d.cname

--10．查找销售员‘王良’在2005年签订的所有销售合同中每一类商品的总金额
select distinct sum(samoney),c.eid,e.cname
from saledetail a,sale b,employee c,commodity e
where a.said=b.said and c.eid=b.eid and e.cid=a.cid
group by a.cid,c.eid,e.cname

--9．查询所有供应‘鲜橙多’的供应商的最低报价
select cname,min(price)
from supplying a,commodity b
where a.cid=b.cid and cname='鲜橙多'
group by a.cid,cname

--8．查找销售员‘王良’在2005年签订的销售合同的详细信息
select c.said,cid,sdnumber,sdprice,sddiscount
from employee a,sale b,saledetail c
where a.eid=b.eid and b.said=c.said
and ename='王良' and year (sadate)=2005

--7．查找购买了编号为‘A001’的供应商供应的商品的客户名称
select CUname from customer
where CUid in
( select CUid from sale where SAid in
    ( select SAid from saleDetail where Cid in
        (select CId from Supplying where Sid='A001'  )
    )
)

--6.请使用内连接完成如下查询：查找每一个供应商供应的商品的种类，要求显示供应商名称，供应的商品的种类
select sname,count(cid)
from supplying inner join supplier
on supplying.sid=supplier.sid
group by supplier.sid,sname

--5．使用左外连接完成如下查询：查找所有客户购买的商品详细信息，要求显示客户名称，商品名称，销售数量，商品单价，没有购买商品的客户也要显示
select CUname,Cname,SDnumber,SDprice
FROM customer LEFT JOIN sale           ON customer.CUid=sale.CUid
              LEFT JOIN saleDetail     ON sale.SAid=saleDetail.SAid
              LEFT JOIN commodity      ON saleDetail.Cid=commodity.cid

--4．使用左外连接完成如下查询：要求查找所有采购员签订的采购合同的详细信息
select *
from  employee ,stock
where stock.Eid=employee.Eid

--第二种方法
select *
from  employee left join stock
on stock.Eid=employee.Eid

--3.使用关键字IN完成查询，要求查找与姓名为‘李云’的采购员签订采购单的供应商的名称
select sname
from supplier where sname in
(select sname from stock a,supplier b,employee c
where a.sid=b.sid and a.eid=c.eid and ename='李云')

--2．使用连接查询查找与姓名为‘李云’的采购员签订采购单的供应商的名称
select sname
from stock a,supplier b,Employee c
where a.sid=b.sid and a.eid=c.eid and ename='李云'

--1．查找每一个供应商供应的商品的名称，要求显示供应商名称和商品名称
select sname,cname
from supplier,supplying,commodity
where supplier.sid=supplying.sid
    and supplying.cid=commodity.cid