================================================================================================ 1. Figure out how to find out all the attributes and their types for a given table in ``psql'', and write down the statement and its result on the Teams table. This kind of a statement is usually unique to the client, and is not part of standard SQL. ------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------ ================================================================================================ 2. Count the total number of teams that used only one goalkeeper throughout the tournament. ------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------ ================================================================================================ 3. Find the ``best'' goalkeeper, defined to be the goalkeeper with the highest saves to goals-against ratio, with at least 10 saves. ------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------ ================================================================================================ 4. Write a query to find out if any teams that were in the same ``group'' met during the postseason. Use the fact that the group play ended on June 23, 2006. ------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------ ================================================================================================ 5. List the top 10 players with the highest totalminutes played, along with their rank. The output would have schema: (name, totalminutes, rank). The player with the highest totalminutes will have rank = 1, player with next highest will have rank = 2 etc. If there is a tie, then both players must get the same rank, and the next rank must be skipped. E.g., if two players tie for the highest totalminutes, then they both get rank 1, and there is no player with rank 2. ------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------ ================================================================================================ 6. The results table is not in a nice format to answer many queries (such as who won or who lost). Write a query to create a table: WinLossKO(winner, loser, matchdate) using the Results table, for matches that took place in Stage 2 (the ``knockout round''). The knockout round started on June 24, 2006. ------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------ ================================================================================================ 7. Write a {\bf trigger} to keep the WinLossKO table updated when a new entry is added to the Results table. Remember that the new tuple added to the Result table may or may not be from the knockout round, so you must check for that (i.e., you shouldn't add those tuples to the WinLossKO table). See here for detailed trigger documentation: http://www.postgresql.org/docs/9.0/static/plpgsql-trigger.html Database systems tend to be very picky about the trigger syntax, so be careful. ------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------ ================================================================================================ 8. Write a query to find the number of points for each team during the ``group play''. A team gets 3 points for a win, 1 point for a tie, and none for a loss. The output should be simply a list of team names, along with their points. Note that this is to be computed only for matches played on or before June 23, 2006. Verify against the webpage to confirm your answers. http://fifaworldcup.yahoo.com/06/en/w/group/index.html This is a somewhat harder problem, and you should use temporary tables (i.e., WITH) liberally. ------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------ ================================================================================================ 9. Some tasks are hard to write as SQL queries. For example, consider the above task of computing the total points in the group play, and (taking it further) finding the top 2 in each group and printing out the first set of knockout round matches. However, this is much easier to do in an embedded language like PL/pgSQL. Documentation for this is at: http://www.postgresql.org/docs/9.0/interactive/plpgsql.html You are to write a procedure in PL/pgSQL to do the above task. Specifically, the output should be the list of countries, and the number of points they accumulated during group play. ------------------------------------------------------------------------------------------------ ------------------------------------------------------------------------------------------------ ================================================================================================