Az I. 129. feladat (2006. március)

I. 129. Egy iskolában adatbázisban rögzítik a rendszergazdák teendőit. A iskola dolgozói és tanulói egy internetes űrlapon jelenthetik be a hibákat. A hibák az adatbázisba kerülnek, a feladatokat az informatikai munkaközösség egyik tagja rendeli a rendszergazdákhoz. A hibabejelentés egy kódot, a bejelentés dátumát, a hibás eszköz azonosítóját és a hiba rövid leírását tartalmazza. A bejelentés után dől el, melyik rendszergazda felelős a hiba elhárításáért. Ekkor állapítják meg a probléma megoldásának fontosságát is. Bejelentéskor a hiba státusza ,,bejelentve'', a hozzárendelés után ,,folyamatban'' és a probléma megoldásakor ,,megoldva''.

Az adatbázis a következő táblákat tartalmazza:

A táblák szerkezete:

 hiba(
   hibaaz : egész; a hibák azonosítója, elsődleges kulcs
   bdat   : dátum; a hibabejelentés dátuma (év-hónap-nap)
   hdat   : dátum; a hibát ekkor rendelték valamelyik rendszergazdához
            (év-hónap-nap)
   rgaz   : egész; a felelős rendszergazda azonosítója
   mdat   : dátum; a probléma megoldásának dátuma (év-hónap-nap)
   stat   : felsorolás; értéke 'bejelentve', 'folyamatban' és 'megoldva' lehet,
            a hiba státusza
   szgaz  : egész; a hibás eszköz kódja
   hibadef: szöveg; a hiba leírása
   prior  : felsorolás; értéke 'A','K' és 'M' lehet (alacsony, közepes, magas),
            a probléma fontosságát jelzi
 )
 rg(
   rgaz   : egész; a rendszergazda azonosítója, elsődleges kulcs
   neve   : szöveg; a rendszergazda neve
   tel    : szöveg; a rendszergazda telefonszáma
 )
 szg(
   szgaz  : egész; a gép azonosítója, elsődleges kulcs
   hely   : egész; a gép szobaszáma
   tip    : felsorolás; értéke 'pc', 'szerver' és 'printer' lehet,
            az eszköz típusa
 )

A következő feladatokat SQL parancsok segítségével kell megoldani. Az első nyolc kérdéshez SQL lekérdezéseket kell írni (,,SELECT''), az utolsó kettőhöz pedig módosító (,,UPDATE'') és törlő (,,DELETE'') parancsot.

Soroljuk fel ,,Kiss Béla'' megoldatlan feladatait!

Hány olyan probléma van, amely több mint 30 napja nincs megoldva?

Melyik szobákban vannak megoldandó feladatok?

Melyik probléma megoldása tartott legtovább? (A bejelentéstől számolva.)

Hány hibabejelentés vonatkozik nyomtatóra?

Listázzuk rendszergazdánként a megoldatlan feladatok számát!

Írjuk ki azon rendszergazdák nevét, akiknek van megoldatlan magas prioritású feladata!

Melyik a legrégebben bejelentett megoldatlan probléma?

Módosítsuk a megoldatlan problémák fontosságát magasra!

Töröljük az adatbázisból a 2005-ben megoldott hibákat!

Segítségül, a KöMaL szerverén létrehoztunk egy példa adatbázist, amely a http://www.komal.hu/i129/ címen elérhető. Lehetőség van SQL lekérdezések futtatására, így az első 8 kérdés tesztelésére is.

Beküldendő a 10 SQL parancs egy egyszerű szöveg fájlban (i129.txt).

(10 pont)

A beküldési határidő 2006. április 18-án LEJÁRT.

Megoldás. A MYSQL 3.23 címkéjű parancsok a Kömal honlapján található teszt adatbázison is lefutnak. A 4.01-es változatban már lehetőség van allekérdezések (subquery) és újabb dátumkezelő függvények használatára, illetve a táblák belső összekapcsolása a FROM záradékban is definiálható.

1. Soroljuk fel 'Kiss Béla' megoldatlan feladatait!

SELECT szg.szgaz, hely, hibadef
FROM hiba,rg,szg
WHERE rg.rgaz = hiba.rgaz
AND szg.szgaz = hiba.szgaz
AND stat = 'folyamatban'
AND neve = 'Kiss Béla'

== MYSQL 4.01 ==

SELECT hiba.*
FROM hiba INNER JOIN rg ON hiba.rgaz = rg.rgaz
WHERE rg.neve='Kiss Béla'
AND stat<>'megoldva'

2. Hány olyan probléma van, amely több mint 30 napja nincs megoldva?

== MYSQL 3.23 ==

SELECT COUNT(*)
FROM hiba
WHERE bdat'megoldva'

vagy:

