--------a
SELECT * FROM VATTU
ORDER BY TENVTU DESC
--------B
SELECT * FROM NHACC
WHERE DIACHI LIKE '%Qu?n 1 HCM'
ORDER BY TENNHACC ASC
--------C
SELECT *,SLNHAP*DGNHAP AS THANHTIEN
FROM CTPNHAP
-------D
SELECT PNHAP.*,TRIGIA=SUM(SLNHAP*DGNHAP)
FROM CTPNHAP,PNHAP
WHERE CTPNHAP.SOPN=PNHAP.SOPN
GROUP BY PNHAP.SOPN,SODH,NGAYNHAP
-------E
SELECT MANHACC,TENNHACC
FROM NHACC
WHERE MANHACC NOT IN(SELECT MANHACC FROM DONDH)
-------F
SELECT PXUAT.SOPX,SUM(SLXUAT*DGXUAT) AS TONGGIATRI
FROM PXUAT,CTPXUAT
WHERE PXUAT.SOPX=CTPXUAT.SOPX
GROUP BY PXUAT.SOPX
ORDER BY SUM(SLXUAT*DGXUAT) DESC
-------G
DELETE FROM CTDONDH
FROM DONDH
WHERE (CTDONDH.SODH=DONDH.SODH) AND(NGAYDH='01/15/2005')
------H
DELETE FROM CTPXUAT
------I
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)
INSERT INTO CTDONDH
VALUES ('D001','DD01',10)
INSERT INTO CTDONDH
VALUES ('D001','DD02',15)
-------J
SELECT SLXUAT,DGXUAT,SOPX,MAVTU,THANHTIEN=(SLXUAT*DGXUAT)
FROM CTPXUAT
GROUP BY SOPX
COMPUTE SUM(SLXUAT*DGXUAT) BY SOPX
---4.2
---a
if(select SUM(DGXUAT)/COUNT(*) FROM CTPXUAT,VATTU
WHERE (CTPXUAT.MAVTU=VATTU.MAVTU) AND (TENVTU='Ð?u DVD Hitachi 1 dia'))>3800000
PRINT 'KHONG THAY DOI GIA BAN'
ELSE
PRINT 'DA DEN LUC TANG GIA BAN'
---B
DECLARE @DEM INT
SELECT @DEM=COUNT(*) FROM DONDH
WHERE DATENAME(DW,NGAYDH)='SUNDAY'
IF(@DEM>0)
SELECT * FROM DONDH
ELSE
PRINT 'CAC NGAY DAT HANG DEU HOP LE'
----C
DECLARE @DEM INT
SELECT @DEM=COUNT(*) FROM PNHAP
WHERE SODH='D001'
IF(@DEM>0)
PRINT 'CO '+CONVERT(CHAR(2),@DEM)+' SO PHIEU NHAP HANG CHO DON DAT HANG D001'
ELSE
PRINT 'CHUA CO DON DAT HANG NAO CHO D001'
--4.3
---A
SELECT MAVTU,TENVTU
INTO VATTU_TEMP
FROM VATTU
----
DECLARE @MAVATTU CHAR(4),@TENVATTU NVARCHAR(100)
WHILE EXISTS (SELECT * FROM VATTU_TEMP)
BEGIN
SELECT TOP 1 @MAVATTU=MAVTU,@TENVATTU=TENVTU FROM VATTU_TEMP
DELETE FROM VATTU_TEMP
WHERE TENVTU=@TENVATTU
PRINT 'DANG XOA VAT TU'+CONVERT(CHAR(100),@TENVATTU)
END
DROP TABLE VATTU_TEMP
----B
ALTER VATTU_TEMP
ADD COLUMN SOPX CHAR(4)
ALTER VATTU_TEMP
ADD COLUMN DGXUAT FLOAT
DECLARE @SOPX CHAR(4)
SELECT @SOPX=SOPX FROM CTPXUAT
WHERE MAVTU='DD01'
DECLARE @TENVTU CHAR(100)
SELECT @TENVTU=TENVTU FROM VATTU
WHERE MAVTU='DD01'
DECLARE @DGXUAT FLOAT
SELECT @DGXUAT=DGXUAT FROM CTPXUAT
WHERE MAVTU='DD01'
DECLARE @DEM INT
SET @DEM=0
WHILE(@DGXUAT<3500000)
BEGIN
SET @DGXUAT=@DGXUAT+@DGXUAT*5/100
SET @DEM=@DEM+1
INSERT INTO VATTU_TEMP
VALUES ('DD01',CONVERT(CHAR(100),@TENVTU),CONVERT(CHAR(4),@SOPX),CONVERT(CHAR(4),CONVERT(CHAR(10),@DGXUAT)
END
PRINT 'VONG LAP DA THUC HIEN '+CONVERT(CHAR(2),@DEM)
-----4.4
---A
SELECT *,THU=CASE (DATENAME(DW,NGAYDH))
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 'SARTURDAY' THEN 'THU BAY'
WHEN 'SUNDAY' THEN 'CHU NHAT'
END
FROM DONDH
---B
SELECT DONGIAXUAT=CASE DGXUAT
create proc spud_dondh_tinhsldat(@sodh char(4),@mavtu char(4))
as
select sldat from ctdondh
where (sodh=@sodh) and (mavtu=@mavtu)
5.1
----a
create proc spud_dondh_tinhsldat(@sodh char(4),@mavtu char(4))
as
select sldat from ctdondh
where (sodh=@sodh) and (mavtu=@mavtu)
---su dung
exec spud_dondh_tinhsldat 'D001','DD01'
---b
alter proc supud_pnhap_tinhtongslnh (@sodh char(4),@mavtu char(4))
as
select mavtu,sum(slnhap) as tongsoluong from pnhap,ctnhap
where (mavtu=@mavtu) and(pnhap.sopn=ctnhap.sopn)and (sodh=@sodh)
group by mavtu
exec supud_pnhap_tinhtongslnh 'N001','DD02'
print 'tong soluong nhap la'+convert(char(4),@tongsl)
create proc tinhtong (@a int,@b int,@tong int output)
as
set @tong=@a+@b
declare @s int
exec tinhtong 3,4,@s
print convert(char(2),@s)
alter proc spud_dondh_tinhsldat
@sodh char(4),@mavtu char(4),@sldat int output
as
declare @kq int
select @kq=sldat from ctdondh
where sodh=@sodh and mavtu=@mavtu
set @sldat=@kq
go
declare @sldat int
exec spud_dondh_tinhsldat 'D001','DD01',@sldat output
print convert(char(3),@sldat)
---c
create proc spud_tonkho_tinhsldau (@namthang datetime,@mavtu char(4),@soluongdau int output)
as
select @soluongdau=sldau from tonkho
where mavtu=@mavtu and namthang=@namthang
declare @sldau int
exec spud_tonkho_tinhsldau '5/2/2020','DD02',@sldau output
print convert(char(4),@sldau)
--5.2
---a
create proc spud_vattu_them (@mavtu char(4),@tenvtu nvarchar(100),@dvtinh nvarchar(10),@phantram int)
as
if(@mavtu not in(select mavtu from vattu))
insert into vattu
values(convert(char(4),@mavtu),convert(nvarchar(100),@tenvtu),convert(nvarchar(10),@dvtinh),@phantram)
else
print 'du lieu dua vao khong hop ly'
--b
create proc spud_vattu_xoa (@mavtu char(4))
as
if(@mavtu not in(select mavtu from ctdondh) and @mavtu not in(select mavtu from ctnhap) and @mavtu not in(select mavtu from ctpxuat) and @mavtu not in(select mavtu from tonkho))
delete from vattu
where mavtu=@mavtu
else
print 'du lieu da trung kg hop le'
exec spud_vattu_xoa 'DD01'
----c
create proc spud_vattu_sua (@mavtu char(4),@tenvtu nvarchar(100),@dvtinh nvarchar(10),@phantram int)
as
update vattu
set tenvtu=@tenvtu,dvtinh=@dvtinh,phantram=@phantram
where mavtu=@mavtu
exec spud_vattu_sua---voi du lieu tu cho them
--5.3
---a
create proc spud_vattu_bcaodanhsach
as
select * from vattu
order by tenvtu asc
exec spud_vattu_bcaodanhsach
---b--trong phan namthang thi chon la kieu char(6)--luu y
create proc spud_vattu_bcaotonkho (@namthang datetime)
as
select tonkho.*,tenvtu
from tonkho,vattu
where (tonkho.mavtu=vattu.mavtu) and (namthang=@namthang)
exec spud_vattu_bcaotonkho '5/1/2020'
---c
create proc spud_pxuat_bcaopxuat(@sopx char(4)=null)
as
if(@sopx is null)
select pxuat.sopx,ngayxuat,tenkh,ctpxuat.mavtu,slxuat,dgxuat,tenvtu
from pxuat,ctpxuat,vattu
where (pxuat.sopx=ctpxuat.sopx) and(ctpxuat.mavtu=vattu.mavtu)
else
select pxuat.sopx,ngayxuat,tenkh,ctpxuat.mavtu,slxuat,dgxuat,tenvtu
from pxuat,ctpxuat,vattu
where (pxuat.sopx=ctpxuat.sopx) and (ctpxuat.mavtu=vattu.mavtu) and (pxuat.sopx=@sopx)
exec spud_pxuat_bcaopxuat ''
--5.4
---a
alter proc spud_dondh_them(@sodh char(4),@manhacc char(4),@ngaydh datetime)
as
if(@sodh in(select sodh from dondh))
print 'da co trong bang dondh'
if(@manhacc not in(select manhacc from nhacc))
print 'chua co manhacc trong bang nhacc'
if(@ngaydh is null)
insert into dondh
values(@sodh,@manhacc,convert(char(8),getdate(),103))
else
insert into dondh
values(@sodh,@manhacc,convert(char(8),@ngaydh,103))
exec spud_dondh_them 'D001','C03',''
---vi du lam thu khg co trong bai
alter proc spud_vattu_xoathu(@mavtu char(4))
as
if(@mavtu in(select mavtu from ctdondh))
print 'da co trong bang ctdondh'
return
if(@mavtu in(select mavtu from ctnhap))
print 'da co trong bang ctpnhap'
return
if(@mavtu in(select mavtu from ctpxuat))
print 'da co trong bang ctpxuat'
return
if(@mavtu in(select mavtu from tonkho))
print 'da co o trong bang ton kho'
return
delete from vattu
where mavtu=@mavtu
exec spud_vattu_xoathu 'DD05'
----thu lai
create proc thu(@mavtu char(4))
as
if exists(select mavtu from ctdondh where mavtu=@mavtu)
print 'da xuat hien trong ctdondh'
if exists(select mavtu from ctnhap where mavtu=@mavtu)
print 'da xuat hien trong ctnhap'
if exists(select mavtu from ctpxuat where mavtu=@mavtu)
print 'da xuat hien trong ctpxuat'
if exists(select mavtu from tonkho where mavtu=@mavtu)
print 'da xuat hien trong tonkho'
delete from vattu
where mavtu=@mavtu
exec thu 'DD01'
---b
alter proc spud_dondh_xoa(@sodh char(4))
as
if(@sodh not in(select sodh from pnhap))
begin
delete from dondh
where (dondh.sodh=@sodh)
delete from ctdondh
where ctdondh.sodh=@sodh
end
else
print 'sodh da co trong bang pnhap'
exec spud_dondh_xoa 'D005'
----c
alter proc spud_dondh_sua123(@sodh char(4),@manhacc char(4),@ngaydh datetime)
as
if @manhacc in(select manhacc from nhacc) and @ngaydh<(select top 1 ngaynhap from pnhap where sodh=@sodh)
update dondh
set manhacc=@manhacc,ngaydh=@ngaydh,sodh=@sodh
else
print 'du lieu kg hop le'
exec spud_dondh_sua123 'D001','C01','01/15/2005'
--d
create proc spud_dondh_them123(@sodh char(4),@mavtu char(4),@sldat int)
as
if (@sodh not in(select sodh from dondh))
print 'du lieu kg co san trong bang don dat hang'
else
if(@mavtu not in(select mavtu from vattu))
print 'du lieu khong co trong bang vattu'
else
if(@sodh not in(select sodh from ctdondh) and @mavtu not in(select mavtu from vattu))
insert into ctdondh
values(@sodh,@mavtu,@sldat)
else
print 'du lieu khong hop le'
exec spud_dondh_them123 'D007','DD01',10
--e
create proc spud_ctdondh_xoa(@sodh char(4),@mavtu char(4))
as
--f
create proc spud_ctdondh_sua(@sodh char(4),@mavtu char(4),@sldat int)
as
if(@sodh not in(select sodh from ctdondh) and @mavtu not in(select mavtu from ctdondh))
print 'du lieu khong co trong bang ctdondh'
else
if(
--bai 6
---6.1--can phai sua them cho chuan
---a
alter function tongnhapthang(@namthang char(6),@mavtu char(4))
returns int
as
begin
declare @tong int
select @tong=sum(slnhap) from ctpnhap,pnhap
where ctpnhap.sopn=pnhap.sopn and mavtu=@mavtu and convert(char(6),namthang,112)=
@namthang
return @tong
end
declare @kq int
set @kq=dbo.tongnhapthang '200201','DD01'
print convert(char(3),@kq)
--b
create function fn_tongxuatthang(@namthang char(6),@mavtu char(4))
returns int
as
begin
declare @tong int
select @tong=sum(slxuat) from pxuat,ctpxuat
where pxuat.sopx=ctpxuat.sopx and mavtu=@mavtu and convert(char(6),ngayxuat,112)=@namthang
return @tong
end
declare @s int
set @s=dbo.fn_tongxuatthang '050117','DD01'
print convert(char(4),@s)
---c
alter function fn_tongnhap(@sodh char(4),@mavtu char(4))
returns int
as
begin
declare @ttong int
select @ttong=sum(slnhap) from ctnhap,pnhap
where (sodh=@sodh) and (mavtu=@mavtu) and(ctnhap.sopn=pnhap.sopn)
return isnull(@ttong,0)
end
declare @tong int
set @tong=dbo.fn_tongnhap('D001','DD01')
print @tong
---d
create function fn_connhap(@sodh char(4),@mavtu cHAR(4))
returns int
as
begin
declare @conlai int,@sldat int
select @sldat=sum(sldat) from ctdondh
where (mavtu=@mavtu) and(sodh=@sodh)
set @[email protected]_tongnhap(@sodh,@mavtu)
return isnull(@conlai,0)
end
declare @tongcon int
set @tongcon=dbo.fn_connhap('D001','DD01')
print @tongcon
---e
create function fn_toncuoi(@mavtu char(4),@namthang char(6))
returns int
as
begin
declare @ton int
set @ton=dbo.fn_tongnhapthang(@namthang,@mavtu)-dbo.fn_tongxuatthang(@namthang,@mavtu)
return (@ton)
end
declare @hangton int
set @hangton=dbo.fn_toncuoi('050117','DD01')
print @hangton
--6.2
---a
create function fn_ds_vattuconnhap(@sodh char(4))
returns table
as
begin
return (select ctdondh.sodh,vattu.mavtu,tenvtu,dbo.fn_ds_vattuconnhap(@sodh,vattu.mavtu) as slconnhapfrom vattu,ctdondh
where ctdondh.mavtu=vattu.mavtu and sodh=@sodh and dbo.fn_ds_vattuconnhap(@sodh,vattu.mavtu)>0
--b
create function fn_dds_vattutonkho(@namthang char(4),@toithieu int)
returns table
as
return (select mavtu from tonkho where namthang=@namthang and slcuoi<@toithieu)
--6.3
--a
create function fn_ds_tonkho_lech_1thang(@namthang char(6))
returns @bangtonkho table
(
namthang1 char(6),
mavtu char(4),
sldau int,
tongsln int,
tongslx int,
slcuoi int
)
as
begin
declare @sldau int
select @sldau=sldau from tonkho where namthang=@namthang
declare @slcuoi int
select @slcuoi=slcuoi from tonkho where convert(int,namthang)=(convert(int,namthang)+0100)
insert into @bangtonkho
select * from tonkho
where namthang=@namthang and @sldau<>@slcuoi
return
end
--b
alter function fn_ds_tonkho_lech()
returns @bangtonkho2 table
(namthang1 char(6),
mavtu char(4),
sldau int,
tongsln int,
tongslx int,
slcuoi int
)
as
begin
declare @namthang char(6)
select @namthang =namthang from tonkho
if exists(select * from dbo.fn_ds_tonkho_lech_1thang(@namthang))
insert into @bangtonkho2
select * from tonkho
return
end
select * from dbo.fn_ds_tonkho_lech()
---7 trigger
--7.1
---a
Bạn đang đọc truyện trên: Truyen2U.Pro