quản lý thông tin báo cáo bài tập thực hành buổi 2 tham số vào là msgv tengv sodt diachi mshh namhh

Bạn đang xem bản rút gọn của tài liệu. Xem và tải ngay bản đầy đủ của tài liệu tại đây (321.32 KB, 18 trang )

<span class="text_page_counter">Trang 1</span><div class="page_container" data-page="1">

<b>ĐẠI HỌC QUỐC GIA THÀNH PHỐ HỒ CHÍ MINH TRƯỜNG ĐẠI HỌC CƠNG NGHỆ THƠNG TIN KHOA KHOA HỌC VÀ KĨ THUẬT THÔNG TIN </b>

<b>QUẢN LÝ THÔNG TIN </b>

<b>BÁO CÁO BÀI TẬP THỰC HÀNH BUỔI 2</b>

<b>GVHD: Nguyễn Gia Tuấn Anh </b>

Phạm Nhật Duy

 Tp. Hồ Chí Minh, 02/2024 

</div><span class="text_page_counter">Trang 2</span><div class="page_container" data-page="2">

<b>--Câu 1 Tham số vào là MSGV, TENGV, SODT, DIACHI, MSHH, NAMHH. Trước khi insert dữ liệu cần kiểm tra MSHH đã tồn tại trong table HOCHAM chưa, nếu chưa thì trả về giá trị 0. </b>

CREATE PROC SP_INSERTGV_MSHH

@MSGV INT, @TENGV NVARCHAR(30), @SODT VARCHAR(10), @DIACHI NVARCHAR(50), @MSHH INT, @NAMHH SMALLDATETIME AS

<b>-- INSERT KO THANH CONG </b>

EXEC SP_INSERTGV_MSHH @MSGV=00555, @TENGV=N'Nguyễn Thị C', @DIACHI=N'TPHCM', @SODT='0123456789', @MSHH=4, @NAMHH='2010'

<b>-- INSERT THANH CONG </b>

EXEC SP_INSERTGV_MSHH 00999, N'Đỗ Phúc', '0987654321', N'Huế', 2, '2022'

SELECT * FROM GIAOVIEN

</div><span class="text_page_counter">Trang 3</span><div class="page_container" data-page="3">

DELETE FROM GIAOVIEN WHERE MSGV = 00999

DROP PROC SP_INSERTGV_MSHH

<b>--Câu 2 Tham số vào là MSGV, TENGV, SODT, DIACHI, MSHH, NAMHH. Trước khi insert dữ liệu cần kiểm tra MSGV trong table GIAOVIEN có trùng khơng, nếu trùng thì trả về giá trị 0. </b>

CREATE PROC INSERTGV

@MSGV INT, @TENGV NVARCHAR(30), @SODT VARCHAR(10), @DIACHI NVARCHAR(50), @MSHH INT, @NAMHH SMALLDATETIME

AS BEGIN

IF EXISTS (SELECT * FROM GIAOVIEN WHERE MSGV = @MSGV) BEGIN

PRINT 'MSGV DA TON TAI' RETURN 0

<b>--INSERT KHONG THANH CONG </b>

EXEC INSERTGV @MSGV=00201, @TENGV=N'Nguyễn Thị C', @DIACHI=N'TPHCM', @SODT='0123456789', @MSHH=4, @NAMHH='2010'

<b>--INSERT THANH CONG </b>

</div><span class="text_page_counter">Trang 4</span><div class="page_container" data-page="4">

EXEC INSERTGV @MSGV=00999, @TENGV=N'Nguyễn Thị C', @DIACHI=N'TPHCM', @SODT='0123456789', @MSHH=2, @NAMHH='2010'

SELECT * FROM GIAOVIEN

DELETE FROM GIAOVIEN WHERE MSGV = 00999

DROP PROC INSERTGV

