SQL Server - 37

declare @masv_cu nvarchar(15) declare @masv_moi nvarchar(15) if update(masv)

begin

select @masv_cu =(select masv from deleted) select @masv_moi =(select masv from inserted) update diem

set masv=@masv_moi where masv=@masv_cu end

END

2. Tạo Trigger cho bảng học phần học Tương tự ý 1

3. Tạo Trigger cho bảng điểm

a. Khi thêm mới một bản ghi thì 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.

b. Chỉ cho phép xóa 2 bản ghi trở xuống.

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

• 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 - 36
• SQL Server - 38
• SQL Server - 39
• SQL Server - 40

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

c. Không cho phép cập nhật điểm thi lớn hơn 10 và nhỏ hơn 0.

- Tạo Trigger cho hành động cập nhật CREATE TRIGGER Trigger_Insert_DIEM ON DIEM

For INSERT AS

BEGIN

declare @kt bit declare @diem float set @kt=1

if not exists (select * from inserted, sinhvien where inserted.masv=sinhvien.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 * from inserted, hocphan where inserted.mahp=hocphan.mahp)

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

set @diem = (select diem from inserted) if @diem<0 or @diem>10

begin

set @kt=0

raiserror('Điểm 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 điểm',16,6) end

else

END

begin

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

- Tạo Trigger cho hành động xóa bản ghi

CREATE TRIGGER Trigger_Delete_DIEM ON Diem

AFter DELETE AS

BEGIN

if (select Count(*) from deleted)>=2

begin rollback

raiserror('Chỉ cho phép xóa 2 bản ghi trở xuống',16,6)

end END

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

CREATE TRIGGER Trigger_Update_DIEM ON DIEM

for UPDATE AS

BEGIN

declare @diem if update(diem) begin

select @diem =(select diem from inserted) if @diem<0 or @diem>10

begin rollback

