/* Just because I can't take another day of hassle for not posting up another puzzle here is a world cup themed one.  This puzzle is by a friend of mine called Tom Sillence.

1) The first puzzle is to produce the group tables as seen on this page:
http://news.bbc.co.uk/sport1/hi/football/world_cup_2006/tables/default.stm


so we want a table with the following columns:
         Group: (e.g. "Angola's Group", "Mexico's Group")
         Country: the name of the country
         P: The number of games this country has played
         W: The number of wins the country has
         D: The number of draws this country has
         L: The number of losses this country has
         F: The number of goals this country has scored
         A: The number of goals this country has conceded
         GD: Goal difference which is (F - A)
         PTS: The total number of points
         Q: contains the letter Q if the team qualifies, otherwise empty string

You have to work out which teams are together in each group by looking at who's played whom. You won't know which is group A, group B etc, so in my results I called them e.g. "Angola's group", picking an arbitrary team to represent the group.

A quick reprise of world cup rules will tell us how the results should be sorted and who qualifies for the next round:

     - Each team gets 3 points for a win, 1 for a draw and 0 for a loss.
     - Teams who are tied on points are sorted by goal difference (GD) and then by goals scored (F)
     - The top two teams in each group qualify for the next round

2) Uncomment the lines below the message "UNCOMMENT THESE LINES FOR QUESTION 2 and 3" at the end of this file :)

(a) We've now removed some matches. Write a query that outputs which matches are still to be played. We want these columns:
Group, TeamA, TeamB

(b) Check that you can still output the group tables with these matches missing. For the purposes of working out the groups, you can assume no more than one match has been removed from each group.

3) Write a query that outputs each team who still has a match left to play, and what they must do in order to qualify. This should include both the result (win/lose/draw) and the margin of victory or defeat that is necessary. There are lots of options for how to format this.

In my solution I was trying to simulate what a commentator would say - I have a hardcoded string for each possible requirement. This is my output:

Angola, currently lying third in their group, play Portugal
     Topping the group is impossible for them
     They must win here or it's curtains for them Portugal, currently lying first in their group, play Angola
     The result is immaterial, they have already qualified top
     Qualification is already assured
Sweden, currently lying second in their group, play England
     They must win in order to top the group
     Qualification is already assured
England, currently lying first in their group, play Sweden
     They only need a draw to qualify top
     Qualification is already assured
France, currently lying third in their group, play Togo
     They can still reach first place if they win my a margin of 3 or more
     Their world cup hopes depend on a win by two or more goals Togo, currently lying last in their group, play France
    err...
    They're playing for pride now. Next stop for them is the airport.

(6 row(s) affected)



FOR THE RECORD:
- I made use of a table variable to avoid retyping the same expression many times, but technically all of this can be done with a single query (it would be huge).

- However, my solution to Q3 only takes account of points and goal difference, not goals scored. Can you do better?

*/

set nocount on

DECLARE @Results Table
(
    TeamA VARCHAR(20) NOT NULL,
    TeamAGoals INT NOT NULL,
    TeamBGoals INT NOT NULL,    
    TeamB VARCHAR(20) NOT NULL
)

INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Saudi Arabia',0,1,'Spain')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Switzerland',2,0,'South Korea')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Togo',0,2,'France')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Ukraine',1,0,'Tunisia')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Croatia',2,2,'Australia')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Czech Republic',0,2,'Italy')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Ghana',2,1,'USA')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Japan',1,4,'Brazil')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Holland',0,0,'Argentina')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Angola',1,1,'Iran')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Ivory Coast',3,2,'Serbia & Montenegro')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Portugal',2,1,'Mexico')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Costa Rica',1,2,'Poland')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Ecuador',0,3,'Germany')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Paraguay',2,0,'Trinidad and Tobago')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Sweden',2,2,'England')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Saudi Arabia',0,4,'Ukraine')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Spain',3,1,'Tunisia')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Togo',0,2,'Switzerland')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Brazil',2,0,'Australia')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('South Korea',1,1,'France')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Japan',0,0,'Croatia')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Czech Republic',0,2,'Ghana')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Italy',1,1,'USA')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Portugal',2,0,'Iran')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Argentina',6,0,'Serbia & Montenegro')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Holland',2,1,'Ivory Coast')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Mexico',0,0,'Angola')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Ecuador',3,0,'Costa Rica')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('England',2,0,'Trinidad and Tobago')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Sweden',1,0,'Paraguay')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Germany',1,0,'Poland')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Spain',4,0,'Ukraine')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Tunisia',2,2,'Saudi Arabia')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Brazil',1,0,'Croatia')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Switzerland',0,0,'France')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('South Korea',2,1,'Togo')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Australia',3,1,'Japan')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES('Italy',2,0,'Ghana')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('USA',0,3,'Czech Republic')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Angola',0,1,'Portugal')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Mexico',3,1,'Iran')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Serbia & Montenegro',0,1,'Holland')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Argentina',2,1,'Ivory Coast')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('England',1,0,'Paraguay')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Trinidad and Tobago',0,0,'Sweden')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Germany',4,2,'Costa Rica')
INSERT INTO @Results(TeamA, TeamAGoals, TeamBGoals, TeamB)
    VALUES ('Poland',0,2,'Ecuador')

---- UNCOMMENT THESE LINES FOR QUESTION 2 and 3:
/*
  delete from @results where teamA = 'Togo' and teamb = 'France'
  delete from @results where teamA = 'Sweden' and teamb = 'England'
  delete from @results where teamA = 'Angola' and teamb = 'Portugal'
  --make this a draw so the france match is interesting :
  update @results set teambgoals = 2
     where teamA = 'Switzerland' and teamb = 'South Korea'
*/