Ràng buộc trong CSDL Quản lý sinh viên với SQL Server

--10.    Với các sinh viên có tham gia đầy đủ các môn học của khoa, chương trình mà sinh viên đang theo học, hãy in ra điểm trung bình cho các sinh viên này.

ALTER FUNCTION CHECK_NUMBER_OF_JOINED_SUBJECT(@MaSV VARCHAR(10))
RETURNS BIT
AS
BEGIN
DECLARE @RESULT_CHECKING BIT
DECLARE @NUMBER_SUBJECT_OF_STUDENT INT
DECLARE @NUMBER_SUBJECT_OF_MON_HOC INT
SET @RESULT_CHECKING = 0
SELECT @NUMBER_SUBJECT_OF_STUDENT = COUNT(*) FROM  (SELECT DISTINCT dbo.Ket_qua.MaMH FROM dbo.Ket_qua 
WHERE dbo.Ket_qua.MaSV = @MaSV) AS TEMP 
SELECT DISTINCT @NUMBER_SUBJECT_OF_MON_HOC = COUNT(*) FROM dbo.Giang_khoa
LEFT JOIN dbo.Chuong_trinh_hoc ON Chuong_trinh_hoc.MaCT = Giang_khoa.MaCT
LEFT JOIN dbo.Mon_hoc ON Mon_hoc.MaMH = Giang_khoa.MaMH
LEFT JOIN dbo.Khoa ON Khoa.MaKhoa = Giang_khoa.MaKhoa
LEFT JOIN dbo.Lop ON Lop.Ma_Khoa = Khoa.MaKhoa
LEFT JOIN dbo.Khoa_hoc ON Khoa_hoc.MaKH = Lop.MaKH
LEFT JOIN dbo.Sinh_Vien ON Sinh_Vien.Malop = Lop.MaLop
WHERE dbo.Giang_khoa.Nam_hoc <= dbo.Khoa_hoc.Nam_ket_thuc AND dbo.Giang_khoa.Nam_hoc >= dbo.Khoa_hoc.Nam_bat_dau AND @MaSV = dbo.Sinh_Vien.MaSV
IF(@NUMBER_SUBJECT_OF_MON_HOC = @NUMBER_SUBJECT_OF_STUDENT)
BEGIN
    SET @RESULT_CHECKING = 1
END
RETURN @RESULT_CHECKING
END
GO

ALTER FUNCTION UF_AVG_SCORE(@MaSV VARCHAR(10))
RETURNS FLOAT
AS
BEGIN
    DECLARE @DTB FLOAT
    SET @DTB = NULL
    IF(@MaSV = NULL)
     SET @DTB = NULL
    IF(DBO.CHECK_NUMBER_OF_JOINED_SUBJECT(@MaSV)=1)
    BEGIN
        SELECT @DTB = dbo.SCORE_AVARAGE(@MaSV)
    END
    RETURN @DTB
END

GO
--TEST
SELECT DBO.UF_AVG_SCORE('0212001')
SELECT DBO.UF_AVG_SCORE('0311002')
SELECT DBO.UF_AVG_SCORE('0212002')
SELECT DBO.UF_AVG_SCORE('0212003')
SELECT DBO.UF_AVG_SCORE('0212004')
SELECT DBO.UF_AVG_SCORE('0311001')

--10.    Với các sinh viên có tham gia đầy đủ các môn học của khoa, chương trình mà sinh viên đang theo học, hãy in ra điểm trung bình cho các sinh viên này.

ALTER FUNCTION CHECK_NUMBER_OF_JOINED_SUBJECT(@MaSV VARCHAR(10))
RETURNS BIT
AS
BEGIN
DECLARE @RESULT_CHECKING BIT
DECLARE @NUMBER_SUBJECT_OF_STUDENT INT
DECLARE @NUMBER_SUBJECT_OF_MON_HOC INT
SET @RESULT_CHECKING = 0
SELECT @NUMBER_SUBJECT_OF_STUDENT = COUNT(*) FROM  (SELECT DISTINCT dbo.Ket_qua.MaMH FROM dbo.Ket_qua 
WHERE dbo.Ket_qua.MaSV = @MaSV) AS TEMP 
SELECT DISTINCT @NUMBER_SUBJECT_OF_MON_HOC = COUNT(*) FROM dbo.Giang_khoa
LEFT JOIN dbo.Chuong_trinh_hoc ON Chuong_trinh_hoc.MaCT = Giang_khoa.MaCT
LEFT JOIN dbo.Mon_hoc ON Mon_hoc.MaMH = Giang_khoa.MaMH
LEFT JOIN dbo.Khoa ON Khoa.MaKhoa = Giang_khoa.MaKhoa
LEFT JOIN dbo.Lop ON Lop.Ma_Khoa = Khoa.MaKhoa
LEFT JOIN dbo.Khoa_hoc ON Khoa_hoc.MaKH = Lop.MaKH
LEFT JOIN dbo.Sinh_Vien ON Sinh_Vien.Malop = Lop.MaLop
WHERE dbo.Giang_khoa.Nam_hoc <= dbo.Khoa_hoc.Nam_ket_thuc AND dbo.Giang_khoa.Nam_hoc >= dbo.Khoa_hoc.Nam_bat_dau AND @MaSV = dbo.Sinh_Vien.MaSV
IF(@NUMBER_SUBJECT_OF_MON_HOC = @NUMBER_SUBJECT_OF_STUDENT)
BEGIN
    SET @RESULT_CHECKING = 1
