================================================================================================
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
``Olympics'' table. This kind of a statement is usually unique to the client,
and is not part of standard SQL.

This statement basically extracts information from one of the "system catalog" 
tables. Find out which system catalog table contains the relevant information.
http://www.postgresql.org/docs/current/static/catalogs.html

------------------------------------------------------------------------------------------------
<YOUR QUERY HERE>
------------------------------------------------------------------------------------------------
<YOUR RESULT HERE>

================================================================================================
2. Team events are going to complicate our life, so we will create two new
tables:  
    IndividualMedals(player_id, country_id, event_id, medal, result)
    TeamMedals(country_id, event_id, medal, result)
Write the queries to create these two tables using the Results table. Use the
``create table ... as'' command.  The TeamMedals table should only contain one
entry for each country for each team event. Fortunately for us, two teams from
the same country can't compete in a team event. The information about whether an
event is a team event is stored in the ``events'' table.

------------------------------------------------------------------------------------------------
<YOUR QUERY HERE>
------------------------------------------------------------------------------------------------
<YOUR RESULT HERE>

================================================================================================
3. For 2000 Olympics, find the 5 countries with the largest values of
``number-of-team-medals/area_sqkm''. Don't use rank() or limit or any other shortcut.
Hint: Use ``WITH''.

------------------------------------------------------------------------------------------------
<YOUR QUERY HERE>
------------------------------------------------------------------------------------------------
<YOUR RESULT HERE>

================================================================================================
4. For 2004 Olympics, generate a list - (birthyear, num-players, num-gold-medals) - 
containing the years in which the atheletes were born, the number of players
born in each of those years who won at least one gold medal, and the number of gold medals 
won by the players born in that year.

------------------------------------------------------------------------------------------------
<YOUR QUERY HERE>
------------------------------------------------------------------------------------------------
<YOUR RESULT HERE>

================================================================================================
5. Report all `individual events' where there was a tie in the score, and two or
more players got awarded a Gold medal. The 'Events' table contains information
about whether an event is individual or not (Hint: Use ``group by'' and
``having'').

------------------------------------------------------------------------------------------------
<YOUR QUERY HERE>
------------------------------------------------------------------------------------------------
<YOUR RESULT HERE>

================================================================================================
6. Find all athletes who won at least one of each medal (GOLD, SILVER and
BRONZE) at a single Olympics. Report as -- "(player-name, olympic-id)".

------------------------------------------------------------------------------------------------
<YOUR QUERY HERE>
------------------------------------------------------------------------------------------------
<YOUR RESULT HERE>

================================================================================================

7.  Write a "trigger" to keep the IndividualMedals table updated when a new entry is
added to the Results table (don't do anything if the new entry refers to a team
event). See here for detailed trigger documentation:
    http://www.postgresql.org/docs/current/static/plpgsql-trigger.html
Database systems tend to be very picky about the trigger syntax, so be careful.

------------------------------------------------------------------------------------------------
<YOUR QUERY HERE>
------------------------------------------------------------------------------------------------
<YOUR RESULT HERE>

================================================================================================
8. Some tasks are hard to write as SQL queries. For example, say you wanted to create a list
of all "gold medalists" in ATH2004, output in XML format as:
<country>
    <name>USA</name>
    <medal>
        <event>Event1</event>      
        <player>Player1</player>  
    </medal>
    <medal>
        <event>Event2</event>      
        <players>
            <player>Player2</player>
            <player>Player3</player>
            <player>Player4</player>
        </players>
    </medal>
    ...
</country>
<country>
    ...
</country>

This would not be easy to do in SQL.  However, this is much easier to do in an embedded language
like PL/pgSQL.  Documentation for this is at: 
    http://www.postgresql.org/docs/current/interactive/plpgsql.html

You are to write a procedure in PL/pgSQL to do the above task. Use "array_agg" to simplify the
"grouping". For simplicity, focus only on USA, and output only the rest of the part for USA, i.e.,:

<medal>
    <event>Event1</event>      
    <player>Player1</player>  
</medal>
<medal>
    <event>Event2</event>      
    <players>
        <player>Player2</player>
        <player>Player3</player>
        <player>Player4</player>
    </players>
</medal>

------------------------------------------------------------------------------------------------
<YOUR QUERY HERE>
------------------------------------------------------------------------------------------------
<YOUR RESULT HERE>

================================================================================================