SELECT COUNT(h.hibaaz)
FROM hiba h 
WHERE h.stat!='megoldva ' AND CURDATE()-h.bdat>30

vagy:

SELECT COUNT(*) Regiek
FROM hiba
WHERE CURRENT_DATE - bdat > 30
AND stat <> 'megoldva'

== MYSQL 4.01 ==

SELECT COUNT(*)
FROM hiba
WHERE stat <> 'megoldva'
AND DATEDIFF(NOW(),bdat)>30

3. Melyik szobákban vannak megoldandó feladatok?

== MYSQL 3.23 ==

SELECT DISTINCT hely
FROM hiba, szg
WHERE hiba.szgaz = szg.szgaz
AND (stat = 'bejelentve' OR stat = 'folyamatban')

== MYSQL 4.01 ==

SELECT szg.hely
FROM szg INNER JOIN hiba ON szg.szgaz = hiba.szgaz
WHERE hiba.stat <>'megoldva'
GROUP BY szg.hely

4. Melyik probléma megoldása tartott legtovább? (A bejelentéstől számolva.)

== MYSQL 3.23 ==

SELECT *, TO_DAYS(mdat)-TO_DAYS(bdat)
FROM hiba
WHERE stat='megoldva'
ORDER BY TO_DAYS(mdat)-TO_DAYS(bdat) DESC
LIMIT 1

== MYSQL 4.01 ==

SELECT *
FROM hiba
WHERE DATEDIFF(mdat,bdat) =
 ( SELECT MAX(DATEDIFF(mdat,bdat))
   FROM hiba
   WHERE stat = 'megoldva' )

5. Hány hibabejelentés vonatkozik nyomtatóra?

== MYSQL 3.23 ==

SELECT COUNT(*)
FROM hiba,szg
WHERE hiba.szgaz = szg.szgaz
AND szg.tip='printer'

== MYSQL 4.01 ==

SELECT Count(*)
FROM hiba INNER JOIN szg ON hiba.szgaz = szg.szgaz
WHERE szg.tip='printer '

6. Listázzuk rendszergazdánként a megoldatlan feladatok számát!

== MYSQL 3.23 ==

SELECT neve, COUNT( * )
FROM hiba, rg
WHERE hiba.rgaz = rg.rgaz
AND stat <> 'megoldva'
GROUP BY rg.rgaz

== MYSQL 4.01 ==

SELECT neve, (
    SELECT COUNT(*)
    FROM hiba
    WHERE hiba.rgaz=rg.rgaz
    AND stat<>'megoldva')
FROM rg

7. Írjuk ki azon rendszergazdák nevét, akiknek van megoldatlan magas prioritású feladata!

== MYSQL 3.23 ==

SELECT DISTINCT rg.neve
FROM rg, hiba
WHERE rg.rgaz = hiba.rgaz
AND prior='M'
AND stat <> 'megoldva'

== MYSQL 4.01 ==

SELECT neve
FROM rg
WHERE rg.rgaz
IN
 (
   SELECT rgaz
   FROM hiba
   WHERE prior = 'M'
   AND stat <> 'megoldva')

8. Melyik a legrégebben bejelentett megoldatlan probléma?

== MYSQL 3.23 ==

SELECT *
FROM hiba
WHERE stat!='megoldva'
ORDER BY bdat
LIMIT 1

== MYSQL 4.01 ==

SELECT *
FROM hiba
WHERE stat <> 'megoldva'
AND bdat =
 (
   SELECT MIN( bdat )
   FROM hiba
   WHERE stat <> 'megoldva')

9. Módosítsuk a megoldatlan problémák fontosságát magasra!

== MYSQL 3.23 és 4.01 ==

UPDATE hiba
SET prior = 'M'
WHERE stat <> 'megoldva'

10. Töröljük az adatbázisból a 2005-ben megoldott hibákat!

== MYSQL 3.23 és 4.01 ==

DELETE FROM hiba
WHERE YEAR(mdat) = 2005 AND stat = 'megoldva'

vagy:

DELETE FROM hiba
WHERE (mdat BETWEEN '2005-01-01' AND '2005-12-31') AND stat='megoldva'

(Gombos Gergely, Kiss Dániel Miklós, Ozsvárt László, Véges Márton és Vincze János dolgozata alapján)

Statisztika:

16 dolgozat érkezett.
10 pontot kapott:	Balambér Dávid, Czigler András, Gilián Zoltán, Gombos Gergely, Kiss Dániel Miklós, Véges Márton, Vincze János.
8 pontot kapott:	5 versenyző.
7 pontot kapott:	1 versenyző.
5 pontot kapott:	1 versenyző.
3 pontot kapott:	1 versenyző.
1 pontot kapott:	1 versenyző.

A KöMaL 2006. márciusi informatika feladatai