®¸p ¸n mét sè bµI tËp

BµI 3:

3.4.b) create view ww_DonDH_tongslnhap (sodh,tongsln)

as

select dondh.sodh, sum(slnhap) as tongsln

from dondh,pnhap, ctpnhap

where (dondh.sodh=pnhap.sodh)and(pnhap.sopn=ctpnhap.sopn)

group by dondh.sodh

3.4.c)

Create view vw_DonDH_TongSLDatNhap(SoDH,TongSLDat,TongSLNhap)

AS

Select CTDONDH.SoDH,Sum(SLDat),Sum(SLNhap)

From VATTU,CTDONDH,CTPNHAP

Where VATTU.MaVTu=CTPNhap.Mavtu and vattu.mavtu=ctdondh.mavtu

Group by ctdondh.sodh

Xem kÕt qu¶: select * from vw_DonDH_TongSLDatNhap

3.4.d)

Create view vw_DonDH_DaNhapDu(SoDH,ThongBao)

AS

Select SoDH, Case when TongSLDat=TongSLNhap then

'Da nhap du'

Else

'Chua nhap du'

end

From vw_DonDH_TongSLDatNhap

Xem kÕt qu¶: select * from vw_DonDH_DaNhapDu

3.4.e.

Create view vw_TongNhaptg(NamThang,MaVatTu,SLNhap)

as

select right(convert(char(10),NgayNhap,101),4)+left(convert(char(10),NgayNhap,101),2) as NamThang,MaVTu,SLNhap

from CTPNHAP,PNHAP

where CTPNHAP.SoPN=PNHAP.SoPN

Create view vw_TongNhap(NamThang,MaVtu,TongSLN)

AS

Select NamThang,MaVatTu,sum(SLNhap)

From vw_TongNhaptg

Group by NamThang,MaVatTu

Xem kÕt qu: select * from vw_TongNhap

3.4.g)

Create view vw_DonDH_MaVTu_TongSLNhap(SoDH,NgayDatHang,MaVatTu,TenVatTu,SoLuongDat,TongSLDaNhap)

AS

Select DonDH.SoDH, DonDH.NgayDH, Vattu.mavtu, vattu.tenvtu, CTDonDH.SLDat, sum(CTPNhap.SLNhap)

From vattu,dondh,ctdondh,ctpnhap

Where vattu.mavtu=ctpnhap.mavtu and vattu.mavtu=ctdondh.mavtu and ctdondh.sodh=dondh.sodh

Group by DonDH.SoDH, DonDH. NgayDH, Vattu.mavtu, vattu.tenvtu, CTDonDH.SLDat

Xem kÕt qu¶: select * From vw_DonDH_MaVTu_TongSLNhap

3.5.a)

Select DonDH.* ,CTDonDH.Mavtu,CTDonDH.SLdat

From DonDH,CTDonDH

Where DonDH.sodh=CTDondh.sodh and DonDH.sodh not in(select Pnhap.Sodh From Pnhap)

3.5.b)

Select * from VatTu

Where Mavtu not in(select mavtu From CTDonDH)

3.5.c)

Select NhaCC.MaNhacc,Nhacc.TenNhacc,Count(DonDH.SoDh) as SoDonDatHang

From Nhacc,DonDH

Where DonDh.MaNhacc=Nhacc.MaNhacc

Group by NhaCC.MaNhacc,Nhacc.TenNhacc

Having Count(DonDH.SoDh)>=All(select Count(DonDH.SoDh) from DonDH Group by MaNhacc)

3.5.d)

Select VaTtu.Mavtu,Vattu.tenvtu,sum(CTPXuat.SLXuat) as TongSLX

From Vattu,CTPXuat

Where Vattu.Mavtu=CTPXuat.Mavtu

Group by Vattu.Mavtu,Vattu.tenvtu

Having sum(CTPXuat.SLXuat)>=All(select sum(SLXuat) from CTPXuat Group by Mavtu)

BµI 4:

4.1.e)

Select manhacc,tennhacc From NHACC

Where manhacc not in (select manhacc from DONDH)

4.1.f)

Select SoPx, TongGT=Sum(SLxuat*DGxuat)

From CTPxuat

Group By Sopx

