Problem I. 275. (October 2011)

I. 275. The file hatar.txt contains some data on the border stations of Hungary and its neighboring countries.

1. Create a new database with name allomasok (stations). Import the data table into the database with name hatar (border). Field names are contained in the first line of the tabulator separated txt data file in UTF-8 encoding.

2. After importing, set the appropriate data formats and the key. Add a unique identifier with name az to the table.

Table:

Now solve the following task. When a query is answered, no other data, only the requested results should appear. Queries should be named as indicated in the parentheses.

3. By using a query, list in alphabetical order the name of the Hungarian settlements of the border railway stations to Slovakia. (3szlovak)

4. List the name of those neighboring countries toward which there are less than 3 border railway stations. (4keves)

5. Make a query that gives the number of railway, highway and planned border stations. Based on this query, create a report to group these results according to countries. Adjust the column width properly in the report such that all data should be visible but columns should not be too wide. The title of the query should be ``Átkel őhelyek száma'' (number of border stations). (5osszesites)

6. Make a query to list all data of those border stations where the name of one of the two settlements contains the name of the other. (6hasonlok)

7. List by a query the name of those Hungarian settlements where there are both railway and highway border stations. The list should contain each settlement's name only once and in alphabetical order. (7kozutesvasut)

8. List by a query the name of those Hungarian settlements having joint border stations with more neighboring settlements. Each name should appear once in this list. (8tobb)

9. Make a form according to table hatar as in the example. Create two control buttons to scroll the records. (9urlap)

10. By using a query, and as in the example, list by countries the number of railway and highway border stations. (10tabla) In the example, ``orszag'' is country, ``vasúti határátkelők'' is ``border railway stations'', while ``közúti átkelők`` is ``highway stations''.

The database (i275.accdb, i275.odb, ...) together with a short documentation (i275.txt, i275.pdf) - also containing the name and version number of the database application - should be submitted in a compressed file (i275.zip).

(10 pont)

Deadline expired on November 10, 2011.

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

Megoldásokról A Határátkelők című érettségi típusú feladat, amely egytáblás, meglepő módon több problémát jelentett a versenyzőknek. Igaz, egyes feladatok megoldási módszere túlmutat az érettségi szintjén. Három 100

Típushibának tekinthetjük, hogy többen a 6hasonlok lekérdezésben a csak az egyik nevet keresték a másikban, pedig a másik is lehet az elsőben. Az összehasonlításhoz a nevekhez elölről és hátulról * jelet kellett fűzni, vagy az InStr() függvényt lehetett használni.

vagy

A 7kozutesvasut és a 8tobb megoldását többen al- vagy segéd-lekérdezésekkel készíttették el. Elegánsabb megoldást hozhatunk létre, ha a táblát két példányban vesszük fel.

Mintamegoldásként Kucsma Levente István (Eger, Dobó István Gimnázium, 9. osztály) megoldását közöljük: allomasok.mdb

A feladatok és a lekérdezések SQL kódja: i275ertekeles.pdf

Statistics:

11 students sent a solution.
10 points:	Adrián Patrik, Gema Barnabás, Kucsma Levente István.
9 points:	Antal János Benjamin, Oravecz Márton.
8 points:	4 students.
7 points:	2 students.

Problems in Information Technology of KöMaL, October 2011