Problem I. 386. (November 2015)
I. 386. A certain school in Hungary bears the name of the Hungarian poet Gyula Juhász (1883-1937). Since the beginning of the 2011 school year they select a poem of Juhász every day and display it on the webpage of the school. Visitors can ``like'' these poems. The database contains data from three consecutive school years.
Create a new database jgy. The two, tabulator-separated and UTF-8 encoded text files (vers.txt, napverse.txt) should be imported into the database by using their original names (vers, napverse). The first line of the file contains the field names. The appropriate types and keys should be set.
You should solve the following tasks and save your queries by using the names in parentheses. Your solution should contain only the requested fields.
1. List all poems that appeared on the webpage in September 2011 and sort them according to their dates. Display the poem title and creation year. (2szept)
2. Consider the poems which appeared more than once on the webpage, and determine which three got the most ``likes''. (3like)
3. Create a report to list the poems that appeared on the webpage during the winter of 2013/2014 (between December and February), grouped according to their creation year and sorted alphabetically. (4tel)
4. Determine which poems appeared on the webpage in all four calendar years. (5negyev)
5. Determine which poems appeared on the webpage only in 2011. (6csak2011)
6. Determine which days had poems with creation year identical to the creation year of the poem of the previous day. (7azonosev)
7. Determine whether Juhász created more poems in his first or last active decade. The first year of the more active decade should be displayed. (8evtized)
Your solution as a database or a text file with the SQL queries should be submitted in a compressed file (i386.zip), also containing a short documentation with the name and version number of the database application.
Downloadable files: vers.txt , napverse.txt.
Deadline expired on 10 December 2015.