Create Database QL_BanHang2

On Primary(name = QL_BanHang_Data2,

Filename='d:\hoc tap\he quan tri sql\QL_BanHang2.MDF',

size = 3MB,

maxsize = 100MB,

filegrowth = 10MB)

Log on(name = QL_BanHang_log2,

Filename = 'd:\hoc tap\he quan tri sql\QL_BanHang2.LDF',

size = 3MB,

Maxsize = 100MB,

filegrowth = 10MB)

Use QL_BanHang2

----Tao bang

Create Table KhachHang

(MaKhachHang nvarchar(10) not null

Constraint pk_KhachHang primary key(MaKhachHang),

TenCongTy nvarchar(50) not null,

TenGiaoDich nvarchar(30),

DiaChi nvarchar (50),

Email nvarchar(30),

DienThoai nvarchar(20),

Fax nvarchar(20)

)

CREATE TABLE NhaCungCap

(MaCongTy nvarchar(10) not null

Constraint pk_NhaCungCap primary key(MaCongTy),

TenCongTy nvarchar(30) not null,

TenGiaoDich nvarchar(30)not null,

DiaChi nvarchar(30),

Email nvarchar(30),

DienThoai nvarchar(20),

Fax nvarchar(20)

)

CREATE TABLE MatHang

(MaHang nvarchar(10) not null

Constraint pk_MaHang primary key(MaHang),

TenHang nvarchar(30) not null,

MaCongTy nvarchar(10),

MaLoaiHang nvarchar(10),

SoLuong numeric(10,0),

DVT nvarchar(10),

GiaHang numeric(18,0)

)

CREATE TABLE DonDatHang

(SoHoaDon nvarchar(10) not null

Constraint pk_DonDatHang primary key(SoHoaDon),

MaKhachHang nvarchar(10),

MaNhanVien nvarchar(10),

NgayDatHang datetime,

NgayGiaoHang datetime,

NgayChuyenHang datetime,

NoiGiaoDich nvarchar(30)

)

CREATE TABLE ChiTietDatHang

(SoHoaDon nvarchar(10) not null,

MaHang nvarchar(10) not null,

GiaBan numeric(18,0),

SoLuong numeric(18,0),

MucGiamGia numeric(4,0),

Constraint pk_ChiTietDatHang primary key(SoHoaDon,MaHang)

)

CREATE TABLE LoaiHang

(MaLoaiHang nvarchar(10) not null

Constraint pk_LoaiHang primary key (MaLoaiHang),

TenLoaiHang nvarchar(15),

MoTa nvarchar(50)

)

CREATE TABLE NhanVien

(MaNhanVien nvarchar(10) not null

Constraint pk_NhanVien primary key(MaNhanVien),

HoDem nvarchar(30),

Ten nvarchar(10),

NgaySinh smalldatetime,

NgayLamViec datetime,

DiaChi nvarchar(50),

DienThoai nvarchar(20),

HeSoLuong numeric(5,2),

PhuCap numeric(10,0)

)

--- Chỉnh Sửa

Alter table MatHang

add

Constraint fk_MatHang_MaCongTy

Foreign key (MaCongTy)

references NhaCungCap(MaCongTy)

on update cascade

on delete cascade,

Constraint fk_MatHang_MaLoaiHang

Foreign key (MaLoaiHang)

references LoaiHang(MaLoaiHang)

on delete cascade

on update cascade

Alter table DonDatHang

add

Constraint fk_DonDatHang_MaKhachHang

Foreign key (MaKhachHang)

References KhachHang(MaKhachHang)

On delete cascade

On update cascade,

Constraint fk_DonDatHang_MaNhanVien

Foreign key(MaNhanVien)

References NhanVien(MaNhanVien)

On delete cascade

On update cascade

Alter table ChiTietDatHang

add

Constraint fk_ChiTietDatHang_SoHoaDon

Foreign key (SoHoaDon)

references DonDatHang(SoHoaDon)

On delete cascade

On update cascade,

Constraint fk_ChiTietDatHang_MaHang

Foreign key (MaHang)

References MatHang(MaHang)

On delete cascade

On update cascade

Alter table ChiTietDatHang

Add

Constraint df_ChiTietDatHang_SoLuong

Default (1) For SoLuong,

Constraint df_ChiTietDatHang_MucGiamGia

Default (0) For MucGiamGia

Alter table DonDatHang

Add

Constraint chk_DonDatHang_ngay

Check(NgayGiaoHang>=NgayDatHang And NgayChuyenHang >=NgayDatHang)

Alter Table NhanVien

Add

Constraint Chk_NhanVien_NgayLamViec

Check(datediff(yy,NgaySinh,NgayLamViec) between 18 and 60)

--- Chèn Dữ Liệu

insert into LoaiHang

values('LH001',N'Quần Áo',N'Có Nhiều Size khác nhau')

insert into LoaiHang

values('LH002',N'Thực Phẩm',N'Nhiều Loại')

Insert into LoaiHang

values('LH003',N'Văn Phòng Phẩm',N'Bút, sách, vở...')

insert into NhanVien

values('NV001',N'Nguyễn Bảo',N'Hà','2/12/1987','9/1/2007',N'Hà Đông - Hà Nội','04.32394853','2.34','300000')

insert into NhanVien

values('NV002',N'Trân Trung',N'Kiên','9/3/1990','4/1/2008',N'Hà Nội','04.3.8763421','1.86','200000')

