Problem I. 129. (March 2006)

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 pont)

Deadline expired on April 18, 2006.

Sorry, the solution is available only in Hungarian. Google translation

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:

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

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.

Already signed up?
New to KöMaL?