®¸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
Bạn đang đọc truyện trên: Truyen2U.Pro