<b>--Câu 3 Giống (1) và (2) kiểm tra xem MSGV có trùng khơng? MSHH có tồn tại chưa? Nếu MSGV trùng thì trả về 0. Nếu MSHH chưa tồn tại trả về 1, ngược lại cho insert dữ liệu. </b>

CREATE PROC INSERTGV2

@MSGV INT, @TENGV NVARCHAR(30), @SODT VARCHAR(10), @DIACHI NVARCHAR(50), @MSHH INT, @NAMHH SMALLDATETIME

AS BEGIN

IF EXISTS (SELECT * FROM GIAOVIEN WHERE MSGV = @MSGV) BEGIN

PRINT 'MSGV DA TON TAI' RETURN 0

</div><span class="text_page_counter">Trang 5</span><div class="page_container" data-page="5">

INSERT INTO GIAOVIEN VALUES (@MSGV, @TENGV, @DIACHI, @SODT, @MSHH, @NAMHH)

PRINT 'THEM THANH CONG' END

<b>--3.1 THUC THI --TH1: MSGV TRÙNG </b>

EXEC INSERTGV2 @MSGV=00201, @TENGV=N'Nguyễn Thị C',

@DIACHI=N'TPHCM', @SODT='0123456789', @MSHH=2, @NAMHH='2010'

EXEC INSERTGV2 @MSGV=00998, @TENGV=N'Nguyễn Thị C',

@DIACHI=N'TPHCM', @SODT='0123456789', @MSHH=4, @NAMHH='2010'

<b>--TH3: THEM THANH CONG </b>

EXEC INSERTGV2 @MSGV=00999, @TENGV=N'Nguyễn Thị C',

@DIACHI=N'TPHCM', @SODT='0123456789', @MSHH=2, @NAMHH='2010'

SELECT * FROM GIAOVIEN

DELETE FROM GIAOVIEN WHERE MSGV = 00999

DROP PROC INSERTGV2

<b>--Câu 4 Đưa vào MSDT cũ, TENDT mới. Hãy cập nhật tên đề tài mới với mã đề tài cũ không đổi nếu không tìm thấy trả về 0, ngược lại cập nhật và trả về 1. </b>

CREATE PROC INSERT_DT

@MSDT CHAR(6), @TENDETAI NVARCHAR(30)

</div><span class="text_page_counter">Trang 6</span><div class="page_container" data-page="6">

AS BEGIN

IF NOT EXISTS (SELECT * FROM DETAI WHERE @MSDT = MSDT) BEGIN

PRINT 'MSDT KHONG TON TAI' RETURN 0

END

UPDATE DETAI

SET TENDT = @TENDETAI WHERE MSDT = @MSDT RETURN 1

END

<b>--TH1: MSDT KHONG TON TAI </b>

EXEC INSERT_DT @MSDT = 97999, @TENDETAI = 'HOC MAY'

<b>--TH2: THANH CONG </b>

EXEC INSERT_DT @MSDT = 97005, @TENDETAI = 'HOC MAY'

SELECT * FROM DETAI WHERE MSDT = 97005

EXEC INSERT_DT @MSDT = 97005, @TENDETAI = N'Xử lý ảnh'

DROP PROC INSERT_DT

</div><span class="text_page_counter">Trang 7</span><div class="page_container" data-page="7">

<b>--Câu 5 Tham số đưa vào MSSV, TENSV mới, DIACHI mới. Hãy cập nhật sinh viên trên với MSSV khơng đổi, nếu khơng tìm thấy trả về 0, ngược lại cập nhật và trả về 1. </b>

CREATE PROC INSERT_SV

@MSSV INT, @TENSV NVARCHAR(30), @DIACHI NCHAR(30) AS

DIACHI = @DIACHI WHERE MSSV = @MSSV

PRINT 'CAP NHAT THANH CONG' RETURN 1

END

<b>--TH1 MSSV KHONG TON TAI </b>

