Problem I. 132. (April 2006)
I. 132. An SQL database is used to maintain data of a secondary school contest of informatics. The results are also published on the Internet, where answers to the following questions are available.
- Display for each problem its number, text and the maximal number points that can be earned.
- Compute the average number of points earned by the contestants.
- List the distribution of points for Problem 4, similarly to the statistics of KöMaL problems.
- List the points earned by ``John Smith'' for each problem.
- Determine the number of contestants from each school.
- List contestants from a particular secondary school.
- Make the final table of results containing the name of contestants, their schools, classes, total number of points awarded. They should be ranked in descending order according to awarded points. (If two numbers are equal, list alphabetically.)
Tables of the database are to be constructed in such a way that one SQL query should be enough in each case. You should also give these queries.
The definition of the database tables and the seven SQL queries should be submitted in plain text (i132.sql, i132.txt).
Deadline expired on May 15, 2006.