//3.1

-- Ten vat tu phai duy nhat, khong trung:

alter table VATTU

add constraint UNI_TenVtu unique(TenVTu)

--Gia tri mac dinh cho cot don vi tinh la "KG"

alter table VATTU

add constraint def_DvTinh default 'KG' for DvTinh

-- 0<=Phantram<=100

alter table VATTU

add constraint CHK_PT check (PhanTram>=0 and PhanTram<=100)

...

--3.2. Them cac rang buoc khoa ngoai cho cac bang: --

+alter table DONDH

add constraint FK_MANCC foreign key ( MaNhaCC) references NHACC(MaNhaCC) ON DELETE CASCADE ON UPDATE CASCADE

+alter table CTDONDH

add constraint FK_MaVTu foreign key ( MaVTu) references VATTU(MaVTu)

ON DELETE CASCADE ON UPDATE CASCADE

--3.4.c

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

--3.4.d

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.

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.5.b. 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.h. 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

--3.5.j.

select SoDH,ThongBao='Chua nhap du'

from vw_DonDH_TongSLDatNhap

where TongSLDat != TongSLNhap

BµI 4:

4.1.g) Delete from CTDONDH

From DONDH

Where convert(char(10),ngaydh,103)='15/01/2005' and DonDH.Sodh=CTDonDh.sodh

4.1.j) (1) SELECT SOPX, MAVTU, SLXUAT, DGXUAT, TTIEN=SLXUAT*DGXUAT

FROM CTPXUAT

ORDER BY SOPX

COMPUTE SUM(SLXUAT*DGXUAT) BY SOPX

(2) SELECT MAVTU,SOPN,SLNHAP,DGNHAP

FROM CTPNHAP

COMPUTE SUM(SLNHAP),MAX(DGNHAP),MIN(DGNHAP)

4.2.a)DECLARE @A REAL

SELECT @A=AVG(DGXUAT)

FROM CTPXUAT,VATTU

WHERE CTPXUAT.MAVTU=VATTU.MAVTU

AND VATTU.MAVTU='DD01'

Print 'Don gia trung binh cua dau DVD Hitachi1 la' + CAST(@A as char(4))

IF @A>3800000

PRINT ' khong nen thay doi gia ban'

else

PRINT ' Da den luc tang gia ban'

4.3.a) select mavtu, tenvtu

into VATTU_temp

From VATTU

declare @d int, @ten nvarchar(100)

select @d=count(*) from vattu_temp

While @d>0

begin

select top 1 @ten=Tenvtu from VATTU_temp

delete from vattu_temp where tenvtu=@ten

Print 'Dang xoa vat tu ' + @ten

break

end

select * from vattu_temp

Drop table VATTU_temp

4.4.a )

Select *,Thu=case datename(dw,ngaydh)

When 'sunday' then 'chu nhat'

when 'monday' then 'thu hai'

when 'tuesday' then 'thu ba'

when 'wenesday' then 'thu tu'

...

end

From dondh

5.1.b.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.2.c.Create proc spud_VATTU_Sua

@MaVTu char(4),@TenVTu varchar(100),@DVTinh varchar(10),@PhanTram real

as

begin

if((select count(*) from VATTU where MaVTu=@MaVTu)>0)

begin

Update VATTU

Set TenVTu = @TenVTu,

DVTinh = @DVTinh,

PhanTram = @PhanTram

where MaVTu=@MaVTu

end

else

begin

print 'MaVTu nay khong co trong bang VATTU'

return

end

end

Thuc hien:

exec spud_VATTU_Sua 'CPUi','central processing unit','chiec','10'

5.3.c

Create proc spud_PXUAT_BcaoPXuat

@SoPX char(4)=null

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'

5.4.a.

Create proc spud_DONDH_Them

@SoDH char(4),@MaNhaCC char(4),@NgayDH datetime

as

Begin

if(exists (select *from DONDH where @SoDH=SoDH))

begin

print 'SoDH phai duy nhat.SoDH nay da co trong bang DONDH!'

return

end

if(not exists (select * from NHACC where @MaNhaCC=MaNhaCC))

begin

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.f.

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

begin

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

begin

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

--6.1a

create function Fn_TongNhapThang(@MaVTu char(4))

returns int

as

begin

declare @tongnhap int

select @tongnhap=sum(SLNhap) from CTPNHAP

where MaVTu=@MaVTu

return @tongnhap

end

declare @tong int

set @tong=dbo.Fn_TongNhapThang ('DD01')

print @tong

--6.2b

create function Fn_TongxuatThang(@MaVTu char(4))

returns int

as

begin

declare @tongxuat int

select @tongxuat=sum(SLXuat) from CTPXUAT

where MaVTu=@MaVTu