EXEC INSERT_SV @MSSV = 13529999, @TENSV = 'NGUYENVANA', @DIACHI = 'THU DUC'

</div><span class="text_page_counter">Trang 8</span><div class="page_container" data-page="8">

EXEC INSERT_SV @MSSV = 13520001, @TENSV = N'Nguyễn Văn An', @DIACHI = N'THỦ ĐỨC'

DROP PROC INSERT_SV

<b>--Câu 1 Đưa vào TENHV trả ra: Số GV thỏa học vị, nếu không tìm thấy trả về 0. </b>

CREATE PROC SP_SOGVTHOAHOCVI @TENHV NVARCHAR(20), @SOGV INT OUTPUT

AS BEGIN

IF NOT EXISTS (SELECT * FROM HOCVI WHERE TENHV = @TENHV) BEGIN

DECLARE @SOGV INT, @TENHV NVARCHAR(20) SET @TENHV = N'Thạc sĩ'

</div><span class="text_page_counter">Trang 9</span><div class="page_container" data-page="9">

EXEC SP_SOGVTHOAHOCVI @TENHV, @SOGV OUTPUT

PRINT N'Số GV có học vị ' + @TENHV + N' là: ' + CAST(@SOGV AS VARCHAR)

DROP PROC SP_SOGVTHOAHOCVI

<b>--Câu 2 Đưa vào MSDT cho biết: Điểm trung bình của đề tài, nếu khơng tìm thấy trả về 0. </b>

CREATE PROC AVERAGE_DIEMDT

@MSDT CHAR (6), @DTB FLOAT OUTPUT AS

SELECT @DTB = ((SUM(GV_HDDT.DIEM) + SUM(GV_PBDT.DIEM)+

SUM(GV_UVDT.DIEM)) / (count(GV_HDDT.DIEM) + count(GV_PBDT.DIEM)+ count(GV_UVDT.DIEM)))

FROM DETAI, GV_HDDT, GV_PBDT, GV_UVDT

WHERE GV_HDDT.MSDT = @MSDT AND GV_PBDT.MSDT = @MSDT AND GV_UVDT.MSDT = @MSDT

RETURN 1 END

DECLARE @DTB FLOAT, @MSDT CHAR(6) SET @MSDT = 97005

EXEC AVERAGE_DIEMDT @MSDT, @DTB OUTPUT

</div><span class="text_page_counter">Trang 10</span><div class="page_container" data-page="10">

PRINT N'Điểm trung bình của đề tài có mã số ' + @MSDT + N' là: ' + CAST(@DTB AS VARCHAR)

DROP PROC AVERAGE_DIEMDT

<b>--Câu 3 Đưa vào TENGV trả ra: SDT của giáo viên đó, nếu khơng tìm thấy trả về 0. Nếu trùng tên thì có báo lỗi khơng? Tại sao? Làm sao để hiện thơng báo có bao nhiêu giáo viên trùng tên và trả về các SDT. </b>

CREATE PROC GV_SDT

@TENGV NVARCHAR(30), @SODT NVARCHAR(30) OUTPUT, @SOGVTRUNG INT OUTPUT

AS BEGIN

<b>--KIỂM TRA XEM CÓ BAO NHIÊU GIÁO VIÊN TRÙNG TÊN </b>

SELECT @SOGVTRUNG = COUNT(*) FROM GIAOVIEN

WHERE TENGV = @TENGV

IF @SOGVTRUNG = 0 BEGIN

SET @SODT = 0 RETURN 0 END

<b>--CÓ NHIỀU GIÁO VIÊN TRÙNG TÊN </b>

IF @SOGVTRUNG > 1 BEGIN

SET @SODT = N'Có nhiều sdt khớp với tên giáo viên trên' SELECT SODT

FROM GIAOVIEN

</div><span class="text_page_counter">Trang 11</span><div class="page_container" data-page="11">

WHERE TENGV = @TENGV END