raiserror(„Không cho phép cập nhật điểm',16,6) end

end END

Các câu còn lại sinh viên tự làm

Bài số 2: Cho CSDL quản lý tuyển sinh (QLTS) gồm các bảng sau:

TS(SBD, Hoten, NS, GT, QQ, DT, DL, DH, SP): Chứa thông tin về các thí sinh UT(MaUT, TenUT, DC): Chứa thông tin về các loại ưu tiên.

NH(MaN, TenN, SoCT): Chứa thông tin về ngành học.

Trong đó: SBD-Số báo danh, Hoten-Họ tên, NS-Ngày tháng năm sinh, GT-Giới tính, QQ-Quê quán, DT-Điểm toán, DL-Điểm lý, DH-Điểm hoá, SP-Số phòng dự thi, MaUT-Mã loại ưu tiên, TenUT-Tên loại ưu tiên, DC-Điểm cộng, MaN-Mã ngành, TenN-Tên ngành, SoCT-Số chỉ tiêu sẽ lấy vào ngành.

Câu 1: Tạo lập cơ sở dữ liệu:

1. Tạo cơ sở dữ liệu với tên là QLTS.

2. Tạo các bảng dữ liệu

3. Thiết kế ràng buộc khóa chính, khóa ngoại, ràng buộc duy nhất, ràng buộc kiểm tra, ràng buộc mặc định cho mỗi bảng dữ liệu.

4. Tạo Diagrams cho cơ sở dữ liệu

5. Tạo chỉ mục: liên cung, phi liên cung, không gian.

6. Nhập tối thiểu 10 bản ghi cho mỗi bảng (Nhập trực tiếp hoặc nhập từ file).

Câu 2: Tạo View thực hiện các công việc sau:

1. Đưa ra danh sách các thí sinh có giới tính là nữ quê “Thái Bình” đăng kí ngành “Kinh tế doanh nghiệp” hoặc “Công nghệ thông tin”.

2. Đưa ra danh sách các thí sinh là nam quê “Hòa Bình” hoặc “Thanh Hoa” đăng kí ngành “Dệt”.

3. Đưa ra danh sách các thí sinh đăng kí dự thi ngành “He thong thong tin” hoặc “Cong nghe han” ở quê quán „Thái Bình‟.

4. Đưa ra các thông tin gồm SBD, họ tên, ngày sinh, tên ưu tiên, điểm toán, điểm lý, điểm hóa, điểm cộng, tổng điểm của những thí sinh đỗ quê “Hải Phòng” có tên là “Minh” .

5. Đưa ra danh sách các thí sinh nữ đăng kí dự thi ngành “Tin hoc ung dung” thi tại phòng với số 1 hoặc phòng số 2.

6. Đưa ra các thông tin gồm SBD, họ tên, ngày sinh, tên ngành, điểm toán, điểm lý, điểm hóa, điểm cộng, tổng điểm của những thí sinh hưởng loại ưu tiên “con thương binh” đỗ ngành “Ke toan”.

7. Đưa ra các thông tin gồm mã ngành, tên ngành, chỉ tiêu, số thí sinh đăng kí vào ngành của những ngành có số thí sinh đăng kí dự thi nhỏ hơn 100.

8. Đưa ra danh sách các ngành không có thí sinh đăng kí dự thi.

9. Đưa ra các thông tin gồm quê quán, số thí sinh đỗ của những quê quán có số thí sinh đỗ lớn hơn 3

10. Đưa ra các thông tin gồm phòng, số thí sinh đỗ của những phòng có số thí sinh đỗ lớn hơn 2.

11. Đưa ra các thông tin gồm SBD, họ tên, tên ngành, tên ưu tiên, ngày sinh, điểm toán, điểm lý, điểm hóa, điểm cộng, tổng điểm của những thí sinh đạt điểm cao nhất.

12. Đưa ra thông tin gồm mã ưu tiên, tên ưu tiên, điểm cộng, số thí sinh thuộc t?ng loại ưu tiên của những loại ưu tiên có số thí sinh thuộc lớn hơn 2.

Biết rằng thí sinh đỗ là thí sinh có điểm toán + điểm lý + điểm hoá + điểm cộng 

15 và không có điểm nào nhỏ hơn 1.

Câu 3: Hãy viết các Stored Procedure để thực hiện các công việc sau:

1. Đưa ra danh sách gồm các thông tin gồm SDB, họ tên, ngày sinh, quê quán, giới tính, tên ngành của những thí sinh đăng kí dự thi ngành “Kế toán” hoặc “Công nghệ thông tin”.

2. Đưa ra danh sách gồm các thông tin gồm SDB, họ tên, ngày sinh, quê quán, giới tính, tên ngành của những thí sinh đăng kí dự thi ngành “Kế toán” hoặc “Công nghệ thông tin”, “Quản trị kinh doanh”, “Công nghệ kỹ thuật điện”.

3. Đưa ra danh sách các thí sinh có giới tính là nữ quê “Thái Bình” đăng kí ngành “Quản trị kinh doanh” hoặc “Công nghệ thông tin”.

4. Đưa ra danh sách các thí sinh là nam quê “Hòa Bình” hoặc “Thanh Hóa” đăng kí ngành “Dệt”.

5. Đưa ra danh sách các thí sinh đăng kí dự thi ngành “Hệ thống thông tin” hoặc “Công nghệ hàn” ở quê quán với quê quán cho trước.

6. Đưa ra danh sách gồm các thông tin gồm SDB, họ tên, ngày sinh, quê quán, giới tính, điểm toán, điểm lý, điểm hoá, điểm cộng, tổng điểm, kết quả của các thí sinh.

7. Đưa ra các thông tin gồm SBD, họ tên, ngày sinh, tên ưu tiên, điểm toán, điểm lý, điểm hóa, điểm cộng, tổng điểm của những thí sinh đỗ quê “Hải Phòng” có tên là “Minh”.

8. Đưa ra danh sách các thí sinh nữ đăng kí dự thi ngành “Hệ thống thông tin” thi tại phòng với số 1 hoặc phòng số 2.

9. Đưa ra các thông tin gồm SBD, họ tên, ngày sinh, tên ngành, điểm toán, điểm lý, điểm hóa, điểm cộng, tổng điểm của những thí sinh hưởng loại ưu tiên “con thương binh” đỗ ngành “Kế toán”.

10. Đưa ra các thông tin gồm mã ngành, tên ngành, chỉ tiêu, số thí sinh đăng kí vào ngành của những ngành có số thí sinh đăng kí dự thi nhỏ hơn 100.

11. Đưa ra danh sách các ngành không có thí sinh đăng kí dự thi.

12. Đưa ra các thông tin gồm quê quán, số thí sinh đỗ của những quê quán có số thí sinh đỗ lớn hơn 3.

13. Đưa ra các thông tin gồm phòng, số thí sinh đỗ của những phòng có số thí sinh đỗ lớn hơn 2.

14. Đưa ra các thông tin gồm SBD, họ tên, tên ngành, tên ưu tiên, ngày sinh, điểm toán, điểm lý, điểm hóa, điểm cộng, tổng điểm của những thí sinh đạt điểm cao nhất.

15. Đưa ra thông tin gồm mã ưu tiên, tên ưu tiên, điểm cộng, số thí sinh thuộc từng loại ưu tiên của những loại ưu tiên có số thí sinh lớn hơn 2.

16. Đưa ra danh sách các thí sinh đạt thủ khoa (là thí sinh có tổng điểm cao nhất).

17. Đưa ra danh sách gồm mã ngành, tên ngành, số thí sinh dự thi, số thí sinh đỗ , số thí sinh trượt của mỗi ngành. Danh sách đưa ra sắp xếp theo chiều giảm dần của số lượng thí sinh dự thi.

18. Thêm bản ghi cho bảng thí sinh (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)

19. Thêm bản ghi cho bảng ưu tiê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), màu mặc định là trắng.

