Problem I. 347. (April 2014)
I. 347. A certain table tennis club organized a Table Tennis Doubles Tournament of Luck over the weekend with the following rules. Each participant draws a number at the beginning, and during the tournament they are assigned to pairs according to these numbers. Both pairs of a turn are drawn just before the start of the play. A player and their actual partner always play two sets (not necessarily two won sets) with their randomly selected opponents. A player cannot play twice with the same teammate and against the same opponents. The whole tournament was based on luck: if a player had luck, they could play with a strong partner. If not, they could also win, since there are more opportunities in a doubles game. During the game, both teammates score a point for each won service, irrespective of whether they won the game or not. A player's points were simply added at the end of the tournament. The champion is who scored the most points. Due to space and time limitations, the total number of players was limited to 44, and finally every player played 7 games. A set was played until 11 points were won.
1. Create a new database named lutri to evaluate the game results. The three given data tables (jatekos.txt, par.txt, merkozes.txt) should be imported into the database by using the file names as table names (jatekos, par, merkozes, meaning ``player'', ``pair'' and ``game''). These files are UTF-8 encoded and tabulator-separated text files, with their first lines containing the field names. After creating the tables, the appropriate types should be set, and the appropriate fields should be marked as keys.
Tables:
Queries in the following tasks should be saved by using the names given in parentheses. Your solution should contain only the requested fields or expressions, and no others. In the solution we can assume that each player has a different name.
2. Create a query to display first the names of the female players, then those of the male players, sorted according to their birth years. The list should contain the names and the birth years. Players having the same birth year should be sored alphabetically. 2nevezok)
3. Make a query to determine the number of games in which the same pair won both sets. (3erosebb)
4. Create a report to list the player named Forrai Laura and her partners for each game. The report should be prepared by using a query. (4forraipartnerei)
5. By using a query, display the members of the pairs who won one of their sets with 0 points. (5tuleros)
6. By using a query, give the points for each game of the oldest player. (6legidosebb)
7. By using a query, determine the first 10 winners and their number of points. (7elso10)
8. List by a query for each player the number of sets won. In the list, the names and the number of won sets should appear in a decreasing order according to this last quantity. (8jatszmagyoztes)
The database (lutri.odb, lutri.mdb) and a short documentation (i347.txt, i347.pdf) should be submitted, also specifying the name and version number of the database manager, in a compressed file (i347.zip). The data tables to be imported into the database can be downloaded from our web page: jatekos.txt, par.txt and merkozes.txt
Downloadable files:
(10 pont)
Deadline expired on May 12, 2014.
Sorry, the solution is available only in Hungarian. Google translation
Minta megoldás:
Kovács Balázs Marcell 11. osztályos tanuló (ELTE Radnóti Miklós Gyakorló Iskola, Budapest) megoldása: lutri.odb
Külön érdemes kiemelni az 5. feladat kétféle megoldását.
A feladat: Lekérdezés segítségével jelenítsük meg azon párosok tagjainak nevével, akik valamelyik szettjüket 0-ra nyerték!
1. megoldás
SELECT j1.nev, j2.nev
FROM par, jatekos AS j1, jatekos AS j2
WHERE j1.id=par.j1id and j2.id=par.j2id and
par.id in (SELECT iif((szett12=0) or (szett22=0),par1,iif((szett11=0) or (szett21=0),par2,0))
FROM merkozes
WHERE szett11=0 or szett21=0 or szett12=0 or szett22=0);
2. megoldás
(SELECT j1.nev, j2.nev
FROM merkozes, par, jatekos AS j1, jatekos AS j2
WHERE merkozes.par1=par.id and par.j1id=j1.id and par.j2id=j2.id and [szett22]*[szett12]=0)
UNION (SELECT j1.nev, j2.nev
FROM merkozes, par, jatekos AS j1, jatekos AS j2
WHERE merkozes.par2=par.id and par.j1id=j1.id and par.j2id=j2.id and [szett11]*[szett21]=0);
Statistics:
10 students sent a solution. 10 points: Kovács Balázs Marcell. 8 points: 1 student. 7 points: 1 student. 6 points: 3 students. 3 points: 3 students. Unfair, not evaluated: 1 solutions.
Problems in Information Technology of KöMaL, April 2014