return @tongxuat

end

declare @tongx int

set @tongx=dbo.Fn_TongXuatThang ('DD01')

print @tongx

--6.2c

Vi du 1: Them moi ban ghi:(7.1.a)

Xay dung Trigger trong bang PNHAP de moi khi nguoi dung them moi thong tin cua mot phieu nhap hang cho mot don dat hang truoc do:

+ Can kiem tra Sodh phai ton tai trong bang DonDH.

+Can kiem tra ngay nhap hang phai sau ngay dat hang (ngaynhap>=ngaydh)

create trigger tg_pnhap_insert

on pnhap

for insert

as

-- kiem tra xem sodh da co trong bang DonDH?

if not exists (select * from inserted,dondh where inserted.sodh=dondh.sodh)

begin

rollback tran

raiserror('Sodh khong ton tai',16,1)

return

end

--tinh ra ngay dat hang luu vao bien @ngaydh

declare @ngaydh datetime

select @ngaydh=ngaydh from dondh,inserted where inserted.sodh=dondh.sodh

--kiem tra ngay nhap phai sau ngay dat hang (ngaynhap>=ngaydh)

if @ngaydh > (select ngaynhap from inserted)

begin

declare @loi char(200)

set @loi='ngay nhap phai sau ngay:' + convert(char(10),@ngaydh,103)

raiserror(@loi,16,1)

rollback tran

end

Thu hoat dong cua Trigger:

alter table pnhap nocheck constraint all

insert into pnhap(sopn,ngaynhap,sodh) values('N005','2005-04-15','D999')

insert into pnhap(sopn,ngaynhap,sodh) values('N005','2005-01-16','D003')

insert into pnhap(sopn,ngaynhap,sodh) values('N007','2005-02-16','D003')

Vi du 2: Xoa ban ghi:

Xay dung Trigger trong bang DonDH de moi khi nguoi dung xoa mot don dat hang(Sodh):

+ Can kiem tra xem don dat hang dinh xoa da duoc nhap hang chua? Neu nhap hang roi thi khong cho xoa don dat hang do. Neu chua nhap hang thi tu dong xoa cac dong du lieu lien quan trong bang CTDonDH.

create trigger tg_dondh_delete

on dondh

for delete

as

--kiem tra xem don dat hang dinh xoa da duoc nhap hang chua?

if exists (select * from pnhap where sodh in(select sodh from deleted))

begin

rollback tran

raiserror(' Sodh nay da nhap roi, khong huy duoc!',16,1)

end

Else

-- tu dong xoa cac dong du lieu lien quan trong bang CTDonDH.

Delete from ctdondh where sodh in (select sodh from deleted)

Thu hoat dong cua Trigger:

alter table pnhap nocheck constraint all

alter table ctdondh nocheck constraint all

delete from dondh where sodh='D002'

delete from dondh where sodh='D006'

Vi du 3: Sua ban ghi:

Xay dung Trigger trong bang DonDH de moi khi nguoi dung sua doi thong tin cua mot don dat hang:

+ Se khong cho phep sua du lieu tai 2 cot Sodh va Manhacc.

+Khi sua doi du lieu o cot Ngaydh, thi phai dam bao ngay dat hang phai truoc ngay nhap hang dau tien cua sodh do.

create trigger tg_dondh_update

on dondh

for update

as

--khi sua Sodh va Manhacc

if update(sodh) or update(manhacc)

begin

rollback tran

raiserror('khong the thay doi sodh hay manhacc',16,1)

return

end

-- Khi sua doi du lieu o cot Ngaydh

if update(ngaydh)

begin

--kiem tra sodh do da duoc nhap chua?

if exists (select * from pnhap where sodh in(select sodh from deleted))

begin

--tinh ra ngay nhap hang dau tien

Declare @minngaynh datetime

select @minngaynh=min(ngaynhap) from pnhap,deleted where pnhap.sodh=deleted.sodh

-- kiem tra ngay dat hang phai truoc ngay nhap hang dau tien

if @minngaynh<(select ngaydh from inserted)

begin

declare @loi char(200)

set @loi='ngay dat hang phai truoc ngay:' + convert(char(10),@minngaynh,103)

raiserror(@loi,16,1)

rollback tran

end

end

end

Thu hoat dong cua Trigger:

alter table dondh nocheck constraint all

+ khi sua du lieu o cot sodh hoac manhacc

update dondh set sodh='D022' where sodh='D002'

update dondh set manhacc='C01' where sodh='D002'

+ don dat hang D001 co ngay dat hang la 15-01-2005 va ngay nhap hang dau tien la 17-01-2005.

update dondh set ngaydh='2005-01-30' where sodh='D001'

update dondh set ngaydh='2005-01-16' where sodh='D001'