20. Thêm bản ghi cho bảng ngành (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)

21. Cập nhật điểm cộng của mỗi loại ưu tiên; biết rằng nếu tên ưu tiên có 2 kí tự đầu là

„KV‟ thì tăng lên 2 điểm, nếu tên ưu tiên là con liệt sĩ thì tăng lên 1 điểm.

22. Cập nhật chỉ tiêu cho mỗi ngành học; nếu chỉ tiêu cũ nhỏ hơn 500 thì tăng lên 20%,

nếu nhỏ hơn 100o thì tăng lên 5%.

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

1. Xây dựng một hàm xét tuyển (đỗ, trượt) cho mỗi thí sinh.

2. Xây dựng một hàm trả về một bảng gồm các thông tin SBD, họ tên, ngày sinh, tên ngành, điểm toán, điểm lý, điểm hóa, điểm cộng, tổng điểm, kết quả (đỗ, trượt).

3. Xây dựng một hàm trả về một bảng gồm các thông tin sau: mã ngành, tên ngành, số thí sinh dự thi, điểm trung bình môn toán, điểm trung bình môn lý, điểm trung bình môn hóa, tổng điểm trung bình của mỗi ngành.

4. Xây dựng một hàm trả về một bảng gồm các thông tin sau: SBD, họ tên, ngày sinh, tên ngành, điểm toán, điểm lý, điểm hóa, điểm cộng, tổng điểm của những thí sinh tổng điểm lớn hơn tổng điểm nhỏ nhất và nhỏ hơn tổng điểm cao nhất.

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

1. Tạo Trigger cho bảng thí sinh theo yêu cầu sau:

 Thêm mới một thí sinh thì 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.

 Chỉ cho phép xóa 2 thí sinh.

 Không cho phép cập nhật số báo danh, kiểm tra tính hợp lệ của dữ liệu được cập nhật.

2. Tạo Trigger cho bảng ưu tiên theo yêu cầu sau:

 Thêm mới một loại ưu tiên thì 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, tên ưu tiên không được trùng nhau .

 Chỉ cho phép xóa 5 ưu tiên trở xuống.

 Không cho phép cập nhật điểm cộng lớn hơn 10 và nhỏ hơn 0.

3. Tạo Trigger cho bảng ngành học theo yêu cầu sau:

 Khi thêm mới một ngành học thì 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, tên ngành không được trùng nhau .

 Chỉ cho phép xóa 5 ngành học trở xuống.

 Không cho phép cập nhật chỉ tiêu tuyển sinh lớn hơn 3000 và nhỏ hơn 0.

Câu 6:Các thao tác trên cơ sở dữ liệu

1. Thiết lập cấu hình dịch vụ của SQL Server.

2. Tạo file sao lưu cho cơ sở dữ liệu trên với tên là „QLD_Backup‟ thuộc loại full backup, Transaction log và differential backup, khôi phục dữ liệu từ file sao lưu đã tạo.

3. Tách bỏ cơ sở dữ liệu khỏi SQL Server.

4. Xuất file cơ sở dữ liệu sang dạng Text, Access, Excel, SQL Server.

5. Tạo lịch trình sao lưu tự động cho cơ sở dữ liệu.

Câu 7:Quản lý bảo mật và người dùng

1. Tạo người dùng đăng nhập bằng tài khoản SQL Server, bằng tài khoản của Window.

2. Gắn các nhóm quyền cho người dùng.

3. Gắn các vai trò cho người dùng.

4. Cấp quyền sử dụng select, update, insert, delete trên bảng cho người dùng.

Bài số 3: Cho CSDL quản lý nhân sự (QLNS) gồm các bảng sau: Hoso(MaNV, Hoten, GT, NS, DC, Ngayvn): Chứa thông tin về nhân viên. Phong(MaP, TenP, VT): Chứa thông tin về phòng ban.

Luong(MaNV, MaP, HSL, HSPC, NgayLL): Chứa thông tin về lương.

Trong đó: MaNV-Mã nhân viên, Hoten-Họ tên, GT-Giới tính, NS-Ngày sinh, DC- Địa chỉ, Ngayvn-Ngày vào ngành, MaP-Mã phòng, TenP-Tên phòng, VT-Vị trí, HSL- Hệ số lương, HSPC-Hệ số phụ cấp, NgayLL-Ngày lên lương lần cuối.

