· 8 years ago · Jan 09, 2018, 01:52 AM
1---PROC NHẬP VÀO MÃ SV Rá»’I ÄƯA RA THÔNG TIN ..
2 ALTER PROC procedure_there @MASV nvarchar(20)
3 AS
4 BEGIN
5 IF EXISTS ( SELECT *
6 FROM SINHVIEN
7 WHERE @MASV = MASV )
8 BEGIN
9 SELECT SINHVIEN.MASV ,
10 SINHVIEN.MALOP ,
11 GVCN.MAGVCN ,
12 GVCN.TENGV
13 FROM SINHVIEN ,
14 LOP ,
15 GVCN
16 WHERE SINHVIEN.MALOP = LOP.MALOP
17 AND GVCN.MALOP = LOP.MALOP
18 AND SINHVIEN.MASV = @MASV
19 END
20 ELSE
21 BEGIN
22 PRINT N' K ÄƯỢC HỌC MÀ CŨNG ÄÃ’I CÓ GIÃO VIÊN CHỦ NHIỆM À CƯNG . OUT . => '
23 ROLLBACK TRAN
24 END
25
26 END
27 EXEC procedure_there N'MASV_02_01'
28
29------CREATE TABLE THEME 18+--------------------------------------------------------------------------------------
30 CREATE TABLE TABLE_RESULT_18
31 (
32 MASV_01 INT NOT NULL ,
33 RESULT NVARCHAR(50)
34 )
35 ALTER TABLE dbo.TABLE_RESULT_18 DROP COLUMN MASV_01
36 ALTER TABLE dbo.TABLE_RESULT_18 ADD MASV_01 NVARCHAR(20) NOT NULL
37
38
39 -----PROC THEME 18+---------ÄANG UPDATE PELSE WAIT
40 SELECT GETDATE()
41
42 ALTER PROC PROCESS_RESULT_18
43 @MASV NVARCHAR(50)
44 AS
45 BEGIN
46 DECLARE @MASV_NS NVARCHAR(50)
47 SET @MASV_NS = ( SELECT SINHVIEN.NGAYSINH
48 FROM SINHVIEN
49 WHERE SINHVIEN.MASV = @MASV
50 )
51 PRINT @MASV_NS
52 DECLARE @AGE_TEST INT
53 SET @AGE_TEST = ( YEAR(GETDATE()) - YEAR(@MASV_NS) )
54 PRINT @AGE_TEST
55 IF EXISTS ( SELECT *
56 FROM SINHVIEN
57 WHERE SINHVIEN.MASV = @MASV )
58 AND @AGE_TEST > 18
59 BEGIN
60 DECLARE @MASV_01 NVARCHAR
61 DECLARE @RESULT NVARCHAR(50)
62 SET @RESULT = N'CHÚC MỪNG THà SINH ÄÃ 18+ <3'
63 INSERT INTO TABLE_RESULT_18
64 ( MASV_01 , RESULT )
65 VALUES ( @MASV, @RESULT );
66 END
67 ELSE
68 BEGIN
69 PRINT 'YOU FAILURE AND TRY AGAIN'
70 ROLLBACK TRAN
71 END
72 END
73
74 EXEC PROCESS_RESULT_18 'MASV_01_0444'
75
76 SELECT * FROM TABLE_RESULT_18
77
78 SELECT YEAR(SINHVIEN.NGAYSINH)
79 FROM SINHVIEN
80 WHERE SINHVIEN.MASV = 'MASV_01_01'
81
82------- END 18+----Äã fixx xong -------------------------------------------------
83
84
85--NHẬP TÊN ÄIỂM VÀ MÃ SINH VIÊN = > ÄƯA RA RESULT
86
87 ALTER PROC procedure_one
88 @diem INT ,
89 @MASV NVARCHAR(20)
90 AS
91 BEGIN
92 IF ( @diem > 0 )
93 AND EXISTS ( SELECT *
94 FROM SINHVIEN
95 WHERE SINHVIEN.MASV = @MASV )
96 BEGIN
97 SELECT DISTINCT SINHVIEN.MASV ,
98 DIEMSV.DIEMSV ,
99 SINHVIEN.TENSV ,
100 MON.TENMON
101 FROM MON ,
102 SINHVIEN ,
103 dbo.DIEMSV
104 WHERE DIEMSV.DIEMSV > @DIEM
105 AND SINHVIEN.MASV = @MASV
106 AND MON.MAMON = DIEMSV.MAMON
107 END
108 END
109
110
111
112
113 EXEC procedure_one 6, N'MASV_01_01'
114
115---END INPUT { ÄIỂM , MASV }--------------------------------------------------------------------------------------------------------------
116
117----INPUT{ MÃ LỚP } => OUTPUT{ COUT(*)}---------------------------------------------------------------------------------------------------
118
119 ALTER PROC procedure_for @MALOP NVARCHAR(20)
120 AS
121 BEGIN
122 IF EXISTS ( SELECT *
123 FROM LOP
124 WHERE LOP.MALOP = @MALOP )
125 BEGIN
126 DECLARE @COUNT INT
127 SET @COUNT = ( SELECT COUNT(SINHVIEN.MASV)
128 FROM SINHVIEN ,
129 LOP
130 WHERE SINHVIEN.MALOP = LOP.MALOP
131 AND LOP.MALOP = @MALOP
132 GROUP BY LOP.MALOP
133 )
134 PRINT @COUNT
135 END
136 ELSE
137 BEGIN
138 PRINT N' Cạn Lá»i'
139 ROLLBACK TRAN
140 END
141 END
142
143
144
145 EXECUTE procedure_for N'LOP_01'
146
147
148--------END --------
149
150-------------------------------CREATE THE LAST---------------------------------------------------
151 CREATE VIEW VIEW_DIEMSV
152 AS
153 SELECT *
154 FROM dbo.DIEMSV
155
156
157
158 ALTER TABLE COPY_DIEMSV ADD DIEMSV NVARCHAR(20)
159
160
161
162 ALTER PROC procudure_different @MALOP NVARCHAR(50)
163 AS
164 BEGIN
165
166 DECLARE @J INT = 1
167 DECLARE @K INT = 0
168 DECLARE @LOP_IN NVARCHAR(20)
169 DECLARE @DIEM FLOAT
170 DECLARE @COUNT INT
171 DECLARE @I INT = 1
172 WHILE @K = 0
173 BEGIN
174 SET @LOP_IN = 'LOP_0'+ CAST(@J AS VARCHAR)
175 IF ( @LOP_IN = @MALOP )
176 BEGIN
177 DECLARE @MASV NVARCHAR(20)
178 SET @COUNT = ( SELECT COUNT(SINHVIEN.MASV)
179 FROM
180 SINHVIEN ,LOP
181 WHERE
182 SINHVIEN.MALOP = LOP.MALOP
183 AND LOP.MALOP = @MALOP
184 GROUP BY LOP.MALOP )
185 WHILE ( @I < @COUNT + 1 )
186 BEGIN
187 SET @MASV = 'MASV_0'+ CAST(@J AS VARCHAR) + '_0'+ CAST(@I AS VARCHAR)
188 SET @DIEM = ( SELECT AVG(DIEMSV.DIEMSV) FROM DIEMSV , SINHVIEN
189 WHERE
190 DIEMSV.MASV = @MASV
191 AND DIEMSV.MASV = SINHVIEN.MASV)
192 IF ( @DIEM > 6 )
193 BEGIN
194 UPDATE
195 COPY_DIEMSV
196 SET
197 DIEMSV = N'ÄẬU !'
198 WHERE
199 MASV = @MASV;
200 END
201 ELSE
202 BEGIN
203 UPDATE
204 COPY_DIEMSV
205 SET
206 DIEMSV = N'TẠCH CMNR'
207 WHERE
208 MASV = @MASV;
209 END
210 SET @I = @I + 1
211
212 END
213 SET @K = 1
214
215 SELECT DISTINCT dbo.SINHVIEN.MASV, dbo.COPY_DIEMSV.DIEMSV FROM dbo.SINHVIEN, dbo.LOP,dbo.COPY_DIEMSV
216 WHERE LOP.MALOP = dbo.SINHVIEN.MALOP AND dbo.COPY_DIEMSV.MASV = dbo.SINHVIEN.MASV AND SINHVIEN.MALOP = @MALOP
217 END
218 ELSE
219 BEGIN
220 SET @J = @J + 1
221
222 END
223 END
224
225 END
226
227 EXEC procudure_different 'LOP_01'
228
229
230 SELECT DISTINCT MASV , DIEMSV AS N'RESULT' FROM dbo.COPY_DIEMSV
231 PRINT @LOP_IN
232
233 SELECT * FROM dbo.DIEMSV
234
235
236 UPDATE COPY_DIEMSV
237 SET DIEMSV = 'TEHHUNG'
238
239 UPDATE COPY_DIEMSV
240 SET DIEMSV = 'XX'
241
242
243
244--------------------------------------------------------------------------------------------------
245SELECT SINHVIEN.MASV FROM SINHVIEN , LOP WHERE LOP.MALOP = 'LOP_01' AND SINHVIEN.MALOP = LOP.MALOP