--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'*/