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:
|
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.
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>30vagy:
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