Câu 1: Tạo lập cơ sở dữ liệu:

1. Tạo cơ sở dữ liệu với tên là QLNS.

2. Tạo các bảng dữ liệu

3. Thiết kế ràng buộc khóa chính, khóa ngoại, ràng buộc duy nhất, ràng buộc kiểm tra, ràng buộc mặc định cho mỗi bảng dữ liệu.

4. Tạo Diagrams cho cơ sở dữ liệu

5. Tạo chỉ mục: liên cung, phi liên cung, không gian.

6. Nhập tối thiểu 10 bản ghi cho mỗi bảng (Nhập trực tiếp hoặc nhập từ file).

Câu 2: Tạo View thực hiện các công việc sau:

1. Đưa ra các thông tin mã nhân viên, họ và tên nhân viên, địa chỉ của những nhân viên nam quê “Ha Noi” vào ngành năm 1970.

2. Đưa ra danh sách nhân viên được xét tăng lương trong năm nay.

3. Đưa ra các thông tin mã nhân viên, họ và tên nhân viên, địaa chỉ của những nhân viên nữ là việc tại phòng ‟Hanh chinh‟ hoặc „Dao tao‟.

4. Đưa ra các thông tin mã nhân viên, họ và tên nhân viên, tên phòng, số tiền được lĩnh của các nhân viên được lĩnh tiền nhiều nhất trong cơ quan. Danh sách đưa ra sắp xếp theo chiều giảm dần của tuổi.

5. Đưa ra thông tin về lương gồm: hệ số lương, số người, tổng số tiền được lĩnh hàng tháng của mỗi hệ số lương có số người nhỏ hơn 2.

6. Đưa ra thông tin về lương gồm: tên phòng, số người, tổng số tiền được lĩnh hàng tháng của mỗi phòng có tổng số tiền được lĩnh lớn hơn 15 triệu.

7. Đưa ra các thông tin mã nhân viên, họ và tên nhân viên, tên phòng, số tiền được lĩnh của các nhân viên có số tiền được lĩnh lớn hơn số tiền được lĩnh trung bình của một người trong cơ quan.

Biết rằng tiêu chuẩn về hưu là nam có tuổi 60 trở lên, nữ có số tuổi từ 55 trở lên; tiêu chuẩn được xét thưởng là người có 25 năm công tác trở lên; tiêu chuẩn được xét tăng lương là cứ 3 năm tăng lương 1 lần kể từ năm lên lương lần cuối; số tiền được lĩnh = (Hệ số lương + hệ số phụ cấp)* 830.

Câu 3: Hãy viết các Stored Procedure để thực hiện các công việc sau:

1. Đưa ra bảng lương của cơ quan gồm mã nhân viên, họ và tên nhân viên, tên phòng, số tiền được lĩnh của các nhân viên theo hệ số lương có hệ số lương cho trước. Danh sách đưa ra sắp xếp theo chiều giảm dần của số tiền được lĩnh.

2. Đưa ra bảng lương của cơ quan gồm mã nhân viên, họ và tên nhân viên, tên phòng, số tiền được lĩnh của các nhân viên theo phòng có tên phòng cho trước. Danh sách đưa ra sắp xếp theo chiều giảm dần của số tiền được lĩnh.

3. Thêm bản ghi cho bảng hoso (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).

4. Thêm bản ghi cho bảng luong (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), hệ số lương mặc định là 2,34.

5. Thêm bản ghi cho bảng phong (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)

6. Cập nhật hệ số lương và ngày lên lương lần cuối của các nhân viên đến hạn tăng lương; biết rằng cứ 3 năm thì tăng lương một lần kể từ lần tăng lương lần cuối và mỗi lần tăng lên 0,33.

7. Cập nhật hệ số phụ cấp của các nhân viên. Biết rằng những nhân viên thuộc phòng đào tạo thì tăng lên 0.2, các nhân viên thuộc phòng hành chính thì tăng lên 0.1.

8. Xóa các nhân viên đã quá tuổi nghỉ hưu.

9. Đưa ra thông tin về lương gồm: mã phòng, tên phòng, số người có hệ số lương lớn hơn 3.0, số người có hệ số lương nhỏ hơn 3.0 của mỗi phòng với tên phòng cho trước.

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

1. Xây dựng một hàm trả về một bảng gồm các thông tin sau: mã phòng, tên phòng, số nhân viên, tổng số tiền được lĩnh hàng tháng.

2. Xây dựng một hàm trả về một bảng gồm các thông tin: mã phòng, hệ số lương, số người hưởng hệ số lương của mỗi phòng, của mỗi hệ số lương mà có số người được hưởng lớn nhất hoặc nhỏ nhất.