SQL Server - 36

END

6. Thêm bản ghi cho bảng sinh viên (thủ tục phải thực hiện kiểm tra tính hợp lệ của dữ liệu cần bổ sung, không trùng khoá chính và đảm bảo toàn vẹn tham chiếu)

Đã làm trong ví dụ 2 chương 8.

7. Thêm bản ghi cho bảng học phần học (thủ tục phải thực hiện kiểm tra tính hợp lệ của dữ liệu cần bổ sung, không trùng khoá chính và đảm bảo toàn vẹn tham chiếu).

CREATE PROCEDURE sp_Insert_HP

@mahp as nvarchar(10),

@tenhp as nvarchar(50),

@sotc as int,

@hocky as nvarchar(2) AS

Có thể bạn quan tâm!

• Xác Định Các Thông Số Của Cơ Sở Dữ Liệu
• Xác Định Các Thông Số Của Khóa Ngoại
• Thực Hiện Import Câu 2: Tạo View Thực Hiện Các Công Việc Sau:
• SQL Server - 37
• SQL Server - 38
• SQL Server - 39

Xem toàn bộ 323 trang tài liệu này.

BEGIN

declare @kt as bit set @kt=1

if exists (select 'true' from hocphan where mahp=@mahp) begin

set @kt=0

raiserror('Mã học phần bị trùng',16,6) end

if LEN(@mahp)=0 begin

set @kt=0

raiserror('Mã học phần không hợp lệ',16,6)

end

if LEN(@tenhp)=0 begin

set @kt=0

raiserror('Tên học phần không hợp lệ',16,6)

end

if sotc<0

begin

set @kt=0

raiserror('Số tín chỉ không hợp lệ',16,6)

end

if @kt=1

begin

insert into hocphan(Mahp,tenhp,sotc, hocky) values (@Mahp,@tenhp,@sotc,@hocky) end

END

8. Thêm bản ghi cho bảng điểm (thủ tục phải thực hiện kiểm tra tính hợp lệ của dữ liệu cần bổ sung, không trùng khoá chính và đảm bảo toàn vẹn tham chiếu).

CREATE PROCEDURE sp_Insert_Diem

@masv as nvarchar(10),

@mahp as nvarchar(50),

@diem as float AS

BEGIN

declare @kt as bit set @kt=1

if not exists (select „True‟ from sinhvien where masv=@masv) begin

set @kt=0

raiserror('Mã sinh viên không tồn tại trong bảng sinh viên',16,6) end

if not exists (select „True‟ from hocphan where mahp=@mahp) begin

set @kt=0

raiserror('Mã học phần không tồn tại trong bảng học phần',16,6) end

if @diem<0 or @diem>10 begin

set @kt=0

raiserror('Điểm thi không hợp lệ',16,6) end

if @kt=1

begin

insert into Diem(Masv,Mahp,diem) values (@Masv,@Mahp,@diem) end

END

9. Xóa các sinh viên mà có điểm trung bình nhỏ hơn 2.0. CREATE PROCEDURE sp_e1_t1_y9

AS BEGIN

delete

from sinhvien where masv in

(Select masv from diem, hocphan where diem.mahp=hocphan.mahp group by masv having sum(diem*sotc)/sum(sotc)<2)

END

10. Tăng số đơn vị học trình của những học phần thuộc học kỳ 2 lên 1 đơn vị học trình, giảm đơn vị học trình của những học phần thuộc học kỳ 1 đi 1 đơn vị học trình.

Create PROCEDURE sp_e1_t1_y10 AS

BEGIN

update hocphan set sotc= CASE

WHEN hocky=2 then sotc+1 WHEN hocky=1 then sotc-1 ELSE sotc

END

END

Câu 4: Xây dựng hàm

1. Xây dựng một hàm xét loại học lực cho mỗi sinh viên. Biết rằng nếu điểm trung bình (DTBHK)  8.4 thì xếp loại giỏi, 7.0  DTBHK < 8.4 thì xếp loại khá, 5.0  DTBHK < 6.9 thì xếp loại trung bình, ngược lại xếp loại yếu. Sau đó, sử dụng hàm này xây dựng một hàm trả về một bảng gồm các thông tin: mã sinh viên, họ tên, ngày sinh, điểm trung bình, xếp loại học lực của từng kỳ.

- Xây dựng hàm xếp loại học lực

CREATE FUNCTION sf_xeploaiHL(@dtb float) RETURNS nvarchar(20)

AS

BEGin

declare @kq nvarchar(20)

If @dtb>=8.4 SELECT @kq='Giỏi' else If @dtb>=7 SELECT @kq='Khá'

else If @dtb>=5 SELECT @kq='Trung bình' else SELECT @kq='Yếu‟

RETURN @kq END

- Xây dựng đưa ra thông tin sinh viên sử dụng hàm xếp loại học lực CREATE FUNCTION If_E1_T4_Y1()

RETURNS TABLE AS

RETURN (

select sinhvien.masv, hoten, ns, hocky, sum(diem*sotc)/sum(sotc) as dtb, dbo.sf_XeploaiHL(sum(diem*sotc)/sum(sotc)) as kq

from sinhvien inner join diem on sinhvien.masv=diem.masv inner join hocphan on diem.mahp=hocphan.mahp

group by sinhvien.masv, hoten, ns, hocky

)

2. Xây dựng một hàm xét học bổng cho mỗi sinh viên của một học kỳ. Biết rằng nếu điểm trung bình học kỳ (DTBHKHK)  9.0 thì được 240.000/tháng, 8.0  DTBHKHK

