use QLBanHang

--1. DS cac doi tac cung cap hang cho cong ty

Select MaCongTy, TenCongTy

from NhaCungCap;

--2. Ma hang, Ten Hang, So Luong cac mat hang hien co

Select MaHang, TenHang, SoLuong

from MaHang;

--3.Ho Ten, Dia Chi, Nam bat dau lam viec

Select HoDem +' '+Ten as "Ho Ten" , DiaChi, year(NgaySinh) as "Nam Bat Dau"

from NhanVien;

--4. Dia Chi, Dien Thoai cua nha NhaCungCap co TenGiaoDich la vinamilk

Select DiaChi, DienThoai

from NhaCungCap

where TenGiaoDich='Vinamilk';

--5.Cho biet MaHang, TenHang co GiaHang>10000 and SoLuong<50

Select ChiTietDatHang.MaHang, MaHang.TenHang

from MaHang, DonDatHang, ChiTietDatHang

Where MaHang.GiaHang>100000 and (MaHang.SoLuong-ChiTietDatHang.SoLuong)<50

and ChiTietDatHang.SoHoaDon=DonDatHang.SoHoaDon

and ChiTietDatHang.MaHang=MaHang.MaHang

--6.Cho biet moi mat hang trong cong ty do ai cung cap

Select MaHang.MaHang, MaHang.TenHang, MaHang.MaCongTy, NhaCungCap.TenCongTy

from MaHang, NhaCungCap

where MaHang.MaCongTy=NhaCungCap.MaCongTy

--7. Cong Ty Viet Tien da cung cap nhung mat hang nao

Select MaHang, TenHang

from MaHang

where MaCongTy='KH003'

--8. LoaiHang ThucPham do nhung cong ty nao cung cap va dia chi cua cac cong ty

Select LoaiHang.TenLoaiHang, MaHang.MaCongTy, NhaCungCap.TenCongTy, NhaCungCap.DiaChi

from LoaiHang, MaHang, NhaCungCap

where LoaiHang.MaLoaiHang='LH002' and

LoaiHang.MaLoaiHang=MaHang.MaLoaiHang and

MaHang.MaCongTy=NhaCungCap.MaCongTy

--9. Nhung khach hang nao(TenGiaoDich) da dat mua su Redielac

Select TenGiaoDich

From KhachHang, DonDatHang, ChiTietDatHang

where ChiTietDatHang.MaHang = 'MH005' and

ChiTietDatHang.SoHoaDon=DonDatHang.SoHoaDon and

DonDatHang.MaKhachHang=KhachHang.MaKhachHang

--10. DonDatHang so 1 do ai dat va do nhan vien nao lap, thoi gian dia diem

Select DonDatHang.MaKhachHang,TenCongTy, MaNhanVien, NgayDatHang, NoiGiaoHang

From DonDatHang, KhachHang

where SoHoaDon=1 and

DonDatHang.MaKhachHang=KhachHang.MaKhachHang

--11. TienLuong tra cho moi nhan vien (luong = HeSoLuong*540000+PhuCap)

Select MaNhanVien,(HeSoLuong*540000+PhuCap) as "Luong"

From NhanVien

--12. DonDatHang so 3 mua nhung mat hang nao va so tien phai tra (SoLuong*GiaBan-SoLuong*GiaBan*MucGiamGia/100)

Select ChiTietDatHang.MaHang,MaHang.TenHang ,(ChiTietDatHang.SoLuong*ChiTietDatHang.GiaBan - ChiTietDatHang.SoLuong*ChiTietDatHang.GiaBan*MucGiamGia) as "Tien"

From DonDatHang, ChiTietDatHang, MaHang

where ChiTietDatHang.SoHoaDon=1 and

ChiTietDatHang.SoHoaDon=DonDatHang.SoHoaDon and

ChiTietDatHang.MaHang=MaHang.MaHang

--13. Nhung khach hang nao lai chinh la doi tac cung cap cua cong ty

Select KhachHang.MaKhachHang

From KhachHang, NhaCungCap

where KhachHang.TenGiaoDich=NhaCungCap.TenGiaoDich

--14. Trong CongTy co nhung nhan vien nao cung ngay sinh

