bai giai SQL
//3.1
-- Bang VATTU --
-- Ten vat tu phai duy nhat, khong trung: ok
alter table VATTU
add constraint unique_TenVtu unique(TenVTu)
--Gia tri mac dinh cho cot don vi tinh la "" ok
alter table VATTU
add constraint def_DvTinh default '' for DvTinh
-- 0<=Phantram<=100 ok
alter table VATTU
add constraint c_PhanTram check (PhanTram>=0 and PhanTram<=100)
-- Bang NhaCC --
-- Ten NhaCC va dia chi NhaCC phai duy nhat, khong trung. ok
alter table NHACC
add constraint unique_TenNhaCC unique(TenNhaCC)
alter table NHACC
add constraint unique_diachi unique(DiaChi)
-- Gia tri mac dinh cho cot dien thoai la 'Chua co' ok
alter table NHACC
add constraint def_dienthoai default 'Chua co' for DienThoai
-- Bang DONDH --
-- gia tri mac dinh cho cot ngay dat hang la ngay hien hanh. dung ham Getdate() ok
alter table DONDH
add constraint def_ngayDH default 'Getdate()' for NgayDH
-- Bang CTDONDH --
--SlDat >0 ok
alter table CTDONDH
add constraint check_SlDat check( SlDat >0)
-- Bang CTPNHAP --
-- SlNhap >0 && DgNhap >0 OK
alter table CTPNHAP
add constraint check_SlNhap check(SlNhap >0)
alter table CTPNHAP
add constraint check_DgNhap check(DgNhap>0)
-- SLNhap mac dinh =0
alter table CTPNHAP
add constraint default_SLNhap default '0' for SLNhap
-- Bang CTPXUAT --
-- SlXuat>0 && DgXuat >0 OK
alter table CTPXUAT
add constraint check_SlXuat check( SlXuat >0)
alter table CTPXUAT
add constraint check_DgXuat check( DgXuat >0)
-- Bang TONKHO --
--SlDau>= 0 && TongSLN >=0 && TongSLX>=0 OK
-- Gia tri mac dinh cho cac cot SlDau, Tong SLN, Tong SLX la 0 OK
alter table TONKHO
add constraint check_SlDau check(SlDau>=0)
alter table TONKHO
add constraint check_TongSLN check(TongSLN >=0)
alter table TONKHO
add constraint check_TongSLX check(TongSLX >=0)
alter table TONKHO
add constraint default_SlDau default '0' for SlDau
alter table TONKHO
add constraint default_TongSLN default '0' for TongSLN
alter table TONKHO
add constraint default_TongSLX default '0' for TongSLX
-- 3.2. Them cac rang buoc khoa ngoai cho cac bang: --
--3.2.1.Bang DONDH cot MaNhaCC Bang tham chieu: NHACC
alter table DONDH
add constraint foreign_MaNhaCC foreign key ( MaNhaCC) references NHACC(MaNhaCC)
--3.2.2Bang CTDONDH cot MaVTu Bang tham chieu VATTU
alter table CTDONDH
add constraint foreign_MaVTu foreign key ( MaVTu) references VATTU(MaVTu)
--3.2.3.Bang CTDONDH cot SoDH Bang tham chieu DONDH
alter table CTDONDH
add constraint foreign_SoDH foreign key ( SoDH) references DONDH(SoDH)
--3.2.4.Bang PNHAP cot MaVTu Bang tham chieu DONDH
alter table PNHAP
add constraint foreign_SoDH_2 foreign key ( SoDH) references DONDH(SoDH)
--3.2.5.Bang CTPNHAP cot MaVTu Bang tham chieu VATTU
alter table CTPNHAP
add constraint foreign_MaVTu_2 foreign key ( MaVTu) references VATTU(MaVTu)
--3.2.6. Bang CTPNHAP cot SoPN Bang tham chieu PNHAP
alter table CTPNHAP
add constraint foreign_SoPN foreign key(SoPN) references PNHAP(SoPN)
--3.2.7. Bang CTPXUAT cot MaVTu Bang VATTU
alter table CTPXUAT
add constraint foreign_MaVTu_3 foreign key ( MaVTu) references VATTU(MaVTu)
--3.2.8. Bang CTPXUAT cot SoPX Bang tham chieu PXUAT
alter table CTPXUAT
add constraint foreign_SoPX foreign key ( SoPX) references PXUAT(SoPX)
--3.2.9.Bang TONKHO cot MaVTu Bang tham chieu VATTU
alter table TONKHO
add constraint foreign_MaVTu_4 foreign key(MaVTu) references VATTU(MaVTu)
-- 3.3. Thiet lap mo hinh quan he du lieu (diagram) cua co so du lieu quan ly ban hang
-- Thay xem trong Database Diagram y a!
-- 3.4. Xay dung cac View:
--3.4.a Ten view: vw_DMT gom: MaVTu, TenVTu
Liet ke danh sach cac VTu hien co trong bang VTU
Create view vw_DMVT (MaVTu, TenVTu)
as
select MaVTu, TenVTu
from VATTU
-- Xem view DMVT:
select *
from vw_DMVT
--3.4.b. Ten view: vw_DonDH_TongSLNhap (SoDH, TongSLNhap)
Thong ke tong so luong nhap theo don dat hang
Create view vw_DonDH_TongSLNhap ( SoDH, TongSLNhap)
as
select PNHAP.SoDH as SoDH, sum (CTPNHAP.SlNhap) as TongSLNhap
from DONDH, CTPNHAP, PNHAP
where DONDH.SoDH= PNHAP.SoDH and PNHAP.SoPN=CTPNHAP.SoPN
group by PNHAP.SoDH
-- Xem view:
select *
from vw_DonDH_TongSLNhap
--3.4.c. Ten view: vw_DonDH_TongSLDatNhap (SoDH, TongSLDat, TongSLNhap)
-- Thong ke nhung don dat hang da duoc nhap hang day du (TongSLNhap=TongSLDat)
Create view vw_DonDH_TongSLDatNhap (SoDH, TongSLDat, TongSLNhap)
as
select pnhap.sodh,(select sum(sldat)
from ctdondh
where ctdondh.sodh=pnhap.sodh
group by sodh) as 'Tong sl Dat', sum(slnhap) as 'Tong sl nhap'
from pnhap, ctpnhap
where ctpnhap.sopn=pnhap.sopn
group by sodh
---- ghi chu: Theo em chi lam dc duy nhat mot cach khi thong qua 3bang: PNHAP, CTDONDH, CTPNHAP la
-- group by trong bang CTDONDH truoc roi cho no vao select vi CTDONDH va PNHAP co chung truong SoDH
-- con Bang CTPNHAP va PNHAP chi co chung truong SoPN, trong khi phai group by = truong SoDH
--( va phai them no vao dong select neu k se bao loi group by ma k co truong do trong bang CTPNHAP)
--
-- Khong thong qua 3 bang: CTDONDH, CTPNHAP va MAVTU duoc vi:
-- ta phai group by = truong SoDH ma chi co duy nhat o bang CTDONDH nen se k gom nhom dc tren bang CTPNHAP
--3.4.d. tao view co ten: vw_DonDh_DaNhapDu(soDH, DaNhapDu==ThongBao) trong do: da nhap du
chua nhap du
Create view vw_DonDh_DaNhapDu( soDH, ThongBao)
as
select SoDH, ThongBao=CASE when TongSLDat=TongSLNhap then 'Da nhap du'
else 'Chua nhap du'
END
from vw_DonDH_TongSLDatNhap
-- Xem view:
select *
from vw_DonDH_DaNhapDu
--3.4.e. Tao view vw_TongNhap(NamThang, MaVTu, TongSLNhap)
Create view vw_TongNhap( NamThang, MaVTu,TongSLNhap)
as
select (right(convert(char(10),NgayNhap,101),4)+ left(convert(char(10),NgayNhap,101),2)), MaVTu, sum(SLNhap)
from CTPNHAP, PNHAP
where CTPNHAP.SoPN=PNHAP.SoPN
group by NgayNhap, MaVTu
-- Xem view:
select *
from vw_TongNhap
-- 3.4.f. Tao view vw_TongXuat( NamThang, MaVTu, TongSLXuat)
Create view vw_TongXuat(NamThang,MaVTu, TongSLXuat)
as
select right(convert(char(10),NgayXuat,101),4)+ left(convert(char(10),NgayXuat,101),2), MaVTu, sum(SLXuat)
from CTPXUAT, PXUAT
where CTPXUAT.SoPX=PXUAT.SoPX
group by NgayXuat,MaVTu
-- Xem view:
select * from vw_TongXuat
--3.4.g.Tao view vw_DonDH_MaVTu_TongSLNhap(SoDH,NgayDH,MaVTu,TenVTu,SLDat,TongSLDaNhap)
Create view vw_DonDH_MaVTu_TongSLNhap(SoDH,NgayDH,MaVTu,TenVTu,SLDat,TongSLDaNhap)
as
select DonDH.SoDH,DonDH.NgayDH, VATTU.MaVTu,VATTU.TenVTu, CTDONDH.SLDat, Sum(CTPNHAP.SLNhap)
from CTDONDH,DONDH,VATTU,CTPNHAP
where CTPNHAP.MaVTu=VATTU.MAVTu and VATTU.MAVTu=CTDONDH.MAVTu and CTDONDH.SoDH=DONDH.SoDH
group by DONDH.SoDH,DonDH.NgayDH, VATTU.MaVTu,VATTU.TenVTu,CTDONDH.SLDat
-- Xem view:
select *
from vw_DonDH_MaVTu_TongSLNhap
/* CAU HOI
3.5. Ket hop cac view ocau 3.4 thuc hien cac truy van chon lua de tra loi cac cau hoi sau:
a. Cho biet danh sach cac phieu dat hang chua tung duoc nhap hang?
b.Cho biet danh sach cac mat hang chua bao gio duoc dat hang
c. Cho biet nha cung cap nao co nhieu don dat hang nhat.
d. Cho biet vat tu nao co tong so luong xuat ban la nhieu nhat.
e. Cho biet don dat hang nao co nhieu mat hang nhat.
f. Cho biet tinh hinh nhap xuat cua vat tu thong tin gom:nam thang, ma vat tu, ten vat tu, tong so luong nhap, tong so luong xuat.
g. Cho biet tinh hinh dat va nhap hang: don dat hang,ma vat tu, so luong dat, tong so luong nhap
h. Thong ke tinh hinh dat hang trong tung ngay: ngay dat hang, ma vat tu, ten vat tu, tong so luong dat hang.
i. Thong ke tinh hinh dat hang trong tung thang: nam thang dat hang (dinh dang YYY-MM) mat vat tu, ten vat tu, tong so luong dat hang
j. Thong ke nhung don dat hang nao chua duoc nhap du so luong hang
*/
-- PHAN TRA LOI
--3.5.a. Cho biet danh sach cac phieu dat hang chua tung duoc nhap hang?
select * from dondh
where sodh not in (select sodh from pnhap)
--3.5.b.Cho biet danh sach cac mat hang chua bao gio duoc dat hang
select * from vattu
where mavtu not in (select mavtu from ctdondh)
--3.5.c. Cho biet nha cung cap nao co nhieu don dat hang nhat.
select MaNhaCC, count (MaNhaCC) as TongSoDH
from DONDH
group by MaNhaCC
having count(MaNhaCC)>=all(select count(MaNhaCC)
from DONDH
group by MaNhaCC)
--3.5.d. Cho biet vat tu nao co tong so luong xuat ban la nhieu nhat.
select MaVTu, sum(SLXuat) as TongSLXuat
from CTPXUAT
group by MaVTU
having sum(SLXuat) >= all (select sum(SLXuat) from CTPXUAT group by MaVTU)
--3.5.e. Cho biet don dat hang nao co nhieu mat hang nhat.
select SoDH, count (MaVTu) as 'Số mặt hàng'
from CTDONDH
group by SoDH
having count(MaVTu) >=all (select count(MaVTu) from CTDONDH group by SoDH)
--3.5.f. Cho biet tinh hinh nhap xuat cua vat tu thong tin gom:nam thang, ma vat tu, ten vat tu, tong so luong nhap, tong so luong xuat.
select VATTU.MaVTu, TenVTu, sum(CTPNHAP.SLNhap)as 'So luong nhap', sum(CTPXUAT.SLXuat)as 'So luong xuat'
from CTPXUAT, VATTU, CTPNHAP
where CTPNHAP.MaVTu= VATTU.MaVTU and VATTU.MaVTu=CTPXUAT.MaVTu
group by VATTU.MaVTu, TenVTu
-- Thầy ơi, nếu muốn có thêm cột NamThang như trong đề bài, em làm như đoạn bên dưới.
-- Làm thế nào để có thể as được thành cột NamThang mà không có lỗi hả thầy???
select (right(convert(char(10),NgayXuat,101),1)+left(convert(char(10),NgayXuat,101),2))as NamThang ,
VATTU.MaVTu, TenVTu, sum(CTPNHAP.SLNhap)as 'So luong nhap', sum(CTPXUAT.SLXuat)as 'So luong xuat'
from CTPXUAT, VATTU, CTPNHAP,PNHAP,PXUAT
where CTPNHAP.MaVTu= VATTU.MaVTU and VATTU.MaVTu=CTPXUAT.MaVTu and
PNHAP.SoPN=CTPNHAP.SoPN and PXUAT.SoPX=CTPXUAT.SoPX and
(right(convert(char(10),NgayXuat,101),1)+left(convert(char(10),NgayXuat,101),2))=
(right(convert(char(10),NgayNhap,101),1)+left(convert(char(10),NgayNhap,101),2))
group by VATTU.MaVTu, TenVTu,NamThang
--3.5.g. Cho biet tinh hinh dat va nhap hang: don dat hang,ma vat tu, so luong dat, tong so luong nhap
select CTDONDH.SoDH, VATTU.MaVTu,sum(SLDat)as 'So luong dat', sum(SLNhap)as 'So luong nhap'
from CTDONDH,VATTU,CTPNHAP
where CTDONDH.MaVTu=VATTU.MaVTU and VATTU.MaVTu=CTPNHAP.MaVTu
group by CTDONDH.SoDH, VATTU.MaVTu
--3.5.h. Thong ke tinh hinh dat hang trong tung ngay: ngay dat hang, ma vat tu, ten vat tu, tong so luong dat hang.
create view vw_SLDat_ngay
as
select NgayDH,VATTU.MaVTu,VATTU.TenVTu,sum(SLDat) as TongSLDat
from VATTU,DONDH,CTDONDH
where CTDONDH.SoDH=DONDH.SoDH and CTDONDH.MaVTu=VATTU.MAVTu
group by NgayDH,VATTU.MaVTu,VATTU.TenVTu
-- Xem view:
select *
from vw_SLDat_ngay
--3.5.i. Thong ke tinh hinh dat hang trong tung thang: NamThang (dinh dang YYY-MM), MaVTu , TenVTu, TongSLDat
select (right(convert(char(10),NgayDH,101),4) + left(convert(char(10),NgayDH,101),2))as NamThang,MaVTu,TenVTu,TongSLDat
from vw_SLDat_ngay
--3.5.j. Thong ke nhung don dat hang nao chua duoc nhap du so luong hang.
( Nghia la chi Thong ke duoc nhung don dat hang nao da nhap nhung chua nhap du ha Thay?)
select SoDH,ThongBao='Chua nhap du'
from vw_DonDH_TongSLDatNhap
where TongSLDat != TongSLNhap
/* CAU HOI
4.1.
a.Hien thi danh sach cac vattu trong bang VATTU, sap xep theo thu tu ten vat tu giam dan
b.Hien thi danh sach cac NhaCC trong bang NHACC co cot DiaChi o Q1HCM, sap xep du lieu theo ho ten tang dan
c.Hien thi danh sach cac thong tin trong bang CTPNHAP co them cot ThanhTien=SLNhap*DgNhap
d.Hien thi danh sach cac thong tin trong bang PNHAP co them cot TriGia=TongThanhTien cua
cac mau tin chi tiet tuong ung trong bang CTPNHAP
e.Hien thi danh sach cac MaNCC, TenNCC khong trung lap du lieu da dat hang trong bang DONDH
f.Hien thi danh sach cac phieu xuat hang gom:SoPX, Tongtrigia. Trong do sap xep theo thu tu
Tongtrigia giam dan
g.Xoa chi tiet cac vat tu trong bang CTDONDH co ngay dat hang la 15/01/2005
h.Xoa toan bo cac dong du lieu trong bang CTPXUAT
i.Su dung laitaptin chua cac lenh them du lieu INSERT INTO truoc day
de chen lai cac du lieu da bi xoa trong bang CTDONDH va CTPXUAT
j.Su dung menh de COMPUTE By va cac ham tinh toan de thong ke nhom du lieu
- Hien thi cac thong tin trong bang CTPXUAT va bo sung them cot ThanhTien
sao cho co thong ke dong tong cong so tien o tung phieu xuat
- Hien thi cac thong tin : MaVTu,So nhap hang, SLNhap,DgNhap trong bang CTPNHAP va
Thong ke dong tong cong so luong, gia thap nhat,gia cao nhat o tung vattu
*/
-- DAP AN
--4.1.a.Hien thi danh sach cac vattu trong bang VATTU, sapxep theo thu tu ten vat tu giam dan.
select TenVTu
from VATTU
order by TenVTu desc
asc ending desc ending
--4.1.b.Hien thi danh sach cac NhaCC trong bang NHACC co cot DiaChi o Q1HCM, sap xep du lieu theo ho ten tang dan
select TenNhaCC
from NHACC
where DiaChi like '%Q1 TP HCM%'
order by TenNhaCC asc
--4.1.c.Hien thi danh sach cac thong tin trong bang CTPNHAP co them cot ThanhTien=SLNhap*DgNhap
select *, ThanhTien=SLnhap*DgNhap
from CTPNHAP
--4.1.d.Hien thi danh sach cac thong tin trong bang PNHAP co them cot TriGia=TongThanhTien cua
-- cac mau tin chi tiet tuong ung trong bang CTPNHAP
select PNHAP.SoPN,PNHAP.NgayNhap, PNHAP.SoDH, TongThanhTien=sum(SLNhap*DgNhap)
from PNHAP,CTPNHAP
group by PNHAP.SoPN,PNHAP.NgayNhap, PNHAP.SoDH
--4.1.e.Hien thi danh sach cac MaNCC, TenNCC khong trung lap du lieu da dat hang trong bang DONDH
select DONDH.MaNhaCC,NHACC.TenNhaCC
from DONDH,NHACC
where DONDH.MaNhaCC=NHACC.MaNhaCC
group by DONDH.MaNhaCC, NHACC.TenNhaCC
--4.1.f.Hien thi danh sach cac phieu xuat hang gom:SoPX, Tongtrigia. Trong do sap xep theo thu tu
--Tongtrigia giam dan
select SoPX,Tongtrigia= sum(SLXuat*DgXuat)
from CTPXUAT
group by SoPX
order by Tongtrigia desc
--4.1.g.Xoa chi tiet cac vat tu trong bang CTDONDH co ngay dat hang la 15/01/2005
-- Sao lai khong co row nao bi anh huong la sao nhi? co 2 row ma mac du thay NgayDh bang truong khac lai duoc. huhu.
Delete From CTDONDH
From DONDH
where DONDH.SoDH=CTDONDH.SoDH and DONDH.NgayDH=01/15/2005
--4.1.h.Xoa toan bo cac dong du lieu trong bang CTPXUAT
Delete From CTPXUAT
--4.1.i.Su dung laitaptin chua cac lenh them du lieu INSERT INTO truoc day
--de chen lai cac du lieu da bi xoa trong bang CTDONDH va CTPXUAT
insert into CTDONDH values('D001','DD01',10)
insert into CTDONDH values('D001','DD02',15)
insert into CTPXUAT values('X001','DD01',2,3500000)
insert into CTPXUAT values('X002','DD01',1,3500000)
insert into CTPXUAT values('X002','DD02',5,4900000)
insert into CTPXUAT values('X003','DD01',3,3500000)
insert into CTPXUAT values('X003','DD02',2,4900000)
insert into CTPXUAT values('X003','VD02',10,3250000)
--4.1.j.Su dung menh de COMPUTE By va cac ham tinh toan de thong ke nhom du lieu
--Hien thi cac thong tin trong bang CTPXUAT va bo sung them cot ThanhTien
-- sao cho co thong ke dong tong cong so tien o tung phieu xuat
--Hien thi cac thong tin : MaVTu,So nhap hang, SLNhap,DgNhap trong bang CTPNHAP va
-- Thong ke dong tong cong so luong, gia thap nhat,gia cao nhat o tung vattu
select *,ThanhTien=SLXuat*DgXuat
from CTPXUAT
group by SoPX,MaVTu,SLXuat,DgXuat
-- hoac dung ham COMPUTE ... By . Lam the nay khong duoc. vi dung compute phai la nhung truong da co trong bang tu truoc
select *,(SLXuat*DgXuat) as ThanhTien
from CTPXUAT
compute sum(SLXuat*DgXuat) by SoPX,MaVTu,SLXuat,DgXuat
--
declare @max,@min,@sum
select SoPN,MaVTu,SLNhap,DgNhap,@sum=sum(DgNhap*SlNhap),@max=max(DgNhap),@min=min(DgNhap)
from CTPNHAP
group by SoPN,MaVTu,SLNhap,DgNhap
print 'Tong cong so luong:' + @sum
print 'Don gia cao nhat:' +@max
print 'Don gia thap nhat:' +@min
/* CAU HOI 4.2.Dung cu phap IF:
a.Cho biet don gia xuat trung binh cua hang hoa"Dau DVD Hitachi 1 dia" trong bang CTPXUAT hien gio la bao nhieu?
Neu lon hon 3800000 thi in ra "khong nen thay doi gia ban" nguoc lai in ra: da den luc tang gia ban"
b.Su dung ham DATENAME de tinh xem co don dat hang nao da duoc lap vao ngay chu nhat khong?
Neu co thi in ra danh sach cac don dat hang do, nguoc lai thi in ra chuoi "Ngay lap cac don dat hang deula hop le"
c.Hay cho biet da co bao nhieu phieu nhap hang cho don dat hang D001,
Neu co thi in ra "Co xx so phieu nhap hang cho don dat hang D001", nguoc lai "Chua co phieu nhap hang nao cho D001"
*/
-- Bai lam:
--4.2.a.Cho biet don gia xuat trung binh cua hang hoa"Dau DVD Hitachi 1 dia" trong bang CTPXUAT hien gio la bao nhieu?
-- Neu lon hon 3800000 thi in ra "khong nen thay doi gia ban" nguoc lai in ra: da den luc tang gia ban"
declare @avg float
select @avg=avg(DgXuat)
from CTPXUAT,VATTU
where VATTU.MaVTu=CTPXUAT.MaVTu and TenVTu='Dau DVD Hitachi 1 dia'
if(@avg>3800000)
print 'Khong nen thay doi gia ban'
else
print 'Da den luc tang gia ban'
--4.2.b.Su dung ham DATENAME de tinh xem co don dat hang nao da duoc lap vao ngay chu nhat khong?
-- Neu co thi in ra danh sach cac don dat hang do, nguoc lai thi in ra chuoi "Ngay lap cac don dat hang deu la hop le"
if exists (select * from DONDH where datename(DW,NgayDH)='Sunday')
(select SoDH from DONDH where datename(DW,NgayDh)='Sunday')
else
print 'Ngay lap cac don dat hang deu la hop le'
--4.2.c.Hay cho biet da co bao nhieu phieu nhap hang cho don dat hang D001,
-- Neu co thi in ra "Co xx so phieu nhap hang cho don dat hang D001", nguoc lai "Chua co phieu nhap hang nao cho D001"
declare @sopn int
select @sopn=count(SoPN)
from PNHAP
where SoDH='D001'
if(@sopn>0)
print 'Co '+convert(char(1),@sopn)+' so phieu nhap hang cho don dat hang D001'
else print 'Chua co phieu nhap hang nao cho D001'
/* CAU HOI 4.3 Su dung cu phap while:
a.Tao mot bang ten VATTU_Temp co cau truc va du lieu dua vao bang VATTU( chi lay hai cot MaVTu,TenVTU).
Sau do su dung vong lap WHILE viet doan chuong trinh dung de xoa tung dong du lieu trong bang VATTU_Temp voi
dieu kien cau lenh ben trong vong lap khi moi lan thuc hien chi duoc phep xoa mot dong du lieu trong bang VATTU_Temp
Trong khi xoa nen thong bao ra man hinh noi dung: "Dang xoa vat tu" + TenVTu
b. Trong bang VATTU_Temp bo sung them hai cot moi: SoPX char(4), DgXuat float.
Kiem tra don gia trung binh cua vat tu D001 trong bang CTPXUAT
Neu don gia trung binh van con <3500000 thi tang don gia len 5%cho cac vattu D001 co gia xuat <3500000
Ket thuc vong lap cho biet da thuc hien viec tang bao nhieu lan trong vong lap.
Truoc moi lan tang don gia phai chen them dong du lieu vao bang VATTU_Temp voi don gia da tang tu du lieu cua cac bang CTPXUAT va VATTU
( De lay cot TenVTu)nham luu lai cac gia tri trong qua trinh tang don gia.
*/
-- Bai Lam:
--4.3.a.
select MaVTu,TenVTu
Into VATTU_Temp
From VATTU
drop table VATTU_Temp
while exists (select* from VATTU_Temp)
declare @TenVTu char(40)
select @TenVTu=TenVTu from VATTU_Temp
delete from VATTU_Temp
where VATTU_Temp.TenVTu=@TenVTu
print 'Dang xoa vat tu: '+ @TenVTu
end
--4.3.b. Trong bang VATTU_Temp bo sung them hai cot moi: SoPX char(4), DgXuat float.
-- Kiem tra don gia trung binh cua vat tu DD01 trong bang CTPXUAT
-- Neu don gia trung binh van con <3500000 thi tang don gia len 5%cho cac vattu DD01 co gia xuat <3500000
-- Ket thuc vong lap cho biet da thuc hien viec tang bao nhieu lan trong vong lap.
-- Truoc moi lan tang don gia phai chen them dong DL vao bang VATTU_Temp voi don gia da tang tu DL cua cac bang CTPXUAT va VATTU
-- ( De lay cot TenVTu)nham luu lai cac gia tri trong qua trinh tang don gia.
alter table VATTU_Temp
add SoPX nchar(4)
alter table VATTU_Temp
add DgXuat float
select * from VATTU_Temp
select * from CTPXUAT
select * from VATTU
declare @avg money, @count int, @i int
set @count=0
while( @avg= (select avg(DgXuat)from CTPXUAT where MaVTu='DD01')) <3500000
set @count=@count+1
Insert into VATTU_Temp
select MaVTu,TenVTu,SoPX,(avg(DgXuat)*1.05) as DgXuat
from VATTU,CTPXUAT
where MaVTu='DD01' and VATTU.MaVTu=CTPXUAT.MaVTu
Update CTPXUAT
Set DgXuat=DgXuat * 1.05
where MaVTu='DD01'
end
print 'Da thuc hien'+cast(@count as char(2)) + 'lan tang.'
/*CAU HOI. 4.4.Su dung cu phap CASE long vao cac lenh truy van can thiet theo yeu cau:
4.4.a.Liet ke danh sach cac don dat hang trong bang DONDH bo sung them cot hien thi thu
trong tuan(Bang tieng Viet) cua NgayDH.
4.4.b.Giam don gia xuat cua cac hang hoa ban ra trong thang 01/2005 theo cac qui tac:
- Khong giam neu so luong <4.
- Giam 5% neu so luong >=4 va <10.
' - Giam 10% neu so luong >=10 va <=20.
- Giam 20% neu so luong >20.
*/
BAI LAM:
--4.4.a.Liet ke danh sach cac don dat hang trong bang DONDH bo sung them cot hien thi thu
-- trong tuan(Bang tieng Viet) cua NgayDH.
select * from DONDH
select DONDH.*,ThuTrongTuan=case (datename(DW,NgayDH))
when 'sunday' then 'Chu nhat'
when 'monday' then 'Thu hai'
when 'tuesday' then 'Thu ba'
when 'wednesday'then 'Thu tu'
when 'Thursday' then 'Thu nam'
when 'Friday' then 'Thu sau'
when 'Saturday' then 'Thu bay'
end
from DONDH
--4.4.b.Giam don gia xuat cua cac hang hoa ban ra trong thang 01/2005 theo cac qui tac:
-- - Khong giam neu so luong <4.
-- - Giam 5% neu so luong >=4 va <10.
--' - Giam 10% neu so luong >=10 va <=20.
-- - Giam 20% neu so luong >20.
select * from CTPXUAT
Update CTPXUAT
set DgXuat= case
when (4<=SLXuat and SLXuat<10) then DgXuat*0.95
when (10<=SLXuat and SLXuat<=20)then DgXuat*0.9
when (SLXuat>20) then DgXuat*0.8
else DgXuat
end
/* CAU HOI 5.1.Tao cac thu tuc noi tai tinh toan:
5.1.a.Xay dung TT tinh so luong dat hang voi ten spud_DONDH_TinhSLDat gom:
2 tham so vao: SoDH vaf MaVTu,1 tham so ra la: So dat hang cua mot vat tu theo mot SoDH.
5.1.b.XD TT tinh tong so luong da nhap hang voi ten spud_PNHAP_TinhTongSLNHang gom:
2 tham so dau vao la: SoDH vaf MaVTu, 1 tham so ra la: Tong so luong da nhap hang
cua 1 vattu theo mot SoDH.
5.1.c.XD TT tinh so luong dau ky cua mot vattu voi ten spud_TONKHO_TinhSLDau gom:
2 tham so dau vao la: NamThang,va MaVTu, 1 tham so ra la:So luong dau ky cua mot vattu
theo nam thang truyen vao.
*/
-- BAI LAM:
--5.1.a.Xay dung TT tinh so luong dat hang voi ten spud_DONDH_TinhSLDat gom:
-- 2 tham so vao: SoDH va MaVTu,1 tham so ra la: So dat hang cua mot vat tu theo mot SoDH.
select * from DONDH
select * from CTDONDH
Create proc spud_DONDH_TinhSLDat
@SoDH char(4),@MaVTu char(4),@SLDat int output
as
select @SLDat=SLDat
from CTDONDH
where @SoDH = SoDH and @MaVTu = MaVTu
Thuc hien:
Declare @a int
Set @a=0
exec spud_DONDH_TinhSLDat 'D001','DD01',@a output
print 'So dat hang cua vat tu DD01 la:'+cast(@a as char(4))
--5.1.b.XD TT tinh tong so luong da nhap hang voi ten spud_PNHAP_TinhTongSLNHang gom:
-- 2 tham so dau vao la: SoDH va MaVTu, 1 tham so ra la: Tong so luong da nhap hang
-- cua 1 vattu theo mot SoDH.
select * from PNHAP
select *from CTPNHAP
Create proc spud_PNHAP_TinhTongSLNHang
@SoDH char(4),@MaVTu char(4),@TongSLNhap int output
as
select @TongSLNHap=sum(SLNhap)
from CTPNHAP,PNHAP
where CTPNHAP.MaVTu=@MaVTu and PNHAP.SoDH=@SoDH and CTPNHAP.SoPN=PNHAP.SoPN
Thuc hien:
Declare @a int
set @a=0
exec spud_PNHAP_TinhTongSLNHang 'D001','DD01',@a output
print 'Tong so luong nhap cua vat tu DD01 la: '+cast(@a as char(4))
--5.1.c.XD TT tinh so luong dau ky cua mot vattu voi ten spud_TONKHO_TinhSLDau gom:
-- 2 tham so dau vao la: NamThang,va MaVTu, 1 tham so ra la:So luong dau ky cua mot vattu
-- theo nam thang truyen vao.
select *from TONKHO
Create proc spud_TONKHO_TinhSLDau
@NamThang char(6),@MaVTu char(4),@SLDau int output
as
select @SLDau=SLDau
from TONKHO
where NamThang=@NamThang and MaVTu=@MaVTu
Thuc hien:
Declare @a int
Set @a=0
exec spud_TONKHO_TinhSLDau '200501','DD01',@a output
print 'So luong dau cua vat tu DD01 la: '+ cast(@a as char(2))
/* CAU HOI 5.2.Tao cac TT noi tai cap nhat DL trong bang VATTU.Cac TT nay:
Co kiem tra cac rang buoc DL va Thong Bao ra cac loi ro rang khi DL vi pham cac rang buoc.
5.2.a.XD TT them moi DL vao bang VATTU voi ten spud_VATTU_Them gom:
4 tham so vao la gia tri them moi cho cac cot trong bang VATTU: MaVTu,TenVTU,DVTinh,PhanTram.
Trong do can kiem tra cac constraint Dl phai hop le truoc khi thuc hien INSERT INTO vao VATTTU
- MaVTu phai chua co trong bang VATTU.
5.2.b.XD TT xoa 1 vattu cotrong bang VATTU voi ten spud_VATTU_Xoa gom:
1 tham so vao:MaVTu can xoa.
Trong do can kiem tra constraint truoc khi delete trong VATTU:
- MaVTu phai chua co trong CTDONDH.
- MaVTu phai chua co trong CTPNHAP.
- MaVTu phai chua co trong CATPXUAT.
- MaVTu phai chua co trong TONKHO.
5.2.c.XD Tt sua doi vattu trong bang VATTU voi ten spud_VATTU_Sua gom:
4 tham so vao: (chinh la gia tri can thay doi cua cac cot trong bang VATTU tru cot MaVTu):
MaVTu,TenVTu,DVTinh,PhanTram.
Trong TT chi thuc hien UPDATE SET de cap nhat DL vao bang VATTU voi cac gia tri tuong ung.
*/
BAI LAM:
--5.2.a.XD TT them moi DL vao bang VATTU voi ten spud_VATTU_Them gom:
-- 4 tham so vao la gia tri them moi cho cac cot trong bang VATTU: MaVTu,TenVTU,DVTinh,PhanTram.
-- Trong do can kiem tra cac constraint DL phai hop le truoc khi thuc hien INSERT INTO vao VATTTU
-- - MaVTu phai chua co trong bang VATTU.
select *from VATTU
Create proc spud_VATTU_Them
@MaVTu char(4), @TenVTu nvarchar(100), @DVTinh nvarchar(50), @PhanTram real
as
if exists ( select * from VATTU where MaVTu=@MaVTu)
print ' MaVTu nay da co trong bang VATTU!'
else
insert into VATTU values (@MaVTu,@TenVTu,@DVTinh,@PhanTram)
Thuc hien:
exec spud_VATTU_Them 'CPU','CPU','chiec','10'
--5.2.b.XD TT xoa 1 vattu cotrong bang VATTU voi ten spud_VATTU_Xoa gom:
-- 1 tham so vao:MaVTu can xoa.
-- Trong do can kiem tra constraint truoc khi delete trong VATTU:
-- - MaVTu phai chua co trong CTDONDH.
-- - MaVTu phai chua co trong CTPNHAP.
-- - MaVTu phai chua co trong CATPXUAT.
-- - MaVTu phai chua co trong TONKHO.
select *from VATTU
Create proc spud_VATTU_Xoa
@MaVTu char(4)
as
begin if exists (select * from CTDONDH where CTDONDH.MaVTu=@MaVTu)
print ' MaVTu nay da co trong bang CTDONDH'
else if exists( select * from CTPNHAP where CTPNHAP.MaVTu=@MaVTu)
print ' MAVTU nay da co trong bang CTPNHAP'
else
if exists( select * from CTPXUAT where CTPXUAT.MaVTu=@MaVTu)
print ' MaVTu nay da co trong bang CTPXUAT'
else
if exists( select * from TONKHO where TONKHO.MAVTu=@MaVTu)
print ' MaVTu nay da co trong bang TONKHO'
else delete from VATTU where MaVtu=@MaVTu
end
Thuc hien:
exec spud_VATTU_Xoa 'DD01'
--5.2.c.XD Tt sua doi vattu trong bang VATTU voi ten spud_VATTU_Sua gom:
-- 4 tham so vao: (chinh la gia tri can thay doi cua cac cot trong bang VATTU tru cot MaVTu):
-- MaVTu,TenVTu,DVTinh,PhanTram.
-- Trong TT chi thuc hien UPDATE SET de cap nhat DL vao bang VATTU voi cac gia tri tuong ung.
select * from VATTU
drop proc spud_VATTU_Sua
Create proc spud_VATTU_Sua
@MaVTu char(4),@TenVTu varchar(100),@DVTinh varchar(10),@PhanTram real
as
if((select count(*) from VATTU where MaVTu=@MaVTu)>0)
Update VATTU
Set TenVTu = @TenVTu,
DVTinh = @DVTinh,
PhanTram = @PhanTram
where MaVTu=@MaVTu
end
else
print 'MaVTu nay khong co trong bang VATTU'
return
end
end
Thuc hien:
exec spud_VATTU_Sua 'CPUi','central processing unit','chiec','10'
exec spud_VATTU_Sua 'CPU','central processing unit','chiec','10'
/* CAU HOI 5.3. Tao cac TT noi tai Liet ke du lieu:
5.3.a.XD TT liet ke cac cot DL trong bang VATTU voi ten spud_VATTU_BcaoDanhSach, TT nay khong co tham so.
Cong viec cua TT nay chi la mot cau lenh select * from VATTU co sap xep theo thu tu TenVTu tang dan
5.3.b.XD TT liet ke cac cot DL trong bang TONKHO co the hien them cot TenVTu trong bang VATTU
voi ten spud_TONKHO_BcaoTonKho gom co:
1 tham so vao la: NamThang muon loc DL.
5.3.c.XD TT liet ke cac cot DL trong 2 bang DL PXUAT vaf CTPXUAT co the hien them cot TenVTU
trong bang VATTU voi ten spud_PXUAT_BcaoPXuat gom:
1 tham so vao la: SoPX muon loc DL co gia tri mac dinh la null.
Nhung neu luc goi thuc hien TT ma khong truyen gia tri SoPX vao thi xem nhu khong loc gi ca,
Khi do TT se liet ke tat ca cac phieu xuat dang co trong bang PXUAT.
*/
BAI LAM:
--5.3.a.XD TT liet ke cac cot DL trong bang VATTU voi ten spud_VATTU_BcaoDanhSach, TT nay khong co tham so.
-- Cong viec cua TT nay chi la mot cau lenh select * from VATTU co sap xep theo thu tu TenVTu tang dan
Create proc spud_VATTU_BcaoDanhSach
as
select *
from VATTU
order by TenVTu asc
Thuc hien:
exec spud_VATTU_BcaoDanhSach
--5.3.b.XD TT liet ke cac cot DL trong bang TONKHO co the hien them cot TenVTu trong bang VATTU
-- voi ten spud_TONKHO_BcaoTonKho gom co:
-- 1 tham so vao la: NamThang muon loc DL.
select * from TONKHO
Create proc spud_TONKHO_BcaoTonKho
@NamThang char(6)
as
select NamThang,TONKHO.MaVTu,TenVTu,SLDau,TongSlN,TongSLX,SLCuoi
from TONKHO,VATTU
where @NamThang=NamThang and TONKHO.MaVTu=VATTU.MaVTu
Thuc hien:
exec spud_TONKHO_BcaoTonKho '200501'
--5.3.c.XD TT liet ke cac cot DL trong 2 bang DL PXUAT vaf CTPXUAT co the hien them cot TenVTU
-- trong bang VATTU voi ten spud_PXUAT_BcaoPXuat gom:
-- 1 tham so vao la: SoPX muon loc DL co gia tri mac dinh la null.
-- Nhung neu luc goi thuc hien TT ma khong truyen gia tri SoPX vao thi xem nhu khong loc gi ca,
-- Khi do TT se liet ke tat ca cac phieu xuat dang co trong bang PXUAT.
select * from PXUAT
select * from CTPXUAT
drop proc spud_PXUAT_BcaoPXuat
Create proc spud_PXUAT_BcaoPXuat
@SoPX char(4)
as
if(@SoPX is null)
(select PXUAT.SoPX,NgayXuat,TenKH,MaVTu,SLXuat,DgXuat
from PXUAT,CTPXUAT
where PXUAT.SoPX=CTPXUAT.SoPX
else
(select PXUAT.SoPX,NgayXuat,TenKH,MaVTu,SLXuat,DgXuat
from PXUAT,CTPXUAT
where PXUAT.SoPX=CTPXUAT.SoPX and PXUAT.SoPX=@SoPX
Thuc hien:
exec spud_PXUAT_BcaoPXuat ''
exec spud_PXUAT_BcaoPXuat 'X001'
/* CAU HOI 5.4. Tao proc dung de update DL trong bang DONDH,CTDONDH de ve sau su dung trong man hinh DONDH.
Cac proc nay co Ktra constraint va thong bao cac loi ro rang khi DL vi pham constraint.
5.4.a.XD proc INSERT DL vao DONDH voi ten spud_DONDH_Them gom:
3 tham so vao (chinh la gia tri them moi cho cac cot trong bang DONDH):SoDH,MaNhaCC,NgayDH.
Trong do phai kiem tra constraint:
- SoDH phai duy nhat.
- MaNhaCC phai co trong bang NHACC.
- NgayDH co the khong truyen vao(optional), khi do se lay gia tri mac dinh la ngay hien hanh.
5.4.b.XD proc xoa DONDH voi ten spud_DONDH_Xoa gom:
1 tham so vao:SoDH can xoa.
Neu DL hop le thi xoa luon cac dong DL lien quan trong bang CTDONDH.
Trong do can kiem tra cac constraint:
- SoDH phai chua co trong bang PNHAP.
5.4.c.XD proc su doi DL trong bang DONDH voi ten spud_DONDH_Them gom:
3 tham so vao(chinh la gia tri can thay doi cua cac cot trong DONDH): SoDH,MaNhaCC,NgayDH
Trong do can kiem tra tinh hop le cua DL:
- MaNhaCC phai co trong bang NHACC.
- NgayDH phai truoc ngay nhap hang(neu don dat hang da duoc nhap ve roi)
5.4.d.XD proc Insert DL vao bang CTDONDH voi ten spud_CTDONDH_Them gom:
3 tham so vao chinh la gia tri them moi vao CTDONDH:SoDH,MaVTU,SLDat.
Trong do can kiem tra tinh hop le cua DL:
- SoDH phai co trong bang DONDH.
- MaVTu phai co trong bang VATTU.
- SoDH va MaVTu phai duy nhat trong bang CTDONDH.
5.4.e.XD proc xoa CTDONDH voi ten spud_CTDONDH_Xoa gom:
2 tham so dau vao la SoDH va MaVTu can xoa.
Trong do can kiem tra tinh hop le du lieu truoc khi xoa trong bang CTDONDH.
5.4.f.XD proc sua doi DL trong bang CTDONDH voi ten spud_CTDONDH_Sua gom:
3 tham so vao chinh la gia tri can thay doi cua cac cot trong bang CTDONDH:
SoDH,MaVTu,SLDat.
Trong do can kiem tra tinh hop le cua DL truoc khi update set de cap nhat du lieu vao bang CTDONDH:
- SoDH va MaVTu phai co trong bangCTDONDH.
- SLDat moi phai >=tong so luong da nhap hang(neu da co nhap hang).
*/
BAI LAM:
--5.4.a.XD proc INSERT DL vao DONDH voi ten spud_DONDH_Them gom:
-- 3 tham so vao (chinh la gia tri them moi cho cac cot trong bang DONDH):SoDH,MaNhaCC,NgayDH.
-- Trong do phai kiem tra constraint:
-- - SoDH phai duy nhat.
-- - MaNhaCC phai co trong bang NHACC.
-- - NgayDH co the khong truyen vao(optional), khi do se lay gia tri mac dinh la ngay hien hanh.
select * from DONDH
Create proc spud_DONDH_Them
@SoDH char(4),@MaNhaCC char(4),@NgayDH datetime
as
Begin
if(exists (select *from DONDH where @SoDH=SoDH))
print 'SoDH phai duy nhat.SoDH nay da co trong bang DONDH!'
return
end
if(not exists (select * from NHACC where @MaNhaCC=MaNhaCC))
print 'MaNhaCC phai co trong bang NHACC!'
return
end
if(@NgayDH is null)
insert into DONDH values(@SoDH,getdate(),@MaNhaCC)
else
insert into DONDH values(@SoDH,@NgayDH,@MaNhaCC)
End
Thuc hien:
exec spud_DONDH_Them 'D006','01/01/2008','C01'
--5.4.b.XD proc xoa DONDH voi ten spud_DONDH_Xoa gom:
-- 1 tham so vao:SoDH can xoa.
-- Neu DL hop le thi xoa luon cac dong DL lien quan trong bang CTDONDH.
-- Trong do can kiem tra cac constraint:
-- - SoDH phai chua co trong bang PNHAP.
Create proc spud_DONDH_Xoa
@SoDH char(4)
as
if exists (select * from PNHAP where SoDH=@SoDH)
print 'so dh da co trong bang phieu nhap'
return
end
delete From DONDH where SoDH=@SoDH
delete From CTDONDH where SoDH=@SoDH
Thuc hien:
exec spud_DONDH_Xoa 'D001'
--5.4.c.XD proc sua doi DL trong bang DONDH voi ten spud_DONDH_Them gom:
-- 3 tham so vao(chinh la gia tri can thay doi cua cac cot trong DONDH): SoDH,MaNhaCC,NgayDH
-- Trong do can kiem tra tinh hop le cua DL:
-- - MaNhaCC phai co trong bang NHACC.
-- - NgayDH phai truoc ngay nhap hang(neu don dat hang da duoc nhap ve roi)
select *from DONDH
select *from NHACC
select *from PNHAP
Create proc spud_DONDH_Sua
@SoDH char(4),@NgayDH datetime, @MaNhaCC char(4)
as
Begin
if( not exists (select * from NHACC where @MaNhaCC = MaNhaCC))
print 'MaNhaCC phai co trong bang NHACC'
return
end
if(exists (select* from PNHAP where NgayNhap<@NgayDH))
print 'Ngay dat dat hang phai truoc ngay nhap hang!'
return
end
Update DONDH
set SoDH=@SoDH,NgayDH=@NgayDH,MaNhaCC=@MaNhaCC
where SoDH=@SoDH
End
Thuc hien:
exec spud_DONDH_Sua 'D001','01/01/2005','C001'
--5.4.d.XD proc Insert DL vao bang CTDONDH voi ten spud_CTDONDH_Them gom:
-- 3 tham so vao chinh la gia tri them moi vao CTDONDH:SoDH,MaVTU,SLDat.
-- Trong do can kiem tra tinh hop le cua DL:
-- - SoDH phai co trong bang DONDH.
-- - MaVTu phai co trong bang VATTU.
-- - SoDH va MaVTu phai duy nhat trong bang CTDONDH.
select *from CTDONDH
Create proc spud_CTDONDH_Them
@SoDH char(4),@MaVTU char(4),@SLDat int
as
Begin
if( not exists (select * from DONDH where SoDH = @SoDH))
print 'So dat hang phai co trong bang DONDH!'
return
end
if(not exists (select * from VATTU where MaVTu = @MaVTu))
print 'Ma vat tu phai co trong bang VATTU!'
return
end
if(exists (select * from CTDONDH where MaVTu = @MaVTu and SoDH = @SoDH ))
print 'Ma vat tu va So dat hang phai duy nhat trong bang CTDONDH!'
return
end
insert into CTDONDH values(@SoDH,@MaVTu,@SLDat)
End
Thuc hien:
exec spud_CTDONDH_Them 'D001','VD02','20'
--5.4.e.XD proc xoa CTDONDH voi ten spud_CTDONDH_Xoa gom:
-- 2 tham so dau vao la SoDH va MaVTu can xoa.
-- Trong do can kiem tra tinh hop le du lieu truoc khi xoa trong bang CTDONDH.
select *from CTDONDH
Create proc spud_CTDONDH_Xoa
@SoDH char(4),@MaVTu char(4)
as
Begin
if( not exists (select * from CTDONDH where MaVTu = @MaVTu ))
print 'Ma vat tu phai co trong bang CTDONDH!'
return
end
if( not exists (select * from CTDONDH where SoDH = @SoDH ))
print ' So dat hang phai co trong bang CTDONDH!'
return
end
delete from CTDONDH
where MaVTu = @MaVTu and SoDH = @SoDH
End
Thuc hien:
exec spud_CTDONDH_Xoa 'D001','VD02'
--5.4.f.XD proc sua doi DL trong bang CTDONDH voi ten spud_CTDONDH_Sua gom:
-- 3 tham so vao chinh la gia tri can thay doi cua cac cot trong bang CTDONDH:
-- SoDH,MaVTu,SLDat.
-- Trong do can kiem tra tinh hop le cua DL truoc khi update set de cap nhat du lieu vao bang CTDONDH:
-- - SoDH va MaVTu phai co trong bangCTDONDH.
-- - SLDat moi phai >=tong so luong da nhap hang(neu da co nhap hang).
select *from CTDONDH
drop proc spud_CTDONDH_Sua
Create proc spud_CTDONDH_Sua
@SoDH char(4), @MaVTu char(4), @SLDat int
as
Begin
if (not exists (select * from CTDONDH where SoDH=@SoDH and MaVTu=@MaVTu))
print' SoDH va MaVTu nay chua co trong bang DonDH!'
return
end
Declare @SLDatmoi int, @TongSLNhap int
select @SLDatmoi=SLDat from CTDONDH where SoDH=@SoDH and MaVTu=@MaVTu
select @TongSLNhap=sum(SLNhap ) from CTPNHAP where MaVTu=@MaVTu
if @SLDatmoi<@TongSLNhap
Print' So luong dat moi phai >= tong sl da nhap'
return
end
Update CTDONDH
Set SLDat=@SLDat
Where SoDH=@SoDH and MaVTu=@MaVTu
End
Thuc hien:
exec spud_CTDONDH_Sua 'D001','DD01',15
/* CAU HOI 5.1.Tao cac thu tuc noi tai tinh toan:
5.1.a.Xay dung TT tinh so luong dat hang voi ten spud_DONDH_TinhSLDat gom:
2 tham so vao: SoDH vaf MaVTu,1 tham so ra la: So dat hang cua mot vat tu theo mot SoDH.
5.1.b.XD TT tinh tong so luong da nhap hang voi ten spud_PNHAP_TinhTongSLNHang gom:
2 tham so dau vao la: SoDH vaf MaVTu, 1 tham so ra la: Tong so luong da nhap hang
cua 1 vattu theo mot SoDH.
5.1.c.XD TT tinh so luong dau ky cua mot vattu voi ten spud_TONKHO_TinhSLDau gom:
2 tham so dau vao la: NamThang,va MaVTu, 1 tham so ra la:So luong dau ky cua mot vattu
theo nam thang truyen vao.
*/
-- BAI LAM:
--5.1.a.Xay dung TT tinh so luong dat hang voi ten spud_DONDH_TinhSLDat gom:
-- 2 tham so vao: SoDH va MaVTu,1 tham so ra la: So dat hang cua mot vat tu theo mot SoDH.
select * from DONDH
select * from CTDONDH
Create proc spud_DONDH_TinhSLDat
@SoDH char(4),@MaVTu char(4),@SLDat int output
as
select @SLDat=SLDat
from CTDONDH
where @SoDH = SoDH and @MaVTu = MaVTu
Thuc hien:
Declare @a int
Set @a=0
exec spud_DONDH_TinhSLDat 'D001','DD01',@a output
print 'So dat hang cua vat tu DD01 la:'+cast(@a as char(4))
--5.1.b.XD TT tinh tong so luong da nhap hang voi ten spud_PNHAP_TinhTongSLNHang gom:
-- 2 tham so dau vao la: SoDH va MaVTu, 1 tham so ra la: Tong so luong da nhap hang
-- cua 1 vattu theo mot SoDH.
select * from PNHAP
select *from CTPNHAP
Create proc spud_PNHAP_TinhTongSLNHang
@SoDH char(4),@MaVTu char(4),@TongSLNhap int output
as
select @TongSLNHap=sum(SLNhap)
from CTPNHAP,PNHAP
where CTPNHAP.MaVTu=@MaVTu and PNHAP.SoDH=@SoDH and CTPNHAP.SoPN=PNHAP.SoPN
Thuc hien:
Declare @a int
set @a=0
exec spud_PNHAP_TinhTongSLNHang 'D001','DD01',@a output
print 'Tong so luong nhap cua vat tu DD01 la: '+cast(@a as char(4))
--5.1.c.XD TT tinh so luong dau ky cua mot vattu voi ten spud_TONKHO_TinhSLDau gom:
-- 2 tham so dau vao la: NamThang,va MaVTu, 1 tham so ra la:So luong dau ky cua mot vattu
-- theo nam thang truyen vao.
select *from TONKHO
Create proc spud_TONKHO_TinhSLDau
@NamThang char(6),@MaVTu char(4),@SLDau int output
as
select @SLDau=SLDau
from TONKHO
where NamThang=@NamThang and MaVTu=@MaVTu
Thuc hien:
Declare @a int
Set @a=0
exec spud_TONKHO_TinhSLDau '200501','DD01',@a output
print 'So luong dau cua vat tu DD01 la: '+ cast(@a as char(2))
/* CAU HOI 5.2.Tao cac TT noi tai cap nhat DL trong bang VATTU.Cac TT nay:
Co kiem tra cac rang buoc DL va Thong Bao ra cac loi ro rang khi DL vi pham cac rang buoc.
5.2.a.XD TT them moi DL vao bang VATTU voi ten spud_VATTU_Them gom:
4 tham so vao la gia tri them moi cho cac cot trong bang VATTU: MaVTu,TenVTU,DVTinh,PhanTram.
Trong do can kiem tra cac constraint Dl phai hop le truoc khi thuc hien INSERT INTO vao VATTTU
- MaVTu phai chua co trong bang VATTU.
5.2.b.XD TT xoa 1 vattu cotrong bang VATTU voi ten spud_VATTU_Xoa gom:
1 tham so vao:MaVTu can xoa.
Trong do can kiem tra constraint truoc khi delete trong VATTU:
- MaVTu phai chua co trong CTDONDH.
- MaVTu phai chua co trong CTPNHAP.
- MaVTu phai chua co trong CATPXUAT.
- MaVTu phai chua co trong TONKHO.
5.2.c.XD Tt sua doi vattu trong bang VATTU voi ten spud_VATTU_Sua gom:
4 tham so vao: (chinh la gia tri can thay doi cua cac cot trong bang VATTU tru cot MaVTu):
MaVTu,TenVTu,DVTinh,PhanTram.
Trong TT chi thuc hien UPDATE SET de cap nhat DL vao bang VATTU voi cac gia tri tuong ung.
*/
BAI LAM:
--5.2.a.XD TT them moi DL vao bang VATTU voi ten spud_VATTU_Them gom:
-- 4 tham so vao la gia tri them moi cho cac cot trong bang VATTU: MaVTu,TenVTU,DVTinh,PhanTram.
-- Trong do can kiem tra cac constraint DL phai hop le truoc khi thuc hien INSERT INTO vao VATTTU
-- - MaVTu phai chua co trong bang VATTU.
select *from VATTU
Create proc spud_VATTU_Them
@MaVTu char(4), @TenVTu nvarchar(100), @DVTinh nvarchar(50), @PhanTram real
as
if exists ( select * from VATTU where MaVTu=@MaVTu)
print ' MaVTu nay da co trong bang VATTU!'
else
insert into VATTU values (@MaVTu,@TenVTu,@DVTinh,@PhanTram)
Thuc hien:
exec spud_VATTU_Them 'CPU','CPU','chiec','10'
--5.2.b.XD TT xoa 1 vattu cotrong bang VATTU voi ten spud_VATTU_Xoa gom:
-- 1 tham so vao:MaVTu can xoa.
-- Trong do can kiem tra constraint truoc khi delete trong VATTU:
-- - MaVTu phai chua co trong CTDONDH.
-- - MaVTu phai chua co trong CTPNHAP.
-- - MaVTu phai chua co trong CATPXUAT.
-- - MaVTu phai chua co trong TONKHO.
select *from VATTU
Create proc spud_VATTU_Xoa
@MaVTu char(4)
as
begin if exists (select * from CTDONDH where CTDONDH.MaVTu=@MaVTu)
print ' MaVTu nay da co trong bang CTDONDH'
else if exists( select * from CTPNHAP where CTPNHAP.MaVTu=@MaVTu)
print ' MAVTU nay da co trong bang CTPNHAP'
else
if exists( select * from CTPXUAT where CTPXUAT.MaVTu=@MaVTu)
print ' MaVTu nay da co trong bang CTPXUAT'
else
if exists( select * from TONKHO where TONKHO.MAVTu=@MaVTu)
print ' MaVTu nay da co trong bang TONKHO'
else delete from VATTU where MaVtu=@MaVTu
end
Thuc hien:
exec spud_VATTU_Xoa 'DD01'
--5.2.c.XD Tt sua doi vattu trong bang VATTU voi ten spud_VATTU_Sua gom:
-- 4 tham so vao: (chinh la gia tri can thay doi cua cac cot trong bang VATTU tru cot MaVTu):
-- MaVTu,TenVTu,DVTinh,PhanTram.
-- Trong TT chi thuc hien UPDATE SET de cap nhat DL vao bang VATTU voi cac gia tri tuong ung.
select * from VATTU
drop proc spud_VATTU_Sua
Create proc spud_VATTU_Sua
@MaVTu char(4),@TenVTu varchar(100),@DVTinh varchar(10),@PhanTram real
as
if((select count(*) from VATTU where MaVTu=@MaVTu)>0)
Update VATTU
Set TenVTu = @TenVTu,
DVTinh = @DVTinh,
PhanTram = @PhanTram
where MaVTu=@MaVTu
end
else
print 'MaVTu nay khong co trong bang VATTU'
return
end
end
Thuc hien:
exec spud_VATTU_Sua 'CPUi','central processing unit','chiec','10'
exec spud_VATTU_Sua 'CPU','central processing unit','chiec','10'
/* CAU HOI 5.3. Tao cac TT noi tai Liet ke du lieu:
5.3.a.XD TT liet ke cac cot DL trong bang VATTU voi ten spud_VATTU_BcaoDanhSach, TT nay khong co tham so.
Cong viec cua TT nay chi la mot cau lenh select * from VATTU co sap xep theo thu tu TenVTu tang dan
5.3.b.XD TT liet ke cac cot DL trong bang TONKHO co the hien them cot TenVTu trong bang VATTU
voi ten spud_TONKHO_BcaoTonKho gom co:
1 tham so vao la: NamThang muon loc DL.
5.3.c.XD TT liet ke cac cot DL trong 2 bang DL PXUAT vaf CTPXUAT co the hien them cot TenVTU
trong bang VATTU voi ten spud_PXUAT_BcaoPXuat gom:
1 tham so vao la: SoPX muon loc DL co gia tri mac dinh la null.
Nhung neu luc goi thuc hien TT ma khong truyen gia tri SoPX vao thi xem nhu khong loc gi ca,
Khi do TT se liet ke tat ca cac phieu xuat dang co trong bang PXUAT.
*/
BAI LAM:
--5.3.a.XD TT liet ke cac cot DL trong bang VATTU voi ten spud_VATTU_BcaoDanhSach, TT nay khong co tham so.
-- Cong viec cua TT nay chi la mot cau lenh select * from VATTU co sap xep theo thu tu TenVTu tang dan
Create proc spud_VATTU_BcaoDanhSach
as
select *
from VATTU
order by TenVTu asc
Thuc hien:
exec spud_VATTU_BcaoDanhSach
--5.3.b.XD TT liet ke cac cot DL trong bang TONKHO co the hien them cot TenVTu trong bang VATTU
-- voi ten spud_TONKHO_BcaoTonKho gom co:
-- 1 tham so vao la: NamThang muon loc DL.
select * from TONKHO
Create proc spud_TONKHO_BcaoTonKho
@NamThang char(6)
as
select NamThang,TONKHO.MaVTu,TenVTu,SLDau,TongSlN,TongSLX,SLCuoi
from TONKHO,VATTU
where @NamThang=NamThang and TONKHO.MaVTu=VATTU.MaVTu
Thuc hien:
exec spud_TONKHO_BcaoTonKho '200501'
--5.3.c.XD TT liet ke cac cot DL trong 2 bang DL PXUAT vaf CTPXUAT co the hien them cot TenVTU
-- trong bang VATTU voi ten spud_PXUAT_BcaoPXuat gom:
-- 1 tham so vao la: SoPX muon loc DL co gia tri mac dinh la null.
-- Nhung neu luc goi thuc hien TT ma khong truyen gia tri SoPX vao thi xem nhu khong loc gi ca,
-- Khi do TT se liet ke tat ca cac phieu xuat dang co trong bang PXUAT.
select * from PXUAT
select * from CTPXUAT
drop proc spud_PXUAT_BcaoPXuat
Create proc spud_PXUAT_BcaoPXuat
@SoPX char(4)
as
if(@SoPX is null)
(select PXUAT.SoPX,NgayXuat,TenKH,MaVTu,SLXuat,DgXuat
from PXUAT,CTPXUAT
where PXUAT.SoPX=CTPXUAT.SoPX
else
(select PXUAT.SoPX,NgayXuat,TenKH,MaVTu,SLXuat,DgXuat
from PXUAT,CTPXUAT
where PXUAT.SoPX=CTPXUAT.SoPX and PXUAT.SoPX=@SoPX
Thuc hien:
exec spud_PXUAT_BcaoPXuat ''
exec spud_PXUAT_BcaoPXuat 'X001'
/* CAU HOI 5.4. Tao proc dung de update DL trong bang DONDH,CTDONDH de ve sau su dung trong man hinh DONDH.
Cac proc nay co Ktra constraint va thong bao cac loi ro rang khi DL vi pham constraint.
5.4.a.XD proc INSERT DL vao DONDH voi ten spud_DONDH_Them gom:
3 tham so vao (chinh la gia tri them moi cho cac cot trong bang DONDH):SoDH,MaNhaCC,NgayDH.
Trong do phai kiem tra constraint:
- SoDH phai duy nhat.
- MaNhaCC phai co trong bang NHACC.
- NgayDH co the khong truyen vao(optional), khi do se lay gia tri mac dinh la ngay hien hanh.
5.4.b.XD proc xoa DONDH voi ten spud_DONDH_Xoa gom:
1 tham so vao:SoDH can xoa.
Neu DL hop le thi xoa luon cac dong DL lien quan trong bang CTDONDH.
Trong do can kiem tra cac constraint:
- SoDH phai chua co trong bang PNHAP.
5.4.c.XD proc su doi DL trong bang DONDH voi ten spud_DONDH_Them gom:
3 tham so vao(chinh la gia tri can thay doi cua cac cot trong DONDH): SoDH,MaNhaCC,NgayDH
Trong do can kiem tra tinh hop le cua DL:
- MaNhaCC phai co trong bang NHACC.
- NgayDH phai truoc ngay nhap hang(neu don dat hang da duoc nhap ve roi)
5.4.d.XD proc Insert DL vao bang CTDONDH voi ten spud_CTDONDH_Them gom:
3 tham so vao chinh la gia tri them moi vao CTDONDH:SoDH,MaVTU,SLDat.
Trong do can kiem tra tinh hop le cua DL:
- SoDH phai co trong bang DONDH.
- MaVTu phai co trong bang VATTU.
- SoDH va MaVTu phai duy nhat trong bang CTDONDH.
5.4.e.XD proc xoa CTDONDH voi ten spud_CTDONDH_Xoa gom:
2 tham so dau vao la SoDH va MaVTu can xoa.
Trong do can kiem tra tinh hop le du lieu truoc khi xoa trong bang CTDONDH.
5.4.f.XD proc sua doi DL trong bang CTDONDH voi ten spud_CTDONDH_Sua gom:
3 tham so vao chinh la gia tri can thay doi cua cac cot trong bang CTDONDH:
SoDH,MaVTu,SLDat.
Trong do can kiem tra tinh hop le cua DL truoc khi update set de cap nhat du lieu vao bang CTDONDH:
- SoDH va MaVTu phai co trong bangCTDONDH.
- SLDat moi phai >=tong so luong da nhap hang(neu da co nhap hang).
*/
BAI LAM:
--5.4.a.XD proc INSERT DL vao DONDH voi ten spud_DONDH_Them gom:
-- 3 tham so vao (chinh la gia tri them moi cho cac cot trong bang DONDH):SoDH,MaNhaCC,NgayDH.
-- Trong do phai kiem tra constraint:
-- - SoDH phai duy nhat.
-- - MaNhaCC phai co trong bang NHACC.
-- - NgayDH co the khong truyen vao(optional), khi do se lay gia tri mac dinh la ngay hien hanh.
select * from DONDH
Create proc spud_DONDH_Them
@SoDH char(4),@MaNhaCC char(4),@NgayDH datetime
as
Begin
if(exists (select *from DONDH where @SoDH=SoDH))
print 'SoDH phai duy nhat.SoDH nay da co trong bang DONDH!'
return
end
if(not exists (select * from NHACC where @MaNhaCC=MaNhaCC))
print 'MaNhaCC phai co trong bang NHACC!'
return
end
if(@NgayDH is null)
insert into DONDH values(@SoDH,getdate(),@MaNhaCC)
else
insert into DONDH values(@SoDH,@NgayDH,@MaNhaCC)
End
Thuc hien:
exec spud_DONDH_Them 'D006','01/01/2008','C01'
--5.4.b.XD proc xoa DONDH voi ten spud_DONDH_Xoa gom:
-- 1 tham so vao:SoDH can xoa.
-- Neu DL hop le thi xoa luon cac dong DL lien quan trong bang CTDONDH.
-- Trong do can kiem tra cac constraint:
-- - SoDH phai chua co trong bang PNHAP.
Create proc spud_DONDH_Xoa
@SoDH char(4)
as
if exists (select * from PNHAP where SoDH=@SoDH)
print 'so dh da co trong bang phieu nhap'
return
end
delete From DONDH where SoDH=@SoDH
delete From CTDONDH where SoDH=@SoDH
Thuc hien:
exec spud_DONDH_Xoa 'D001'
--5.4.c.XD proc sua doi DL trong bang DONDH voi ten spud_DONDH_Them gom:
-- 3 tham so vao(chinh la gia tri can thay doi cua cac cot trong DONDH): SoDH,MaNhaCC,NgayDH
-- Trong do can kiem tra tinh hop le cua DL:
-- - MaNhaCC phai co trong bang NHACC.
-- - NgayDH phai truoc ngay nhap hang(neu don dat hang da duoc nhap ve roi)
select *from DONDH
select *from NHACC
select *from PNHAP
Create proc spud_DONDH_Sua
@SoDH char(4),@NgayDH datetime, @MaNhaCC char(4)
as
Begin
if( not exists (select * from NHACC where @MaNhaCC = MaNhaCC))
print 'MaNhaCC phai co trong bang NHACC'
return
end
if(exists (select* from PNHAP where NgayNhap<@NgayDH))
print 'Ngay dat dat hang phai truoc ngay nhap hang!'
return
end
Update DONDH
set SoDH=@SoDH,NgayDH=@NgayDH,MaNhaCC=@MaNhaCC
where SoDH=@SoDH
End
Thuc hien:
exec spud_DONDH_Sua 'D001','01/01/2005','C001'
--5.4.d.XD proc Insert DL vao bang CTDONDH voi ten spud_CTDONDH_Them gom:
-- 3 tham so vao chinh la gia tri them moi vao CTDONDH:SoDH,MaVTU,SLDat.
-- Trong do can kiem tra tinh hop le cua DL:
-- - SoDH phai co trong bang DONDH.
-- - MaVTu phai co trong bang VATTU.
-- - SoDH va MaVTu phai duy nhat trong bang CTDONDH.
select *from CTDONDH
Create proc spud_CTDONDH_Them
@SoDH char(4),@MaVTU char(4),@SLDat int
as
Begin
if( not exists (select * from DONDH where SoDH = @SoDH))
print 'So dat hang phai co trong bang DONDH!'
return
end
if(not exists (select * from VATTU where MaVTu = @MaVTu))
print 'Ma vat tu phai co trong bang VATTU!'
return
end
if(exists (select * from CTDONDH where MaVTu = @MaVTu and SoDH = @SoDH ))
print 'Ma vat tu va So dat hang phai duy nhat trong bang CTDONDH!'
return
end
insert into CTDONDH values(@SoDH,@MaVTu,@SLDat)
End
Thuc hien:
exec spud_CTDONDH_Them 'D001','VD02','20'
--5.4.e.XD proc xoa CTDONDH voi ten spud_CTDONDH_Xoa gom:
-- 2 tham so dau vao la SoDH va MaVTu can xoa.
-- Trong do can kiem tra tinh hop le du lieu truoc khi xoa trong bang CTDONDH.
select *from CTDONDH
Create proc spud_CTDONDH_Xoa
@SoDH char(4),@MaVTu char(4)
as
Begin
if( not exists (select * from CTDONDH where MaVTu = @MaVTu ))
print 'Ma vat tu phai co trong bang CTDONDH!'
return
end
if( not exists (select * from CTDONDH where SoDH = @SoDH ))
print ' So dat hang phai co trong bang CTDONDH!'
return
end
delete from CTDONDH
where MaVTu = @MaVTu and SoDH = @SoDH
End
Thuc hien:
exec spud_CTDONDH_Xoa 'D001','VD02'
--5.4.f.XD proc sua doi DL trong bang CTDONDH voi ten spud_CTDONDH_Sua gom:
-- 3 tham so vao chinh la gia tri can thay doi cua cac cot trong bang CTDONDH:
-- SoDH,MaVTu,SLDat.
-- Trong do can kiem tra tinh hop le cua DL truoc khi update set de cap nhat du lieu vao bang CTDONDH:
-- - SoDH va MaVTu phai co trong bangCTDONDH.
-- - SLDat moi phai >=tong so luong da nhap hang(neu da co nhap hang).
select *from CTDONDH
drop proc spud_CTDONDH_Sua
Create proc spud_CTDONDH_Sua
@SoDH char(4), @MaVTu char(4), @SLDat int
as
Begin
if (not exists (select * from CTDONDH where SoDH=@SoDH and MaVTu=@MaVTu))
print' SoDH va MaVTu nay chua co trong bang DonDH!'
return
end
Declare @SLDatmoi int, @TongSLNhap int
select @SLDatmoi=SLDat from CTDONDH where SoDH=@SoDH and MaVTu=@MaVTu
select @TongSLNhap=sum(SLNhap ) from CTPNHAP where MaVTu=@MaVTu
if @SLDatmoi<@TongSLNhap
Print' So luong dat moi phai >= tong sl da nhap'
return
end
Update CTDONDH
Set SLDat=@SLDat
Where SoDH=@SoDH and MaVTu=@MaVTu
End
Thuc hien:
exec spud_CTDONDH_Sua 'D001','DD01',15
/* CAU HOI6.1.XD ham don tri:
6.1.a.Fn_TongNhapThang (@NamThang,@MaVTu) tra ve so luong da nhap trong thang cua vat tu.
Chu y:@NamThang co dang YYYYMM
6.1.b. Fn_TongXuatThang(@NamThang,@MaVTu) tr ve so luong da xuat trong thang cua vat tu.
6.1.c.Fn_TongNhap(@SoDH,@MaVTu) tr ve tong so luong da nhap cua vattu theo SoDH.
6.1.d.Su dungham Fn_TongNhap da tao de vietham Fn_ConNHap(@SoDH,@MaVTu)
Tra ve so luong con phai nhap cua vat tu theo SoDH.
6.1.e.Fn_TonCuoi(@MaVTu,@NamThang) tra ve so luong ton cuoi ky cua vat tu theo NamThang.
*/
BAI LAM:
--6.1.a.Fn_TongNhapThang (@NamThang,@MaVTu) tra ve so luong da nhap trong thang cua vat tu.
select *from CTPNHAP
select *from PNHAP
Create function Fn_TongNhapThang(@NamThang char(6),@MaVTu char(4))
returns int
as
Begin
Declare @TongSL int
select @TongSL=sum(SLNhap)
from CTPNHAP,PNHAP
where CTPNHAP.SoPN = PNHAP.SoPN and @MaVTu=MaVTu and
@NamThang =(right(convert(char(10),NgayNhap,101),4)+left(convert(char(10),NgayNhap,101),2))
return @TongSL
End
Thuc hien:
print DBO.Fn_TongNhapThang('200501','DD01')
--6.1.b. Fn_TongXuatThang(@NamThang,@MaVTu) tr ve so luong da xuat trong thang cua vat tu.
select *from CTPXUAT
select *from PXUAT
Create function Fn_TongXuatThang(@NamThang char(6),@MaVTu char(4))
returns int
as
Begin
Declare @SLXuat int
select @SLXuat=sum(SLXuat)
from CTPXUAT,PXUAT
where CTPXUAT.SoPX = PXUAT.SoPX and @MaVTu=MaVTu and
@NamThang =(right(convert(char(10),NgayXuat,101),4)+left(convert(char(10),NgayXuat,101),2))
return @SLXuat
End
Thuc hien:
print DBO.Fn_TongXuatThang('200501','DD01')
--6.1.c.Fn_TongNhap(@SoDH,@MaVTu) tra ve tong so luong da nhap cua vattu theo SoDH.
select *from CTPNHAP
select *from PNHAP
Create function Fn_TongNhap (@SoDH char(4) ,@MaVTu char(4))
returns int
as
Begin
Declare @SLNhap int
select @SLNhap=sum(SLNhap)
from CTPNHAP,PNHAP
where CTPNHAP.SoPN=PNHAP.SoPN and MaVTu=@MaVTu and SoDH=@SoDH
group by SoDH
return @SLNhap
End
Thuc hien:
print DBO.Fn_TongNhap('D001','DD01')
----6.1.d.Su dungham Fn_TongNhap da tao de viet ham Fn_ConNHap(@SoDH,@MaVTu)
-- Tra ve so luong con phai nhap cua vat tu theo SoDH.
select * from CTDONDH
Create function Fn_ConNhap (@SoDH char(4) ,@MaVTu char(4))
returns int
as
Begin
Declare @ConNhap int
select @ConNhap=SlDat-DBO.Fn_TongNhap(@SoDH,@MaVTu)
from CTDONDH
where CTDONDH.SoDH=@SoDH and CTDONDH.MaVTu=@MaVTu
return @ConNhap
End
Thuc hien:
print DBO.Fn_ConNhap('D001','DD01')
--6.1.e.Fn_TonCuoi(@MaVTu,@NamThang) tra ve so luong ton cuoi ky cua vat tu theo NamThang.
select *from TONKHO
Create function Fn_TonCuoi(@MaVTu char(4),@NamThang char(6))
returns int
as
Begin
Declare @SlCuoi int
select @Slcuoi=SLCuoi
from TONKHO
where MaVTu=@MaVTu and @NamThang = NamThang
return @SLCuoi
End
Thuc hien:
print DBO.Fn_TonCuoi('DD01','200501')
/*CAU HOI: 6.2. XD cac ham doc bang:
6.2.a. Su dung ham Fn-ConNhap da tao de viet ham Fn_DS_VatTuConNhap(@SoDH)
Liet ke SoDH,MaVTu,TenVTu,SLConNhap cua cac vat tu chua nhap du.
6.2.b.Fn_DS_VatTuTonKho(@NamThang,@ToiThieu)
Liet ke danh sach cac vattu trong @NamThang voi so luong ton kho muc toi thieu la @ToiThieu.
*/
BAI LAM:
--6.2.a. Su dung ham Fn_ConNhap da tao de viet ham Fn_DS_VatTuConNhap(@SoDH)
-- Liet ke SoDH,MaVTu,TenVTu,SLConNhap cua cac vat tu chua nhap du.
DBO.Fn_ConNhap('D001','DD01')
DBO.Fn_ConNhap(@SoDH,@MaVTu)
select *from CTPNHAP
select *from CTDONDH
Create function Fn_DS_VatTuConNhap (@SoDH char(4))
returns table
as
return(
select CTDONDH.SoDH,VATTU.MaVTu,VATTU.TenVTu,SLConNhap= DBO.Fn_ConNhap(@SoDH,CTDONDH.MaVTu)
from VATTU,CTDONDH
where SoDH=@SoDH and DBO.Fn_ConNhap(@SoDH,CTDONDH.MaVTu) !=0 and VATTU.MaVTu=CTDONDH.MaVTu )
Thuc hien:
select *from Fn_DS_VatTuConNhap ('D001')
--6.2.b.Fn_DS_VatTuTonKho(@NamThang,@ToiThieu)
-- Liet ke danh sach cac vattu trong @NamThang voi so luong ton kho muc toi thieu la @ToiThieu.
select *from TONKHO
Create function Fn_DS_VatTuTonKho (@NamThang char(6),@ToiThieu int)
returns table
as
return( select *
from TONKHO
where @NamThang=NamThang and @ToiThieu<=SLCuoi
Thuc hien:
select *from Fn_DS_VatTuTonKho('200501','5')
/* CAU HOI 6.3. XD ham tao bang:
6.3.a.Su dung ham Fn-TonCuoi da tao de viet ham Fn_DS_TonKho_Lech_1Thang(@NamThang)
Liet ke cac thong tin trong bang TONKHO cua cac vattu ma thong tin dau ky cua NamThang truyen vao
khong bang thong tinton cuoi ky cua NamThang truoc.
6.3.b.Su dung ham Fn_DS_TonKho_Lech_1Thang(@NamThang) da tao o tren de viet ham Fn_DS_TonKho_Lech()
Liet ke cac thong tin trong bang TONKHO cua cac vattu ma
thong tin ton dau ky cua thang nay khong bang thong tin ton cuoi ky cua thang truoc
*/
BAI LAM:
--6.3.a.Su dung ham Fn-TonCuoi da tao de viet ham Fn_DS_TonKho_Lech_1Thang(@NamThang)
-- Liet ke cac thong tin trong bang TONKHO cua cac vattu ma thong tin dau ky cua NamThang truyen vao
-- khong bang thong tinton cuoi ky cua NamThang truoc.
select * from TONKHO
DBO.Fn_TonCuoi('DD01','200501')
DBO.Fn_TonCuoi(MaVTu,NamThang)
Create function Fn_DS_TonKho_Lech_1Thang(@NamThang char(6))
returns @BangTK table
NamThang char(6),
MaVTu char(4),
SLDau int,
TongSLN int,
TongSLX int,
SLCuoi int
as
Begin
Insert into @BangTK(NamThang,MaVTu,SLDau,TongSLN,TongSLX,SLCuoi)
select *
from TONKHO
where @NamThang=NamThang and DBO.Fn_TonCuoi(TONKHO.MaVTu,(left(@NamThang,4)+cast((cast(right(@NamThang,2) as int)-1) as char(2))))
return
End
-- Thay oi, chi co 2 thang la 200501 va 200502 thoi. lam the nao de tinh duoc thang truoc khi nhap vao thang nay?
-- Tức là làm thế nào để tính được tháng trước của tháng 200502 là 200501 hả thầy?
--Em làm như thế đã đúng chưa hả thầy?
-- Phần này em ghi bị thiếu. Có gì sai xót thầy gửi cho em qua email của em nhé thầy:
-- Em cảm ơn thầy!!!
--6.3.b.Su dung ham Fn_DS_TonKho_Lech_1Thang(@NamThang) da tao o tren de viet ham Fn_DS_TonKho_Lech()
-- Liet ke cac thong tin trong bang TONKHO cua cac vattu ma
-- thong tin ton dau ky cua thang nay khong bang thong tin ton cuoi ky cua thang truoc
/*7.1
Tao trigger them moi du lieu de kiem tra cac rang buoc toan ven du lieu nhu yeu cau
ben duoi
a. Xay dung trigger khi them moi du lieu vao bang PNHAP voi ten Tg_phieunhap_them
trong do
can kiem tra cac rang buoc du lieu phai hop le.
-So dh phai co trong bang don dh
-Ngay nhap hang phai sau ngay dat hang
b. Xd trigger khi Insert vao bang CTPNHAP voi ten Tg_CTPNHAP_Them trong do can kt cac
rang buoc du lieu phai hop le
-So luong nhap hang <= (So luong dat-Tong so luong da nhap truoc do)
*/
--BAI LAM:
--a. Xay dung trigger khi them moi du lieu vao bang PNHAP voi ten Tg_phieunhap_them
--trong do
--can kiem tra cac rang buoc du lieu phai hop le.
---So dh phai co trong bang don dh
---Ngay nhap hang phai sau ngay dat hang
drop trigger Tg_PNHAP_Them
create trigger Tg_PNHAP_Them
on PNHAP
For Insert
as
Begin
Declare @SoDH char(4), @NgayNhap datetime
select @SoDH=SoDH,@NgayNhap=NgayNhap
from Inserted
If(exists (select * from DONDH where SoDH= @SoDH and NgayDH<=@NgayNhap))
print ' Hop le'
else
RollBack Tran
if(( select count(*) from DONDH where SoDh=@SoDH and NgayDH>@NgayNhap )>0)
print 'Ngay Nhap phai sau ngay dat hang'
else if ((select count(*) from DONDH where SoDH!= @SoDH and NgayDh<=@NGayNhap)>0)
print 'SoDh phai co trong bang DONDH'
else
print 'Ngay Nhap phai sau ngay dat hang va SoDH phai co trong bang DONDH'
end
End
select * from PNhap
select * from DonDH
insert into PNHAP values('N011','01/01/2009','D000')
----7.1.b. Xd trigger khi Insert vao bang CTPNHAP voi ten Tg_CTPNHAP_Them trong do can kt cac
-- rang buoc du lieu phai hop le
-- So luong nhap hang <= (So luong dat-Tong so luong da nhap truoc do)
select *from PNHAP
select *from CTPNHAP
create trigger Tg_CTPNHAP_Them
on CTPNHAP
for Insert
as
Begin
Declare @DaNhap int,@SLDat int,@MaVTu char(4),@Nhap int
select @MaVTu=MaVTu,@Nhap=SLNhap
from Inserted
select @DaNhap=sum(SLNhap)
from CTPNHAP
where @MaVTu=MaVTu
select @SLDat=SLDat
from CTDONDH
where @MaVTU=MaVTu
if(@Nhap<=(@SLDat-@DaNhap))
print 'Hop le'
else
RollBack Tran
print 'Tong so luong nhap vuot qua so luong dat hang!'
end
End
insert into CTPNHAP values('N006','TV29',20,3500000)
/* CAU HOI. 7.2.
Tao trigger khi xoa du lieu dung de kiem tra cac rang buoc toan ven du lieu nhu yc:
7.2.a.XD Trigger khi xoa DL trong bang PXUAT voi ten tg_PXUAT_Xoa.
Trong do can thuc hien : tu dong xoa cac dong du lieu lien quan ben bang CTPXUAT
7.2.b.XD Trigger khi xoa DL trong bang PNHAP voi ten tg_PNHAP_Xoa:
Thuc hien tu dong xoa cac dong DL lien quan ben bang CTPNHAP
*/
-- BAI LAM:
--7.2.a.XD Trigger khi xoa DL trong bang PXUAT voi ten tg_PXUAT_Xoa.
-- Trong do can thuc hien : tu dong xoa cac dong du lieu lien quan ben bang CTPXUAT
create Trigger tg_PXUAT_Xoa
on PXUAT
for Delete
as
declare @SoPX char(4)
select @SoPX=SoPX
from Deleted
Delete from CTPXUAT
where @SoPX=SoPX
end
end
--7.2.b.XD Trigger khi xoa DL trong bang PNHAP voi ten tg_PNHAP_Xoa:
--Thuc hien tu dong xoa cac dong DL lien quan ben bang CTPNHAP
Create Trigger tg_PNHAP_Xoa
on PNHAP
for Delete
as
Begin
Declare @SoPN char(4)
select @SoPN=SoPN
from Deleted
Delete from CTPNHAP
Where @SoPN=SoPN
end
End
/*. CAU HOI 7.3. Khi sua Du lieu de kiem tra cac rang buoc toan ven du lieu
7.3.a.XD Trigger khi sua du lieu trong bang PNHAP voi ten tg_PNHAP_Sua:
- Khong cho phep sua doi gia tri cua cac cot: So nhap hang,so dat hang
- Kiem tra cac gia tri moi cua cot ngaynhap hang phai sau ngay dat hang
7.3.b.XD Trigger khu sua DL trong bang PXUAT voi ten tg_PXUAT_Sua:
- Khong cho phep sua doi gia tri cot so phieu xuat
- Kiem tra gia tri moi cua ngay xuat phai cung nam thang voi gia tricua cua ngay xuat.
Neu khac nhau thi thong bao loi khong cho sua doi.
*/
-- BAI LAM:
--7.3.a.XD Trigger khi sua du lieu trong bang PNHAP voi ten tg_PNHAP_Sua:
-- - Khong cho phep sua doi gia tri cua cac cot: So nhap hang,so dat hang
-- - Kiem tra cac gia tri moi cua cot ngay nhap hang phai sau ngay dat hang
drop trigger tg_PNHAP_Sua
Create Trigger tg_PNHAP_Sua
on PNHAP
for Update
as
Begin
Declare @SoPN char(4), @SoDH char(4)
select @SoPN=SoPN, @SoDH=SoDH
from Deleted
if( exists (select *from Inserted where @SoPN!=SoPN or @SoDH!=SoDH))
RollBack Tran
print 'Kiem tra lai SoPN hoac SoDH!'
print 'Khong duoc sua mot trong 2 cot SoPN va SoDH!'
end
Declare @NgayDH datetime
select @NgayDH=NgayDH
from DONDH
where SoDH=@SoDH
if(exists (select*from PNHAP where NgayNhap<@NgayDH and SoDH=@SoDH))
RollBack Tran
print 'Ngay nhap hang phai sau ngay dat hang!'
end
End
-- Hoac lam theo cach sau:
Create Trigger tg_PNHAP_Sua
on PNHAP
for Update
as
Begin
If(Update(SoPN) or Update(SoDH))
RollBack Tran
print 'Kiem tra lai SoPN hoac SoDH!'
print 'Khong duoc sua mot trong 2 cot SoPN va SoDH!'
end
If(Update(NgayNhap))
Declare @NgayDH datetime
select @NgayDH=NgayDH
from DONDH
If(NgayNhap<@NgayDH)
RollBack Tran
print 'Ngay nhap hang phai sau ngay dat hang!'
end
end
End
--7.3.b.XD Trigger khu sua DL trong bang PXUAT voi ten tg_PXUAT_Sua:
-- - Khong cho phep sua doi gia tri cot so phieu xuat
-- - Kiem tra gia tri moi cua ngay xuat phai cung nam thang voi gia tri cua ngay xuat.
-- Neu khac nhau thi thong bao loi khong cho sua doi.
Drop trigger tg_PXUAT_Sua
Create trigger tg_PXUAT_Sua
on PXUAT
for Update
as
Begin
Declare @SoPX char(4)
select @SoPX=SoPX
from Deleted
if(exists (select *from Inserted where @SoPX = SoPX) and ((select SoPX from Inserted) ! = @SoPX ))
RollBack Tran
print ' Khong duoc sua SoPX!'
end
Declare @NamThang char(6)
select @NamThang=(right(convert(char(6),NgayXuat,101),4)+ left(convert(char(6),NgayXuat,101),2))
from Inserted
if (exists(select * from Deleted where (right(convert(char(6),NgayXuat,101),4)+ left(convert(char(6),NgayXuat,101),2))!=@NamThang))
RollBack Tran
print 'Khong cho phep sua doi nam thang cua ngay xuat!'
end
End
/* CAU HOI 7.4.Tao trigger Khi them moi du lieu de kiem tra cac rang buoc toan ven DL
va tinh toan tu dong
7.4.a.Trong bang PNHAP tao them cot TongTriGia co ten TongTG kieu float dung de: luu
tong tri gia cua 1 phieu nhap hang. trong trigger tg_CTPNHAP_Them da xay dung truoc do o phan 1b.
Bo sung them cac tinh toan tu dong sau:
- Tang gia tri cot TongTG trong bang PNHAPkhi DL trong bang CTPNHAP duoc them vao
- Tang gia tri cot TongSLN trong bang TONKHO khi DL trong bang CTPNHAP duoc them vao
7.4.b.Trong bang PXUAT tao them cot TONGTG kieu float dung de:
luu trong trigia cua 1 phieu xuat hang.
XD Trigger khi them moi DL vao bang CTPXUAT voi ten tg_PXUAT_Them. Trong do:
- Kiem tra SoPX phai ton tai trong bang PXUAT
- Kiem tra MaVTu phai ton tai trong bang VATTU
- Kiem tra SLXuat phai du trong bang TONKHO
- Kiem tra DgXuat phai duong.
Neu tat ca cac rang buoc o tren deu hop le thi tu dong thuc hien:
- Tang gia tri tai cot TONGTG trong bang PXUAT
- Tang gia tri tai cot TONGSLX trong bang TONKHO.
*/
-- BAI LAM:Tao trigger:
--7.4.a.Trong bang PNHAP tao them cot TongTriGia co ten TongTG kieu float dung de: luu
-- tong tri gia cua 1 phieu nhap hang. trong trigger tg_CTPNHAP_Them
-- da xay dung truoc do o phan 1b.
-- Bo sung them cac tinh toan tu dong sau:
-- - Tang gia tri cot TongTG trong bang PNHAPkhi DL trong bang CTPNHAP duoc them vao
-- - Tang gia tri cot TongSLN trong bang TONKHO khi DL trong bang CTPNHAP duoc them vao
select * from ctPNHAP
select * from PNHAP
select * from TONKHO
Alter table PNHAP
add TongTG int
Update PNHAP
set TongTG=(select sum(SLNhap*DgNhap)from CTPNHAP where CTPNHAP.SoPN=PNHAP.SoPN group by SoPN)
from CTPNHAP
where CTPNHAP.SoPN=PNHAP.SoPN
Create trigger tg_CTPNHAP_Them_2
on CTPNHAP
for Insert
as
Begin
Declare @DaNhap int,@SLDat int,@MaVTu char(4),@Nhap int
select @MaVTu=MaVTu,@Nhap=SLNhap
from Inserted
select @DaNhap=sum(SLNhap)
from CTPNHAP
where @MaVTu=MaVTu
select @SLDat=SLDat
from CTDONDH
where @MaVTU=MaVTu
if(@Nhap<=(@SLDat-@DaNhap))
print 'Hop le'
Update PNHAP
set TongTG=(select sum(SLNhap*DgNhap)from CTPNHAP where CTPNHAP.SoPN=PNHAP.SoPN group by SoPN)
from CTPNHAP
where CTPNHAP.SoPN=PNHAP.SoPN
Update TONKHO
set TongSLN=TongSLN+(select SLNhap from Inserted)
from TONKHO,PNHAP
where TONKHO.MaVTu=@MaVTu and TONKHO.NamThang=(right(convert(char(10),NgayNhap,101),4)+left(convert(char(10),NgayNhap,101),2))
end
else
RollBack Tran
print 'Tong so luong nhap vuot qua so luong dat hang!'
end
End
--7.4.b.Trong bang PXUAT tao them cot TONGTG kieu float dung de:
-- luu trong trigia cua 1 phieu xuat hang.
-- XD Trigger khi them moi DL vao bang CTPXUAT voi ten tg_PXUAT_Them. Trong do:
-- - Kiem tra SoPX phai ton tai trong bang PXUAT
-- - Kiem tra MaVTu phai ton tai trong bang VATTU
-- - Kiem tra SLXuat phai du trong bang TONKHO
-- - Kiem tra DgXuat phai duong.
-- Neu tat ca cac rang buoc o tren deu hop le thi tu dong thuc hien:
-- - Tang gia tri tai cot TONGTG trong bang PXUAT
-- - Tang gia tri tai cot TONGSLX trong bang TONKHO.
select * from PXUAT
select * from CTPXUAT
select * from TONKHO
Alter table PXUAT
add TongTG int
Update PXUAT
set TongTG=(select sum(SLXuat*DgXuat)from CTPXUAT where CTPXUAT.SoPX=PXUAT.SoPX group by SoPX)
from CTPXUAT
where CTPXUAT.SoPX=PXUAT.SoPX
Create trigger tg_PXUAT_Them
on CTPXUAT
for insert
as
Begin
Declare @SoPX char(4),@MaVTu char(4),@SLXuat int, @DgXuat money
select @SoPX=SoPX,@MaVTu=MaVTu,@SLXuat=SLXuat,@DgXuat=DgXuat
from inserted
if(not exists (select * from PXUAT where @SoPX=SoPX))
RollBack Tran
print 'SoPX phai co trong bang PXUAT!'
end
if( not exists (select * from VATTU where @MaVTu=MaVTu))
RollBack Tran
print 'MaVTu phai co trong bang VATTU!'
end
if( not exists (select * from TONKHO where @MaVTu=MaVTu having (sum(SLDau)-sum(TongSLN)-Sum(TongSLX))>= @SLXuat ))
RollBack Tran
print 'SLXuat phai du trong bang TONKHO!'
end
Update PXUAT
set TongTG=(select sum(SLXuat*DgXuat)from CTPXUAT where CTPXUAT.SoPX=PXUAT.SoPX group by SoPX)
from CTPXUAT
where CTPXUAT.SoPX=PXUAT.SoPX
Update TONKHO
set TongSLX=TongSLX+(select SLXuat from Inserted)
from TONKHO,PXUAT
where TONKHO.MaVTu=@MaVTu and TONKHO.NamThang=(right(convert(char(10),NgayXuat,101),4)+left(convert(char(10),NgayXuat,101),2))
End
/*CAU HOI. 7.5.Tao trigger de kiem tra constraint va tinh toan tu dong:
7.5.a.XD trigger khi xoa DL trong bang CTPXUAT voi ten tg_CTPXUAT_Xoa.
Trong do can thuc hien tinh toan:
- Giam gia tri tai cot TongTG trong bang PXUAT.
- Giam gia tri tai cot TONGSLX trong bang TONKHO.
7.5.b.XD trigger khi xoa DL trong bang CTPNHAP voi ten tg_CTPNHAP_Xoa:
- Giam gia tri tai cot TongTG trong bang PNHAP.
- Giam gia tri tai cot TONGSLN trong bang TONKHO.
*/
BAI LAM:
--7.5.a.XD trigger khi xoa DL trong bang CTPXUAT voi ten tg_CTPXUAT_Xoa.
-- Trong do can thuc hien tinh toan:
-- - Giam gia tri tai cot TongTG trong bang PXUAT.
-- - Giam gia tri tai cot TONGSLX trong bang TONKHO.
select * from CTPXUAT
select *from PXUAT
select *from TONKHO
Create trigger tg_CTPXUAT_Xoa
on CTPXUAT
for Delete
as
Begin
Declare @SoPX char(4),@MaVTu char(4)
select @SoPX=SoPX, @MaVTu=MaVTu
from deleted
Update PXUAT
set TongTG=TongTG-(select SlXuat*DgXuat from deleted)
where @SoPX=SoPX
Declare @NamThang char(6)
select @NamThang=(right(convert(char(10),NgayXuat,101),4)+left(convert(char(10),NgayXuat,101),2))
from PXUAT,CTPXUAT
where PXUAT.SoPX=CTPXUAT.SoPX and CTPXUAT.MaVTu=@MaVTu
Update TONKHO
Set TongSLX=TongSLX-(select SLXuat from deleted)
where NamThang=@NamThang and MaVTu=@MaVTu
End
Thuc hien:
--7.5.b.XD trigger khi xoa DL trong bang CTPNHAP voi ten tg_CTPNHAP_Xoa:
-- - Giam gia tri tai cot TongTG trong bang PNHAP.
-- - Giam gia tri tai cot TONGSLN trong bang TONKHO.
select *from CTPNHAP
select * from PNHAP
select * from TONKHO
Create trigger tg_CTPNHAP_Xoa
on CTPNHAP
for Delete
as
Begin
Declare @SoPN char(4), @MaVTu char(4)
select @SoPN=SoPN, @MaVTu=MaVTu
from deleted
Update PNHAP
Set TongTG=TongTG-(select SLNhap*DgNhap from deleted)
where SoPN=@SoPN
Declare @NamThang char(6)
select @NamThang=(right(convert(char(10),NgayNhap,101),4)+ left(convert(char(10),NgayNhap,101),2))
from PNHAP
where PNHAP.SoPN=@SoPN
Update TONKHO
Set TongSLN=TongSLN-(select SlNhap from deleted)
where NamThang=@NamThang and MaVTu=@MaVTu
End
Bạn đang đọc truyện trên: Truyen247.Pro