================================================================================================
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.

------------------------------------------------------------------------------------------------
\d teams
------------------------------------------------------------------------------------------------
<YOUR RESULT HERE>

================================================================================================
2. Count the total number of teams that used only one goalkeeper throughout the tournament.

------------------------------------------------------------------------------------------------
select t.name
from GoalKeepers g, Players p, Teams t
where g.name = p.name and t.teamid = p.teamid
group by t.name 
having count(*) = 1;
------------------------------------------------------------------------------------------------
<YOUR RESULT HERE>

================================================================================================
3. Find the ``best'' goalkeeper, defined to be the goalkeeper with the highest
saves to goals-against ratio, with at least 10 saves.

------------------------------------------------------------------------------------------------
select *
from GoalKeepers
where saves > 9 and abs(saves/goalsagainst -  (select max(saves/goalsagainst) from GoalKeepers where saves > 9)) < 0.00001;

Division by zero error, meaning that there are goalkeepers with 0 goals against. 
In that case, lets look for the goalkeeper with highest number of saves, with 0 goals against.

select * 
from GoalKeepers
where saves = (select max(saves) from GoalKeepers where saves > 9 and goalsagainst = 0) and goalsagainst = 0;
------------------------------------------------------------------------------------------------
<YOUR RESULT HERE>

================================================================================================
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. 

------------------------------------------------------------------------------------------------
select t1.name, t2.name, matchdate
from teams t1, teams t2, results 
where ((t1.teamid = results.teamone and t2.teamid = results.teamtwo) or 
       (t1.teamid = results.teamtwo and t2.teamid = results.teamone)) 
       and t1.groupname = t2.groupname 
       and matchdate >= to_date('06-24-2006', 'MM-DD-YYYY');
------------------------------------------------------------------------------------------------
<YOUR RESULT HERE>

================================================================================================
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.

------------------------------------------------------------------------------------------------
select name, totalminutes, (select count(*) 
                            from players p2 
                            where p2.totalminutes > p1.totalminutes) as rank
from players p1
where (select count(*) from players p2 where p2.totalminutes > p1.totalminutes) < 10
order by rank asc;

I could have used "limit", but that would be a problem if there is a tie for the 10th position.
------------------------------------------------------------------------------------------------
<YOUR RESULT HERE>

================================================================================================
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. 

------------------------------------------------------------------------------------------------
create table WinLossKO as
(
select teamone as winner, teamtwo as loser, matchdate
from Results
where scoreone < scoretwo and matchdate >= to_date('06-24-2006', 'MM-DD-YYYY')
)
union
(
select teamtwo as winner, teamone as loser, matchdate
from Results
where scoreone > scoretwo and matchdate >= to_date('06-24-2006', 'MM-DD-YYYY')
);
------------------------------------------------------------------------------------------------
<YOUR RESULT HERE>

================================================================================================
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.

------------------------------------------------------------------------------------------------
create function UpdateWinLossKOFunction() returns trigger as $UpdateWinLossKO$
begin
    if new.matchdate >= to_date('06-24-2006', 'MM-DD-YYYY') then
        if new.scoreone > new.scoretwo then
            insert into WinLossKO values(new.teamone, new.teamtwo, new.matchdate);
        end if;
        if new.scoreone < new.scoretwo then
            insert into WinLossKO values(new.teamtwo, new.teamone, new.matchdate);
        end if;
    end if;
return new;
end;

$UpdateWinLossKO$ language plpgsql;
create trigger UpdateWinLossKO after insert on results for each row execute procedure UpdateWinLossKOFunction();

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

================================================================================================
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. 


------------------------------------------------------------------------------------------------
with temp1 as (
(select teamone as team, scoreone as goalsfor, scoretwo as goalsagainst 
from results where matchdate <= to_date('06-23-2006', 'MM-DD-YYYY'))
union all
(select teamtwo as team, scoretwo as goalsfor, scoreone as goalsagainst
from results where matchdate <= to_date('06-23-2006', 'MM-DD-YYYY'))
),
temp2 as (
select team, (case 
                when goalsfor > goalsagainst then 3
                when goalsfor < goalsagainst then 0
                when goalsfor = goalsagainst then 1
              end) as point
from temp1
)
select name, sum(point) as points
from temp2 , teams
where temp2.team = teams.teamid
group by name
order by name;
------------------------------------------------------------------------------------------------
<YOUR RESULT HERE>

================================================================================================
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. 

------------------------------------------------------------------------------------------------
***** This is probably not the most efficient way to do it, but quite simple

DO language plpgsql $$
    DECLARE
    vr record;
    points integer;
    temp integer;

    BEGIN
        FOR vr IN SELECT name, teamid FROM teams ORDER BY name
        LOOP
            points := 0;

            SELECT count(*) INTO temp FROM results WHERE matchdate <= to_date('06-23-2006', 'MM-DD-YYYY') AND vr.teamid = teamone AND scoreone > scoretwo;
            points := points + temp * 3;
            SELECT count(*) INTO temp FROM results WHERE matchdate <= to_date('06-23-2006', 'MM-DD-YYYY') AND vr.teamid = teamtwo AND scoreone < scoretwo;
            points := points + temp * 3;
            SELECT count(*) INTO temp FROM results WHERE matchdate <= to_date('06-23-2006', 'MM-DD-YYYY') AND (vr.teamid = teamone OR vr.teamid = teamtwo) AND scoreone = scoretwo;
            points := points + temp;

            RAISE NOTICE 'Team = % Points = %', vr.name, points;
        END LOOP;

     END
$$;
------------------------------------------------------------------------------------------------
<YOUR RESULT HERE>

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