SQLzoo JOIN assignments

sqlzooJOIN.sql

/* Show matchid and player name for all goals scored by Germany */ 
SELECT matchid, player
FROM goal
WHERE teamid = 'GER'

/* Show id, stadium, team1, team2 for just game 1012 */
SELECT id, stadium, team1, team2
FROM game
WHERE id = 1012 

/* Show player, teamid, stadium and mdate for every German goal */
SELECT player, teamid, stadium, mdate
FROM game JOIN goal ON (id=matchid) 
WHERE teamid = 'GER'

/* Show team1, team2 and player for every goal scored by a player called Mario */
SELECT team1, team2, player
FROM game JOIN goal ON (id = matchid)
WHERE player LIKE 'Mario%'

/* Show player, teamid, coach, gtime for all goals stcored in the first 10 minutes */
SELECT player, teamid, coach, gtime
FROM goal JOIN eteam ON (goal.teamid = eteam.id)
WHERE gtime  <= 10

/* List the dates of the matches and the name of the team in which Fernando Santos
was the team1 coach */ 
SELECT mdate, teamname
FROM game JOIN eteam ON (game.team1 = eteam.id)
WHERE coach = 'Fernando Santos'

/* List the player for every goal scored in a game where the stadium was 
National Stadium, Warsaw */
 SELECT player
 FROM game JOIN goal ON (game.id = goal.matchid)
 WHERE stadium  = 'National Stadium, Warsaw'

/* More Difficult Questions */ 
/* Show the name of all players who scored a goal against Germany */ 



/* Show teamname and the total number of goals scored */
SELECT teamname, COUNT(gtime)
FROM eteam JOIN goal ON (id = teamid)
GROUP BY teamname

/* Show the stadium and the number of goals scored in each stadium */
SELECT stadium, COUNT(gtime) AS goals_scored
FROM game JOIN goal ON game.id = goal.matchid
GROUP BY teamid

/* For every match involving Poland, show the matchid, date, and number of goals scored */
SELECT matchid, mdate, COUNT(gtime) AS goals_scored
FROM goal JOIN game ON matchid = id
WHERE (team1 = 'POL' OR team2 = 'POL')
GROUP BY matchid, mdate

/* For every match where Germany scored, show matchid, match date and the number
of goals scored by Germany */ 
SELECT matchid, mdate, COUNT(gtime) AS goals_scored
FROM goal JOIN game ON matchid = id
WHERE (teamid = 'GER')
GROUP BY matchid, mdate