Problem I. 383. (October 2015)

I. 383. Data about some mining regions with license from the Hungarian authorities are available in the files telek.txt, banya.txt and nyersanyag.txt. The files are tabulator-separated, UTF-8 encoded text files with the first lines containing the field names.

Create a new database i383. First import the above data files into this database by using their original names. Upon importing, you should set the appropriate data formats and keys; the tables should not have any new fields.

Tables:

You should solve the following tasks. For each query, only the requested values should appear and no other data. Your solutions should be saved by using the names specified in the parentheses.

1. By using a query, list all settlements where they discontinued underground mining. A settlement name cannot appear more than once in your list. (3bezart)

2. Which mining site has the thickest layer of coal? Display the settlement name and the layer thickness. (4sokszen)

3. For each rock mine determine, by using a query, what other raw materials are or were extracted. The list should not contain ``rock'' itself. (5kavics)

4. Determine the active mines in which there is extractable raw material between 400 and 500 meters above sea level. The list should contain the settlement of the mine and the name of the raw material. (6magas)

5. Various hydrocarbons\(\displaystyle -\)even in different state-of-matter forms\(\displaystyle -\)are likely to occur together. By using a query, list the settlement names where both oil and natural gas are extracted. The list should contain the settlement names and the number of mining sites with the above property. (7szenhidrogen)

6. To maintain the database, one needs to have data on the elevation of the top and bottom layers of the raw material. Create a report listing the sites with at least one missing piece of data. In the report you should highlight the settlement name, give the site identifiers for each mine, the name of the raw material extracted, and\(\displaystyle -\)if known\(\displaystyle -\)the elevation values for the topmost and bottommost layers. You should use a query or an auxiliary table to prepare the report. In your report, you should take into account the order of the fields, the title, and the way the field names are displayed in the sample. The format of your report can be different from that of the sample. (8hiany)

7. Surface mining or surface and underground mining can vastly change the landscape. By using a query determine the number of settlements having any of these mining methods. (9tajrombolas)

The database with a short documentation (together with the name and version number of the database application) should be submitted in a compressed file i383.zip. Downloadable files: nyersanyag.txt, banya.txt, telek.txt (banyak.zip).

(10 pont)

Deadline expired on November 10, 2015.

Statistics:

9 students sent a solution.
10 points:	Olexó Gergely.
9 points:	Kelkó Balázs, Szakali Benedek, Szemerédi Levente.
8 points:	2 students.
7 points:	2 students.
2 points:	1 student.

Problems in Information Technology of KöMaL, October 2015