Problem I. 317. (March 2013)
I. 317. The Hungarian Central Statistical Office (abbreviated as KSH in Hungarian) publishes some basic data on settlements in Hungary each year. In our exercise I305, you had to create a database based on their report made available on January 1, 2011. Queries in the current exercise should use the same data. We again remark that it contains data for the capital city as well, however, not as a whole city, rather than data for each of its districts.
1. You should create an empty database i317. You should import data into the tables helyseg, kisterseg, megye, megyeresz, onkormanyzat, telepulestipus, kisebbseg from the text files with similar names. (The text files are UTF-8 encoded and tabulator separated with first lines containing the field identifiers.) The relations among the tables are shown in the figure.
Tables:
2. Create a query to give those cities with county rights that are not county seats, together with their county names. (2megyeijoguvarosok)
3. Create a query to list the district of the capital city having the smallest number of minority governments, and how many. (3keveskisebbseg)
4. Give the name, region name and county name of those settlements that contain the string ``magyar'' (= Hungarian) somewhere in their name. (4magyarok)
5. By creating a query, give the name, county name and the number of their settlements together with their total population of the regions that have more than 20 settlements. The list should be sorted according to the county names. (5nagykistersegek)
6. List by a query which minority has no local government in any settlement in county Heves. (6hevesbennincs)
7. How many percent of people in Hungary live in cities? Besides cities, you should also take into account people living in cities with county rights, in county seats, moreover, in districts of the capital city. (7varoslakok)
8. Give by a query the number of cities in each county, and the number of districts in the capital city. Again, besides cities themselves, cities with county rights and county seats should be counted here as well. (8varosszam)
9. For each county, determine the average number of persons living in one household in each of the different settlement types. (9lakok)
10. Give the name of those counties in which more than 50% of the people live in villages. (10falusitobbseg)
The database (i317.mdb, i317.accdb, i317.odb, ...) together with a short documentation (i317.txt, i317.pdf, ...) - also containing the name and version number of the database application - should be submitted.
(10 pont)
Deadline expired on April 10, 2013.
Sorry, the solution is available only in Hungarian. Google translation
A sok hibátlan megoldás közül kiemelném Gema Barnabás (Veszprém, Lovassy László Gimnázium) munkáját, neki még a lekérdezések eredményének érthetőségét is volt ideje fokozni. Mindenkinek kellemes nyarat kívánok!
Statistics:
8 students sent a solution. 10 points: Fényes Balázs, Gema Barnabás, Láposi Viktória, Qian Lívia, Szőts Dávid István. 9 points: Németh 017 András. 7 points: 1 student. 6 points: 1 student.
Problems in Information Technology of KöMaL, March 2013