Order by TongGT desc

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(@X as char(4)

IF @A>3800000

PRINT ' khong nen thay doi gia ban'

else

PRINT ' Da den luc tang gia ban'

4.2.b)

IF EXISTS (SELECT * FROM DONDH WHERE DATENAME(DW,NGAYDH)='Sunday')

SELECT * FROM DONDH

WHERE DATENAME(DW,NGAYDH)='Sunday'

ELSE

PRINT ' NGAY LAP CAC DON DAT HANG DEU HOP LE'

4.2.c)

declare @d int

select @d=count(*) from pnhap where sodh='D001'

if @d>0

Print 'Co ' + cast(@d as char(2)) + ' so phieu nhap hang cho don dat hang D001'

else

Print ' Chua co nhap hang nao cho D001'

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.3.b)

Alter table VATTU_temp

add SOPX CHAR(4), DGXUAT float

declare @dgtb FLOAT, @d int

SET @D=0

Select @dgtb=avg(dgxuat) From ctpxuat Where mavtu='DD01'

print @dgtb

While @dgtb<3500000

begin

Update ctpxuat

set dgxuat=dgxuat*1.05

where mavtu='DD01' and dgxuat<3000000

Insert into VATTU_temp(mavtu,tenvtu,sopx,dgxuat)

select vattu.mavtu, tenvtu,sopx,dgxuat

from ctpxuat,vattu

where ctpxuat.mavtu=vattu.mavtu and ctpxuat.mavtu='DD01' and dgxuat<3000000

set @d=@d+1

end

print 'Da tang ' + cast(@d as char(2)) + 'lan'

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

4.4.b)

Update ctpxuat

Set dgxuat=CASE when slxuat<4 then dgxuat

when slxuat>=4 and slxuat <10 then dgxuat*0.95

when slxuat>=10 and slxuat <=20 then dgxuat*0.90

ELSE

dgxuat*0.8

END

From pxuat

Where pxuat.sopx=ctpxuat.sopx and Right(convert(varchar(11),ngayxuat,103),7)='01/2005'

{ hoÆc month(ngayxuat)=1 and year(ngayxuat)=20025}

BµI 5:

5.1.b)

Create Proc PNhap_Tongsl

@SoDH char(4), @Mavtu char(4), @tongSL int output

AS

Select @tongSl=sum(slnhap)

From ctpnhap,pnhap

Where ctpnhap.sopn=pnhap.sopn and @SoDH=sodh and @mavtu=mavtu

Gäi thñ tôc:

declare @a int

set @a=0

exec PNhap_Tongsl 'D002','VD02', @a output

print 'Tong so luong nhap la:' + cast(@a as char(4))

5.2.b)

Create Proc VatTu_Xoa

@MaVtu char(4)

AS

If Exists (select * from CTDonDH where Mavtu=@Mavtu)

Print 'Da co vat tu nay trong bang CTDonDH nen khong xoa duoc'

Else If Exists (select * from CTPXuat where Mavtu=@Mavtu)

Print 'Da co vat tu nay trong bang CTPXuat nen khong xoa duoc'

Else If Exists (select * from CTPNhap where Mavtu=@Mavtu)

Print 'Da co vat tu nay trong bang CTPNhap nen khong xoa duoc'

Else If Exists (select * from TonKho where Mavtu=@Mavtu)

Print 'Da co vat tu nay trong bang TonKho nen khong xoa duoc'

Else

Delete From VatTu Where mavtu=@mavtu

Gäi thñ tôc: EXEC VatTu_Xoa @Mavtu='DD01'

5.3.b)

Create Proc BaoCaoTonKho

@NamThang char(8)

AS

Select TonKho.*, Vattu.TenVtu

From Vattu, TonKho

Where TonKho.Mavtu=Vattu.mavtu

Gäi thñ tôc: EXEC BaoCaoTonKho '2000501'

5.4.a)

Create Proc DonDH_Them

@SoDH char(4),@NgayDH DateTime=Null, @Manhacc char(4)

AS

IF Exists (select * from dondh where sodh=@sodh)

begin

print 'so dh da co trong bang dondh'

return

end

IF not Exists (select * from nhacc where manhacc=@manhacc)

begin

