Programming | SQL » BMF-NIK MS SQL Transact SQL példafeladatok by Sanyo

MSSQL példafeladatok by Sanyo --2. Tarolt eljaras kioszt n db x jegyet a hallgatok kozott --megj: tarolt eljaras nelkul 1 db update CREATE PROCEDURE JEGYOSZTAS(@ezt int, @ennyit int) AS DECLARE @jegy int DECLARE oszto CURSOR FOR SELECT jegy FROM Tanulmany WHERE jegy <> @ezt OPEN oszto FETCH NEXT FROM oszto INTO @jegy WHILE @@FETCH STATUS = 0 BEGIN IF @ennyit > 0 BEGIN UPDATE Tanulmany SET jegy = @ezt WHERE CURRENT OF oszto SET @ennyit = @ennyit - 1 END FETCH NEXT FROM oszto INTO @jegy END CLOSE oszto DEALLOCATE oszto --3. Hallgatok kozul torolni az atlag alatt teljesitoket --megj: tarolt eljaras nelkul 1 db DELETE CREATE PROCEDURE ATLAG ALATTIAK TORLESE AS DECLARE @atlag float DECLARE @hallgato char(100), @atlaga float SET @atlag = (SELECT AVG(CAST(jegy AS float)) FROM Tanulmany) DECLARE atlagok CURSOR FOR SELECT hallgato, AVG(CAST(jegy AS float)) FROM Tanulmany -- hallgatonkent kinek mennyi az atlaga GROUP BY hallgato OPEN atlagok FETCH NEXT FROM atlagok INTO @hallgato,

@atlaga WHILE @@FETCH STATUS = 0 BEGIN IF @atlaga < @atlag -- egyesevel vegignezzuk es aki nem erte el az atlagot azt mas palyara iranyitjuk BEGIN DELETE FROM Tanulmany WHERE hallgato = @hallgato END FETCH NEXT FROM atlagok INTO @hallgato, @atlaga END CLOSE atlagok DEALLOCATE atlagok 1. oldal, összesen: 4 --4. Hallgatok szetosztasa terembe egy megadott targyhoz CREATE PROCEDURE SZETOSZTAS(@targy char(100)) AS DECLARE @hallgato char(100) DECLARE @joterem char(5) DECLARE hallgatok CURSOR FOR -- azok a hallgatok akik meg nincsenek teremhez rendelve ebbol a targybol SELECT hallgato FROM Tanulmany WHERE targy = @targy AND teremszam IS NULL OPEN hallgatok FETCH NEXT FROM hallgatok INTO @hallgato WHILE @@FETCH STATUS = 0 BEGIN SET @joterem = (SELECT TOP 1 azonosito FROM Terem -- az egyik olyan terem, WHERE targy = @targy AND -- ahol ezt a targyat tanitjak (SELECT count(*) FROM Tanulmany -- es van meg szabad hely WHERE targy = @targy AND teremszam = azonosito) < kapacitas) IF

