数据库学习笔记及课后作业

-- 2. 参照ER图计算不同客户的购买金额（写出连接过程）
select username,sum(price*amount) 购买金额
from orderinfo left join userinfo on orderinfo.userid = userinfo.userid
group by orderinfo.userid;

-- 3. 参照ER图计算不同产品的销售数量（写出连接过程）
select goodsname,sum(amount)
from orderinfo left join goodsinfo on orderinfo.goodsid=goodsinfo.goods_id
group by goodsid;

-- 4. 参照ER图计算不同区域的订单数量（写出连接过程）
select regionname,sum(amount)
from orderinfo left join regioninfo on orderinfo.regionid=regioninfo.regionid
group by orderinfo.regionid;

-- 5. 参照ER图计算不同品牌的销售利润（写出连接过程）
select typename,sum((price-goodsprice)*amount)
from orderinfo,goodsinfo,brandtype
where goodsinfo.goods_id=orderinfo.goodsid and goodsinfo.typeid=brandtype.typeid
group by goodsinfo.typeid;

-- 6. 参照ER图计算不同区域下不同客户的购买金额（写出连接过程）
select regionname,username,sum(price*amount)
from orderinfo left join regioninfo on orderinfo.regionid=regioninfo.regionid
left join userinfo on orderinfo.userid=userinfo.userid
group by orderinfo.regionid,orderinfo.userid;

-- 7. 参照ER图计算不同销售人员下不同区域的销售数量（写出连接过程）
select s_name,regionname,sum(amount)
from orderinfo left join regioninfo on orderinfo.regionid=regioninfo.regionid
left join salesinfo on orderinfo.s_id=salesinfo.s_id
group by orderinfo.regionid,orderinfo.s_id;