Problem I. 207. (February 2009)

I. 207. Thanks to the successes of the national team, ice hockey is gaining popularity in Hungary. From the webpage of the Hungarian Ice Hockey Federation, you can download results of the matches in 2008 (www.icehockey.hu). (The file jeghoki.csv on our server also contains these data.)

1. Create a database named i207. The above table containing data about the matches should be imported as ``match'' into the database. The source file is a semicolon-separated text file.

2. After importing, the appropriate data format and key should be set.

Table: match (id, date, opponent, type, city, country, scoreh, scoreo)

You should create queries to solve the following problems. Try to implement each task by using a single SQL query (or an equivalent reformulation).

3. Determine the date when the Hungarian team won with the biggest difference in scores. Create a query to get the date, the name of the opponent and the result. If there is more than one answer, you can give any or all of them. (3winning)

4. Create a query to determine how many times the Hungarian team won in consecutive years. (4years)

5. Create a query to list when a given opponent was first defeated by the Hungarian team. (5first)

6. Create a query to list those opponents that were never defeated by the Hungarian team. Each opponent should be listed only once. (6notwon)

7. As for football, Austria was the most common opponent for Hungary. Is the situation similar in ice hockey? Make a query to list those opponents that were more common opponents than Austria. (7austria)

8. Create a single query to determine the number of matches in which the Hungarian team won, achieved a draw or lose. (8stat)

9. Create a query to list the matches played against Romania in chronological order. The first column is the number of the match, the next column is the date, then the city follows, then points scored by the Hungarian team, finally, points scored by the opponent are listed. (9romania)

The following example shows the first few lines of query 9romania.

The database (i207.odb, i207.mdb) or a text document (i207.txt, i207.pdf, ...) should be submitted, containing the creation of the table and SQL codes of the queries (arranged neatly), together with a short documentation (i207dok.txt, i207dok.pdf, ...), also describing the name and version number of the database application.

(10 pont)

Deadline expired on March 16, 2009.

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

Megoldás:

3gyozelem:

4evenkent:

5elso:

6nyeretlen:

7ausztria:

8stat:

9romania:

Statistics:

11 students sent a solution.
10 points:	Szabó 928 Attila.
9 points:	Balla Attila, Kővágó Zoltán, Tóth Szabolcs.
8 points:	2 students.
7 points:	1 student.
6 points:	2 students.
3 points:	1 student.
2 points:	1 student.

Problems in Information Technology of KöMaL, February 2009