博客

1.show thematchidandplayername for all goals scored by Germany. To identify German players, check for:teamid = 'GER'.
SELECT matchid,player FROM goal 
  WHERE teamid = 'GER';

2.Show id, stadium, team1, team2 for just game 1012.
SELECT id,stadium,team1,team2
  FROM game
WHERE id = '1012';

3.show the player, teamid, stadium and mdate for every German goal.
SELECT player,teamid,stadium,mdate
  FROM game INNER JOIN goal ON id=matchid
WHERE teamid = 'GER'; 
或 SELECT b.player,b.teamid,a.stadium,a.mdate
FROM game AS a INNER JOIN goal AS b
ON a.id=b.matchid
WHERE teamid = 'GER';

4.Show the team1, team2 and player for every goal scored by a player called Mario player LIKE 'Mario%'.
SELECT a.team1,a.team2,b.player
 FROM game AS a INNER JOIN goal AS b
ON a.id=b.matchid
WHERE b.player LIKE 'Mario%';

5.Show player, teamid, coach, gtime for all goals scored in the first 10 minutes gtime<=10.
SELECT a.player, a.teamid, b.coach, a.gtime
FROM goal AS a INNER JOIN eteam AS b
ON a.teamid=b.id
WHERE a.gtime<=10;

6.List the dates of the matches and the name of the team in which 'Fernando Santos' was the team1 coach.
SELECT a.mdate, b.teamname
FROM game AS a INNER JOIN eteam AS b 
ON a.team1 = b.id
WHERE coach = 'Fernando Santos';

7.List the player for every goal scored in a game where the stadium was 'National Stadium, Warsaw'.
SELECT a.player
FROM goal AS a INNER JOIN game AS b 
ON a.matchid = b.id
WHERE stadium =  'National Stadium, Warsaw';

8.show the name of all players who scored a goal against Germany.
SELECT DISTINCT b.player
  FROM game AS a INNER JOIN goal AS b ON a.id = b.matchid 
    WHERE (b.teamid = a.team1 AND a.team2='GER') OR (b.teamid = a.team2 AND a.team1='GER');

9.Show teamname and the total number of goals scored.
SELECT c.teamname, COUNT(b.player)
  FROM goal AS b INNER JOIN eteam AS c ON b.teamid = c.id
 GROUP BY c.teamname;

10.Show the stadium and the number of goals scored in each stadium.
SELECT a.stadium, COUNT(b.player)
  FROM game AS a INNER JOIN goal AS b ON a.id = b.matchid
 GROUP BY a.stadium;

11.For every match involving 'POL', show the matchid, date and the number of goals scored.
SELECT b.matchid,a.mdate,COUNT(b.player)
  FROM game AS a INNER JOIN goal AS b ON b.matchid = a.id 
 WHERE (team1 = 'POL' OR team2 = 'POL')
GROUP BY b.matchid,a.mdate;

12.For every match where 'GER' scored, show matchid, match date and the number of goals scored by 'GER'.
SELECT b.matchid,a.mdate,COUNT(b.player)
  FROM game AS a INNER JOIN goal AS b ON b.matchid = a.id 
 WHERE b.teamid = 'GER'
GROUP BY b.matchid,a.mdate;

13.List every match with the goals scored by each team as shown.  Sort your result by mdate, matchid, team1 and team2.
SELECT a.mdate,
a.team1,SUM(CASE WHEN a.team1=b.teamid THEN 1 ELSE 0 END) AS score1,
a.team2,SUM(CASE WHEN a.team2=b.teamid THEN 1 ELSE 0 END) AS score2
FROM game AS a LEFT JOIN goal AS b ON b.matchid = a.id
GROUP BY a.id,a.mdate,a.team1,a.team2
ORDER BY a.mdate,a.id,a.team1,a.team2;