KöMaL - Középiskolai Matematikai és Fizikai Lapok
 English
Információ
A lap
Pontverseny
Cikkek
Hírek
Fórum

Rendelje meg a KöMaL-t!

ELTE

VersenyVizsga portál

Kísérletek.hu

Matematika oktatási portál

I. 129. The tasks of system administrators of a school are stored in a database. There is an online form to report errors. These reports are then saved in the database and allocated to a sysadmin. The error report consists of an ID code, the actual date, the ID of the faulty device and a short description of the error. Only after an error has been reported is it allocated to a sysadmin. The priority of the problem is also determined then. The status of a problem first is ``reported'', after the allocation is ``processed'', finally it is labelled ``resolved''.

The database contains the following tables:

error list of errors reported
dev list of devices in the school
sysadmin the system administrators

with the following structure:


error(
errorID : integer; ID of the error, primary key
repdat : date; the report is reported at (month-day-year)
allocdat : date; the error is allocated to a sysadmin at (month-day-year)
sysadmin : integer; ID of the responsible sysadmin
soldat : date; the problem is resolved at (month-day-year)
stat : list; with values "reported", "processed" and "resolved",
status of the error
devID : integer; ID of the faulty device
errordesc : text; description of the error
prior : list; its value can be "L","M" and "H" (low, medium, high),
priority of the problem
)
sysadmin(
sysadmin : integer; ID of the sysadmin, primary key
name : text; name of sysadmin
phone : text; phone number of sysadmin
)
dev(
devID : integer; ID of the machine, primary key
loc : integer; location of the machine, room number
type : list; with value "PC", "SERVER" or "PRINTER",
type of the device
)

Your task is to answer the following questions using SQL. For the first 8 questions, an SQL ``SELECT'' should be used, while an ``UPDATE'' and ``DELETE'' command for the last 2 questions.

  • List the unaccomplished tasks of ``John Smith''.

  • How many tasks are still left undone for more than 30 days?

  • Which rooms contain tasks to be done?

  • Which problem required the longest time to resolve? (Since being reported.)

  • How many reports are there concerning printers?

  • List the number of unaccomplished tasks for each sysadmin.

  • List sysadmins having a high priority unresolved task.

  • Which is the oldest reported problem still unresolved?

  • Set the priority of unresolved problems HIGH.

  • Delete problems that were resolved in 2005 from the database.

    To help you, we maintain a sample database on the KöMaL server at http://www.komal.hu/i129. You can run SQL queries, hence test the first 8 questions.

    The 10 SQL commands are to be submitted in a plain text file (i129.txt).

    (10 points)

    Deadline expired on 18 April 2006.


    Google Translation (Sorry, the solution is published in Hungarian only.)

    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)


    Statistics on problem I. 129.
    16 students sent a solution.
    10 points: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 points:5 students.
    7 points:1 student.
    5 points:1 student.
    3 points:1 student.
    1 point:1 student.


  • Problems in Information Technology of KöMaL, March 2006

  • Támogatóink:   Ericsson   Cognex   Emberi Erőforrás Támogatáskezelő   Emberi Erőforrások Minisztériuma   Nemzeti Tehetség Program    
    MTA Energiatudományi Kutatóközpont   MTA Wigner Fizikai Kutatóközpont     Nemzeti
Kulturális Alap   ELTE   Morgan Stanley