< 9 thì được 180.000/tháng, 7.0  DTBHKHK < 8 thì được 150.000/tháng. Sau đó sử dụng hàm này xây dựng một hàm trả về một bảng gồm các thông tin: kỳ, mã sinh viên, họ tên, ngày sinh, điểm trung bình, mức học bổng.

- Xây dựng hàm xét mức học bổng

CREATE FUNCTION sf_hocbong(@dtb float) RETURNS int

AS

BEGin

declare @mhb int

If @dtb>=9 SELECT @mhb=240000 else If @dtb>=8 SELECT @mhb=180000 else If @dtb>=7 SELECT @mhb=150000 else SELECT @mhb=0

RETURN @mhb END

- Xây dựng đưa ra thông tin sinh viên sử dụng hàm xét mức học bổng CREATE FUNCTION If_E1_T4_Y2()

RETURNS TABLE AS

RETURN (

select sinhvien.masv, hoten, ns, hocky, sum(diem*sotc)/sum(sotc) as dtb, dbo.sf_hocbong(sum(diem*sotc)/sum(sotc)) as hb

from sinhvien inner join diem on sinhvien.masv=diem.masv inner join hocphan on diem.mahp=hocphan.mahp

group by sinhvien.masv, hoten, ns, hocky

)

3. Xây dựng một hàm trả về một bảng gồm các thông tin sau: mã lớp, số sinh viên, điểm trung bình học phần có mã học phần là „PTTK‟ hoặc „CSDL‟.

CREATE FUNCTION If_E1_T4_Y3() RETURNS TABLE

AS RETURN (

select malop, count(sinhvien.masv) as sosv, AVG(diem) as dtb, from sinhvien inner join diem on sinhvien.masv=diem.masv

where diem.mahp=‟PTTK‟ or diem.mahp= „CSDL‟ group by malop

)

4. Xây dựng một hàm trả về một bảng gồm các thông tin sau: mã lớp, mã sinh viên, họ và tên, ngày sinh, điểm trung bình của mỗi học kỳ, của mỗi sinh viên mà có điểm trung bình lớn nhất với học kỳ cho trước.

- Tạo hàm trung gian đưa ra các thông tin gồm: mã lớp, mã sinh viên, họ và tên, ngày sinh, điểm trung bình của mỗi học kỳ, của mỗi sinh viên

CREATE FUNCTION If_E1_T4_Y4_TG() RETURNS TABLE

AS RETURN (

select sinhvien.masv, hoten, ns, hocky, malop,

sum(diem*sotc)/sum(sotc) as dtb

from sinhvien inner join diem on sinhvien.masv=diem.masv inner join hocphan on diem.mahp=hocphan.mahp

group by sinhvien.masv, hoten, ns, hocky, malop

)

- Tạo hàm đưa ra các sinh viên có điểm lớn nhất với học kỳ cho trước CREATE FUNCTION tf_E1_T4_y4](@hk nvarchar(2)

RETURNS @dtbln TABLE

(

Masv nvarchar(10), hoten nvarchar(40), ns date,

malop nvarchar(40), dtb float

) AS

BEGIN

declare @dtb_max float set @dtb_max =

(select Max(dtb) from if_e1_t4_y4_TG() where ltrim(rtrim(hocky))=ltrim(rtrim(@hk)))

insert into @dtbln(masv, hoten, ns, malop, dtb) select masv, hoten, ns, malop, dtb

from if_e1_t4_y4_TG()

where dtb= @dtb_max and ltrim(rtrim(hocky))=ltrim(rtrim(@hk)) RETURN

END

Câu 5: Viết các Trigger

1. Tạo Trigger cho bảng sinh viên

- Tạo Trigger cho hành động thêm bản ghi mới CREATE TRIGGER Trigger_Insert_SV ON Sinhvien

for INSERT

declare @kt bit

declare @masv nvarchar(15) declare @hoten nvarchar(15) declare @ns date

declare @gt nvarchar(3) declare @dc nvarchar(50) declare @mal nvarchar(15) set @kt=1

set @masv = (select masv from inserted) set @hoten = (select hoten from inserted) set @gt = (select gt from inserted)

set @ns = (select ns from inserted)

set @dc = (select diachi from inserted) set @mal = (select malop from inserted)

if (select count(*)from sinhvien where masv=@masv)>1 begin

set @kt=0

raiserror('Số báo danh đã tồn tại trong bảng thí sinh',16,6) end

if len(@hoten)=0

begin

set @kt=0

raiserror('Họ tên không hợp lệ',16,6) end

if len(@mal)=0

begin

set @kt=0

raiserror('Mã lớp không hợp lệ',16,6) end

if len(@dc)=0

begin

set @kt=0

raiserror('Địa chỉ không hợp lệ',16,6) end

if @ns>Getdate()

set @kt=0

raiserror('Ngày sinh không hợp lệ',16,6) end

if @kt=0

begin rollback tran

raiserror('Không thêm được thông tin vào bảng sinh viên',16,6) end

else

END

begin

raiserror('Đã thêm được thông tin vào bảng sinh viên',16,6) end

- Tạo Trigger cho hành động xóa bản ghi CREATE TRIGGER Trigger_Delete_SV ON Sinhvien

AFter DELETE AS

BEGIN

declare @masv nvarchar(15)

set @masv = (select masv from deleted) if ltrim(rtrim(@masv)) not in

(select ltrim(rtrim(sinhvien.masv))from sinhvien inner join diem on sinhvien.masv=diem.masv inner join hocphan on diem.mahp=hocphan.mahp group by sinhvien.masv having sum(diem*sotc)/sum(sotc)<6)

begin rollback

raiserror('Không xóa được sinh viên này',16,6)

end END

- Tạo Trigger cho hành động cập nhật

CREATE TRIGGER Trigger_Update_SV ON Sinhvien

for UPDATE