END
RETURN @RESULT_CHECKING
END
GO

ALTER FUNCTION UF_AVG_SCORE(@MaSV VARCHAR(10))
RETURNS FLOAT
AS
BEGIN
    DECLARE @DTB FLOAT
    SET @DTB = NULL
    IF(@MaSV = NULL)
     SET @DTB = NULL
    IF(DBO.CHECK_NUMBER_OF_JOINED_SUBJECT(@MaSV)=1)
    BEGIN
        SELECT @DTB = dbo.SCORE_AVARAGE(@MaSV)
    END
    RETURN @DTB
END

GO
--TEST
SELECT DBO.UF_AVG_SCORE('0212001')
SELECT DBO.UF_AVG_SCORE('0311002')
SELECT DBO.UF_AVG_SCORE('0212002')
SELECT DBO.UF_AVG_SCORE('0212003')
SELECT DBO.UF_AVG_SCORE('0212004')
SELECT DBO.UF_AVG_SCORE('0311001')

Bài tập về nhà

Thêm 1 quan hệ

XepLoai:

1. Quy định : ketQua của sinh viên là ”Đạt‘ nếu diemTrungBinh (chỉ tính các môn đã có điểm) của sinh viên đó lớn hơn hoặc bằng 5 và không quá 2 môn dưới 4 điểm, ngược lại thì kết quả là không đạtĐưa dữ liệu vào bảng xếp loại. Sử dụng function 3 đã viết ở bài 4

Đối với những sinh viên có ketQua là ”Đạt‘ thì hocLuc được xếp loại như sau:

·         diemTrungBinh >= 8 thì hocLuc là ”Giỏi”

·        7 < = diemTrungBinh < 8 thì hocLuc là ”Khá” Còn lại là ”Trung bình”  

USE Student_Management

GO

CREATE TABLE Xeploai

(

MaSV VARCHAR(10) PRIMARY KEY,

DTB FLOAT,

KQ NVARCHAR(20),

HOCLUC NVARCHAR(20)

FOREIGN KEY(MaSV) REFERENCES dbo.Sinh_Vien(MaSV)

)

GO

INSERT INTO DBO.Xeploai(dbo.Xeploai.MaSV)

SELECT DISTINCT dbo.Ket_qua.MaSV FROM dbo.Ket_qua

GO

CREATE FUNCTION UF_CHECK_NUMBER_OF_UNDER_4_SCORE(@MaSV VARCHAR(10))

RETURNS INT

AS

BEGIN

DECLARE @TRACKING INT

SELECT @TRACKING =  COUNT(*) FROM dbo.Ket_qua LEFT JOIN

(SELECT dbo.Ket_qua.MaSV,dbo.Ket_qua.MaMH, MAX(dbo.Ket_qua.Lan_thi) AS LAST_EXAM

FROM dbo.Ket_qua

WHERE MaSV = @MaSV

GROUP BY dbo.Ket_qua.MaSV,dbo.Ket_qua.MaMH) AS TEMPL_TABLE ON TEMPL_TABLE.MaMH = Ket_qua.MaMH AND TEMPL_TABLE.MaSV = Ket_qua.MaSV

WHERE dbo.Ket_qua.Lan_thi = TEMPL_TABLE.LAST_EXAM AND Diem_thi <= 4

RETURN @TRACKING

END

GO

SELECT dbo.UF_CHECK_NUMBER_OF_UNDER_4_SCORE('0212001')

GO

CREATE FUNCTION UF_KETQUA(@MaSV VARCHAR(10))

RETURNS NVARCHAR(20)

AS

BEGIN

DECLARE @KET_QUA NVARCHAR(20)

DECLARE @DTB FLOAT

SET @DTB = dbo.SCORE_AVARAGE(@MaSV)

SET @KET_QUA = N'KHÔNG ĐẠT'

IF (@MaSV = NULL)

SET @KET_QUA = NULL

IF(@DTB>5 AND dbo.UF_CHECK_NUMBER_OF_UNDER_4_SCORE(@MaSV)<=2)

SET @KET_QUA = N'ĐẠT'

RETURN @KET_QUA

END

GO

CREATE FUNCTION UF_HOCLUC(@MaSV VARCHAR(10))

RETURNS NVARCHAR(30)

AS

BEGIN

DECLARE @HOCLUC NVARCHAR(30) = NULL

DECLARE @DTB FLOAT

DECLARE @KET_QUA NVARCHAR(20)

SET @KET_QUA = dbo.UF_KETQUA(@MaSV)

