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