giải bài tập CSDL3

use csdl3
select * from daily, mua
--2.a) tìm đơn giá mua trung bình của bia Sài Gòn trên tất cả các đại lí
select avg(trigia_mua/solg_mua) as dongiatrungbinhbiaSG
from mua a, daily b, hanghoa d
where a.ma_hang = d.ma_hang
and a.stt_dl = b.stt_dl
and ten_hg = 'bia saigon'


--2.b)  tìm đơn giá mua trung bình của bia Sài Gòn trên từng đại lí
-- muon group by cai gi thi tren select phai de them cai do
select ten_dl, ten_hg, avg(trigia_mua/solg_mua) as dongiatrungbinhbiaSGtrentungdaily
from mua a, daily b, hanghoa d
where a.ma_hang = d.ma_hang
and a.stt_dl = b.stt_dl
and ten_hg = 'bia saigon'
group by ten_dl, ten_hg

--cách khác
select ten_dl, avg(trigia_mua/solg_mua)
from mua a, hanghoa b, daily c
where a.ma_hang = b.ma_hang
and a.stt_dl = c.stt_dl
and ten_hg ='bia saigon'
group by ten_dl


--2.c) hiển thị số lượng tồn kho của từng mặt hàng theo tưng đại lí
select ten_dl, ten_hg, (solg_mua-solg_ban) as soluongtonkho
from mua a, ban b, daily c, hanghoa d
where b.stt_dl = c.stt_dl
and c.stt_dl = a.stt_dl
and b.ma_hang = d.ma_hang


--2.d) tìm tên, địa chỉ của đại lí có tổng trị giá mua trong 1 ngày > 700.000
select ten_dl, dchi_dl, ngay_mua, sum(trigia_mua) as trigiamua
from daily a, mua b
where a.stt_dl = b.stt_dl
group by ten_dl, dchi_dl, ngay_mua
having  sum(trigia_mua )>700000


--2.e) với mỗi đại lí,  hãy cho biết ngày nào có số lượng mặt hàng bán ra là ít nhất
select distinct ten_dl, ngay_ban, (count(c.ma_hang)) as soluongbanitnhat
from daily a, ban b, hanghoa c
where a.stt_dl = b.stt_dl
and b.ma_hang = c.ma_hang
group by ten_dl, ngay_ban

--cách khác
select ten_dl, min(solg_ban) as solg_ban, ngay_ban into #a
from daily, ban
where daily.stt_dl = ban.stt_dl
group by ten_dl, ngay_ban
select * from #a

--cách khác
select ten_dl, ngay_ban, ma_hang
from ban a, daily b
where a.stt_dl = b.stt_dl
order by ten_dl, ngay_ban

--Thay Long: vào ngày nào, đại lí nào bán ra được nhiều mặt hàng
select ten_dl, ngay_ban, count(distinct ma_hang) as SoLGMH into #tam
from ban a, daily b
where a.stt_dl = b.stt_dl
group by ten_dl, ngay_ban
order by ten_dl, ngay_ban

select ten_dl,min(solgmh) as SoLGMin into tam2
from #tam
group by ten_dl

select a.ten_dl, a.ngay_ban, solgmin
from #tam a, #tam2 b
where a.ten_dl = b.ten_dl and a.SoLGMH = b.SoLGMin

--2.f)tìm tên và địa chỉ những đại lí có tổng trị giá bán cao nhất
select ten_dl, dchi_dl, sum(trigia_ban) as tonggiatribancaonhat
from daily a, ban b
where a.stt_dl = b.stt_dl
group by ten_dl, dchi_dl
having sum(trigia_ban)>all (select max(trigia_ban) from ban)

--cách khác
drop table #a
select ten_dl, dchi_dl, sum(trigia_ban) as tonggiatribancaonhat into #a
from daily a, ban b
where a.stt_dl = b.stt_dl
group by ten_dl, dchi_dl
select * from #a where tonggiatribancaonhat=(select Max(tonggiatribancaonhat) from #a)


--2.g) tính tổng số tiền mà mỗi đại lí đã chi để mua hàng theo từng tháng
select ten_dl, dchi_dl, sum(trigia_mua*solg_mua) as tongsotiendachidemuahangtheohangthang
from daily a, mua c
where  a.stt_dl = c.stt_dl
group by ten_dl, dchi_dl


--2.h) tìm tên và tổng số lượng bán ra của các mặt hàng đã bán được với số lượng nhiều nhất cả từng đại lí
select TEN_DL, TEN_HG,SUM(SOLG_BAN)AS SO_LUONG_BAN
FROM DAILY A, HANGHOA B, BAN C
WHERE A.STT_DL=C.STT_DL
AND B.MA_HANG=C.MA_HANG
GROUP BY TEN_DL,TEN_HG
HAVING SUM(SOLG_BAN) >ALL (SELECT MAX(SOLG_BAN) FROM BAN)


--2.i) cho biết tổng số món hàng mà mỗi đại lí kinh doanh (mua và bán)
SELECT   TEN_DL,COUNT(A.MA_HANG)AS TONG_SO_MON_HANG
FROM MUA A, BAN B, HANGHOA C, DAILY D
WHERE A.MA_HANG=B.MA_HANG
AND A.MA_HANG=C.MA_HANG
AND A.STT_DL=D.STT_DL
GROUP BY TEN_DL


--2.j) tìm tên đại lí đã mua vào mặt hàng bia HEINEKEN với đơn giá mua là cao nhất
SELECT TEN_DL,TEN_HG, max(TRIGIA_MUA/SOLG_MUA)AS DONGIA
FROM DAILY A, MUA B,HANGHOA C
WHERE A.STT_DL=B.STT_DL
AND B.MA_HANG=C.MA_HANG
AND TEN_HG='BIA HEINEKEN'
GROUP BY TEN_DL,TEN_HG,TRIGIA_MUA/SOLG_MUA