print 'Manhacc chua 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)

Gäi thñ tôc: EXEC DonDH_Them 'D001','1/15/2005','C03'

5.4.b)

Create Proc DonDH_Xoa

@SoDH char(4)

AS

IF Exists (select * from Pnhap where sodh=@sodh)

begin

print 'so dh da co trong bang phieu nhap'

return

end

delete From DonDH where Sodh=@sodh

delete From CTDonDH where Sodh=@sodh

Gäi thñ tôc: exec DonDH_Xoa 'D004'

5.4.f)

Create proc CTDonDH_Sua

@Sodh char(4), @Mavtu char(4), @sldat int

AS

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, @tongsln int

select @sldatmoi=sldat from ctdondh where sodh=@sodh and mavtu=@mavtu

select @tongsln=sum(slnhap) from ctpnhap where mavtu=@mavtu

if @sldatmoi<@tongsln

begin

Print' So luong dat moi phai >= tong sl da nhap'

return

end

Update CTDonDH

Set sodh=@sodh, mavtu=@mavtu, sldat=@sldat

Where sodh=@sodh and mavtu=@mavtu

Gäi thñ tôc: exec CTDonDH_Sua 'D002','DD01',13

BµI 6:

6.1.a)

Create Function Fn_TongNhapThang(@mavtu char(4),@namthang char(6))

returns int

As

Begin

Declare @TongSLN int

Select @TongSLN=sum(TongSLN) From TonKho Where (MaVtu=@Mavtu) AND (namthang=@namthang)

Return(@TongSLN)

End

Xem KQ: Print DBO.Fn_TongNhapThang('DD02','200501')

6.1.c)

Create Function Fn_TongNhap(@SoDH char(4),@mavtu char(4))

returns int

As

Begin

Declare @TongSLN int

Select @TongSLN= sum(SLNhap) From CTPNhap Where (Mavtu=@Mavtu) And (Sopn in(Select sopn From Pnhap where Sodh=@sodh))

Return(@TongSLN)

End

Xem KQ: Print DBO.Fn_TongNhap('D001','DD01')

6.2.b)

Create Function Fn_DS_VatTuTonKho(@NamThang char(6),@ToiThieu int)

returns Table

As

Return(Select * From TONKHO Where (Namthang=@namthang) And (SLCuoi >=@Toithieu))

Xem KQ: Select * from Dbo.Fn_DS_VatTuTonKho('200501',4)

BµI 7:

7.1.a)

Create Trigger PNhap_Them

on pnhap

for insert

AS

Declare @ngaynhapmoi datetime, @ngaydat datetime,@SoDH char(4)

Select @SoDH=SoDH from inserted

Select @ngaynhapmoi=ngaynhap from inserted

select @ngaydat=ngaydh from dondh,Inserted

Where DonDH.sodh=Inserted.sodh

if not exists (Select sodh from dondh where sodh=@sodh)

begin

rollBack Tran

print 'SoDH nay chua co trong bang DonDH'

end

if @ngaynhapmoi <@ngaydat

begin

rollBack Tran

print 'Ngay nhap hang phai sau ngay dat hang'

end

Chay thu: Insert into PNHAP Values('N009','01/17/2004','D001')

7.1.b)

Create Trigger CTPNhap_Them

on ctpnhap

for insert

AS

declare @slnmoi int,@Tongsldat int,@TongslnCu int, @mavtumoi char(4)

select @mavtumoi=mavtu from inserted

select @SLNmoi=SLnhap from inserted

select @tongslnCu=sum(slnhap)-@slnmoi from ctpnhap

where mavtu=@mavtumoi

SELECT @TONGSLDAT=SUM(SLDAT) FROM CTDONDH

WHERE MAVTU=@MAVTUMOI

if @slnmoi >(@tongsldat-@tongslnCu)

begin

print 'nhap lai so luong'

rollback tran

end

Chay thu: Insert into CTPNhap values('N004','DD01',1,200000)

7.3.a)

CREATE TRIGGER PNHAP_SUA

ON PNHAP

FOR UPDATE

AS

IF UPDATE(SOPN) OR UPDATE(SODH)

BEGIN

ROLLBACK TRAN

PRINT 'KHONG CHO PHEP SUA'

