· 6 years ago · Jan 07, 2020, 05:18 PM
1use SP48PM_vizsga
2
3/*
4--- terem létrehozása, vizsga módosítása
5CREATE TABLE terem(szam INT PRIMARY KEY, fõ INT, gép INT, proj BIT)
6 ALTER TABLE vizsga ADD
7 terem INT, gepes BIT,proj BIT, perc INT
8 ALTER TABLE vizsga ADD CONSTRAINT FK_terem FOREIGN KEY (terem) REFERENCES terem(szam)
9GO
10*/
11
12--- Vizsga létrehozása teremmel
13
14IF OBJECT_ID('uj_vizsga_terem') IS NOT NULL
15BEGIN
16 DROP PROCEDURE uj_vizsga_terem
17END
18GO
19
20CREATE PROCEDURE uj_vizsga_terem (@szak CHAR(3),@targy CHAR(4), @ido SMALLDATETIME, @tanár INT, @max_fo SMALLINT, @terem INT, @gepes BIT, @perc INT, @proj BIT)
21AS
22BEGIN
23 DECLARE cur CURSOR FOR SELECT * FROM terem WHERE szam = @terem
24 DECLARE cur1 CURSOR FOR SELECT vizsga FROM vizsga WHERE terem = @terem AND idő = @ido
25 DECLARE @_szam INT,
26 @_fő INT,
27 @_gép INT,
28 @_vizsga INT,
29 @_proj BIT
30
31 OPEN cur
32 OPEN cur1
33 FETCH NEXT FROM cur INTO @_szam,@_fő,@_gép
34 FETCH NEXT FROM cur1 INTO @_vizsga
35 IF @_szam IS NULL
36 BEGIN
37 DEALLOCATE cur
38 DEALLOCATE cur1
39 PRINT 'Nincs ilyen terem'
40 RETURN
41 END
42 IF @gepes = 1 AND @_gép=0
43 BEGIN
44 CLOSE cur1
45 CLOSE cur
46 PRINT 'Nem gépes terem'
47 RETURN
48 END
49 IF @max_fo>@_fő
50 BEGIN
51 CLOSE cur1
52 CLOSE cur
53 PRINT 'Nincs elég férõhely'
54 RETURN
55 END
56 IF @max_fo>@_gép
57 BEGIN
58 CLOSE cur1
59 CLOSE cur
60 PRINT 'Nincs elég számítógép'
61 RETURN
62 END
63 IF @_vizsga IS NOT NULL
64 BEGIN
65 CLOSE cur1
66 CLOSE cur
67 PRINT 'Ebben a teremben már van vizsga a megadott idõben'
68 RETURN
69 END
70 IF @proj > @_proj
71 BEGIN
72 CLOSE cur1
73 CLOSE cur
74 PRINT 'Ebben a teremben nincs projektor, a vizsgához kell projektor'
75 RETURN
76 END
77 INSERT INTO vizsga(szak,tárgy,idő,tanár,max_fő,terem,gepes,perc) VALUES(@szak,@targy,CONVERT(DATETIME,@ido,23),@tanár,@max_fo,@terem,@gepes,@perc)
78 CLOSE cur1
79 CLOSE cur
80 PRINT 'Vizsga létrehozva'
81END
82GO
83
84
85---3. feladat
86IF OBJECT_ID('tanar_uj_vizsga') IS NOT NULL
87BEGIN
88 DROP PROCEDURE tanar_uj_vizsga
89END
90GO
91
92CREATE PROCEDURE tanar_uj_vizsga(@tkód CHAR(4),@szak CHAR(3),@targy CHAR(4),@ido SMALLDATETIME,@tanár INT,@max_fo SMALLINT,@terem INT,@gepes BIT,@perc INT)
93AS
94BEGIN
95 DECLARE cur CURSOR FOR SELECT vez_tanár FROM tantárgy WHERE tkód = @tkód
96 DECLARE @_vez INT
97 OPEN cur
98 FETCH NEXT FROM cur INTO @_vez
99 IF @_vez IS NULL
100 BEGIN
101 CLOSE cur
102 PRINT 'Nem megfelelő jogosultság új vizsga kiírásához!'
103 RETURN
104 END
105 ---2.feladatból
106 EXEC uj_vizsga_terem @szak,@targy,@ido,@tanár, @max_fo, @terem,@gepes,@perc
107END
108
109
110
111
112
113---4.Feladat
114
115IF OBJECT_ID('FK_tematika_tematika', 'F') IS NOT NULL
116 ALTER TABLE tematika DROP CONSTRAINT FK_tematika_tematika
117GO
118
119IF OBJECT_ID('FK_vizsga_tematika', 'F') IS NOT NULL
120 ALTER TABLE vizsga DROP CONSTRAINT FK_vizsga_tematika
121GO
122
123
124IF OBJECT_ID('PK_tematika', 'PK') IS NOT NULL
125 ALTER TABLE tematika DROP CONSTRAINT PK_tematika
126GO
127
128INSERT INTO tantárgy VALUES ('WEBP','Webes programozás', 9)
129
130GO
131
132UPDATE tematika SET előzmény='WEBP' WHERE előzmény IS NULL
133
134GO
135
136ALTER TABLE tematika ALTER COLUMN előzmény CHAR(4) NOT NULL
137
138GO
139
140ALTER TABLE tematika ADD CONSTRAINT PK_tematika PRIMARY KEY CLUSTERED
141(
142 szak ASC,
143 tárgy ASC,
144 előzmény ASC
145)
146GO
147
148IF OBJECT_ID('új_vizsga', 'P') IS NOT NULL
149 DROP PROC új_vizsga
150
151GO
152
153CREATE PROCEDURE új_vizsga
154 (
155 @szak CHAR(3),
156 @targy CHAR(4),
157 @ido SMALLDATETIME,
158 @tanár INT,
159 @max_fo SMALLINT,
160 @proj BIT,
161 @perc INT,
162 @gepes BIT,
163 @terem INT
164
165 )
166AS
167BEGIN
168 DECLARE @ok INT
169 SET @ok=1
170
171 IF @szak NOT IN (SELECT szkód FROM szak GROUP BY szkód)
172 BEGIN
173 SET @ok=0;
174 RAISERROR('Szak nem létezik',11,1)
175 END
176
177 IF @targy NOT IN (SELECT tkód FROM tantárgy GROUP BY tkód)
178 BEGIN
179 SET @ok=0;
180 RAISERROR('Tantárgy nem létezik.',11,1)
181 END
182
183 IF @targy NOT IN (SELECT tárgy FROM tematika WHERE szak = @szak GROUP BY tárgy)
184 BEGIN
185 SET @ok=0;
186 RAISERROR('Tematika nem létezik.',11,1)
187 END
188
189 IF @ido<convert(SMALLDATETIME,getdate())
190 BEGIN
191 RAISERROR('Hibás időpont!',11,1)
192 SET @ok=0;
193 END
194
195
196 IF (@tanár) IS NOT NULL
197 BEGIN
198 IF @tanár NOT IN (SELECT szkód FROM személy s LEFT JOIN hallgató h ON s.szkód = h.hkód WHERE h.hkód IS NULL )
199 BEGIN
200 RAISERROR('Nem létező tanár!',11,1)
201 SET @ok=0;
202 END
203 END
204
205
206 IF @ok=1
207 BEGIN
208 INSERT INTO vizsga VALUES (@szak, @targy, @ido, @tanár, @max_fo,@terem,@gepes,@perc,@proj)
209 RAISERROR('Sikeres vizsgajelentkezés!',1,1)
210
211 END
212END
213GO
214
215RAISERROR('Sikertelen próbálkozás:',1,1)
216--
217-- hibás vizsga
218EXEC új_vizsga @szak='MIN',@targy='PRI', @ido='2019-01-13 11:00:00',@tanár=6,@max_fo=1000, @proj=0,@perc=60,@gepes=0 ,@terem= 2;
219
220RAISERROR('Sikeres próbálkozás:',1,1)
221--
222-- helyes vizsga
223EXEC új_vizsga @szak='MIT',@targy='MATA', @ido='2020-01-14 16:00:00',@tanár=16,@max_fo=1000,@proj=1,@terem= 2,@gepes=1,@perc=120;
224
225
226
227
228
229
230
231
232
233---7. Feladat
234IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_TYPE='BASE TABLE' AND TABLE_NAME='tárgy_felvétel')
235
236BEGIN
237 CREATE TABLE tárgy_felvétel(hallgató INT NOT NULL, tárgy CHAR(4) NOT NULL,
238 akt_szem INT NOT NULL, PRIMARY KEY (hallgató,tárgy,akt_szem))
239END
240GO
241
242IF object_id('felvetel','p') IS NOT NULL
243 DROP PROCEDURE felvetel
244GO
245
246CREATE PROCEDURE felvetel(@hallgato INT, @mit INT)
247AS
248 DECLARE @ok BIT
249 SET @ok=1;
250 IF @hallgato NOT IN(SELECT hkód FROM hallgató WHERE hkód=@hallgato)
251 BEGIN
252 RAISERROR('Nincs ilyen hallgató!',16,1)
253 SET @ok=0;
254 END
255
256 IF @ok=1
257 BEGIN
258 DECLARE @hallgato_osszesen_elozo INT
259
260 SET @hallgato_osszesen_elozo= (SELECT COUNT(*) FROM jelentkezés AS j, vizsga AS v,
261 tematika AS t, hallgató AS h WHERE j.jegy<=1 AND
262 j.hkód=@hallgato AND v.vizsga=j.vizsga AND v.szak=t.szak
263 AND v.tárgy=t.tárgy AND h.hkód=j.hkód AND t.szemeszter<h.akt_szem)
264
265 DECLARE @hallgato_osszesen_mostani INT
266
267 SET @hallgato_osszesen_mostani= (SELECT COUNT(*) FROM tematika AS t, hallgató AS h WHERE
268 h.hkód=@hallgato AND h.szak=t.szak AND h.akt_szem=t.szemeszter)
269
270
271 IF @hallgato IN(SELECT hallgató FROM tárgy_felvétel WHERE hallgató=@hallgato)
272 BEGIN
273 IF @mit=1
274 BEGIN
275
276 IF (SELECT t.tárgy FROM jelentkezés AS j, vizsga AS v,
277 tematika AS t, hallgató AS h WHERE j.jegy<=1 AND
278 j.hkód=@hallgato AND v.vizsga=j.vizsga AND v.szak=t.szak
279 AND v.tárgy=t.tárgy AND h.hkód=j.hkód AND t.szemeszter<h.akt_szem)
280 IN
281 (SELECT t.tárgy FROM tárgy_felvétel AS t, hallgató AS h WHERE
282 h.hkód=@hallgato AND h.hkód=t.hallgató AND h.akt_szem=t.akt_szem)
283 BEGIN
284 RAISERROR('A hallgató már felvette az elmaradtakat!',16,1)
285 SET @ok=0;
286 END
287 END
288 IF @mit=0
289 BEGIN
290
291 IF (SELECT t.tárgy FROM tematika AS t, hallgató AS h WHERE
292 h.hkód=@hallgato AND h.szak=t.szak AND h.akt_szem=t.szemeszter)
293 IN
294 (SELECT t.tárgy FROM tárgy_felvétel AS t, hallgató AS h WHERE
295 h.hkód=@hallgato AND h.hkód=t.hallgató AND h.akt_szem=t.akt_szem)
296 BEGIN
297 RAISERROR('A hallgató már felvette a tárgyait!',16,1)
298 SET @ok=0;
299 END
300 END
301 END
302 END
303
304 IF @ok=1
305 BEGIN
306 DECLARE @targy_osszesen INT
307 DECLARE @hallgato_osszesen INT
308
309 SET @hallgato_osszesen= (SELECT COUNT(*) FROM jelentkezés AS j, vizsga AS v,
310 tematika AS t WHERE j.jegy>1 AND
311 j.hkód=@hallgato AND v.vizsga=j.vizsga AND v.szak=t.szak AND v.tárgy=t.tárgy)
312
313 SET @targy_osszesen= (SELECT COUNT(*) FROM jelentkezés AS j, vizsga AS v,
314 tematika AS t WHERE j.hkód=@hallgato AND v.vizsga=j.vizsga AND v.szak=t.szak AND v.tárgy=t.tárgy)
315
316 IF @hallgato_osszesen=@targy_osszesen
317 BEGIN
318 RAISERROR('A hallgatónak nincs sem elmaradt, sem új tárgya!',16,1)
319 SET @ok=0;
320 END
321
322 IF @mit=1
323 BEGIN
324 IF @hallgato_osszesen_elozo<1
325 BEGIN
326 RAISERROR('A hallgatónak nincs elmaradt vizsgája!',16,1)
327 SET @ok=0;
328 END
329 ELSE
330 BEGIN
331 INSERT INTO tárgy_felvétel (hallgató,tárgy,akt_szem)
332 SELECT h.hkód,t.tárgy,h.akt_szem FROM jelentkezés AS j, vizsga AS v,
333 tematika AS t, hallgató AS h WHERE j.jegy<=1 AND
334 j.hkód=@hallgato AND v.vizsga=j.vizsga AND v.szak=t.szak
335 AND v.tárgy=t.tárgy AND h.hkód=j.hkód AND t.szemeszter<h.akt_szem
336 RAISERROR('A hallgató fevette az elmaradt tárgyait!',16,1)
337 END
338 END
339 IF @mit=0
340 BEGIN
341 IF @hallgato_osszesen_mostani<1
342 BEGIN
343 RAISERROR('A hallgatónak nincs új tárgya!',16,1)
344 SET @ok=0;
345 END
346 ELSE
347 BEGIN
348 INSERT INTO tárgy_felvétel (hallgató,tárgy,akt_szem)
349 SELECT h.hkód,t.tárgy,h.akt_szem FROM tematika AS t, hallgató AS h WHERE
350 h.hkód=@hallgato AND h.szak=t.szak AND h.akt_szem=t.szemeszter
351 RAISERROR('A hallgató fevette az új tárgyait!',16,1)
352 END
353 END
354 END
355GO
356
357
358
359
360
361
362---6.Feladat
363
364
365
366ALTER TABLE hallgató ADD
367állapot VARCHAR(20)
368
369IF object_id('vegzett_nem','p') IS NOT NULL
370 DROP PROCEDURE vegzett_nem
371GO
372
373CREATE PROCEDURE vegzett_nem(@hallgato INT)
374AS
375 DECLARE @ok INT
376 SET @ok=1;
377 DECLARE @hallgato_szem INT
378 DECLARE @szak_szem INT
379
380 SELECT @hallgato_szem=akt_szem FROM hallgató WHERE hkód=@hallgato
381 SELECT @szak_szem=sz.szemeszter FROM hallgató AS h, szak AS sz WHERE h.hkód=@hallgato AND sz.szkód=h.szak
382
383 IF @hallgato IN (SELECT hkód FROM hallgató WHERE állapot='végzett')
384 BEGIN
385 SET @ok=0;
386 RAISERROR('A hallgató már végzett!',16,1)
387 END
388 IF @ok=1
389 BEGIN
390 IF (@hallgato_szem>=@szak_szem)
391 BEGIN
392
393 DECLARE @targy_osszesen INT
394 DECLARE @hallgato_osszesen INT
395
396 SET @hallgato_osszesen= (SELECT COUNT(*) FROM jelentkezés AS j, vizsga AS v,
397 tematika AS t WHERE j.jegy>1 AND
398 j.hkód=@hallgato AND v.vizsga=j.vizsga AND v.szak=t.szak AND v.tárgy=t.tárgy)
399
400 SET @targy_osszesen= (SELECT COUNT(*) FROM jelentkezés AS j, vizsga AS v,
401 tematika AS t WHERE j.hkód=@hallgato AND v.vizsga=j.vizsga AND v.szak=t.szak AND v.tárgy=t.tárgy)
402
403 IF @hallgato_osszesen=@targy_osszesen
404 BEGIN
405 UPDATE hallgató SET állapot='végzett' WHERE hkód=@hallgato
406 RAISERROR('A hallgató végzett státuszba került!',16,1)
407 END
408 ELSE
409 BEGIN
410 RAISERROR('A hallgató elérte az ajánlott szemeszterszámot, de nem teljesített minden tárgyat!',16,1)
411 END
412 END
413 ELSE
414 BEGIN
415 UPDATE hallgató SET akt_szem=akt_szem+1 WHERE hkód=@hallgato
416 UPDATE hallgató SET állapot='aktív' WHERE hkód=@hallgato
417 RAISERROR('A hallgató szemesztere eggyel nőtt!',16,1)
418 END
419 END
420
421GO