@joterem IS NOT NULL -- ha van ilyen terem egyaltalan BEGIN UPDATE Tanulmany SET teremszam = @joterem WHERE CURRENT OF hallgatok END FETCH NEXT FROM hallgatok INTO @hallgato END CLOSE hallgatok DEALLOCATE hallgatok --1. A jegyek tablaba uj sor felvetele eseten, ha a megadott targyat a hallgato anyja tartja, -- akkor automatikusan beirodjon az 5-os jegy CREATE TRIGGER anyjaegybolotos ON Jegyek AFTER INSERT, UPDATE AS UPDATE Jegyek SET erdemjegy = 5 WHERE EXISTS ( SELECT hallgato FROM inserted -- a valtozott adatok JOIN Hallgato ON (inserted.hallgato = Hallgatonev) -- anyja miatt WHERE Jegyek.hallgato = insertedhallgato AND Jegyektargy = insertedtargy AND oktato = anyjaneve ) --2. Ha egy hallgatonak mar 5 egyese van es az atlaga nem jobb mint 1,5 akkor torlodjon az adatbazisbol CREATE TRIGGER sokegyes ON Jegyek AFTER INSERT, UPDATE AS DELETE FROM Hallgato WHERE nev IN ( SELECT DISTINCT hallgato FROM inserted a -- a sok egyesel es rossz atlaggal rendelkezo hallgatok WHERE (SELECT

count(*) FROM Jegyek b WHERE a.hallgato = bhallgato AND erdemjegy = 1) >= 5 AND (SELECT avg(cast(erdemjegy as float)) FROM Jegyek c WHERE a.hallgato = challgato) <= 15 ) 2. oldal, összesen: 4 DELETE FROM Jegyek -- hogy ne maradjanak logo sorok a Jegyek tablaban WHERE NOT EXISTS(SELECT nev FROM Hallgato WHERE Jegyek.hallgato = Hallgatonev) --3. Minden 20 egyes kiosztasa utan novekedjen az oktato fizetese 3000 Ft-al CREATE TRIGGER premium ON Jegyek AFTER INSERT, UPDATE AS UPDATE Oktato SET fizetes = fizetes + 3000 WHERE nev IN ( SELECT DISTINCT oktato FROM inserted a -- az egyest ado tanar(ok), akiknel igy epp 20-al oszthato lett az egyesek szama WHERE erdemjegy = 1 AND (SELECT count(*) FROM Jegyek b WHERE a.oktato = boktato AND erdemjegy = 1) % 20 = 0 ) --4. Egy oktatohoz nem jarhat 50-nel tobb hallgato (ne engedje a jelentkezest) CREATE TRIGGER max50a ON Jegyek AFTER INSERT, UPDATE AS IF EXISTS(SELECT oktato FROM inserted -- eleg csak azokat szamolni akiknel tortent

valtozas WHERE (SELECT count(*) FROM Jegyek WHERE Jegyek.oktato = insertedoktato) > 50) ROLLBACK -- 4.c Ha Nincs a felteleknek megfelelo oktato, toroljuk a hallgatot az adatbazisbol CREATE TRIGGER max50a ON Jegyek FOR INSERT, UPDATE AS DECLARE @hallgato char(100) DECLARE @targy char(50) DECLARE @tulterhelt char(100), @helyettes char(100) DECLARE @tulteher int WHILE EXISTS(SELECT oktato FROM inserted a -- addig menjen a ciklus, amig fennall a problema WHERE (SELECT count(*) FROM Jegyek b WHERE b.oktato = aoktato) > 50) BEGIN -- kivesszuk az egyik tulterhelt oktatot SET @tulterhelt = (SELECT TOP 1 oktato FROM inserted a WHERE (SELECT count(*) FROM Jegyek b WHERE b.oktato = aoktato) > 50) -- a tulterheltseg merteke SET @tulteher = (SELECT count(*) FROM Jegyek WHERE oktato = @tulterhelt) - 50 -- tanitott hallgatok DECLARE tanitja CURSOR FOR SELECT hallgato, targy FROM Jegyek 3. oldal, összesen: 4 WHERE oktato = @tulterhelt OPEN tanitja FETCH NEXT FROM tanitja INTO @hallgato,

@targy WHILE @tulteher > 0 AND @@FETCH STATUS = 0 BEGIN -- van ilyen targyat tanito nem tulterhelt oktato? SET @helyettes = (SELECT TOP 1 nev FROM Oktato x WHERE targy = @targy AND (SELECT count(*) FROM Jegyek y WHERE y.oktato = xnev) < 50 AND nev <> @tulterhelt) IF @helyettes IS NOT NULL BEGIN UPDATE jegyek SET oktato = @helyettes WHERE CURRENT OF tanitja -- helyettesitjuk SET @tulteher = @tulteher - 1 -- csokkentjuk a tulterheltseget END ELSE BEGIN DELETE FROM Jegyek WHERE CURRENT OF tanitja SET @tulteher = @tulteher - 1 -- csokkentjuk a tulterheltseget -- 4.b annyival egyszerubb, hogy itt feladjuk (ROLLBACK) -- kieg: ha egy tanar tobb targyat is tanit, akkor elkepzelheto, hogy nem talalunk epp ilyen targyat oktatot szabad -- kapacitassal, viszont talalunk olyat akinek valamelyik masik targyat at lehet rakni egy harmadikhoz, es igy a felszabadult -- helyre at lehet rakni a hallgatot -- de ennel mar egyszerubb torolni az osszes jelentkezest es ujra kiosztani az oktatokat

mindenkihez END IF @tulteher > 0 -- ha meg kell, a kovetkezo beolvasasa FETCH NEXT FROM tanitja INTO @hallgato, @targy END CLOSE tanitja DEALLOCATE tanitja END --5. Ha egy hallgato mar 3x veszi fel ugyanazt a targyat, automatikusan kapjon egy 1-est, statisztikailag valoszinu -- hogy ugyis azt fog kapni CREATE TRIGGER harmadszoregyes ON Jegyek FOR INSERT -- csak insertre AS UPDATE Jegyek SET erdemjegy = 1 -- adjunk osztonzest mindenkinek, aki WHERE hallgato IN (SELECT DISTINCT hallgato FROM inserted -- szerepel azon hallgatok kozt, akik most vettek fel egy targyat es WHERE (SELECT count(*) FROM Jegyek -- mar legalabb harmadszor vettek fel ezt a targyat WHERE Jegyek.hallgato = insertedhallgato AND Jegyektargy = insertedtargy) >= 3) AND erdemjegy IS NULL -- az eddigi jegyeket ne modositsuk 4. oldal, összesen: 4