[SQL][sqlzoo] The JOIN operation

JOIN                                                                                                                       

The JOIN operation - SQLZOO

1.

SELECT matchid, player FROM goal 
  WHERE teamid = 'GER'

2.

SELECT id,stadium,team1,team2
  FROM game
where id='1012'

3.

SELECT player,teamid,stadium,mdate 
  FROM game a 
JOIN goal b ON (a.id=b.matchid)
where b.teamid='ger'

4.

SELECT team1, team2,player 
FROM game a 
JOIN goal b ON (a.id=b.matchid) 
where b.player LIKE 'Mario%'

5.

SELECT player, teamid, coach, gtime
  FROM goal a
join eteam b on (a.teamid = b.id)
WHERE a.gtime<=10

6.

select mdate,teamname 
from game 
JOIN eteam ON (team1=eteam.id) 
where coach ='Fernando Santos'

7.

select player 
from goal 
join game on(id=matchid) 
where stadium ='National Stadium, Warsaw'

8.

select DISTINCT player 
from goal 
join game on (id =matchid) 
where teamid!='GER' and (team1='GER' or team2='GER')

9.

SELECT teamname, count(gtime)
  FROM eteam JOIN goal ON id=teamid
 GROUP BY teamname

10.

select stadium,count(gtime) 
from goal 
join game on id=matchid 
group by stadium

11.

SELECT matchid,mdate,COUNT(gtime)
  FROM game JOIN goal ON matchid = id 
 WHERE (team1 = 'POL' OR team2 = 'POL')
 GROUP BY matchid, mdate

12.

select matchid,mdate,count(teamid) 
from goal 
join game on id=matchid 
where teamid='ger' 
group by matchid,mdate

13.

SELECT mdate, team1, SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) AS score1, team2, SUM(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) AS score2 
FROM game 
left JOIN goal ON matchid = id 
GROUP BY mdate, matchid, team1, team2

JOIN Quiz                                                                                                                       

1.

 game  JOIN goal ON (id=matchid)

2.

 matchid, teamid, player, gtime, id, teamname, coach

3.

SELECT player, teamid, COUNT(*)
  FROM game JOIN goal ON matchid = id
 WHERE (team1 = "GRE" OR team2 = "GRE")
   AND teamid != 'GRE'
 GROUP BY player, teamid

4.

DEN	9 June 2012
GER	9 June 2012

5.

  SELECT DISTINCT player, teamid 
   FROM game JOIN goal ON matchid = id 
  WHERE stadium = 'National Stadium, Warsaw' 
 AND (team1 = 'POL' OR team2 = 'POL')
   AND teamid != 'POL'

6.

SELECT DISTINCT player, teamid, gtime
  FROM game JOIN goal ON matchid = id
 WHERE stadium = 'Stadion Miejski (Wroclaw)'
   AND (( teamid = team2 AND team1 != 'ITA') OR ( teamid = team1 AND team2 != 'ITA'))

7.

Netherlands	2
Poland	2
Republic of Ireland	1
Ukraine	2