--2. 1 Cho biet danh sach cac doi tac cung cap hang cho cong ty.
/*select *
from Nhacungcap*/
--2. 2 Ma hang, ten hang va so luong cua cac mat hang hien co trong cong ty.
/*select Mahang, Tenhang, Soluong
from Mathang*/
--2. 3 Ho ten va dia chi va nam bat dau lam viec cua cac nhan vien trong cong ty.
/*select Ho+''+Ten as 'Ho va ten', Diachi, year(Ngaylamviec)
from Nhanvien*/
--2. 4 Dia chi va dien thoai cua nha cung cap co ten giao dich la VINAMILK la gi?
/*select diachi, dienthoai, tengiaodich
from nhacungcap
where tengiaodich like'VINAMILK'*/
--2. 5 Cho biet ma va ten cac mat hang co gia lon hon 100000 va so luong it hon 50.
/*select Mahang, Tenhang, Giahang, Soluong
from Mathang
where Giahang>100000 and Soluong<50 */
--2. 6 Cho biet mat hang trong cong ty do ai cung cap.
/*select Mathang.Tenhang 'Ten mat hang', Nhacungcap.Tencongty 'Ten nha cung cap'
from Mathang, Nhacungcap
where Mathang.Macongty=Nhacungcap.Macongty */
--2. 7 Con ty Viet Tien da cung cap nhung mat hang nao?
/*select Nhacungcap.Tencongty 'Ten cong ty', Mathang.Tenhang 'Ten mat hang'
from Nhacungcap, Mathang
where Nhacungcap.Macongty=Mathang.Macongty and Nhacungcap.Tencongty ='Viet Tien'*/
--2. 8 Loai hang thuc pham do nhung cong ty nao cung cap va dia chi cua cac cong ty do la gi?.
/*select Loaihang.Tenloaihang 'Ten loai hang', Nhacungcap.Tencongty 'Ten nha cung cap', Nhacungcap.Diachi 'Dia chi nha cung cap'
from Loaihang, Nhacungcap,Mathang
where Nhacungcap.Macongty=Mathang.Macongty and Loaihang.Maloaihang=Mathang.Maloaihang and Loaihang.Tenloaihang like'Thuc pham'*/
--2. 9 Nhung khach hang nao (ten giao dich) da dat mua hang sua hop xyz cua cong ty?.
/*Select Nhacungcap.Tengiaodich 'Khach hang', Mathang.Tenhang
from Nhacungcap, Mathang
where Nhacungcap.Macongty=Mathang.Macongty and Mathang.Tenhang='Sua hop XYZ'*/
--2. 10 Don dat hang so 1 do ai dat va do nhan vien nao lap, thoi gian va dia diem giao hang la o dau?
/*select dondathang.sohoadon 'So hoa don',khachhang.tencongty 'Ten khach hang', nhanvien.ten 'Ten nhan vien lap hoa don', dondathang.ngaydathang, dondathang.ngaygiaohang, dondathang.ngaychuyenhang
from dondathang, nhanvien, khachhang
where dondathang.manhanvien=nhanvien.manhanvien and dondathang.makhachhang=khachhang.makhachhang and dondathang.sohoadon='001'*/
--2. 11 Hay cho biet so tien luong ma cong ty phai tra cho moi nhan vien la bao nhieu( luong= luong co ban + phu cap).
/*select Ho+' '+Ten 'Ho va ten', luongcoban+phucap 'Luong nhan vien(VND)'
from nhanvien*/
--2. 12 Trong don dat hang so 3, dat mua nhung mat hang nao va so tien ma khach hang phai tra cho moi mat hang la bao nhieu( so tien phai tra duoc tienh theo cong thuc soluong*giaban-soluong*giaban*mucgiamgia/100)
/*select chitietdathang.sohoadon, mathang.tenhang, chitietdathang.Soluong*chitietdathang.Giaban-chitietdathang.soluong*chitietdathang.Giaban*chitietdathang.Mucgiamgia/100 'So tien khach hang phai tra cho moi mat hang'
from chitietdathang, mathang
where chitietdathang.mahang=mathang.mahang*/
--2. 13 Hay cho biet co nhung khach hang nao lai chinh la doi tac cung cap hang cho cong ty( tuc la co cung ten giao dich)
/*select khachhang.makhachhang 'Ma khach hang',khachhang.tencongty 'Ten khach hang', nhacungcap.tencongty 'Ten nha cung cap'
from khachhang, dondathang, chitietdathang,mathang, nhacungcap
where khachhang.makhachhang=dondathang.makhachhang and dondathang.sohoadon=chitietdathang.sohoadon
and chitietdathang.mahang=mathang.mahang and mathang.macongty=nhacungcap.macongty
and khachhang.tencongty = nhacungcap.tencongty */
--2. 14 Trong cong ty co nhung nhan vien nao co cung ngay sinh?
/*select manhanvien, ten, ngaysinh
from nhanvien
order by ngaysinh*/
--2. 15 Nhung don dat hang nao yeu cau giao hang ngay tai cong ty dat hang va nhung don do la cua cong ty nao?
/*select chitietdathang.sohoadon'Don dat hang',dondathang.noigiaohang'Noi dat hang', nhacungcap.diachi'Dia chi cong ty', nhacungcap.tencongty
from dondathang, chitietdathang, mathang, nhacungcap
where dondathang.sohoadon=chitietdathang.sohoadon
and chitietdathang.mahang=mathang.mahang
and mathang.macongty=nhacungcap.macongty
and dondathang.noigiaohang=nhacungcap.diachi*/
--2. 16 Cho biet ten cong ty, ten giao dich, dia chi va dien thoai cua cac khach hang va cac nha cung cap hang cho cong ty.
/*select khachhang.tencongty'Ten khach hang', khachhang.diachi'Diachi khach hang', khachhang. dienthoai 'Dien thoai khach hang',
nhacungcap.tencongty'Ten nha cung cap', nhacungcap.tengiaodich'Ten giao dich', nhacungcap.diachi'Dia chi nha cung cap',
nhacungcap.dienthoai 'Dien thoai cua nha cung cap'
from khachhang, dondathang, chitietdathang, mathang, nhacungcap
where khachhang.makhachhang=dondathang.makhachhang
and dondathang.sohoadon=chitietdathang.sohoadon
and chitietdathang.mahang=mathang.mahang
and mathang.macongty=nhacungcap.macongty*/
--2. 17 Nhung mat hang nao chua tung duoc khach hang dat mua ?
/*select mathang.mahang'Ma mat hang'
from mathang left join chitietdathang on mathang.mahang=chitietdathang.mahang
where chitietdathang.sohoadon is null*/
--2. 18 Nhung nhan vien nao cua cong ty chua tung lap bat ky mot hoa don dat hang nao?
/*select nhanvien.manhanvien, nhanvien.ten
from nhanvien left join dondathang on nhanvien.manhanvien=dondathang.manhanvien
where dondathang.sohoadon is null*/
--2. 19 Nhung nhan vien nao cua cong ty co luong co ban cao nhat ?.
/*select manhanvien' Ma nhan vien', luongcoban'Luong co ban cao nhat'
from nhanvien
where luongcoban=(select max(luongcoban) from nhanvien)*/
--2. 20 Tong so tien ma khach hang phai tra cho moi don dat hang la bao nhieu?
/*select khachhang.tencongty 'Ten khach hang', dondathang.sohoadon'So hoa don',
chitietdathang.Soluong*chitietdathang.Giaban-chitietdathang.soluong*chitietdathang.Giaban*chitietdathang.Mucgiamgia/100 'So tien khach hang phai tra cho moi don dat hang'
from khachhang, dondathang, chitietdathang
where khachhang.makhachhang=dondathang.makhachhang and dondathang.sohoadon=chitietdathang.sohoadon*/
--2. 21 Trong nam 2003, nhung mat hang nao chi duoc dat mua dung mot lan
/*select mathang.tenhang, Year(dondathang.ngaydathang) 'Nam dat hang'
from mathang, dondathang, chitietdathang
where dondathang.sohoadon=chitietdathang.sohoadon and chitietdathang.mahang=mathang.mahang
and year(dondathang.ngaydathang) like'2003'
group by mathang.tenhang, Year(dondathang.ngaydathang)
having Count(Mathang.tenhang)<2*/
--2. 22 Hay cho biet moi mot khach hang da phai bo ra bao nhieu tien de dat mua hang cua cong ty ?
/*select khachhang.tencongty 'Ten khach hang', dondathang.sohoadon'So hoa don',
chitietdathang.Soluong*chitietdathang.Giaban-chitietdathang.soluong*chitietdathang.Giaban*chitietdathang.Mucgiamgia/100 'So tien khach hang phai tra cho moi don dat hang'
from khachhang, dondathang, chitietdathang
where khachhang.makhachhang=dondathang.makhachhang and dondathang.sohoadon=chitietdathang.sohoadon*/
--2. 23 Moi nhan vien cua cong ty da lap bao nhieu don dat hang(neu nhan vien chua he lap mot hoa don nao thi cho ket qua = 0)
/*select nhanvien.manhanvien, count(dondathang.sohoadon)
from nhanvien left join dondathang on nhanvien.manhanvien=dondathang.manhanvien
group by nhanvien.manhanvien*/
--2. 24 Cho biet tong so tien hang cua cua hang thu duoc trong moi thang cua nam 2003(thoi gian tinh theo ngay dat hang)
/*select distinct Month(ngaydathang)'Thang( nam 2003)',soluong*giaban-soluong*Giaban*mucgiamgia/100'Tong tien'
from dondathang, chitietdathang
where dondathang.sohoadon=chitietdathang.sohoadon and year(ngaydathang)like'2003'
Group by Soluong*Giaban-soluong*Giaban*mucgiamgia/100,Month(dondathang.ngaydathang)*/
--2. 25 Hay cho biet tong so tien loi ma cong ty thu duoc tu moi mat hang trong nam 2003
-- So tien loi ma cong ty thu duoc = soluong*giamathang-(Soluong*giaban-soluong*Giaban*mucgiamgia/100)
/*select Tenhang 'Ten mat hang', (chitietdathang.soluong*giaban-chitietdathang.soluong*giaban*mucgiamgia/100) - chitietdathang.soluong*giamathang'Tong so tien loi nam 2003'
from mathang, chitietdathang, dondathang
where mathang.mahang=chitietdathang.mahang and dondathang.sohoadon=chitietdathang.sohoadon
and year(ngaydathang)like'2003'*/
--2. 26 Hay cho biet tong so luong hang cua moi mat hang ma cong ty da co( tong so luong hang hien co va da ban).
/*Select mahang'Ma hang', tenhang'Ten mat hang',soluong 'Tong so luong cua mat hang ma cong ty da co'
from mathang*/
--2. 27 Nhan vien nao cua cong ty ban duoc so luong hang nhieu nhat va so luong hang ban duoc cua nhung nhan vien nay la bao nhieu?
/*select distinct nhanvien.manhanvien'Ma nhan vien', ten'Ten nhan vien', sum(soluong) 'So luong hang ban duoc'
from nhanvien, dondathang, chitietdathang
where nhanvien.manhanvien=dondathang.manhanvien and dondathang.sohoadon=chitietdathang.sohoadon
group by nhanvien.manhanvien, ten, soluong*/
--2. 28 Don dat hang nao co so luong hang duoc dat mua it nhat ?
/*select dondathang.sohoadon'So hoa don', min(soluong)'So luong hang dat mua it nhat'
from dondathang, chitietdathang
where dondathang.sohoadon= chitietdathang.sohoadon
group by dondathang.sohoadon, soluong*/
--2. 29 So tien nhieu nhat ma moi khach hang da tung bo ra de dat hang trong dot dat hang la bao nhieu ?
/*select Max(soluong*giaban-soluong*giaban*mucgiamgia/100)'So tien nhieu nhat ma moi khach hang bo ra de dat hang trong don dat hang'
from khachhang, dondathang, chitietdathang
where khachhang.makhachhang=dondathang.makhachhang and dondathang.sohoadon=chitietdathang.sohoadon*/
--2. 30 Moi mot don dat hang dat mua nhung mat hang nao va tong so tien ma moi don dat hang phai tra la bao nhieu ?
/*select dondathang.sohoadon, mathang.mahang, tenhang, chitietdathang.soluong*giaban-chitietdathang.soluong*giaban*mucgiamgia/100' Tong so tien ma moi don dat hang phai tra'
from dondathang, chitietdathang, mathang
where dondathang.sohoadon=chitietdathang.sohoadon and chitietdathang.mahang=mathang.mahang*/
--2. 31 Hay cho biet moi mot loai hang bao gom nhung mat hang nao, tong so luong hang cua moi loai va tong so luong cua tat ca cac mat hang hien co trong cong ty la bao nhieu?
/*select loaihang.maloaihang, tenloaihang, tenhang, soluong
from mathang, loaihang
where mathang.maloaihang=loaihang.maloaihang*/
--2. 32 Thong ke xem trong nam 2003, moi mot mat hang trong moi thang va trong ca nam ban duoc voi so luong bao nhieu ?.
/*Select month(ngaydathang)'Thang', year(ngaydathang)'Nam',soluong 'So luong hang ban ra'
from dondathang, chitietdathang
where dondathang.sohoadon=chitietdathang.sohoadon and year(ngaydathang)like'2003'*/
Bạn đang đọc truyện trên: Truyen2U.Pro