Functions.sql

-- D--
--1
-- Function to calculate the average score of a topic
GF
CREATE FUNCTION DTB_DT (@MSDT CHAR (6)) RETURNS FLOAT
AS
BEGIN
	DECLARE @DTB FLOAT
	SELECT @DTB = AVG(DIEM)
	FROM (SELECT * FROM GV_HDDT UNION
		  SELECT * FROM GV_PBDT UNION
		  SELECT * FROM GV_UVDT) AS DIEMTONGHOP
	WHERE MSDT = @MSDT
	IF @DTB IS NULL
		SET @DTB = 0
	RETURN @DTB
END
GO
-- THUC THI
SELECT DBO.DTB_DT ('97002') AS KQ
-- 2
-- Function returns the results of the topic according to the entered MSDT
GO
CREATE FUNCTION KQ_DT (@MSDT CHAR (6)) RETURNS NVARCHAR(50)
AS
BEGIN
	DECLARE @DTB FLOAT
	DECLARE @KQ NVARCHAR(50)
	SELECT @DTB = AVG(DIEM)
	FROM (SELECT * FROM GV_HDDT UNION
		  SELECT * FROM GV_PBDT UNION
		  SELECT * FROM GV_UVDT) AS DIEMTONGHOP
	WHERE MSDT = @MSDT

	IF @DTB >=5
		SET @KQ='DAT'
	ELSE SET @KQ ='KHONGDAT'
	RETURN @KQ
END
GO
-- THUC THI
SELECT DBO.KQ_DT('97002') AS KQ
-- 3
-- Returns the student code and full name of the students conducting the project with the corresponding entered MSDT
GO
CREATE FUNCTION SV_TH_DT (@MSDT CHAR (6)) RETURNS TABLE
AS
RETURN(
	SELECT MSSV,TENSV
	FROM SINHVIEN
	WHERE MSSV IN
	(SELECT MSSV FROM SV_DETAI WHERE MSDT=@MSDT)
);
GO
-- THUC THI
SELECT*FROM SV_TH_DT ('97001')