SET @DTB = dbo.SCORE_AVARAGE(@MaSV)

IF (@KET_QUA = N'ĐẠT')

BEGIN

IF(@DTB>=8)

SET @HOCLUC = N'Giỏi'

ELSE IF(@DTB>7)

SET @HOCLUC = N'Khá'

ELSE

SET @HOCLUC = N'Trung bình'

END

RETURN @HOCLUC

END

GO

UPDATE dbo.Xeploai

SET DTB = dbo.SCORE_AVARAGE(MaSV)

GO

UPDATE dbo.Xeploai

SET dbo.Xeploai.KQ = dbo.UF_KETQUA(MaSV)

GO

UPDATE dbo.Xeploai

SET HOCLUC = dbo.UF_HOCLUC(MaSV)

KẾT QUẢ :

1. Với các sinh viên có tham gia đầy đủ các môn học của khoa, chương trình mà sinh viên đang theo học, hãy in ra điểm trung bình cho các sinh viên này.

CREATE FUNCTION CHECK_NUMBER_OF_JOINED_SUBJECT(@MaSV VARCHAR(10))

RETURNS BIT

AS

BEGIN

DECLARE @RESULT_CHECKING BIT

DECLARE @NUMBER_SUBJECT_OF_STUDENT INT

DECLARE @NUMBER_SUBJECT_OF_MON_HOC INT

SET @RESULT_CHECKING = 0

SELECT DISTINCT @NUMBER_SUBJECT_OF_STUDENT = COUNT(*) FROM dbo.Ket_qua WHERE @MaSV = dbo.Ket_qua.MaSV

SELECT DISTINCT @NUMBER_SUBJECT_OF_MON_HOC = COUNT(*) FROM dbo.Mon_hoc

IF(@NUMBER_SUBJECT_OF_MON_HOC = @NUMBER_SUBJECT_OF_STUDENT)

BEGIN

    SET @RESULT_CHECKING = 1

END

RETURN @RESULT_CHECKING

END

GO

CREATE FUNCTION UF_AVG_SCORE(@MaSV VARCHAR(10))

RETURNS FLOAT

AS

BEGIN

    DECLARE @DTB FLOAT

   SET @DTB = NULL

   IF(@MaSV = NULL)

    SET @DTB = NULL

   IF(DBO.CHECK_NUMBER_OF_JOINED_SUBJECT(@MaSV)=1)

   BEGIN

       SELECT @DTB = dbo.SCORE_AVARAGE(@MaSV)

   END

   RETURN @DTB

END

GO

--TEST

SELECT DBO.UF_AVG_SCORE('0212001')

SELECT DBO.UF_AVG_SCORE('0311002')

SELECT DBO.UF_AVG_SCORE('0212002')

SELECT DBO.UF_AVG_SCORE('0212003')

SELECT DBO.UF_AVG_SCORE('0212004')

SELECT DBO.UF_AVG_SCORE('0311001')

Chào HowKTeam!

Sau khi mình làm FUNC, PROC, TRIGGE cho các bài tập đầy đủ, thì mình mở Bảng Điểm để kiểm tra lại các lệnh truy vấn có sai sót gì ko, thì phát hiện ra cột Diem_Thi của bảng Điểm bị thay đổi NULL toàn bộ, sau đó mình có nhập lại điểm, phù hợp theo các TRIGGE đã tạo, nhưng khi tắt & mở lại bảng Điểm thì vẫn NULL, 1 số dòng thì lại có nhưng kết quả ko đúng như ban đầu mình đã nhập.

Rất mong các bạn hỗ trợ, chia sẽ. Cảm ơn HowKTeam rất nhiều.

/*    Câu 8: Số tiết lý thuyết của mỗi giảng khóa không nhỏ hơn số tiết thực hành
            Liên bộ trên 1 quan hệ */
    -- Cách 1: Dùng CHECK
    --ALTER TABLE dbo.Giang_Khoa
    --ADD CONSTRAINT TVDT_So_Tiet
    --CHECK (STLT >= STTH)
    --GO
    -- Cách 2: Dùng TRIGGER
    ALTER TRIGGER TVDT_So_Tiet
    ON dbo.Giang_Khoa
    FOR INSERT, UPDATE
    AS
    BEGIN
        --DECLARE @STLT INT;
        --DECLARE @STTH INT;
        --SELECT @STLT = Inserted.STLT FROM Inserted
        --SELECT @STTH = Inserted.STTH FROM Inserted
        --IF (@STLT < @STTH)
            --PRINT N'Số tiết lý thuyết của mỗi giảng khóa không nhỏ hơn số tiết thực hành !'
            --ROLLBACK

        IF NOT EXISTS (SELECT * FROM Inserted
                        WHERE Inserted.STLT !< Inserted.STTH)
        BEGIN
            RAISERROR(N'Số tiết lý thuyết của mỗi giảng khóa không nhỏ hơn số tiết thực hành !', 16, 1)
            ROLLBACK TRANSACTION
        END
    END
    GO