Problem I. 395. (February 2016)
I. 395. (É). The files lemeztar.txt, ismerteto.txt and skala.txt contain data about some old LP jazz records of a commercial company. The field names are contained in the first lines of the tab-separated and UTF8-encoded text files.
1. Create a new database i395. The above data files (downloadable from our web page) should be imported into the database by using the same names.
2. The appropriate data formats and keys should be set upon importing. The table ismerteto should contain the key field, but no other fields should appear in the other tables.
Tables:
Now solve the following tasks. At each query below, only the requested values should appear. Your solutions should be saved by using the names in parentheses.
3. By using a query, list all data corresponding to LP records created in East Germany (country code GDR, before the German reunification). Sort the list into descending order according to price. (3ndk)
4. By using a query, give the country codes of the 3 countries producing the most LP records bewtween 1960 and 1970 inclusive. (4soklemez)
5. Unopened LP records are the most valuable ones. By using a query, list the performer, title and price of the records whose cover condition is still unopened (,,bontatlan''). (5bontatlan)
6. By using a query, create a report about the solo albums of the jazz pianist Oscar Peterson. The report should contain the price, title, publication year and the (English form of the) condition of the LP record. Follow the sample when creating column headings. (6peterson)
7. For a long time it was difficult to publish records in Hungary (country code H) and abroad at the same time. By using a query, list those performers who published records both in Hungary and abroad. The list should contain each name only once. (7nemzetkozi)
8. There are jazz concert recordings that were published in many countries. By using a query, list all data of the LP records having the same performer names, title and publication year, but were published in different countries. The list should contain each LP record only once. (8tobb)
The database with a short documentation (containing the name and version number of the database application) should be submitted in a compressed file i395.zip.
Downloadable files: lemeztar.txt, ismerteto.txt, skala.txt.
(10 pont)
Deadline expired on March 10, 2016.
Statistics:
17 students sent a solution. 10 points: Kovács 246 Benedek, Nagy Ábel, Olexó Gergely, Radnai Bálint. 9 points: Németh 729 Gábor, Szemerédi Levente. 8 points: 2 students. 5 points: 2 students. 4 points: 4 students. 3 points: 3 students.
Problems in Information Technology of KöMaL, February 2016