· 6 years ago · Oct 27, 2019, 03:14 PM
1CREATE DATABASE QLSV
2GO
3USE QLSV
4GO
5
6CREATE TABLE Khoa(
7 MaKhoa VARCHAR(10) UNIQUE,
8 TenKhoa NVARCHAR(50),
9 NamThanhLap INT
10)
11CREATE TABLE LopHoc(
12 MaLop VARCHAR(10) UNIQUE,
13 MaKhoa VARCHAR(10),
14 SiSo INT,
15 CONSTRAINT FK_LH_K FOREIGN KEY (MaKhoa) REFERENCES Khoa(MaKhoa)
16)
17CREATE TABLE SinhVien(
18 MaSV VARCHAR(10) UNIQUE,
19 HoTen NVARCHAR(50),
20 DiemTB FLOAT,
21 MaLop varchar(10),
22 NamSinh INT,
23 NamBD INT,
24 NamKT INT,
25 TinhTrang NVARCHAR(30),
26 CONSTRAINT FK_SV_LH FOREIGN KEY (MaLop) REFERENCES LopHoc(MaLop)
27)
28CREATE TABLE MonHoc (
29 MaMH VARCHAR(10) UNIQUE,
30 TenMonHoc VARCHAR(50),
31 SoChi INT,
32 MaKhoa VARCHAR(10),
33 CONSTRAINT FK_MH_K FOREIGN KEY (MaKhoa) REFERENCES Khoa(MaKhoa)
34)
35
36CREATE TABLE KetQua(
37 MaSV VARCHAR(10),
38 MaMH VARCHAR(10),
39 LanThi INT,
40 Diem FLOAT
41 CONSTRAINT uc_KetQua UNIQUE(MaSV,MaMH,LanThi)
42 CONSTRAINT FK_KQ_SV FOREIGN KEY (MaSV) REFERENCES SinhVien(MaSV),
43 CONSTRAINT FK_KQ_MH FOREIGN KEY (MaMH) REFERENCES MonHoc(MaMH)
44)
45------------------------------------------------------------------------
46INSERT INTO Khoa
47VALUES ('CNTT',N'Công Nghệ Thông Tin','2010'),
48('DTVT',N'Điện Tử Viễn Thông','2012'),
49('HH',N'Hoá học','2013'),
50('CNSH',N'Công Nghệ Sinh Học','2011')
51
52INSERT INTO LopHoc
53VALUES ('LH001','CNTT',500),
54('LH002','HH',300),
55('LH003','DTVT',100),
56('LH004','CNSH',450)
57
58INSERT INTO MonHoc
59VALUES('MH001',N'Nhập môn CNTT 1',3,'CNTT'),
60('MH011',N'Thực tập hoá hữu cơ 2',2,'HH'),
61('MH005',N'Mạng máy tính',4,'DTVT'),
62('MH003',N'Vi sinh',3,'CNSH')
63
64INSERT INTO SinhVien
65VALUES('SV001',N'Nguyễn Khánh Anh',8.5,'LH001',1999,2017,2021,N'Đang học'),
66('SV002',N'Trần Lý Thuý Nga',9,'LH002',1999,2017,2021,N'Đang học'),
67('SV003',N'Huỳnh Khánh Vũ',4.5,'LH003',1999,2015,2021,N'Buộc thôi học'),
68('SV004',N'Trương Vĩ Khoa',5,'LH004',1999,2017,2021,N'Cảnh cáo học vụ')
69
70INSERT INTO KetQua
71VALUES ('SV001','MH001',1,8),
72('SV002','MH011',1,9),
73('SV003','MH005',4,5),
74('SV004','MH003',2,6)
75----------------------------------------------------------------
76-- TRIGGERS
77-- Ho ten: Nguyen Vu Linh
78-- MSSV: 1760096
79-- BT05 - 17CK1
80-----------------------------------------------------------------
81-- CAU 1:
82---------
83
84/*
854bang=> 4trigger
86*/
87DROP TRIGGER trg_1
88CREATE TRIGGER trg_1 ON KetQua
89FOR Update, insert
90AS
91BEGIN
92 IF NOT EXISTS (SELECT *
93 FROM inserted I, LopHoc LH,MonHoc MH,SinhVien SV
94 WHERE I.MaMH = MH.MaMH AND I.MaSV = SV.MaSV AND SV.MaLop = LH.MaLop AND LH.MaKhoa = MH.MaKhoa)
95 BEGIN
96 raiserror (N'Lỗi !!! Sinh viên chỉ được học môn của khoa mình', 16, 1)
97 rollback
98 end
99END
100GO
101
102Update KetQua
103Set MaMH ='MH003'
104Where MaSV = 'SV002'
105INSERT SinhVien VALUES('SV005',N'Nguyễn Anh',8,'LH002',1999,2017,2021,N'Đang học')
106UPDATE SinhVien
107SET MaLop = 'LH001'
108WHERE MaSV = 'SV005'
109
110INSERT KetQua
111VALUES('SV005','MH011',3,6)
112GO
113SELECT * FROM KetQua
114--------------------------------------------------------------------------------------
115-- Cau 2:
116
117
118
119DROP TRIGGER trg_2
120
121CREATE TRIGGER trg_2 ON KetQua
122FOR Update,insert
123AS
124BEGIN
125 IF EXISTS (SELECT *
126 FROM inserted I
127 WHERE I.LanThi < 5 AND I.LanThi < 3)
128 /*Diem lan sau cung <5 ????*/
129 BEGIN
130 raiserror (N'Lỗi !!! Bạn không được phép thi lại', 16, 1)
131 rollback
132 END
133END
134GO
135
136SELECT * FROM KetQua
137UPDATE KetQua
138SET LanThi = 4
139WHERE MaSV ='SV002'
140
141UPDATE KetQua
142SET Diem = 10
143WHERE MaSV ='SV002'
144INSERT SinhVien VALUES('SV006',N'Nguyễn Anh Vũ',8,'LH002',1999,2017,2021,N'Đang học')
145INSERT KetQua VALUES('SV006','MH011', 6, 4)
146-------------------------------------------------------------------------------------
147-- Cau 3:
148
149DROP TRIGGER trg_3
150
151CREATE TRIGGER trg_3 ON SinhVien
152FOR Update,insert,delete
153AS
154BEGIN
155/*Sai from */
156 IF EXISTS (SELECT *
157 FROM inserted I, LopHoc LH
158 WHERE COUNT(I.MaLop) != LH.SiSo)
159 BEGIN
160 raiserror (N'Lỗi !!! Số SV phải bằng sỉ số lớp', 16, 1)
161 rollback
162 END
163END
164GO
165
166-----------------------------------------------------------------
167-- Cau 4:
168DROP TRIGGER trg_4
169
170CREATE TRIGGER trg_4 on SinhVien
171INSTEAD OF DELETE
172AS
173BEGIN
174 delete from KetQua
175 where EXISTS (SELECT * FROM KetQua KQ join deleted D on KQ.MaSV = D.MaSV)
176END
177GO
178
179
180SELECT * FROM SinhVien
181SELECT * FROM LopHoc
182SELECT * FROM KetQua
183DELETE FROM SinhVien WHERE MaSV = 'SV007'
184
185------------------------------------------------------------
186-- Cau 5:
187DROP TRIGGER trg_5
188
189/*3 trigger */
190
191CREATE TRIGGER trg_5 on SinhVien
192FOR UPDATE,INSERT,DELETE
193AS
194BEGIN
195 DECLARE @TongDiem float, @TongChi FLOAT
196 SET @TongDiem = (SELECT SUM(Diem) from KetQua)
197 SET @TongChi = (SELECT SUM(SoChi) from MonHoc)
198 IF EXISTS (SELECT *
199 FROM inserted i
200 WHERE i.DiemTB != @TongDiem/@TongChi)
201 BEGIN
202 raiserror (N'Lỗi !!! DTB phải bằng Tổng điểm chia tổng chỉ', 16, 1)
203 rollback
204 END
205END
206GO
207
208INSERT SinhVien VALUES('SV006',N'Nguyễn Anh Vũ',8,'LH002',1999,2017,2021,N'Đang học')
209
210-------------------------------------------------------------------------------------
211-- Cau 6:
212
213DROP TRIGGER trg_6
214
215CREATE TRIGGER trg_6 on SinhVien
216FOR UPDATE,INSERT
217AS
218BEGIN
219 IF EXISTS (SELECT *
220 FROM inserted i
221 WHERE DATEDIFF(YY,i.NamSinh,i.NamBD) < 18 OR DATEDIFF(YY,i.NamSinh,i.NamBD) > 22)
222 BEGIN
223 raiserror (N'Lỗi !!! Sinh viên chỉ được nhập học từ 18 đến 22 tuổi', 16, 1)
224 rollback
225 END
226END
227GO
228SELECT * FROM SinhVien
229INSERT KetQua VALUES('SV007','MH011', 6, 4)
230INSERT SinhVien VALUES('SV008',N'Nguyễn Khanh',NULL,'LH002',1999,2020,2024,N'Đang học')
231
232--------------------------------------------------------------------------------------------
233-- Cau 7:
234
235/*3 trigger */
236DROP TRIGGER trg_7
237
238CREATE TRIGGER trg_7 on SinhVien
239FOR UPDATE,INSERT
240AS
241BEGIN
242 IF EXISTS (SELECT *
243 FROM inserted i
244 JOIN LopHoc LH ON i.MaLop = LH.MaLop JOIN Khoa K ON LH.MaKhoa = K.MaKhoa
245 WHERE i.NamBD > i.NamKT OR i.NamBD < K.NamThanhLap)
246 BEGIN
247 raiserror (N'Lỗi !!! Năm BD phải nhỏ hơn Năm KT và phải lớn hơn năm thành lặp khoa', 16, 1)
248 rollback
249 END
250END
251GO
252
253INSERT SinhVien VALUES('SV009',N'Nguyễn Ngọc Vũ Linh',NULL,'LH002',1999,2000,2024,N'Đang học')
254-----------------------------------------------------------------------------------------------
255-- Cau 8:
256DROP TRIGGER trg_8
257
258create trigger trg_8
259on SinhVien
260for insert, update
261as if update(TinhTrang)
262begin
263
264 DECLARE @flag INT
265 set @flag = 0
266 /* Sua lai thanh inserted*/
267 IF EXISTS (SELECT * FROM SinhVien WHERE (DiemTB < 5 or NamKT >= YEAR(GETDATE())) AND TinhTrang=N'Đã tốt nghiệp')
268 SET @flag = 1
269 IF EXISTS (SELECT * FROM SinhVien WHERE NamKT < YEAR(GETDATE()) and TinhTrang=N'Đang học')
270 SET @flag = 2
271 IF EXISTS (SELECT * FROM SinhVien WHERE (DiemTB >= 5 or NamKT <= YEAR(GETDATE())) and TinhTrang=N'Bị thôi học')
272 SET @flag = 3
273 IF (@flag >0)
274 BEGIN
275 raiserror (N'Lỗi !!!', 16, 1)
276 rollback
277 END
278
279end
280GO
281
282UPDATE SinhVien
283SET TinhTrang = N'Đã tốt nghiệp'
284WHERE MaSV = 'SV010'
285UPDATE SinhVien
286SET TinhTrang = N'Bị thôi học'
287WHERE MaSV = 'SV010'
288SELECT *from SinhVien