Stored Procedure trong dự án quản lý sinh viên với SQL Server

Anh ơi cho em hỏi câu 4 phải có thểm điều kiện để tách khoa chứ nhỉ, em cảm ơn ạ.

đây là bài của em ạ, anh xem giúp em.

ALTER PROC Print_Student_Thuoc_Khoa
    @maKhoa VARCHAR(10)
AS
BEGIN
    SELECT SINHVIEN.* FROM SINHVIEN
    LEFT JOIN LOP ON SINHVIEN.MaLop = LOP.MaLop
    LEFT JOIN KHOA ON LOP.MaKhoa = KHOA.MaKhoa
    WHERE KHOA.MaKhoa = @maKhoa
END

·      Hướng dẫn thực hành

    Hãy viết các Stored Procedure sau:

1.  In danh sách các sinh viên của 1 lớp học

CREATE PROC TAKE_STUDENT_LIST(@MaMH VARCHAR(10))

AS

BEGIN

SELECT dbo.Sinh_Vien.Ho_ten

FROM dbo.Sinh_Vien LEFT JOIN dbo.Lop

ON Lop.MaLop = Sinh_Vien.Malop

WHERE dbo.Lop.MaLop = @MaMH

END

EXEC dbo.TAKE_STUDENT_LIST @MaMH = 'TH2002/02' -- varchar(10)

1. Nhập vào 2 sinh viên, 1 môn học, tìm xem sinh viên nào có điểm thi môn học đó lần đầu tiên là cao hơn.

CREATE PROC Compare_Score(@MaSV1 VARCHAR(10),@MaSV2 VARCHAR(10),@MaMH VARCHAR(10))

AS

BEGINS

IF((

SELECT dbo.Ket_qua.Diem_thi FROM dbo.Ket_qua

WHERE dbo.Ket_qua.MaSV = @MaSV1 AND Lan_thi = '1' AND dbo.Ket_qua.MaMH = @MaMH) >

(SELECT dbo.Ket_qua.Diem_thi FROM dbo.Ket_qua

WHERE dbo.Ket_qua.MaSV = @MaSV2 AND Lan_thi = '1' AND dbo.Ket_qua.MaMH = @MaMH))

SELECT dbo.Ket_qua.MaSV,dbo.Sinh_Vien.Ho_ten FROM dbo.Ket_qua LEFT JOIN dbo.Sinh_Vien ON Sinh_Vien.MaSV = Ket_qua.MaSV WHERE dbo.Ket_qua.MaSV = @MaSV1

ELSE

SELECT dbo.Ket_qua.MaSV,dbo.Sinh_Vien.Ho_ten FROM dbo.Ket_qua LEFT JOIN dbo.Sinh_Vien ON Sinh_Vien.MaSV = Ket_qua.MaSV WHERE dbo.Ket_qua.MaSV = @MaSV2

END

EXEC dbo.Compare_Score @MaSV1 = '0212004', -- varchar(10)

                       @MaSV2 = '0212002', -- varchar(10)

                       @MaMH = 'THT01'   -- varchar(10)

1. Nhập vào 1 môn học và 1 mã sv, kiểm tra xem sinh viên có đậu môn này trong lần thi đầu tiên không, nếu đậu thì xuất ra là “Đậu”, không thì xuất ra “Không đậu”

CREATE PROC Check_Result(@masv VARCHAR(10),@mamh VARCHAR(10))

AS

BEGIN

IF((SELECT dbo.Ket_qua.Diem_thi FROM dbo.Ket_qua

WHERE dbo.Ket_qua.MaSV = @masv AND dbo.Ket_qua.MaMH = @mamh AND Lan_thi = '1')>5)

PRINT N'đậu'

ELSE

PRINT N'Không đậu'

END

EXEC dbo.Check_Result @masv = '0212002', -- varchar(10)

                      @mamh = 'THCS01'  -- varchar(10)

1. Nhập vào 1 khoa, in danh sách các sinh viên (mã sinh viên, họ tên, ngày sinh) thuộc khoa này.

CREATE PROC BP_List_Student_In_Khoa(@makhoa VARCHAR(10))

AS

BEGIN

    SELECT dbo.Sinh_Vien.Ho_ten FROM dbo.Sinh_Vien LEFT JOIN lop ON Lop.MaLop = Sinh_Vien.Malop LEFT JOIN dbo.Khoa ON Khoa.MaKhoa = Lop.Ma_Khoa

   WHERE dbo.Khoa.MaKhoa = @makhoa

