QLSINHVIEN.sql

CREATE DATABASE QLSV1
USE QLSV1

CREATE TABLE LOP
(
MALOP CHAR(10) PRIMARY KEY NOT NULL,
TENLOP NVARCHAR(20) NOT NULL,
PHONG CHAR(10) NOT NULL
)

CREATE TABLE SV
(
MASV CHAR(10) PRIMARY KEY NOT NULL,
TENSV NVARCHAR(20) NOT NULL,
MALOP CHAR(10) NOT NULL,
CONSTRAINT FK1 FOREIGN KEY(MALOP) REFERENCES LOP(MALOP)
)


INSERT INTO SV VALUES
('1', 'A', '1'),
('2', 'B', '2'),
('3', 'C', '1'),
('4', 'D', '3')

INSERT INTO LOP VALUES
('1', 'CD', '1'),
('2', 'DH', '2'),
('3', 'CD', '2'),
('4', 'CH', '4')

SELECT * FROM SV
SELECT * FROM LOP

CREATE FUNCTION THONGKE(@PHONG CHAR(10))
RETURNS INT
AS
BEGIN
	IF (NOT EXISTS(SELECT * FROM LOP WHERE PHONG = @PHONG))
		RETURN 0
	ELSE
		DECLARE @DEM INT
		SELECT @DEM = COUNT(MALOP)
		FROM LOP
		WHERE PHONG = @PHONG
		GROUP BY PHONG
		RETURN @DEM
END

SELECT DBO.THONGKE('3')


CREATE FUNCTION CAU5(@PHONG CHAR(10))
RETURNS @TK1 TABLE (MASV CHAR(10), TENSV CHAR(10), TENLOP CHAR(10))
AS
BEGIN
	IF (NOT EXISTS(SELECT * FROM LOP WHERE PHONG = @PHONG))
		BEGIN
			INSERT INTO @TK1
			SELECT MASV, TENSV, TENLOP
			FROM SV INNER JOIN LOP
			ON SV.MALOP = LOP.MALOP
		END
	ELSE
		INSERT INTO @TK1
		SELECT MASV, TENSV, TENLOP
		FROM SV INNER JOIN LOP
		ON SV.MALOP = LOP.MALOP
		WHERE PHONG = @PHONG
	RETURN
END


SELECT * FROM CAU5('5')