Bài tập chương 3 cơ sở dữ liệu với các thủ tục và hàm trong quản lý sách | Môn Hệ quản trị cơ sở dữ liệu - Đại học Sư phạm Kỹ thuật Thành phố Hồ Chí Minh

lOMoAR cPSD| 58728417
BÀI TẬP CHƯƠNG 3
1. Stored-procedure tính tổng của 2 số nguyên. CREATE PROCEDURE sum_numbers @num1 INT, @num2 INT AS BEGIN SET NOCOUNT ON; DECLARE @result INT; SET @result = @num1 + @num2; SELECT @result AS 'Sum'; END
--Trong stored procedure này, hai tham số đầu vào ‘@num1’ và ‘@num2’ là kiểu `INT`, sau đó
tạo biến `@result` để lưu kết quả của phép tính tổng. Sau đó, tính tổng của hai số đầu vào và lưu
kết quả vào biến `@result`, và cuối cùng trả về kết quả của biến `@result`. --run: EXEC sum_numbers a, b;
2. Stored procedure liệt kê những thông tin của đầu sách, thông tin tựa sách và số lượng
sách hiện chưa được mượn của một đầu sách cụ thể (ISBN).
Với Tuasach (ma_tuasach, tuasach, tacgia, tomtat) lOMoAR cPSD| 58728417
Dausach (isbn, ma_tuasach, ngonngu, bia, trangthai)
Cuonsach (isbn, ma_cuonsach, tinhtrang) CREATE PROCEDURE BookInfo @ISBN varchar(20) AS BEGIN
SELECT Tuasach.ma_tuasach, Tuasach.tuasach, Tuasach.tacgia, Tuasach.tomtat,
Dausach.ngonngu, Dausach.bia, Dausach.trangthai,
COUNT(CASE WHEN Cuonsach.tinhtrang = 'chưa mượn' THEN 1 END) AS 'SoLuongChuaMuon' FROM Tuasach
INNER JOIN Dausach ON Tuasach.ma_tuasach = Dausach.ma_tuasach
LEFT JOIN Cuonsach ON Dausach.isbn = Cuonsach.isbn WHERE Dausach.isbn = @ISBN
GROUP BY Tuasach.ma_tuasach, Tuasach.tuasach, Tuasach.tacgia, Tuasach.tomtat,
Dausach.ngonngu, Dausach.bia, Dausach.trangthai END
--run: EXEC BookInfo '1234567890'
3. Viết hàm tính tuổi của người có năm sinh được nhập vào như một tham số của hàm. CREATE FUNCTION calculate_age ( lOMoAR cPSD| 58728417 @year_of_birth INT ) RETURNS INT AS BEGIN DECLARE @age INT;
SET @age = YEAR(GETDATE()) - @year_of_birth; RETURN @age; END
--use: SELECT calculate_age(2003) AS 'Age';
-- ‘2003’ là năm sinh của người cần tính tuổi. Hàm sẽ trả về giá trị tuổi của người đó. 4. Cho CSDL như sau:
DocGia (ma_DocGia, ho, tenlot, ten, ngaysinh)
Nguoilon (ma_DocGia, sonha, duong, quan, dienthoai, han_sd)
Treem (ma_DocGia, ma_DocGia_nguoilon)
Tuasach (ma_tuasach, tuasach, tacgia, tomtat)
Dausach (isbn, ma_tuasach, ngonngu, bia, trangthai)
Cuonsach (isbn, ma_cuonsach, tinhtrang)
DangKy (isbn, ma_DocGia, ngay_dk, ghichu)
Muon (isbn, ma_cuonsach, ma_DocGia, ngay_muon, ngay_hethan)
QuaTrinhMuon(isbn, ma_cuonsach, ngay_muon, ma_DocGia, ngay_hethan, ngay_tra,
tien_muon, tien_datra, tien_datcoc, ghichu) lOMoAR cPSD| 58728417
Việt các stored procedures thưc hiện các việc sau:
a. Xem thông tin độc giả Tên: sp_ThongtinDocGia
Nội dung: Liệt kê những thông tin của độc giả tương ứng với mã độc giả. Nếu độc giả là
người lớn thì hiển thị thông tin độc giả + thông tin trong bảng người lớn. Nếu độc giả là
trẻ em thì hiển thị những thông tin độc giả + thông tin của bảng trẻ em. Thực hiện: [1]
Kiểm tra độc giả này thuộc loại ngườilớn hay trẻ em. [2]
Nếu là người lớn thì: In những thôngđộc giả này, gồm
có: thông tin độc giả + thông tin người lớn. [3]
Nếulà trẻ em thì: In những thông tin liên quan đến
độc giả này, gồm có: thông tin độc giả + thông tin trẻ em.
CREATE PROCEDURE sp_ThongtinDocGia AS
IF EXISTS(SELECT * FROM DocGia, Nguoilon WHERE DocGia.ma_DocGia = Nguoilon.ma_DocGia ) BEGIN SELECT *
FROM DocGia, Nguoilon WHERE DocGia.ma_DocGia = Nguoilon.ma_DocGia END
IF EXISTS (SELECT * FROM DocGia, Treem WHERE DocGia.ma_DocGia = Treem.ma_DocGia ) BEGIN SELECT *
FROM DocGia, Treem WHERE DocGia.ma_DocGia = Treem.ma_DocGia END;
Để chạy procedure này, thực hiện: EXEC sp_ThongtinDocGia;
b. Thông tin đầu sách:
Tên: sp_ThongtinDausach
Nội dung: Liệt kê những thông tin của đầu sách, thông tin tựa sách và số lượng sách
hiện chưa được mượn của một đầu sách cụ thể (ISBN).
CREATE PROCEDURE sp_ThongtinDausach @isbn int lOMoAR cPSD| 58728417 AS BEGIN
SELECT tuasach, tacgia, ngonngu, bia, trangthai, count(*) AS SoLuongChuaMuon
FROM Dausach ds JOIN Tuasach ts ON ds.ma_tuasach = ts.ma_tuasach
JOIN Cuonsach cs ON ds.isbn = cs.isbn
WHERE ds.isbn = @isbn AND tinhtrang = 'yes'
GROUP BY tuasach, tacgia, ngonngu, bia, trangthai; END;
Để chạy procedure này, thực hiện:
EXEC sp_ThongtinDausach @isbn = 123;
c. Liệt kê những độc giả người lớn đang mượn sách:
Tên: sp_ThongtinNguoilonDangmuon
Nội dung: Liệt kê những thông tin của tất cả độc giả đang mượn sách của thư viện.
CREATE PROCEDURE sp_ThongtinNguoilonDangmuon AS BEGIN
-- Liệt kê những thông tin của tất cả độc giả người lớn đang mượn sách của thư viện
SELECT dg.*, ngl.sonha AS Sonha, ngl.duong AS Duong, ngl.quan AS Quan, ngl.dienthoai AS DienThoai, ngl.han_sd AS HanSuDungThe, m.isbn AS MaSachMuon,
m.ma_cuonsach AS MaCuonSachMuon, m.ngay_muon AS NgayMuon,
m.ngay_hethan AS NgayHetHanTra
FROM DocGia dg JOIN Nguoilon ngl ON dg.ma_DocGia = ngl.ma_DocGia
JOIN Muon m ON dg.ma_DocGIa = m.ma_DocGIa; END;
Để chạy procedure này, thực hiện:
EXEC sp_ThongtinNguoilonDangmuon;
d. Liệt kê những độc giả người lớn đang mượn sách quá hạn:
Tên: sp_ThongtinNguoilonQuahan lOMoAR cPSD| 58728417
Nội dung: Liệt kê những thông tin của tất cả độc giả đang mượn sách của thư
viện đang trong tình trạng mượn quá hạn 14 ngày.
CREATE PROCEDURE sp_ThongtinNguoilonQuahan AS BEGIN
-- Liệt kê những thông tin của tất cả độc giả người lớn đang mượn sách quá hạn 14 ngày
SELECT dg.*, ngl.sonha AS Sonha, ngl.duong AS Duong, ngl.quan AS Quan, ngl.dienthoai AS DienThoai, ngl.han_sd AS HanSuDungThe, m.isbn AS MaSachMuon,
m.ma_cuonsach AS MaCuonSachMuon, m.ngay_muon AS NgayMuon,
m.ngay_hethan AS NgayHetHanTraSach
FROM DocGia dg JOIN Nguoilon ngl ON dg.ma_DocGia = ngl.ma_DocGia
JOIN Muon m ON dg.ma_DocGia = m.ma_DocGia
WHERE DATEDIFF(day,m.ngay_muon,getdate()) > 14; END;
Để chạy stored procedure này, thực hiện:
EXEC sp_ThongtinNguoilonQuahan;
e. Liệt kê những độc giả người lớn đang mượn sách có trẻ em cũng đang mượn sách: Tên: sp_DocGiaCoTreEmMuon
Nội dung: Liệt kê những những độc giả đang trong tình trạng mượn sách và
những trẻ em độc giả này đang bảo lãnh cũng đang trong tình trạng mượn sách.
CREATE PROCEDURE sp_DocGiaCoTreEmMuon AS BEGIN
-- Liệt kê những những độc giả người lớn đang trong tình trạng mượn sách và những trẻ
em độc giả này đang bảo lãnh cũng đang trong tình trạng mượn sách
SELECT dg.*, ngl.sonha AS Sonha, ngl.duong AS Duong, ngl.quan AS Quan, ngl.dienthoai AS DienThoai, ngl.han_sd AS lOMoAR cPSD| 58728417 HanSuDungThe, m.isbn AS MaSachMuon,
m.ma_cuonsach AS MaCuonSachMuon, m.ngay_muon AS NgayMuon,
m.ngay_hethan AS NgayHetHanTra
FROM DocGia dg JOIN Nguoilon ngl ON dg.ma_DocGIa = ngl.ma_DocGIa
JOIN Muon m ON dg.ma_DocGIa = m.ma_DocGIa
WHERE EXISTS (SELECT * FROM Treem te JOIN Muon m ON te.ma_DocGia = m.ma_DocGIa
WHERE te.ma_DocGia_nguoilon = dg.ma_DocGia); END;
Để chạy procedure này, thực hiện EXEC sp_DocGiaCoTreEmMuon;
Bài tập 5: Tạo một số Trigger như sau trong CSDL Thư viện: 5.1. tg_delMuon:
Nội dung: Cập nhật tình trạng của cuốn sách là yes.
CREATE TRIGGER tg_delMuon ON Muon AFTER DELETE AS BEGIN
DECLARE @del_isbn char(10), @del_ma_sach char(10)
SELECT @del_isbn = isbn, @del_ma_sach = ma_sach FROM deleted
UPDATE Cuonsach SET status = yes WHERE isbn = @del_isbn AND ma_sach = @del_ma_sach END 5.2. tg_insMuon:
Nội dung: Cập nhật tình trạng của cuốn sách là no.
CREATE TRIGGER tg_insMuon ON Muon AFTER INSERT AS lOMoAR cPSD| 58728417 BEGIN
DECLARE @ins_isbn char(10), @ins_ma_cuonsach char(10)
SELECT @ins_isbn = isbn, @ins_ma_cuonsach = ma_cuonsach FROM inserted
UPDATE Cuonsach SET status = no WHERE isbn = @ins_isbn
AND ma_cuonsach = @ins_ma_cuonsach END 5.3. tg_updCuonSach:
Nội dung: Khi thuộc tính tình trạng trên bảng cuốn sách được cập nhật thì trạng thái của
đầu sách cũng được cập nhật theo. Cài đặt các thủ tục sau cho CSDL Quản lý thư viện.
CREATE TRIGGER tg_updCuonSach ON Cuonsach AFTER UPDATE AS BEGIN
DECLARE @upd_status bit, @upd_isbn char(10)
SELECT @upd_status = status, @upd_isbn = isbn FROM inserted
UPDATE Dausach SET trangthai = @upd_status AND isbn =@upd_isbn END 5.4 tg_InfThongBao
Nội dung: Viết trigger khi thêm mới, sửa tên tác giả, thêm/sửa một tựa sách thì in ra câu thông báo bằng Tiếng
Việt ‘Đã thêm mới tựa sách’.
CREATE TRIGGER tg_InfThongBao ON Tuasach AFTER INSERT, UPDATE AS
PRINT(N‘Đã thêm mới tựa sách’) lOMoAR cPSD| 58728417 Gợi ý :
Kiểm tra trigger đã tạo bằng khối lệnh để dữ liệu không bị thay đổi : begin tran
--khối lệnh thêm,xóa,sửa rollback Bài tập 6:
Viết các Function sau trong CSDL Đề án
6.1. Viết hàm trả về tổng tiền lương trung bình của một phòng ban tùy ý (truyền vào MaPB)
CREATE FUNCTION avg_salary_by_departmentID (department_id varchar(2)) RETURNS NUMERIC(18,0) BEGIN
DECLARE @total_salary NUMERIC(20,0); DECLARE @employee_count int;
DECLARE @avg_salary NUMERIC(20,0);
SELECT @total_salary = SUM(LUONG), @employee_account = COUNT(*); FROM NHANVIEN WHERE PHG IN( SELECT MAPHG FROM PhongBan lOMoAR cPSD| 58728417 WHERE MAPHG = department_id );
IF @employee_count > 0 THEN
SET @avg_salary = @total_salary / @employee_count; ELSE SET @avg_salary = 0; END RETURN @avg_salary; END;
6.2. Viết hàm trả về tổng lương nhận được của nhân viên theo dự án (truyền vào MaNV và MaDA)
CREATE FUNCTION sum_salary_by_projectID (employee_id varchar(9), project_id varchar(2)) RETURNS NUMERIC(18,0) BEGIN
DECLARE @total_salary NUMERIC(20,0);
SELECT @total_salary = SUM(LUONG) FROM NHANVIEN
JOIN PHANCONG ON NHANVIEN.MANV = PHANCONG.MA_NVIEN
JOIN DEAN ON DEAN.MADA = PHANCONG.SODA WHERE NHANVIEN.MANV = employee_id AND DEAN.MADA = project_id RETURN @total_salary; END;
6.3. Viết hàm trả về tổng tiền lương trung bình của các phòng ban
CREATE FUNCTION avg_salary_department() RETURNS NUMERIC(18,0) BEGIN
DECLARE @total_avg_salary NUMERIC(18,0);
SELECT @total_avg_salary = SUM(avg_salary) lOMoAR cPSD| 58728417 FROM (
SELECT PHG, AVG(LUONG) avg_salary FROM PHONGBAN JOIN NHANVIEN
ON PHONGBAN.MAPHG = NHANVIEN.PHG GROUP BY PHG ) deparment_avg_salary RETURN @total_avg_salary END;
6.4. Viết hàm trả về tổng tiền thưởng cho nhân viên dựa vào tổng số giờ tham gia dự án(Time_Total) như sau:
- Nếu Time_Total >=30 và <=60 thì tổng tiền thưởng = 500 ($)
- Nếu Time_Total >60 và <100 thì tổng tiền thưởng = 1000 ($)
- Nếu Time_Total >=100 và <150 thì tổng tiền thưởng =1200($)
- Nếu Time_Total >=150 thì tổng tiền thưởng = 1600 ($)
CREATE FUNCTION get_bonus_salary(time_total int) RETURNS NUMERIC(18,0) BEGIN
IF (time_total >= 30 AND time_total <=60) RETURN 500;
IF (time_total > 60 AND time_total <100) RETURN 1000;
IF (time_total >= 100 AND time_total <150) RETURN 1200; IF (time_total >= 150) RETURN 1600; RETURN 0; END;
6.5. Viết hàm trả ra tổng số dự án theo mỗi phòng ban.
CREATE FUNCTION total_project_deparment() RETURNS INT BEGIN DECLARE @total_project INT lOMoAR cPSD| 58728417
SELECT @total_project = COUNT(*) FROM ( SELECT PHG, PHANCONG.SODA PHONGBAN
JOIN NHAVIEN ON PHONGBAN.MAPHG = NHANVIEN.PHG
JOIN PHANCONG ON PHANCONG.MA_NVIEN =
NHANVIEN.MANV) department_by_project RETURN @total_project; END;
6.6. Viết hàm trả về kết quả là một bảng (Table), viết bằng hai cách: Inline Table-Valued
Functions và Multistatement Table-Valued. Thông tin gồm: MaNV, HoTen, NgaySinh, NguoiThan, TongLuongTB.
Inline Table-Valued Functions
CREATE FUNCTION get_summary_of_employee() RETURNS TABLE AS RETURN ( SELECT NHANVIEN.MANV, NHANVIEN.HONV, NHANVIEN.TENLOT, NHANVIEN.TEN, NHANVIEN.TENTN, AVG(LUONG) AS LuongTB FROM NHANVIEN
JOIN THANNHAN ON NHANVIEN.MANV = THANNHAN.MA_NVIEN GROUP BY NHANVIEN.MANV, NHANVIEN.HONV, NHANVIEN.TENLOT, NHANVIEN.TEN, NHANVIEN.TENTN ); lOMoAR cPSD| 58728417
Multistatement Table-Valued
CREATE FUNCTION get_summary_of_employee()
RETURNS @employee_table TABLE( MANV varchar(9), HONV varchar(15), TENLOT varchar(30), TEN varchar(30), NGUOITHAN varchar(20), LUONGTB NUMERIC(18,0) ) AS BEGIN
DECLARE @avg_salary NUMERIC(18,0)
INSERT INTO @employee_table (MANV, HONV, TENLOT, TEN, NGUOITHAN, LUONGTB) SELECT NHANVIEN.MANV, NHANVIEN.HONV, NHANVIEN.TENLOT, NHANVIEN.TEN, NHANVIEN.TENTN, @avg_salary = AVG(LUONG) FROM NHANVIEN
JOIN THANNHAN ON NHANVIEN.MANV = THANNHAN.MA_NVIEN GROUP BY NHANVIEN.MANV, NHANVIEN.HONV, NHANVIEN.TENLOT, NHANVIEN.TEN, NHANVIEN.TENTN
UPDATE @employee_table SET LuongTB = @avg_salary; RETURN; END;