<b>--CHỈ CÓ 1 GIÁO VIÊN TRÙNG TÊN </b>

ELSE BEGIN

SELECT @SODT = SODT FROM GIAOVIEN

WHERE TENGV = @TENGV END

END

<b>***Với đoạn lệnh sql trên thì khi có nhiều giáo viên có trùng tên, nó sẽ đưa ra bảng gồm nhiều tên và sdt, trong dòng message sẽ in ra biến @SODT đã gán vào chuỗi ký tự thông báo là có nhiều giáo viên trùng tên. Cịn nếu chỉ có 1 tên khớp thì nó sẽ đưa ra sdt của người đó ngay. </b>

</div><span class="text_page_counter">Trang 12</span><div class="page_container" data-page="12">

DELETE FROM GIAOVIEN WHERE MSGV = 00206

DROP PROC GV_SDT

<b>--Câu 4 Đưa vào MSHD cho biết: Điểm trung bình các đề tài của hội đồng đó. </b>

CREATE PROC AVERAGE_DT_DIEM @MSHD INT, @DTB FLOAT OUTPUT AS

BEGIN

SELECT @DTB = ((SUM(hd.DIEM) + SUM(uv.DIEM)+ SUM(pb.DIEM)) / (count(hd.DIEM) + count(uv.DIEM)+ count(pb.DIEM)))

FROM HOIDONG , GV_HDDT HD, GV_PBDT PB,GV_UVDT UV

WHERE HOIDONG.MSGV=HD.MSGV AND HOIDONG.MSGV=UV.MSGV AND HOIDONG.MSGV=PB.MSGV AND HOIDONG.MSHD=@MSHD

DROP PROC AVERAGE_DT_DIEM

<b>--Câu 5 Đưa vào TENGV cho biết: Số đề tài hướng dẫn, số đề tài phản biện do giáo viên đó phụ trách. Nếu trùng tên thì có báo lỗi khơng hay hệ thống sẽ đếm tất cả các đề tài của những giáo viên trùng tên đó? </b>

</div><span class="text_page_counter">Trang 13</span><div class="page_container" data-page="13">

ON DETAI

INSTEAD OF DELETE AS

BEGIN

DECLARE @MSDT CHAR(6)

SELECT @MSDT = MSDT FROM DELETED

-- XOA CAC BANG LIEN QUAN

DELETE SV_DETAI WHERE MSDT = @MSDT DELETE GV_HDDT WHERE MSDT = @MSDT DELETE GV_PBDT WHERE MSDT = @MSDT DELETE GV_UVDT WHERE MSDT = @MSDT DELETE HOIDONG_DT WHERE MSDT = @MSDT

-- XOA BANG DETAI

DELETE DETAI WHERE MSDT = @MSDT END

SELECT * FROM DETAI

SELECT * FROM HOIDONG_DT

DELETE FROM DETAI WHERE MSDT = '97005'

INSERT INTO DETAI VALUES('97005',N'Xử lý ảnh') INSERT INTO SV_DETAI VALUES('13520002','97005') INSERT INTO SV_DETAI VALUES('13520006','97005') INSERT INTO GV_HDDT VALUES(00203,'97005',9)

</div><span class="text_page_counter">Trang 14</span><div class="page_container" data-page="14">

INSERT INTO GV_PBDT VALUES(00201,'97005',8) INSERT INTO GV_UVDT VALUES(00205,'97005',8) INSERT INTO GV_UVDT VALUES(00202,'97005',7) INSERT INTO GV_UVDT VALUES(00204,'97005',9) INSERT INTO HOIDONG_DT VALUES(1,'97005',N'Được')

DROP TRIGGER TRIG_XOATHONGTINDETAI

<b>--Câu 2 Tạo Trigger thỏa mãn ràng buộc là khi đổi 1 mã số giáo viên (MSGV) thì sẽ thay đổi các thơng tin liên quan. </b>