Select a.manhanvien, a.ngaysinh

From NhanVien a, nhanvien b

where a.manhanvien<>b.manhanvien

And a.ngaysinh=b.ngaysinh

--15. Don hang yeu cau giao hang tai cong ty va cua cong ty nao

Select SoHoaDon, KhachHang.TenCongTy

From DonDatHang, KhachHang

where NoiGiaoHang='tai cong ty' and

DonDatHang.MaKhachHang=KhachHang.MaKhachHang

--16. Cho biet............... cua cac khach hang va cac nha cung cap hang cho cong ty

Select KhachHang.TenCongTy, KhachHang.TenGiaoDich, KhachHang.DiaChi, KhachHang.DienThoai, NhaCungCap.TenCongTy, NhaCungCap.TenGiaoDich, NhaCungCap.DiaChi, NhaCungCap.DienThoai

From KhachHang, NhaCungCap, ChiTietDatHang, MaHang, DonDatHang

where

ChiTietDatHang.SoHoaDon=DonDatHang.SoHoaDon

and ChiTietDatHang.MaHang=MaHang.MaHang

and MaHang.MaCongTy=NhaCungCap.MaCongTy

and DonDatHang.MaKhachHang=KhachHang.MaKhachHang

--17. Nhung mat hang chua tung duoc dat mua

Select MaHang.TenHang, MaHang.MaHang

From ChiTietDatHang Right Outer Join MaHang

On MaHang.MaHang = ChiTietDatHang.MaHang

Where (ChiTietDatHang.MaHang is Null)

--17. Sua cau 17 dung SubQuery

Select mahang,tenhang

From MaHang

Where not exists