insert into NhanVien

values('NV003',N'Nguyễn Mai',N'Hiền','2/12/1984','1/9/2005',N'Cầu Giấy - Hà Nội',null,'3.00','400000')

Insert into KhachHang

values('KH001',N'TNHH Bảo Vinh',N'baovinh jointStock',N'Thành Phố Hồ Chí Minh',N'[email protected]','084.3.8345845','084.3.8345845')

Insert into KhachHang

values('KH002',N'Cp Phú Lâm Hữu Nghị',N'PL FriendShip',N'Hà Nội','[email protected]','084.3.8736453','084.3.8736454')

Insert into KhachHang

values('KH003',N'Việt Linh',N'vietlinh JoinStock',N'hải phòng',N'[email protected]','084.3.7342341','084.3.7342343')

insert into MatHang

values('MH001',N'Áo sơ mi nam','KH003','LH001','900',N'Chiếc','200000')

insert into MatHang

values('MH002',N'Áo sơ mi nữ','KH003','LH001','300',N'Chiếc','150000')

insert into MatHang

values('MH003',N'Quần kaki nam','KH003','LH001',null,null,null)

insert into MatHang

values('MH004',N'Cafe monent','KH005','LH002','200',N'Hộp','100000')

insert into MatHang

values('MH005',N'Sữa redielac','KH005','LH002','200',N'Hộp','300000')

insert into MatHang

values('MH006',N'Vở Học Sinh','KH004','LH003','100',N'Quyển','2000')

insert into MatHang

values('MH007',N'Mực Học Sinh','KH004','LH003','100',N'Hộp','1000')

insert into MatHang

values('MH008',N'Laptop lenovo','KH003','LH001','120',N'Cái','800000')

insert into MatHang

values('MH009',N'Cá đông lạnh sashimi','KH004','LH002','123',N'Hộp','23000')

insert into NhaCungCap

values('KH001',N'Cổ phần công nghệ FPT ',N'FPT Corporation',N'Cầu Giấy,Hà Nội','[email protected]','84.4.37301515','84.4.37689067')

insert into NhaCungCap

values('KH002',N'Cổ phần Hồng Hà',N'hongha Corporation',N'Hoàn Kiếm,Hà Nội','[email protected]','84.4.36523329','84.4.36.524157')

insert into NhaCungCap

values('KH003',N'Cổ phần may việt tiến',N'viettien JointStock',N'Q. Tân Bình - TP Hồ Chí Minh','[email protected]','84.4.38640800','')

insert into NhaCungCap

values('KH004',N'TNHH Hải Hà',N'ALPHASEA CO, LTD',null,null,null,null)

insert into NhaCungCap

values('KH005',N'công ty sữa Việt Nam',N'vinamilk',N'Quận 3 - TP Hồ Chí Mình','[email protected]','84.8.39300358','84.8.39300359')

Insert into DonDatHang

values('1','KH001','NV002','2/1/2009','4/2/2009','4/2/2009',N'Tại công ty mua...')

Insert into DonDatHang

values('2','KH003','NV001','2/1/2009','9/5/2009','5/5/2009','')

Insert into DonDatHang

values('3','KH002','NV003','3/23/2009','3/23/2009','3/23/2009',N'Tại Chỗ')

Insert into DonDatHang

values('4','KH001','NV003','2/22/2009','2/27/2009','2/26/2009',N'Tại Chỗ')

Insert into DonDatHang

values('5','KH001','NV003','2/22/2009','2/27/2009','2/26/2009',N'hà nội')

select * from dondathang

insert into ChiTietDatHang

values('1','MH004','150000','340','0.1')

insert into ChiTietDatHang

values('1','MH005','350000','200','0.05')

insert into ChiTietDatHang

values('2','MH001','340000','94','0')

-----Khung Nhin

Create View vw_MatHang

as

select tenhang,mahang, soluong*giahang as thanhtien, dvt,maloaihang

from Mathang

select * from vw_mathang

Create view vw_chitiet

as

select Tencongty, tengiaodich,tenhang

from nhacungcap, mathang

where nhacungcap.macongty = mathang.macongty

select * from vw_mathang

insert into vw_mathang(tenhang,mahang)

values('testing','1')

Create view test

as

select nhacungcap.tencongty, mathang.tenhang

from nhacungcap inner join mathang on nhacungcap.macongty = mathang.macongty

where tenhang = N'áo sơ mi nam'

drop view test

select * from test

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

update dondathang set sotien = (select sum(soluong*giaban - ChiTietDatHang.soluong*giaban*mucgiamgia/100)

from chitietdathang where sohoadon = dondathang.sohoadon

group by sohoadon

--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) )

3.1 xóa nhân viên quá 40 năm

delete from nhanvien where datediff(yy,ngaysinh,getdate()) >40

3.2 xóa đơn đặt hang trước 2000

delete from dondathang where year(ngaydathang)<2000

3.3 xóa loại hàng ko co mathang

delete from loaihang where not exists(select maloaihang from mathang where maloaihang = loaihang.maloaihang)

3.4 xóa khách hàng ko có hóa đơn đặt hàng nào

delete from khachhang where not exists (select makhachhang from dondathang where makhachhang = khachhang.makhachhang)

3.5 xóa mặt hàng có SL = 0

delete from mathang where soluong = 0 and

not exists(select mahang from chitietdathang where mahang = mathang.mahang )