END

EXEC dbo.BP_List_Student_In_Khoa @makhoa = 'CNTT' -- varchar(10)

EXEC dbo.BP_List_Student_In_Khoa @makhoa = 'VL' -- varchar(10)

1. Nhập vào 1 sinh viên và 1 môn học, in điểm thi của sinh viên này của các lần thi môn học đó.

CREATE PROC BP_Score_List_1(@masv VARCHAR(10),@mamh VARCHAR(10))

AS

BEGIN

SELECT dbo.Mon_hoc.Ten_Mon_hoc,dbo.Ket_qua.Lan_thi,dbo.Ket_qua.Diem_thi FROM dbo.Ket_qua LEFT JOIN dbo.Mon_hoc ON Mon_hoc.MaMH = Ket_qua.MaMH

WHERE dbo.Ket_qua.MaMH = @mamh AND dbo.Ket_qua.MaSV = @masv

END

EXEC dbo.BP_Score_List_1 @masv = '0212001', -- varchar(10)

                       @mamh = 'THT01'  -- varchar(10)

EXEC dbo.BP_Score_List_1 @masv = '0212003', -- varchar(10)

                         @mamh = 'THT02'  -- varchar(10)

EXEC dbo.BP_Score_List_1 @masv = '0212004', -- varchar(10)

                         @mamh = 'THT01'  -- varchar(10)

Ví dụ:  Lần 1 : 10 Lần 2: 8

1. Nhập vào 1 sinh viên, in ra các môn học mà sinh viên này phải học.

CREATE PROC BP_LIST_MH_1(@MaSV VARCHAR(10))

AS

BEGIN

SELECT DISTINCT dbo.Mon_hoc.Ten_Mon_hoc FROM dbo.Ket_qua

LEFT JOIN dbo.Mon_hoc

ON Mon_hoc.MaMH = Ket_qua.MaMH

WHERE @MaSV = dbo.Ket_qua.MaSV

END

EXEC dbo.BP_LIST_MH_1 @MaSV = '0212001' -- varchar(10)

1. Nhập vào 1 môn học, in danh sách các sinh viên đậu môn này trong lần thi đầu tiên. 

CREATE PROC BP_SV_DAU_LIST(@mamh VARCHAR(10))

AS

BEGIN

SELECT dbo.Sinh_Vien.Ho_ten FROM dbo.Ket_qua

LEFT JOIN dbo.Sinh_Vien ON Sinh_Vien.MaSV = Ket_qua.MaSV

WHERE @mamh = dbo.Ket_qua.MaMH AND dbo.Ket_qua.Lan_thi = '1' AND dbo.Ket_qua.Diem_thi>4

END

go

EXEC dbo.BP_SV_DAU_LIST @mamh = 'THT01' -- varchar(10)

EXEC dbo.BP_SV_DAU_LIST @mamh = 'THCS01' -- varchar(10)

1. In điểm các môn học của sinh viên có mã số là maSinhVien được nhập vào.

Chú ý: điểm của môn học là điểm thi của lần thi sau cùng

CREATE PROC BP_LIST_LAST_SCORE_1(@Masv VARCHAR(10))

AS

BEGIN

SELECT dbo.Mon_hoc.Ten_Mon_hoc,dbo.Ket_qua.Diem_thi FROM dbo.Ket_qua LEFT JOIN dbo.Mon_hoc ON Mon_hoc.MaMH = Ket_qua.MaMH

LEFT JOIN (SELECT dbo.Ket_qua.MaMH,MAX(dbo.Ket_qua.Lan_thi) AS LAST_LAN_THI FROM dbo.Ket_qua

WHERE MaSV = @Masv GROUP BY dbo.Ket_qua.MaMH) AS LAST_RESULT ON LAST_RESULT.MaMH = Ket_qua.MaMH

WHERE dbo.Ket_qua.MaSV = @Masv AND LAST_RESULT.LAST_LAN_THI = dbo.Ket_qua.Lan_thi

END

GO

EXEC dbo.BP_LIST_LAST_SCORE_1 @Masv = '0212001' -- varchar(10)

EXEC dbo.BP_LIST_LAST_SCORE_1 @Masv = '0212003' -- varchar(10)

