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'Van 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 so mi nam','KH003','LH001','900',N'Chi?c','200000')

insert into MatHang

values('MH002',N'Áo so 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á dô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 so mi nam'

drop view test

select * from test

----------------Bài T?p

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

--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, tenhang,tencongty

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

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

select maloaihang, tenhang, tencongty as 'nhà cung c?p'

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

where tencongty = N'c? ph?n may vi?t ti?n'

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

select distinct nhacungcap.macongty,tencongty, diachi

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

inner join loaihang on mathang.maloaihang = loaihang.maloaihang

where tenloaihang = N'Th?c ph?m'

--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 hodem + ' '+ ten as 'h? tên',noigiaodich,ngaygiaohang,ngaydathang,ngaychuyenhang

from nhanvien inner join dondathang on nhanvien.manhanvien = dondathang.manhanvien

where sohoadon = 1

--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 b.mahang,tenhang, b.soluong*giaban-(b.soluong*giaban*mucgiamgia/100)as 's? ti?n'

from mathang as a inner join chitietdathang as b on a.mahang= b.mahang

where sohoadon = 2

--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, tencongty,ngaygiaohang

from dondathang inner join khachhang on dondathang.noigiaodich=khachhang.diachi

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

select tencongty,tengiaodich,diachi,dienthoai

from khachhang

union all

select tencongty,tengiaodich,diachi,dienthoai

from nhacungcap

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

select tenhang,mahang

from mathang

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

--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 tenhang,c.sohoadon,b.soluong*giaban-b.soluong*giaban*mucgiamgia/100

from (mathang as a inner join chitietdathang as b on a.mahang = b.mahang) inner join

dondathang as c on c.sohoadon =b.sohoadon

order by c.sohoadon

compute sum(b.soluong*giaban-b.soluong*giaban*mucgiamgia/100) by c.sohoadon