· 8 years ago · Jan 09, 2018, 02:38 AM
1
2 --0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000--
3 --000000000000000000000000000-- PHẦN 1 : THIẾT KẾ DATABASE --00000000000000000000000000000000--
4 --0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000--
5
6 ----------------------TẠO DB----------------------------------
7
8 USE[master]
9 GO
10
11 CREATE DATABASE STUDENT_INFOR
12 GO
13
14 USE STUDENT_INFOR
15 GO
16
17
18 ---------------- TẠO BẢNG VÀ TẠO PK, FK-----------------------
19
20 CREATE TABLE KHOA
21 (
22 STT INT IDENTITY(1,1),
23 MAKHOA NVARCHAR(111) PRIMARY KEY NOT NULL, -- PK
24 TENKHOA NVARCHAR(111)
25 )
26 GO
27
28 CREATE TABLE MON
29 (
30 STT INT IDENTITY(1,1),
31 MAMON NVARCHAR(111) PRIMARY KEY NOT NULL, -- PK
32 TENMON NVARCHAR(111),
33 MAKHOA NVARCHAR(111) NOT NULL,
34 FOREIGN KEY (MAKHOA) REFERENCES KHOA(MAKHOA) -- FK
35 )
36 GO
37
38 CREATE TABLE LOP
39 (
40 STT INT IDENTITY(1,1),
41 MALOP NVARCHAR(111),
42 MAKHOA NVARCHAR(111) NOT NULL,
43 CONSTRAINT PK_MALOP PRIMARY KEY (MALOP), -- PK
44 FOREIGN KEY (MAKHOA) REFERENCES KHOA(MAKHOA), -- FK
45 )
46 GO
47
48 CREATE TABLE GVCN
49 (
50 MAGVCN NVARCHAR(103),
51 TENGV NVARCHAR(122),
52 GIOITINH NVARCHAR(222),
53 NGAYSINH DATE,
54 MALOP NVARCHAR(111),
55 CONSTRAINT PK_MAGVCN_MALOP PRIMARY KEY(MAGVCN,MALOP) -- PK
56 )
57 GO
58 ALTER TABLE GVCN
59 ADD CONSTRAINT FK_MALOP
60 FOREIGN KEY (MALOP) REFERENCES LOP(MALOP)
61
62 CREATE TABLE SINHVIEN
63 (
64 STT INT IDENTITY(1,1),
65 MASV NVARCHAR(111) PRIMARY KEY NOT NULL, -- PK
66 TENSV NVARCHAR(111),
67 GIOITINH NVARCHAR(111),
68 NGAYSINH DATE,
69 MALOP NVARCHAR(111) NOT NULL,
70 FOREIGN KEY (MALOP) REFERENCES LOP(MALOP) -- FK
71 )
72 GO
73
74 CREATE TABLE DIEMSV
75 (
76 MASV NVARCHAR(111) NOT NULL,
77 MAMON NVARCHAR(111) NOT NULL,
78 DIEMSV FLOAT NOT NULL,
79 Constraint PK_DIEMSV_MASV_MAMON primary key(MASV,MAMON) -- PK
80 )
81 GO
82 -- THÊM FK CHO BẢNG DIEMSV
83 Alter table DIEMSV
84 add
85 constraint FK_DIEMSV_MASV foreign key(MASV)references SINHVIEN (MaSV), -- FK
86 constraint FK_MONHOC_MAMON foreign key(MAMON) references MON(MAMON)
87
88
89 --0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000--
90 --000000000000000000000000000000-- PHẦN 1 : INSERT DỮ LIỆU --00000000000000000000000000000000--
91 --0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000--
92
93 -----------------TABLE KHOA-----------------------
94
95 INSERT INTO KHOA
96 VALUES ('CNTT',N'Công nghệ thông tin'),
97 ('HH',N'Hóa há»c'),
98 ('SH',N'Sinh há»c'),
99 ('VL',N'Váºt lý')
100
101 SELECT * FROM KHOA
102
103 ------------------TABLE MON-----------------------
104 INSERT INTO MON
105 VALUES ('JS','JAVA_SRIPT','CNTT'),('j','JAVA','CNTT'),('DB','DATABASE','CNTT'),
106 ('HHC',N'Hóa hữu cÆ¡','HH'),('HL',N'Hóa lý','HH'),('HPT',N'Hóa phân tÃch','HH'),
107 ('DV',N'Äá»™ng váºt','SH'),('TV',N'Thá»±c váºt','SH'),('VSV',N'Vi sinh váºt','SH'),
108 ('MD',N'Mạch Ä‘iện','VL'),('TT',N'Từ trÆ°á»ng','VL'),('TS',N'Tia sáng','VL')
109
110 select * from MON
111
112 ------------------TABLE LOP---------------------
113
114 INSERT INTO LOP
115 VALUES ('LOP_01','VL'),
116 ('LOP_02','SH'),
117 ('LOP_03','HH'),
118 ('LOP_04','CNTT')
119 GO
120
121 SELECT * FROM LOP
122
123 ----------------TABLE GVCN --------------
124
125 INSERT INTO GVCN
126 VALUES
127 -- insert thông tin các sv của lớp 01
128 ('MAGV_01',N'TC.THUẬN','NAM','1989-12-02','LOP_01'),
129 ('MAGV_02',N'TC.HUY','NAM','1989-06-02','LOP_02'),
130 ('MAGV_03',N'TC.SÆ N','NAM','1982-04-02','LOP_03'),
131 ('MAGV_04',N'TC.TÂM',N'NỮ','1980-04-02','LOP_04')
132
133 SELECT * FROM GVCN
134
135 ---------------TABLE SINHVIEN-----------------
136
137 INSERT INTO SINHVIEN
138 VALUES
139 -- insert thông tin các sv của lớp 01
140 ('MASV_01_01',N'KHANH','NAM','1999-12-02','LOP_01'),
141 ('MASV_01_02',N'TUẤN','NAM','1999-06-02','LOP_01'),
142 ('MASV_01_03',N'HÙNG','NAM','1992-04-02','LOP_01'),
143 ('MASV_01_04',N'PHÆ¯Æ NG',N'Ná»®','1992-04-02','LOP_01'),
144 ('MASV_01_05',N'HÀ',N'NỮ','1997-04-02','LOP_01'),
145 ('MASV_01_06',N'HƯNG',N'NAM','1999-06-16','LOP_01'),
146 -- insert thông tin các sv của lớp 02
147 ('MASV_02_01',N'NAM','NAM','1996-08-02','LOP_02'),
148 ('MASV_02_02',N'ÄIỆP',N'Ná»®','1993-07-02','LOP_02'),
149 ('MASV_02_03',N'LONG','NAM','1994-01-02','LOP_02'),
150 ('MASV_02_04',N'HẢI','NAM','1998-07-02','LOP_02'),
151 ('MASV_02_05',N'HOÀI',N'NỮ','1995-09-02','LOP_02'),
152 ('MASV_02_06',N'THÃI',N'NAM','1997-11-13','LOP_02'),
153 -- insert thông tin các sv của lớp 03
154 ('MASV_03_01',N'HIỆP','NAM','1990-08-02','LOP_03'),
155 ('MASV_03_02',N'PHƯỢNG',N'NỮ','1999-11-02','LOP_03'),
156 ('MASV_03_03',N'LAN',N'Ná»®','1995-03-06','LOP_03'),
157 ('MASV_03_04',N'ÄỨC','NAM','1995-03-03','LOP_03'),
158 ('MASV_03_05',N'TIÊN',N'NAM','1993-05-02','LOP_03'),
159 ('MASV_03_06',N'THỦY',N'NỮ','1999-12-08','LOP_03'),
160 -- insert thông tin các sv của lớp 04
161 ('MASV_04_01',N'HOÀNG','NAM','1992-05-02','LOP_04'),
162 ('MASV_04_02',N'Lá»°C','NAM','1999-03-02','LOP_04'),
163 ('MASV_04_03',N'TUYẾT',N'NỮ','1997-08-02','LOP_04'),
164 ('MASV_04_04',N'VÆ¯Æ NG','NAM','1996-03-02','LOP_04'),
165 ('MASV_04_05',N'LINH',N'Ná»®','1996-07-02','LOP_04'),
166 ('MASV_04_06',N'HUYỀN',N'NỮ','1998-09-10','LOP_04')
167 GO
168
169 SELECT * FROM SINHVIEN
170
171 -----------------TABLE DIEMSV-------------------
172
173 INSERT INTO DIEMSV
174
175 -- -- insert điểm các sv của lớp 01
176 VALUES ('MASV_01_01','MD',10),('MASV_01_01','TS',10),('MASV_01_01','TT',10),
177
178 ('MASV_01_02','MD',1),('MASV_01_02','TS',1),('MASV_01_02','TT',1),
179
180 ('MASV_01_03','MD',5),('MASV_01_03','TS',4),('MASV_01_03','TT',8),
181
182 ('MASV_01_04','MD',8),('MASV_01_04','TS',7),('MASV_01_04','TT',2),
183
184 ('MASV_01_05','MD',6),('MASV_01_05','TS',6),('MASV_01_05','TT',9),
185
186 ('MASV_01_06','MD',7),('MASV_01_06','TS',4),('MASV_01_06','TT',8),
187
188 -- -- insert điểm các sv của lớp 02
189
190 ('MASV_02_01','DV',9),('MASV_02_01','TV',7),('MASV_02_01','VSV',2),
191
192 ('MASV_02_02','DV',8),('MASV_02_02','TV',6),('MASV_02_02','VSV',8),
193
194 ('MASV_02_03','DV',1),('MASV_02_03','TV',1),('MASV_02_03','VSV',1),
195
196 ('MASV_02_04','DV',9),('MASV_02_04','TV',7),('MASV_02_04','VSV',3),
197
198 ('MASV_02_05','DV',8),('MASV_02_05','TV',6),('MASV_02_05','VSV',2),
199
200 ('MASV_02_06','DV',10),('MASV_02_06','TV',7),('MASV_02_06','VSV',6),
201
202 -- -- insert điểm các sv của lớp 03
203
204 ('MASV_03_01','HHC',9),('MASV_03_01','HL',5),('MASV_03_01','HPT',2),
205
206 ('MASV_03_02','HHC',8),('MASV_03_02','HL',5),('MASV_03_02','HPT',8),
207
208 ('MASV_03_03','HHC',1),('MASV_03_03','HL',5),('MASV_03_03','HPT',10),
209
210 ('MASV_03_04','HHC',9),('MASV_03_04','HL',3),('MASV_03_04','HPT',10),
211
212 ('MASV_03_05','HHC',3),('MASV_03_05','HL',7),('MASV_03_05','HPT',2),
213
214 ('MASV_03_06','HHC',10),('MASV_03_06','HL',7),('MASV_03_06','HPT',4),
215
216 -- -- insert điểm các sv của lớp 04
217
218 ('MASV_04_01','DB',3),('MASV_04_01','J',7),('MASV_04_01','JS',5),
219
220 ('MASV_04_02','DB',7),('MASV_04_02','J',2),('MASV_04_02','JS',5),
221
222 ('MASV_04_03','DB',10),('MASV_04_03','J',7),('MASV_04_03','JS',1),
223
224 ('MASV_04_04','DB',3),('MASV_04_04','J',6),('MASV_04_04','JS',3),
225
226 ('MASV_04_05','DB',8),('MASV_04_05','J',7),('MASV_04_05','JS',10),
227
228 ('MASV_04_06','DB',9),('MASV_04_06','J',5),('MASV_04_06','JS',8)
229
230 SELECT * FROM DIEMSV
231
232 ---------------------------------------------------
233
234 --0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000--
235 --000000000000000000000000000----PHẦN 2 : TRUY VẤN SELECT----00000000000000000000000000000000--
236 --0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000--
237
238 --===============TRUY VẤN 1: In ra khoa và các môn há»c tÆ°Æ¡ng ứng
239
240 SELECT LOP.STT,TENKHOA, KHOA.MAKHOA, MON.TENMON, MON.MAMON
241 FROM MON, KHOA, LOP
242 WHERE KHOA.MAKHOA = MON.MAKHOA
243 AND LOP.MAKHOA = KHOA.MAKHOA
244 GROUP BY LOP.STT,KHOA.TENKHOA, KHOA.MAKHOA, MON.TENMON, MON.MAMON
245
246 --===============TRUY VẤN 2: In ra tên thông tin lớp và giáo viên chủ nhiệm tương ứng.
247
248 SELECT
249 LOP.MALOP AS N'LỚP',
250 KHOA.TENKHOA AS N'TÊN KHOA',
251 GVCN.TENGV AS N'GIÃO VIÊN CHỦ NHIỆM'
252
253 FROM LOP, GVCN, KHOA
254 WHERE KHOA.MAKHOA = LOP.MAKHOA
255 AND GVCN.MALOP = LOP.MALOP
256
257 --===============TRUY VẤN 3: In ra top 5 GIỎI NHẤT năm há»c ( Ä‘iểm tổng kết cuối năm lá»›n nhất ).
258
259 SELECT TOP 5
260 SINHVIEN.TENSV as N'Tên',
261 SINHVIEN.MASV as N'Mã Sinh Viên',
262 SINHVIEN.GIOITINH as N'Giá»›i TÃnh',
263 SINHVIEN.NGAYSINH as N'Ngà y Sinh',
264 year(GETDATE()) - YEAR(SINHVIEN.NGAYSINH) AS N'Tuổi',
265 LOP.MALOP as N'Há»c Sinh Lá»›p',
266 (SUM(DIEMSV.DIEMSV)/3) AS N'Äiểm Tổng Kết Cuối Năm'
267 FROM DIEMSV, SINHVIEN, MON, LOP, KHOA
268 WHERE KHOA.MAKHOA = LOP.MAKHOA
269 AND KHOA.MAKHOA = MON.MAKHOA
270 AND MON.MAKHOA = LOP.MAKHOA
271 AND LOP.MALOP = SINHVIEN.MALOP
272 AND MON.MAMON = DIEMSV.MAMON
273 AND DIEMSV.MAMON = MON.MAMON
274 AND DIEMSV.MASV = SINHVIEN.MASV
275 GROUP BY
276 SINHVIEN.TENSV, SINHVIEN.MASV,
277 SINHVIEN.GIOITINH, SINHVIEN.NGAYSINH,
278 LOP.MALOP
279 ORDER BY [Äiểm Tổng Kết Cuối Năm] DESC
280
281 --===============TRUY VẤN 3: In ra top 5 NGU NHẤT năm há»c ( Ä‘iểm tổng kết cuối năm lá»›n nhất ).
282
283 SELECT TOP 5
284 SINHVIEN.TENSV as N'Tên',
285 SINHVIEN.MASV as N'Mã Sinh Viên',
286 SINHVIEN.GIOITINH as N'Giá»›i TÃnh',
287 SINHVIEN.NGAYSINH as N'Ngà y Sinh',
288 YEAR(GETDATE()) - YEAR(SINHVIEN.NGAYSINH) AS N'Tuổi',
289 LOP.MALOP as N'Há»c Sinh Lá»›p',
290 (SUM(DIEMSV.DIEMSV)/3) AS N'Äiểm Tổng Kết Cuối Năm'
291 FROM DIEMSV, SINHVIEN, MON, LOP, KHOA
292 WHERE KHOA.MAKHOA = LOP.MAKHOA
293 AND KHOA.MAKHOA = MON.MAKHOA
294 AND MON.MAKHOA = LOP.MAKHOA
295 AND LOP.MALOP = SINHVIEN.MALOP
296 AND MON.MAMON = DIEMSV.MAMON
297 AND DIEMSV.MAMON = MON.MAMON
298 AND DIEMSV.MASV = SINHVIEN.MASV
299 GROUP BY
300 SINHVIEN.TENSV, SINHVIEN.MASV,
301 SINHVIEN.GIOITINH, SINHVIEN.NGAYSINH,
302 LOP.MALOP
303 ORDER BY [Äiểm Tổng Kết Cuối Năm] ASC
304
305 --===============TRUY VẤN 4: In ra bảng xếp hạng lá»›p có tổng số Ä‘iểm tổng kết của SINH VIÊN CAO nhất năm há»c.
306
307 SELECT
308 LOP.MALOP AS N'Tên Lớp',
309 KHOA.MAKHOA AS N'Mã Khoa',
310 KHOA.TENKHOA AS N'Tên Khoa',
311 (SUM(DIEMSV.DIEMSV)/3) AS N'Tổng số điểm trung bình cuối năm'
312 FROM LOP, KHOA, MON, DIEMSV, SINHVIEN
313 WHERE KHOA.MAKHOA = LOP.MAKHOA
314 AND KHOA.MAKHOA = MON.MAKHOA
315 AND MON.MAKHOA = LOP.MAKHOA
316 AND LOP.MALOP = SINHVIEN.MALOP
317 AND MON.MAMON = DIEMSV.MAMON
318 AND DIEMSV.MAMON = MON.MAMON
319 AND DIEMSV.MASV = SINHVIEN.MASV
320 GROUP BY LOP.MALOP, KHOA.MAKHOA,KHOA.TENKHOA
321 ORDER BY (SUM(DIEMSV.DIEMSV)/3) DESC
322
323 --============== TRUY VẤN 5 : WHERE - IN ================
324
325 select * from sinhvien
326 where MALOP in ('LOP_01','LOP_02')
327
328
329 --0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000--
330 --000000000000000000000000000---- PHẦN 3 : TẠO VIEW ----000000000000000000000000000--
331 --0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000--
332
333
334
335
336 --0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000--
337 --000000000000000000000000000----PHẦN 4 : TẠO STORED PROCEDURE----000000000000000000000000000--
338 --0000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000--
339
340 -- tạo procedure lấy thông tin của sinh viên Ä‘c truyá»n tham số
341
342 CREATE PROCEDURE LAYTTSV22
343 @MA_SV NVARCHAR(111)
344 AS
345 BEGIN
346 SELECT * FROM DIEMSV
347 WHERE @MA_SV = DIEMSV.MASV
348 END
349
350 EXEC LAYTTSV22'MASV_01_02'
351
352 -- tạo procedure in ra tên giống ký tá»± nháºp và o
353
354 ALTER PROC TIM_KIEM_SINH_VIEN
355 @TEN NVARCHAR(100)
356 AS
357 BEGIN
358 SELECT * FROM SINHVIEN AS SV
359 WHERE TENSV LIKE '%'+@TEN+'%'
360 END
361EXEC TIM_KIEM_SINH_VIEN 'K'
362
363--========== tạo proc auto update Ä‘iểm sv khi truyá»n và o tham số mã môn và điểm ================
364
365CREATE PROC SUA_DIEM_SV
366@MASV NVARCHAR(100),
367@MON NVARCHAR(20),
368@DIEM_SUA INT
369AS
370BEGIN
371 UPDATE DIEMSV SET DIEMSV = @DIEM_SUA
372 WHERE MASV = @MASV AND MAMON = @MON
373
374 SELECT TENSV, MAMON, DIEMSV
375 FROM SINHVIEN AS SV, DIEMSV AS DIEM
376 WHERE SV.MASV = DIEM.MASV AND SV.MASV = @MASV AND DIEM.MAMON = @MON
377END
378
379EXEC SUA_DIEM_SV 'MASV_01_02','tt', 6
380
381
382--=========== Tạo proc tá»± Ä‘á»™ng add há»c lá»±c dá»±a và o Ä‘iểm tb ===========
383
384CREATE PROC add_hoc_luc_sv2155
385@masv NVARCHAR(100)
386AS
387 BEGIN
388 declare @dtb smallmoney
389 select @dtb = (SUM(DIEMSV.DIEMSV)/3) from DIEMSV
390 where MASV = @masv;
391
392 IF(@dtb >= 5 AND @dtb < 7)
393 UPDATE SINHVIEN SET HOC_LUC = N'Trung bình'
394 where SINHVIEN.MASV = @masv
395 IF(@dtb >= 7 AND @dtb < 9)
396 UPDATE SINHVIEN SET HOC_LUC = N'Khá'
397 where SINHVIEN.MASV = @masv
398 IF(@dtb >= 9)
399 UPDATE SINHVIEN SET HOC_LUC = N'Giá»i'
400 IF(@dtb >= 3 AND @dtb < 5)
401 UPDATE SINHVIEN SET HOC_LUC = N'Yếu'
402 where SINHVIEN.MASV = @masv
403 if(@dtb < 3)
404 UPDATE SINHVIEN SET HOC_LUC = N'Kém'
405 where SINHVIEN.MASV = @masv
406 END
407 ---- auto add há»c lá»±c bằng proc
408
409EXEC add_hoc_luc_sv215'MASV_01_01'
410EXEC add_hoc_luc_sv215'MASV_01_02'
411EXEC add_hoc_luc_sv215'MASV_01_03'
412EXEC add_hoc_luc_sv215'MASV_01_04'
413EXEC add_hoc_luc_sv215'MASV_01_05'
414EXEC add_hoc_luc_sv215'MASV_01_06'
415
416EXEC add_hoc_luc_sv215'MASV_02_01'
417EXEC add_hoc_luc_sv215'MASV_02_02'
418EXEC add_hoc_luc_sv215'MASV_02_03'
419EXEC add_hoc_luc_sv215'MASV_02_04'
420EXEC add_hoc_luc_sv215'MASV_02_05'
421EXEC add_hoc_luc_sv215'MASV_02_06'
422
423EXEC add_hoc_luc_sv215'MASV_03_01'
424EXEC add_hoc_luc_sv215'MASV_03_02'
425EXEC add_hoc_luc_sv215'MASV_03_03'
426EXEC add_hoc_luc_sv215'MASV_03_04'
427EXEC add_hoc_luc_sv215'MASV_03_05'
428EXEC add_hoc_luc_sv215'MASV_03_06'
429
430EXEC add_hoc_luc_sv215'MASV_04_01'
431EXEC add_hoc_luc_sv215'MASV_04_02'
432EXEC add_hoc_luc_sv215'MASV_04_03'
433EXEC add_hoc_luc_sv215'MASV_04_04'
434EXEC add_hoc_luc_sv215'MASV_04_05'
435EXEC add_hoc_luc_sv215'MASV_04_06'
436
437SELECT * FROM SINHVIEN
438
439---
440
441
442
443
444
445
446
447
448
449
450---=================================================
451---------=============================== MR.HÙNG ====================================================================
452
453
454
455---PROC NHẬP VÀO MÃ SV Rá»’I ÄƯA RA THÔNG TIN ..
456 ALTER PROC procedure_there @MASV nvarchar(20)
457 AS
458 BEGIN
459 IF EXISTS ( SELECT *
460 FROM SINHVIEN
461 WHERE @MASV = MASV )
462 BEGIN
463 SELECT SINHVIEN.MASV ,
464 SINHVIEN.MALOP ,
465 GVCN.MAGVCN ,
466 GVCN.TENGV
467 FROM SINHVIEN ,
468 LOP ,
469 GVCN
470 WHERE SINHVIEN.MALOP = LOP.MALOP
471 AND GVCN.MALOP = LOP.MALOP
472 AND SINHVIEN.MASV = @MASV
473 END
474 ELSE
475 BEGIN
476 PRINT N' K ÄƯỢC HỌC MÀ CŨNG ÄÃ’I CÓ GIÃO VIÊN CHỦ NHIỆM À CƯNG . OUT . => '
477 ROLLBACK TRAN
478 END
479
480 END
481 EXEC procedure_there N'MASV_02_01'
482
483------CREATE TABLE THEME 18+--------------------------------------------------------------------------------------
484 CREATE TABLE TABLE_RESULT_18
485 (
486 MASV_01 INT NOT NULL ,
487 RESULT NVARCHAR(50)
488 )
489 ALTER TABLE dbo.TABLE_RESULT_18 DROP COLUMN MASV_01
490 ALTER TABLE dbo.TABLE_RESULT_18 ADD MASV_01 NVARCHAR(20) NOT NULL
491
492
493 -----PROC THEME 18+---------KIEM TRA SINH VIEN 18_ HAY CHUA
494 SELECT GETDATE()
495
496 ALTER PROC PROCESS_RESULT_18
497 @MASV NVARCHAR(50)
498 AS
499 BEGIN
500 DECLARE @MASV_NS NVARCHAR(50)
501 SET @MASV_NS = ( SELECT SINHVIEN.NGAYSINH
502 FROM SINHVIEN
503 WHERE SINHVIEN.MASV = @MASV
504 )
505 PRINT @MASV_NS
506 DECLARE @AGE_TEST INT
507 SET @AGE_TEST = ( YEAR(GETDATE()) - YEAR(@MASV_NS) )
508 PRINT @AGE_TEST
509 IF EXISTS ( SELECT *
510 FROM SINHVIEN
511 WHERE SINHVIEN.MASV = @MASV )
512 AND @AGE_TEST > 18
513 BEGIN
514 DECLARE @MASV_01 NVARCHAR
515 DECLARE @RESULT NVARCHAR(50)
516 SET @RESULT = N'CHÚC MỪNG THà SINH ÄÃ 18+ <3'
517 INSERT INTO TABLE_RESULT_18
518 ( MASV_01 , RESULT )
519 VALUES ( @MASV, @RESULT );
520 END
521 ELSE
522 BEGIN
523 PRINT 'YOU FAILURE AND TRY AGAIN'
524 ROLLBACK TRAN
525 END
526 END
527
528 EXEC PROCESS_RESULT_18 'MASV_01_0444'
529
530 SELECT * FROM TABLE_RESULT_18
531
532 SELECT YEAR(SINHVIEN.NGAYSINH)
533 FROM SINHVIEN
534 WHERE SINHVIEN.MASV = 'MASV_01_01'
535
536------- END 18+----Äã fixx xong -------------------------------------------------
537
538
539--NHẬP TÊN ÄIỂM VÀ MÃ SINH VIÊN = > ÄƯA RA RESULT
540
541 ALTER PROC procedure_one
542 @diem INT ,
543 @MASV NVARCHAR(20)
544 AS
545 BEGIN
546 IF ( @diem > 0 )
547 AND EXISTS ( SELECT *
548 FROM SINHVIEN
549 WHERE SINHVIEN.MASV = @MASV )
550 BEGIN
551 SELECT DISTINCT SINHVIEN.MASV ,
552 DIEMSV.DIEMSV ,
553 SINHVIEN.TENSV ,
554 MON.TENMON
555 FROM MON ,
556 SINHVIEN ,
557 dbo.DIEMSV
558 WHERE DIEMSV.DIEMSV > @DIEM
559 AND SINHVIEN.MASV = @MASV
560 AND MON.MAMON = DIEMSV.MAMON
561 END
562 END
563
564
565
566
567 EXEC procedure_one 6, N'MASV_01_01'
568
569---END INPUT { ÄIỂM , MASV }--------------------------------------------------------------------------------------------------------------
570
571----INPUT{ MÃ LỚP } => OUTPUT{ COUT(*)}---------------------------------------------------------------------------------------------------
572
573 ALTER PROC procedure_for @MALOP NVARCHAR(20)
574 AS
575 BEGIN
576 IF EXISTS ( SELECT *
577 FROM LOP
578 WHERE LOP.MALOP = @MALOP )
579 BEGIN
580 DECLARE @COUNT INT
581 SET @COUNT = ( SELECT COUNT(SINHVIEN.MASV)
582 FROM SINHVIEN ,
583 LOP
584 WHERE SINHVIEN.MALOP = LOP.MALOP
585 AND LOP.MALOP = @MALOP
586 GROUP BY LOP.MALOP
587 )
588 PRINT @COUNT
589 END
590 ELSE
591 BEGIN
592 PRINT N' Cạn Lá»i'
593 ROLLBACK TRAN
594 END
595 END
596
597
598
599 EXECUTE procedure_for N'LOP_01'
600
601
602--------END --------
603
604-------------------------------CREATE THE LAST---------------------------------------------------
605
606 CREATE VIEW VIEW_DIEMSV
607 AS
608 SELECT *
609 FROM dbo.DIEMSV
610
611
612
613 ALTER TABLE COPY_DIEMSV ADD DIEMSV NVARCHAR(20)
614
615
616
617 ALTER PROC procudure_different @MALOP NVARCHAR(50)
618 AS
619 BEGIN
620
621 DECLARE @J INT = 1
622 DECLARE @K INT = 0
623 DECLARE @LOP_IN NVARCHAR(20)
624 DECLARE @DIEM FLOAT
625 DECLARE @COUNT INT
626 DECLARE @I INT = 1
627 WHILE @K = 0
628 BEGIN
629 SET @LOP_IN = 'LOP_0'+ CAST(@J AS VARCHAR)
630 IF ( @LOP_IN = @MALOP )
631 BEGIN
632 DECLARE @MASV NVARCHAR(20)
633 SET @COUNT = ( SELECT COUNT(SINHVIEN.MASV)
634 FROM
635 SINHVIEN ,LOP
636 WHERE
637 SINHVIEN.MALOP = LOP.MALOP
638 AND LOP.MALOP = @MALOP
639 GROUP BY LOP.MALOP )
640 WHILE ( @I < @COUNT + 1 )
641 BEGIN
642 SET @MASV = 'MASV_0'+ CAST(@J AS VARCHAR) + '_0'+ CAST(@I AS VARCHAR)
643 SET @DIEM = ( SELECT AVG(DIEMSV.DIEMSV) FROM DIEMSV , SINHVIEN
644 WHERE
645 DIEMSV.MASV = @MASV
646 AND DIEMSV.MASV = SINHVIEN.MASV)
647 IF ( @DIEM > 6 )
648 BEGIN
649 UPDATE
650 COPY_DIEMSV
651 SET
652 DIEMSV = N'ÄẬU !'
653 WHERE
654 MASV = @MASV;
655 END
656 ELSE
657 BEGIN
658 UPDATE
659 COPY_DIEMSV
660 SET
661 DIEMSV = N'TẠCH CMNR'
662 WHERE
663 MASV = @MASV;
664 END
665 SET @I = @I + 1
666
667 END
668 SET @K = 1
669
670 SELECT DISTINCT dbo.SINHVIEN.MASV, dbo.COPY_DIEMSV.DIEMSV FROM dbo.SINHVIEN, dbo.LOP,dbo.COPY_DIEMSV
671 WHERE LOP.MALOP = dbo.SINHVIEN.MALOP AND dbo.COPY_DIEMSV.MASV = dbo.SINHVIEN.MASV AND SINHVIEN.MALOP = @MALOP
672 END
673 ELSE
674 BEGIN
675 SET @J = @J + 1
676
677 END
678 END
679
680 END
681
682 EXEC procudure_different 'LOP_01'
683
684
685 SELECT DISTINCT MASV , DIEMSV AS N'RESULT' FROM dbo.COPY_DIEMSV
686 PRINT @LOP_IN
687
688 SELECT * FROM dbo.DIEMSV
689
690
691 UPDATE COPY_DIEMSV
692 SET DIEMSV = 'TEHHUNG'
693
694 UPDATE COPY_DIEMSV
695 SET DIEMSV = 'XX'
696
697
698
699--------------------------------------------------------------------------------------------------
700SELECT SINHVIEN.MASV FROM SINHVIEN , LOP WHERE LOP.MALOP = 'LOP_01' AND SINHVIEN.MALOP = LOP.MALOP
701
702
703
704
705
706
707
708
709
710
711
712
713
714
715
716
717
718
719
720
721
722---------=============================== MR.PHÆ¯Æ NG ====================================================================
723
724
725-- CREATE VIEW
726 CREATE VIEW SINHVIEN_VIEW AS
727 SELECT SV.MASV, TENSV, GIOITINH, DATEDIFF(YY,NGAYSINH,GETDATE()) AS TUOI, MALOP, MAMON, DIEMSV
728 FROM SINHVIEN AS SV, DIEMSV AS DSV
729 WHERE SV.MASV = DSV.MASV
730
731 SELECT * FROM SINHVIEN_VIEW
732
733 SELECT * FROM LOP
734
735 SELECT * FROM GVCN
736
737 CREATE VIEW GVCN_VIEW AS
738 SELECT MAGVCN, TENGV, GIOITINH, DATEDIFF(YY,NGAYSINH,GETDATE()) AS TUOIGV, GVCN.MALOP, MAKHOA
739 FROM GVCN, LOP
740 WHERE GVCN.MALOP = LOP.MALOP
741
742 SELECT * FROM GVCN_VIEW
743
744 SELECT * FROM LOP
745 SELECT * FROM KHOA
746
747 CREATE VIEW LOPSINHVIEN_VIEW AS
748 SELECT LOP.MALOP, LOP.MAKHOA, TENKHOA, TENSV, GIOITINH, DATEDIFF(YY,NGAYSINH,GETDATE()) AS TUOI
749 FROM LOP, KHOA, SINHVIEN
750 WHERE LOP.MAKHOA = KHOA.MAKHOA AND SINHVIEN.MALOP = LOP.MALOP
751
752 SELECT * FROM LOPSINHVIEN_VIEW
753
754 -- VIẾT TRUY VẤN VIEW
755 --1,XUẤT TẤT CẢ TÊN, TUá»”I, GIỚI TÃNH CỦA SINH VIÊN HỌC GVCN NÀO TRONG CÙNG 1 LỚP
756
757 SELECT DISTINCT TENSV, SVW.GIOITINH, SVW.TUOI, TENGV, GVW.GIOITINH, GVW.TUOIGV
758 FROM SINHVIEN_VIEW AS SVW, GVCN_VIEW AS GVW
759 WHERE SVW.MALOP = GVW.MALOP
760
761 SELECT * FROM SINHVIEN_VIEW
762 SELECT * FROM GVCN_VIEW
763 --2,XUẤT ÄỘ TUá»”I TRUNG BÃŒNH CỦA SINH VIÊN
764
765 SELECT AVG(TUOI) AS TUOI_TB_SV
766 FROM LOPSINHVIEN_VIEW
767
768 --3, XUẤT TỔNG SỠSINH VIÊN NAM VÀ NỮ
769 SELECT GIOITINH, COUNT(*) AS TONG
770 FROM LOPSINHVIEN_VIEW
771 GROUP BY GIOITINH
772 --4, LẤY TÊN, TUOI, GIOI TINH CỦA SINH CÓ TUá»”I LỚN HÆ N TUá»”I TRUNG BÃŒNH CỦA TẤT CẢ SINH VIÊN
773 SELECT TENSV, TUOI, GIOITINH
774 FROM LOPSINHVIEN_VIEW
775 WHERE TUOI > (SELECT AVG(TUOI) FROM LOPSINHVIEN_VIEW)
776
777 --5, XUẤT RA CÃC TÊN VÀ MÃ KHOA CUA SINH VIÊN NAM CÓ TRONG LỚP HỌC CHUNG 1 GVCN TRONG LỚP CÓ MÃ LÀ LOP_01
778 SELECT TENSV, LOPSV.MAKHOA, TENGV, LOPSV.MALOP
779 FROM LOPSINHVIEN_VIEW AS LOPSV, GVCN_VIEW AS GV
780 WHERE LOPSV.GIOITINH = 'NAM' AND LOPSV.MALOP = GV.MALOP AND LOPSV.MAKHOA = GV.MAKHOA AND LOPSV.MALOP = 'LOP_01'
781 --6, XUẤT THÔNG TIN SINH VIÊN CÓ TUỔI LỚN NHẤT
782 SELECT *
783 FROM SINHVIEN_VIEW
784 WHERE TUOI = (SELECT MAX(TUOI) FROM LOPSINHVIEN_VIEW)
785
786 --7, XUẤT Sá» MÔN CÓ ÄIỂM CỦA CÃC SINH VIÊN TÆ¯Æ NG ỨNG VỚI TÊN SINH VIÊN
787 SELECT TENSV, COUNT(MASV) AS SL_MON_CUA_SV
788 FROM SINHVIEN_VIEW
789 GROUP BY TENSV
790
791 --8, XUẤT Tá»”NG ÄIỂM 3 MÔN CỦA TỪNG SINH VIÊN VỚI TÊN SINH VIÊN TÆ¯Æ NG ỨNG
792 SELECT TENSV, SUM(DIEMSV) AS TONG_DIEM_CUA_SV
793 FROM SINHVIEN_VIEW
794 GROUP BY TENSV
795
796 --9, XUẤT ÄIỂM TRUNG BÃŒNH VÀ TÊN CỦA TỪNG SINH VIÊN
797 SELECT TENSV, AVG(DIEMSV) AS DIEMTB_CUA_SV
798 FROM SINHVIEN_VIEW
799 GROUP BY TENSV
800
801 --10, XUẤT TÊN SINH VIÊN CÓ ÄIỂM TRUNG BÃŒNH < 5
802
803 SELECT TENSV, AVG(DIEMSV) AS DIEMTB_CUA_SV
804 FROM SINHVIEN_VIEW
805 GROUP BY TENSV
806 HAVING AVG(DIEMSV) > 5
807 ORDER BY DIEMTB_CUA_SV
808
809 --11, XUẤT SỠSINH VIÊN CỦA TỪNG LỚP
810
811 SELECT MALOP, COUNT(MALOP) AS SO_LUONG_SINHVIEN
812 FROM LOPSINHVIEN_VIEW
813 GROUP BY MALOP
814
815 --ÄÃNH CHỈ MỤC INDEX CHO TABLE
816
817 CREATE INDEX TENSV_IND ON SINHVIEN(TENSV)
818
819 CREATE INDEX TENMON_IND ON MON(TENMON)
820
821 CREATE INDEX MALOP_IND ON LOP(MALOP)
822
823 CREATE INDEX TENKHOA_IND ON KHOA(TENKHOA)
824
825 CREATE INDEX TENGV_IND ON GVCN(TENGV)
826
827 CREATE INDEX DIEM_IND ON DIEMSV(DIEMSV)