/* Câu 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.
(Chú ý: Điểm trung bình được tính dựa trên điểm thi lần sau cùng).
Sử dụng function 3 đã viết ở bài 4

=> tham gia đầy đủ các môn học của khoa => phải lấy ra được danh sách
các môn học thuộc khoa & có kết quả thi
=> Viết FUNC liệt kê danh sách SV thuộc khoa: dbo.TVDF_DS_SV_Of_Khoa
=> Viết FUNC tính điểm trung bình: dbo.TVDF_Diem_TB_C2
=> Viết FUNC tìm điểm thi của lần sau cùng : dbo.TVDF_DS_Lan_Thi_Cuoi
=> Viết View tạo report  "KẾT QUẢ HỌC TẬP" */

-- Liệt kê danh sách Sinh Viên thuộc Khoa
ALTER FUNCTION TVDF_DS_SV_Of_Khoa
(
    @MaKhoa VARCHAR(10)
)
RETURNS TABLE
RETURN
    SELECT DISTINCT SV.MaSV, SV.Ho_Ten, SV.Nam_Sinh, K.Ten_Khoa FROM dbo.Giang_Khoa AS GK    
    LEFT JOIN dbo.Khoa AS K ON K.Ma_Khoa = GK.Ma_Khoa    
    LEFT JOIN dbo.Lop AS L ON L.Ma_Khoa = K.Ma_Khoa         
    LEFT JOIN dbo.Sinh_Vien AS SV ON SV.Ma_Lop = L.Ma_Lop
    LEFT JOIN dbo.Khoa_Hoc AS KH ON KH.Ma_Khoa_Hoc = L.Ma_Khoa_Hoc
    LEFT JOIN dbo.Mon_Hoc AS MH ON MH.MaMH = GK.MaMH
    LEFT JOIN dbo.Chuong_Trinh_Hoc AS CT ON CT.Ma_CT = GK.Ma_CT
    WHERE  K.Ma_Khoa = @MaKhoa
    AND GK.Nam_Hoc >= KH.Nam_Bat_Dau
    AND GK.Nam_Hoc <= KH.Nam_Ket_Thuc
GO    

-- Tạo View KẾT QUẢ HỌC TẬP
ALTER VIEW TVDV_Ket_Qua_Hoc_Tap
AS
    SELECT DISTINCT SV.MaSV AS [MaSV],
    SV.Ho_Ten AS [HoVaTen],
    CT.Ten_CT AS [TenChuongTrinh],
    dbo.TVDF_Check_SV_Of_Khoa(SV.MaSV) AS [TenKhoa],
    dbo.TVDF_Diem_TB_C2(SV.MaSV) AS [DiemTrungBinh]
    FROM dbo.Sinh_Vien AS SV
    LEFT JOIN dbo.Lop AS L ON L.Ma_Lop = SV.Ma_Lop
    LEFT JOIN dbo.Khoa AS K ON K.Ma_Khoa = L.Ma_Khoa
    LEFT JOIN dbo.Chuong_Trinh_Hoc AS CT ON CT.Ma_CT = L.Ma_CT
    LEFT JOIN dbo.Mon_Hoc AS MH ON MH.Ma_Khoa = K.Ma_Khoa
    LEFT JOIN dbo.Khoa_Hoc AS KH ON KH.Ma_Khoa_Hoc = L.Ma_Khoa_Hoc
    LEFT JOIN dbo.Giang_Khoa AS GK ON GK.Ma_Khoa = K.Ma_Khoa
    WHERE GK.Nam_Hoc >= KH.Nam_Bat_Dau
    AND GK.Nam_Hoc <= KH.Nam_Ket_Thuc
GO
-- Câu truy vấn View Ket_Qua_Hoc_Tap
SELECT * FROM TVDV_Ket_Qua_Hoc_Tap
GO

-- CÁCH 2: Tạo Table
CREATE TABLE Xep_Loai 
/*    - Tạo table Xep_Loai
    - Insert MaSV vào table Xep_Loai
    - Tạo các FUNC truy vấn: (Tái sử dụng lại các câu truy vấn trên CÁCH 1: Tạo VIEW)
        + Điểm trung bình
        + Kết quả
        + Học lực
    - Update truy vấn table Xep_Loai */
(
    MaSV VARCHAR(10),
    DiemTrungBinh FLOAT(2),
    KetQua NVARCHAR(10),
    HocLuc NVARCHAR(10)
    PRIMARY KEY (MaSV)
    FOREIGN KEY (MaSV) REFERENCES dbo.Sinh_Vien(MaSV)
)