(Select mahang

From chitietdathang

Where chitietdathang.mahang=mahang.mahang

--18. Nhan vien chua tung lap bat kia hoa don nao

Select NhanVien.HoDem, Ten, NhanVien.MaNhanVien

From NhanVien Left Outer Join DonDatHang

On NhanVien.MaNhanVien=DonDatHang.MaNhanVien

where (DonDatHang.MaNhanVien is null)

--18.Sua cau 18

Select Manhanvien, hodem+' '+ ten as "Ho Ten"

From NhanVien

Where not exists

(Select *

From dondathang

Where nhanvien.manhanvien=dondathang.manhanvien)

--19. Nhan Vien co luong cao nhat

Select MaNhanVien, (HeSoLuong*540000) as "Luong Co Ban"

From NhanVien

Order By (HeSoLuong*540000) Desc

--19. Sua cau 19

Select MaNhanVien, (HeSoLuong*540000) as "Luong Co Ban"

From NhanVien

Where (HeSoLuong*540000)=

(Select max(HeSoLuong*540000)

From Nhanvien)

--20. Tong so tien khach hang tra cho moi don dat hang

Select ChiTietDatHang.SoHoaDon, sum(ChiTietDatHang.GiaBan*SoLuong-GiaBan*SoLuong*MucGiamGia) as "Tien"

From ChiTietDatHang, DonDatHang

Where ChiTietDatHang.SoHoaDon=DonDatHang.SoHoaDon

Group By ChiTietDatHang.SoHoaDon

--21.Trong nam 2008 nhung mat hang chi duoc mua dung 1 lan

Select ChiTietDatHang.MaHang, year(DonDatHang.NgayDatHang) as "Nam Dat Hang"

From ChiTietDatHang, DonDatHang

Where ChiTietDatHang.SoHoaDon=DonDatHang.SoHoaDon and

year(DonDatHang.NgayDatHang)=2008

Group By ChiTietDatHang.MaHang, year(DonDatHang.NgayDatHang)

HaVing Count(ChiTietDatHang.MaHang)=1

Select mahang

from chitietdathang

where mahang=any

(select mahang

from chitietdathang,dondathang

where chitietdathang.sohoadon=dondathang.sohoadon

and year(ngaydathang)=2008

group by mahang

having count(mahang)=1)

--22. Moi KhachHang bo bao nhieu tien ra de mua hang cua NhaCungCap

Select DonDatHang.MaKhachHang, sum( ChiTietDatHang.GiaBan*ChiTietDatHang.SoLuong-GiaBan*SoLuong*MucGiamGia)

From DonDatHang, ChiTietDatHang, KhachHang

Where ChiTietDatHang.SoHoaDon = DonDatHang.SoHoaDon

and DonDatHang.MaKhachHang=KhachHang.MaKhachHang

Group By DonDatHang.MaKhachHang

--23. Moi nhan vien lap bao nhieu don dat hang

Select DonDatHang.MaNhanVien, Count(SoHoaDon) as "So Luong Don Dat Hang"

From DonDatHang right Outer Join NhanVien

On NhanVien.MaNhanVien = DonDatHang.MaNhanVien

Group By DonDatHang.MaNhanVien

--24. Tong so tien moi cua hang thu duoc

Select NgayDatHang,sum(ChiTietDatHang.SoLuong*GiaBan - SoLuong*GiaBan*MucGiamGia) as "Tien"

From DonDatHang, ChiTietDatHang

Where DonDatHang.SoHoaDon=ChiTietDatHang.SoHoaDon

and year(NgayDatHang)=2008

Group By NgayDatHang

--25. Tien lai cua moi MatHang

Select ChiTietDatHang.MaHang, Sum(ChiTietDatHang.SoLuong * ( (1-MucGiamGia)*GiaBan - GiaHang) )

From ChiTietDatHang, DonDatHang, MaHang

Where ChiTietDatHang.MaHang=MaHang.MaHang

and ChiTietDatHang.SoHoaDon=DonDatHang.SoHoaDon

Group By ChiTietDatHang.MaHang

--26. Tong so luong hang moi mat hang ma cong ty co (Hien co va da ban)

Select ChiTietDatHang.MaHang, sum(ChiTietDatHang.SoLuong) as "So Luong Da Ban", Sum( (MaHang.SoLuong) - (ChiTietDatHang.SoLuong) ) as "So Luong Hien Cos"

From ChiTietDatHang, DonDatHang, MaHang

Where ChiTietDatHang.SoHoaDon=DonDatHang.SoHoaDon

and ChiTietDatHang.MaHang=MaHang.MaHang

Group By ChiTietDatHang.MaHang

--27. Nhan Vien Ban duoc so luong hang nhieu nhat va ban duoc bao nhieu

Select DonDatHang.MaNhanVien, sum(ChiTietDatHang.SoLuong) as "So Luong"

From ChiTietDatHang, DonDatHang, NhanVien

Where ChiTietDatHang.SoHoaDon=DonDatHang.SoHoaDon

and DonDatHang.MaNhanVien=NhanVien.MaNhanVien

Group By DonDatHang.MaNhanVien

Order By sum(ChiTietDatHang.SoLuong) Desc

--28. DonDatHang co so luong mua it nhat

Select DonDatHang.SoHoaDon, sum(ChiTietDatHang.SoLuong)

From DonDatHang, ChiTietDatHang

Where DonDatHang.SoHoaDon=ChiTietDatHang.SoHoaDon

Group By DonDatHang.SoHoaDon

Order By sum(ChiTietDatHang.SoLuong)

--29. So tien nhieu nhat ma moi khach hang da tung bo ra de dat hang trong cac don dat hang

Select DonDatHang.MaKhachHang, sum(ChiTietDatHang.SoLuong*GiaBan) as "Tien"

From KhachHang, ChiTietDatHang, DonDatHang

where ChiTietDatHang.SoHoaDon=DonDatHang.SoHoaDon

and DonDatHang.MaKhachHang=KhachHang.MaKhachHang

Group By DonDatHang.MaKhachHang

Order By sum(ChiTietDatHang.SoLuong*GiaBan) desc

--30. Moi 1 DonDatHang mua nhung MaHang nao va tong so tien phai tra cho moi don dat hang

Select ChiTietDatHang.SoHoaDon, ChiTietDatHang.MaHang

From ChiTietDatHang, MaHang

Where ChiTietDatHang.MaHang=MaHang.MaHang

/*=======================================UPDATE=======================================*/

/*====================================================================================*/

--37.Tang luong len 1.5 lan cho nhung nhan vien ban duoc hang >100 trong nam 2009

Update NhanVien

Set Phucap=PhuCap*1.5

Where manhanvien= any (

Select manhanvien

From dondathang, chitietdathang

Where dondathang.sohoadon=chitietdathang.sohoadon

and Year(Ngaydathang)=2009

Group by manhanvien

Having sum(chitietdathang.soluong)>100)

--38. Tang phu cap len bang 50% luong cho nhan vien ban duoc hang nhieu nhat

--Luong=(54000*HeSoLuong)+PhuCap

Update Nhanvien

Set PhuCap=( (54000*hesoluong)+phucap )*0.5

Where Manhanvien = any (

Select DonDatHang.manhanvien

From nhanvien, dondathang, chitietdathang

Where dondathang.manhanvien=nhanvien.manhanvien

and chitietdathang.sohoadon=dondathang.sohoadon

and chitietdathang.soluong=(

Select Max(chitietdathang.soluong)

From chitietdathang) )

Update Nhanvien

Set phucap=phucap*2

Where manhanvien in (

Select manhanvien from dondathang join chitietdathang on

dondathang.sohoadon=chitietdathang.sohoadon

Group By Manhanvien

Having Sum(soluong)>=all(Select sum(soluong) from chitietdathang))

--40.Chua lam

/*=======================================DELETE=======================================*/

/*====================================================================================*/

/*=======================================TRIGGER=======================================*/

/*====================================================================================*/

--1. Chua lam duoc

--39. Giam 25% luong cua nhung nhan vien trong nam 2008 khong lap bat ky hoa don nao

Update NhanVien

Set PhuCap=0.25*phucap

Where manhanvien = any (

Select manhanvien

From nhanvien

Where not exists (

Select *

From dondathang

Where dondathang.manhanvien=nhanvien.manhanvien) )

--2.Thong ke tong so luong ban hang cua mot ma hang

Create Proc sp_2

As

Begin

Select mahang as 'Ma Hang', sum(soluong) as 'So Luong'

From chitietdathang, dondathang

Where chitietdathang.sohoadon=dondathang.sohoadon

Group By mahang

End;

Drop Proc sp_2

Execute sp_2

--3.

Create Function Fun_3

Return nvarchar(5)

Begin

Select mahang from mahang

End;

Select * from chitietdathang

where soluong in(200,400,500)

Select * from nhanvien

where manhanvien in ('NV001','NV003','NV002','NV000')

select manhanvien

From dondathang join chitietdathang on dondathang.sohoadon = chitietdathang.sohoadon

group by manhanvien

having sum(soluong) = any (

select sum(soluong)

From dondathang join chitietdathang on dondathang.sohoadon = chitietdathang.sohoadon

group by manhanvien )

Select sum(soluong)

From mahang

where mahang='MH001'

Create Function Fun_sl(@date datetime)

returns int

as

Declare @getdate int

Set @getdate = year(@date)

return @getdate

end

Drop Function Fun_sl

Select * From Fun_sl '2/8/1989'

--Su dung ham noi tuyen inline

Create Function Fun_inline()

returns table

as

return (Select mahang, count(maloaihang),sum(soluong) from mahang

Group By mahang)

Drop Function Fun_inline

Select * from Fun_inline()

--4. Viet ham

Create Function Fun1_sl()

returns @thongke table (mahang nvarchar(5), soluong int)

as

Begin

insert into @thongke

Select mahang, sum(soluong)

From chitietdathang

Group By mahang

Return

End

--cau 4: Su dung ham de thong ke hang da ban va hien co

--so luong hien co = mahang.soluong-chitietdathang.soluong

Select Fun1_sl.Mahang, Fun1_sl.soluong as 'Da ban',(mahang.soluong-Fun1_sl.soluong)as'Hien co'

From mahang, Fun1_sl()

Where mahang.mahang=Fun1_sl.mahang

Select * from chitietdathang

/*=============================================Triggers=============================================*/

Create Proc sp_Display(@mahang nvarchar(5))

As

Begin

select * from MaHang

Where mahang=@mahang

End;

Execute sp_Display 'MH001'

Select Manhanvien From Nhanvien

Where not exists

( Select manhanvien from DonDatHang

Where nhanvien.manhanvien=dondathang.manhanvien

)