football data
This tutorial introducesJOIN
which allows you to use data from two or more tables. The tables contain all matches and goals from UEFA EURO 2012 Football Championship in Poland and Ukraine.
The data is available (mysql format) athttp://sqlzoo.net/euro2012.sql
1.
Show the matchid and player name for all goals scored by Germany. To identify German players, check for:teamid = 'GER'
2.
Show id, stadium, team1, team2 for just game 1012
3.
show the player, teamid, stadium and mdate for every German goal.
4.
Show the team1, team2 and player for every goal scored by a player called Mario player LIKE 'Mario%'
5.
Showplayer
,teamid
,coach
,gtime
for all goals scored in the first 10 minutesgtime <= 10
6.
List the the dates of the matches and the name of the team in which 'Fernando Santos' was the team1 coach.
7.
List the player for every goal scored in a game where the stadium was 'National Stadium, Warsaw'
8.
show the name of all players who scored a goal against Germany.
9.
Show teamname and the total number of goals scored.
10.
Show the stadium and the number of goals scored in each stadium.
11.
For every match involving 'POL', show the matchid, date and the number of goals scored.
12.
For every match where 'GER' scored, show matchid, match date and the number of goals scored by 'GER'
13.
List every match with the goals scored by each team as shown. This will use "CASE WHEN" which has not been explained in any previous exercises.
Notice in the query given every goal is listed. If it was a team1 goal then a 1 appears in score1, otherwise there is a 0. You could SUM this column to get a count of the goals scored by team1.Sort your result by mdate, matchid, team1 and team2.
Solution 1: use subquries (less favorable, less efficient)
To get each score, go to goal table, filter useful rows and count the number.
Solution 2: use CASE operator (more favorable, more efficient)
Last updated