CREATE TRIGGER UPDATE_MSGV ON GIAOVIEN

AFTER UPDATE AS

BEGIN

IF UPDATE(MSGV) BEGIN

DECLARE @MSGV CHAR(6), @OLD CHAR(6) SELECT @MSGV = MSGV FROM INSERTED SELECT @OLD = MSGV FROM DELETED

UPDATE GV_HV_CN SET MSGV = @MSGV WHERE MSGV = @OLD

UPDATE GV_HDDT SET MSGV = @MSGV WHERE MSGV = @OLD

</div><span class="text_page_counter">Trang 15</span><div class="page_container" data-page="15">

UPDATE GV_PBDT SET MSGV = @MSGV WHERE MSGV = @OLD

UPDATE GV_UVDT SET MSGV = @MSGV WHERE MSGV = @OLD

UPDATE HOIDONG_GV SET MSGV = @MSGV WHERE MSGV = @OLD END

SELECT * FROM GIAOVIEN SELECT * FROM GV_UVDT

UPDATE GIAOVIEN SET MSGV = 00999 WHERE MSGV = 00201

DROP TRIGGER UPDATE_MSGV

<b>--Câu 3 Tạo Trigger thỏa mãn ràng buộc là một hội đồng không quá 10 đề tài. Dùng “Group by” có được khơng? Giải thích. </b>

CREATE TRIGGER HD_KHONGQUA_10DT ON HOIDONG_DT

FOR INSERT AS

BEGIN

DECLARE @MSHD INT

</div><span class="text_page_counter">Trang 16</span><div class="page_container" data-page="16">

SELECT @MSHD = MSHD FROM INSERTED

IF(SELECT COUNT(*) FROM HOIDONG_DT WHERE MSHD = @MSHD GROUP BY MSHD) > 10

BEGIN

PRINT 'HOI DONG NAY KHONG DUOC QUA 10 DE TAI' ROLLBACK TRANSACTION

END END

***Dùng group by được vì ta dùng hàm count để đếm số đề tài mà hội đồng đó đăng kí nên sẽ cần GROUP BY MSHD

<b>---VI TALBE DETAI KHONG CO DU SO LUONG DE TAI DANG KI </b>

INSERT DETAI VALUES ('97007',N'Quản lý thư viện'), ('97008',N'Quản lý thư viện'),

('97009',N'Quản lý thư viện'), ('97010',N'Quản lý thư viện'), ('97011',N'Quản lý thư viện');

INSERT HOIDONG_DT VALUES (1,'97003',N'Được'), (1,'97004',N'Được'),

(1,'97006',N'Được'), (1,'97007',N'Được'), (1,'97008',N'Được'), (1,'97009',N'Được'), (1,'97010',N'Được'), (1,'97011',N'Được');

DELETE HOIDONG_DT WHERE MSDT IN

</div><span class="text_page_counter">Trang 17</span><div class="page_container" data-page="17">

RETURN @DTB END

<b>--1.1 KIEM TRA THONG QUA CAU 2 CO SU DUNG FUNCTION TREN --1.2 XOA </b>

DROP FUNCTION TINH_DTB_DT

<b>--Câu 2 Trả về kết quả của đề tài theo MSDT nhập vào. Kết quả là DAT nếu như điểm trung bình từ 5 trở lên, và KHONGDAT nếu như điểm trung bình dưới 5. </b>

CREATE FUNCTION KETQUADT(@MSDT CHAR(6)) RETURNS VARCHAR(10)

AS BEGIN

DECLARE @DTB FLOAT

SELECT @DTB = DBO.TINH_DTB_DT(MSDT) FROM DETAI

WHERE MSDT = @MSDT IF(@DTB >= 5)

PRINT DBO.TINH_DTB_DT(@MSDT)

</div><span class="text_page_counter">Trang 18</span><div class="page_container" data-page="18">

DROP FUNCTION KETQUADT

</div>