Bai tap 1 :
cau 1 :
create function cau1(@MaLoTrinh nvarchar(10))
returns table
AS
return(
select 'So xe'=Soxe, 'Ma trong tai'=MaTrongTai, 'So luong van tai'=SoLuongVT,
'Ngay di'=Ngaydi, 'Ngay den'=Ngayden
from ChiTietVanTai
where @MaLoTrinh=MaLoTrinh)
select *from cau1('PK')
Cau 2 :
create function cau2(@Soxe int)
returns table
as
return(
select
'Ma lo trinh'=MaLoTrinh, 'Ma trong
tai '=MaTrongTai, 'So luong van tai'=SoluongVT, 'Ngay
di '=Ngaydi, 'Ngay đen'=Ngayden
from ChiTietVanTai
where @Soxe=Soxe)
select *from cau2(333)
Cau 3 :
create function cau3(@trongtai int)
returns table
as
return (
select distinct 'So xe'=Soxe
from ChiTietVanTai a join TrongTai b on
a.matrongtai=b.matrongtai
where b. trongtaiQD>=@trongtai)
select *from cau3(3)
Cau 4 :
CREATE FUNCTION cau4(@MaTrongTai INT, @MaLoTrinh NVARCHAR(10))
RETURNS TABLE
AS
RETURN (
SELECT COUNT(Soxe) AS 'So luong xe'
FROM ChiTietVanTai
WHERE MaLoTrinh = @MaLoTrinh
AND MaTrongTai >= @MaTrongTai
);
SELECT * FROM cau4(50, 'PK');
Cau 5 :
CREATE PROCEDURE cau5 (
@malotrinh NVARCHAR(10),
@soluong INT OUTPUT
)
AS
BEGIN
SELECT @soluong = COUNT(DISTINCT soxe)
FROM ChiTietVanTai
WHERE MaLoTrinh = @malotrinh;
END;
GO
DECLARE @t1 INT;
EXEC cau5 'PK', @t1 OUTPUT;
PRINT @t1;
GO
Cau 6 :
CREATE PROCEDURE cau6 (
@malotrinh NVARCHAR(10),
@nam INT,
@sotien MONEY OUTPUT
)
AS
BEGIN
SELECT @sotien = SUM(DonGia)
FROM ChiTietVanTai
WHERE MaLoTrinh = @malotrinh AND YEAR(NgayDi) = @nam;
END;
DECLARE @st MONEY;
EXEC cau6 'PK', 2014, @st OUTPUT;
PRINT N'So tien: ' + CAST(@st AS NVARCHAR(10));
Cau 7 :
CREATE PROCEDURE cau6 (
@malotrinh NVARCHAR(10),
@nam INT,
@sotien MONEY OUTPUT
)
AS
BEGIN
SELECT @sotien = SUM(DonGia)
FROM ChiTietVanTai
WHERE MaLoTrinh = @malotrinh AND YEAR(NgayDi) = @nam;
END;
DECLARE @st MONEY;
EXEC cau6 'PK', 2014, @st OUTPUT;
PRINT N'So tien: ' + CAST(@st AS NVARCHAR(10));
Cau 7 :
CREATE PROCEDURE cau7(
@soxe NVARCHAR(10),
@nam INT,
@sotien MONEY OUTPUT
)
AS
BEGIN
SELECT @sotien = SUM(DonGia)
FROM ChiTietVanTai
WHERE SoXe = @soxe AND YEAR(NgayDi) = @nam;
END;
DECLARE @st MONEY;
EXEC cau7 '444', 2014, @st OUTPUT;
PRINT N'So tien: ' + CAST(@st AS NVARCHAR(10));
Cau 8 :
CREATE PROCEDURE cau8(
@matrongtai NVARCHAR(10),
@s1 INT OUTPUT
)
AS
BEGIN
SELECT @s1 = COUNT(DISTINCT SoXe)
FROM ChiTietVanTai a
JOIN TrongTai b ON a.MaTrongTai = b.MaTrongTai
WHERE @matrongtai = a.MaTrongTai AND SoLuongVT > TrongTaiQD;
END;
DECLARE @soluong INT;
EXEC cau8 '50', @soluong OUTPUT;
PRINT N'Sốlượng xe: ' + CAST(@soluong AS NVARCHAR(10));
Bai 2 :
cau 1 :
create function caul(@nam int)
returns table
as
return (
select 'ma nhan vien'=manv, 'Ho ten nhan vien'=ho+ten
from tNhanVien
where @nam=year (NTNS))
select *from caul(1963)
Cau 2 :
create function cau2(@thamnien int)
returns table
as
return
select 'mã nhân viên'=manv, 'Ho ten nhan
vien'=ho+ten, 'So nam tham nien'=year (getdate())-
year (ngaybd)
from tnhanvien
where @thamnien=year (getdate())-year(ngaybd)
select *from cau2(25)
Cau 3 :
CREATE FUNCTION cau3 (@chucvu NVARCHAR(10))
RETURNS TABLE
AS
RETURN (
SELECT manv AS 'ma nhan vien'
FROM tChiTietNhanVien
WHERE Chucvu = @chucvu
);
SELECT * FROM cau3('TP');
Cau 4 :
create function cau4()
returns table
as
return
select 'ma nhan vien'=manv, 'Ho ten nhan vien'=ho+ten
from tnhanvien
where year (getdate())-year(ngaybd) >=3