END

DECLARE @NGAYNHAPSUA DATETIME,@NGAYDAT DATETIME,@SODHSUA CHAR(4)

SELECT @NGAYNHAPSUA=NGAYNHAP,@SODHSUA=SODH FROM INSERTED

SELECT @NGAYDAT=NGAYDH FROM DONDH WHERE SODH=@SODHSUA

IF @NGAYNHAPSUA < @NGAYDAT

BEGIN

ROLLBACK TRAN

PRINT 'XEM LAI NGAY'

END

UPDATE PNHAP SET NGAYNHAP='01/13/2005'

WHERE SOPN='N001'

7.3.b)

CREATE TRIGGER PXUAT_SUA

ON PXUAT

FOR UPDATE

AS

IF UPDATE(SOPX)

BEGIN

ROLLBACK TRANSACTION

PRINT 'KHONG CHO PHEP SUA'

END

DECLARE @NGAYXUATMOI DATETIME,@NGAYXUATCU DATETIME

SELECT @NGAYXUATMOI=NGAYXUAT FROM INSERTED

SELECT @NGAYXUATCU=NGAYXUAT FROM DELETED

IF (YEAR(@NGAYXUATMOI)<>YEAR(@NGAYXUATCU)) OR (MONTH(@NGAYXUATMOI)<>MONTH(@NGAYXUATCU))

BEGIN

ROLLBACK TRANSACTION

PRINT 'XEM LAI NGAY'

END

UPDATE PXUAT SET NGAYXUAT='01/13/2002'

WHERE SOPX='X001'

SELECT * FROM PXUAT

· Hµm DATENAME(§¬n_vÞ, Ngµy): Hµm tr¶ vÒ chuçi thêi gian ®¹i diÖn cña mét ngµy chØ ®Þnh theo 1 ®¬n vÞ thêi gian bÊt kú.

Trong ®ã:

+ Ngµy: lµ mét biÓu thøc, tªn cét d÷ liÖu, gi¸ trÞ cô thÓ cã kiÓu d÷ liÖu ngµy.

+ §¬n_vÞ: lµ ®¬n vÞ thêi gian ®¹i diªn, cô thÓ:

YY: tr¶ vÒ gi¸ trÞ n¨m (tõ 1990-9999)

MM: tr¶ vÒ gi¸ trÞ th¸ng (tõ 1-12)

DY: tr¶ vÒ gi¸ trÞ ngµy trong n¨m(tõ 1-366)

DD: tr¶ vÒ gi¸ trÞ ngµy trong th¸ng(tõ 1-31)

WK: tr¶ vÒ gi¸ trÞ tuÇn trong n¨m (tõ 1- 53)

DW: tr¶ vÒ gi¸ trÞ ngµy trong tuÇn(tõ 1-7 hoÆc: Sunday-Saturday)

· Hµm CONVERT(<kiÓu dl>,<biÓu thøc>[,<®Þnh d¹ng>])

Hµm chuyÓn ®æi mét biÓu thøc nµo ®ã sang mét kiÓu d÷ liÖu bÊt kú vµ cã thÓ theo mét ®Þnh d¹ng nµo ®ã (víi kiÓu d÷ liÖu ngµy).

Trong ®ã:

+KiÓu dl: lµ kiÓu d÷ liÖu mµ biÓu thøc sÏ ®­îc chuyÓn ®æi sang.

+ BiÓu thøc: lµ tªn cña mét cét bªn trong b¶ng hoÆc mét biÓu thøc tÝnh to¸n muèn chuyÓn sang kiÓu d÷ liÖu míi.

+§Þnh d¹ng: lµ mét con sè chØ ®Þnh viÖc ®Þnh d¹ng ®Ó chuyÓn ®æi d÷ liÖu tõ d¹ng ngµy sang d¹ng chuçi.

B¶ng sau lµ mét sè ®Þnh d¹ng th­êng dïng:

§Þnh d¹ng

HiÓn thÞ d÷ liÖu

1

mm/dd/yy

101

mm/dd/yyyy

3

dd/mm/yy

103

dd/mm/yyyy

5

dd-mm-yy

105

dd-mm-yyyy

10

mm-dd-yy

110

mm-dd-yyyy