-- Insert MaSV từ table Sinh_Vien vào table Xep_Loai
INSERT INTO dbo.Xep_Loai (MaSV)
SELECT MaSV FROM dbo.Sinh_Vien
GO
SELECT * FROM dbo.Xep_Loai
GO

-- Update truy vấn table Xep_Loai
UPDATE  dbo.Xep_Loai
SET     DiemTrungBinh = dbo.TVDF_Diem_TB_C2(MaSV)
GO    
UPDATE  dbo.Xep_Loai
SET     KetQua = dbo.TVDF_Ket_Qua(MaSV)
GO    
UPDATE  dbo.Xep_Loai
SET     HocLuc = dbo.TVDF_Hoc_Luc(MaSV)
GO

-- Truy vấn table Xep_Loai:

SELECT * FROM dbo.Xep_Loai
GO

/* Câu 9. Đưa dữ liệu vào bảng Xếp Loại. Sử dụng function 3 đã viết ở bài 4
Qui đị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 đó >= 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'
Đố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' */

/*-- CÁCH 1: Tạo View 
=> Tạo FUNC Tính điểm trung bình (TVDF_Diem_TB_C2)
=> Tạo FUNC kết quả (không quá 2 môn dưới 4 điểm => Đạt / Không đạt)
=> Tạo FUNC học lực (Giỏi, Khá, Trung Bình)
=> Tạo View Xep_Loai (MaSV, DiemTrungBinh, KetQua, HocLuc)  */

-- Tạo Function Điểm Trung Bình:

CREATE FUNCTION TVDF_Diem_TB_C2
(
    @MaSV VARCHAR(10)
)
RETURNS FLOAT(2)
AS
BEGIN
DECLARE @DiemTB FLOAT(2) = 0;
SELECT @DiemTB = ROUND(AVG(DiemThiCuoi), 3) FROM     
(
    SELECT DISTINCT KQ.MaMH, dbo.TVDF_Diem_Thi_Cuoi(@MaSV,KQ.MaMH) AS [DiemThiCuoi] FROM dbo.Ket_Qua AS KQ
    WHERE KQ.MaSV = @MaSV
) AS Ket_Qua_1
RETURN @DiemTB
END
GO

-- Tạo Function Kết Quả
CREATE FUNCTION TVDF_Ket_Qua
(
    @MaSV VARCHAR(10)    
)
RETURNS NVARCHAR(10)
AS
BEGIN
    DECLARE @Kq NVARCHAR(10);
    DECLARE @Dtb FLOAT (2);
    SELECT @Dtb = dbo.TVDF_Diem_TB_C2(@MaSV)
    IF @Dtb >= 5
        SET @Kq = N'Đạt'
    ELSE
        SET @Kq = N'Không đạt'
RETURN @Kq
END
GO

-- Tạo Function Học Lực
CREATE FUNCTION TVDF_Hoc_Luc
(
    @MaSV VARCHAR(10)    
)
RETURNS NVARCHAR(10)
AS
BEGIN
    DECLARE @Kq NVARCHAR(10);
    DECLARE @Dtb FLOAT (2);
    DECLARE @HocLuc NVARCHAR(10);
    
    SELECT @Dtb = dbo.TVDF_Diem_TB_C2(@MaSV)
    SELECT @Kq = dbo.TVDF_Ket_Qua(@MaSV)
    IF (@Kq = N'Đạt' AND @Dtb >= 8)
        SET @HocLuc = N'Giỏi'
    ELSE 
        IF (@Kq = N'Đạt' AND @Dtb < 8 AND @Dtb > 7)
            SET @HocLuc = N'Khá'
        ELSE 
            IF @Dtb IS NOT NULL
                SET @HocLuc = N'Trung Bình'
            ELSE
                SET @HocLuc = N''
RETURN @HocLuc
END
GO

-- Tạo View Xếp Loại
ALTER VIEW TVDV_Xep_Loai
AS
    SELECT SV.MaSV AS [MaSV],
    dbo.TVDF_Diem_TB_C2(SV.MaSV) AS [DiemTrungBinh],
    dbo.TVDF_Ket_Qua(SV.MaSV) AS [KetQua],
    dbo.TVDF_Hoc_Luc(SV.MaSV) AS [HocLuc]
    FROM dbo.Sinh_Vien AS SV
GO

-- Truy vấn View Xếp Loại
SELECT * FROM dbo.TVDV_Xep_Loai
GO