SQLZOO(The JOIN operation\More JOIN operations)答案

The first example shows the goal scored by a player with the last name ‘Bender’. The * says to list all the columns in the table - a shorter way of saying matchid, teamid, player, gtime

Modify it to show the matchid and player name for all goals scored by Germany. To identify German players, check for: teamid = ‘GER’

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

SELECT player,teamid,stadium,mdate FROM
game JOIN goal ON goal.matchid=game.id AND goal.teamid = ‘GER’

Use the same JOIN as in the previous question.

Show the team1, team2 and player for every goal scored by a player called Mario player LIKE ‘Mario%’

SELECT team1,team2,player
FROM game JOIN goal ON game.id = goal.matchid AND goal.player LIKE ‘%Mario%’

Show player, teamid, coach, gtime for all goals scored in the first 10 minutes gtime<=10

SELECT player, teamid,coach, gtime
FROM goal JOIN eteam ON goal.teamid = eteam.id
WHERE gtime<=10

List the 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 (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 goal JOIN game WHERE goal.matchid = game.id AND game.stadium = ‘National Stadium, Warsaw’

The example query shows all goals scored in the Germany-Greece quarterfinal.
Instead show the name of all players who scored a goal against Germany.

SELECT DISTINCT player
FROM goal JOIN game ON goal.matchid = game.id
WHERE (team1=‘GER’ OR team2=‘GER’) AND teamid != ‘GER’

Show teamname and the total number of goals scored.

SELECT teamname,COUNT(teamid)
FROM eteam JOIN goal ON id=teamid
GROUP BY teamname
ORDER BY teamname

Show the stadium and the number of goals scored in each stadium.
球场在goal中出现了几次就是有几次进球

SELECT stadium,COUNT(1)
FROM goal JOIN game WHERE game.id=goal.matchid
GROUP BY stadium

For every match involving ‘POL’, show the matchid, date and the number of goals scored.
按matchid分类，并数它一共出现了几次，就是进了几球

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

For every match where ‘GER’ scored, show matchid, match date and the number of goals scored by ‘GER’

SELECT matchid,mdate,COUNT(matchid) FROM goal JOIN game
WHERE goal.matchid = game.id AND
goal.teamid = ‘GER’
GROUP BY matchid

More JOIN operations（从第11题开始）

11.Which were the busiest years for ‘Rock Hudson’, show the year and the number of movies he made each year for any year in which he made more than 2 movies.

SELECT yr,COUNT(title) FROM
movie JOIN casting ON movie.id=movieid
JOIN actor ON actorid=actor.id
WHERE name=‘Rock Hudson’
GROUP BY yr
HAVING COUNT(title) > 2

List the film title and the leading actor for all of the films ‘Julie Andrews’ played in.
这道题找的是电影演员里面有JA的电影和他的主演，所以条件应该是找到movieid且ord=1

SELECT title,name
FROM movie JOIN casting ON movie.id = casting.movieid
JOIN actor ON actor.id = casting.actorid
WHERE movieid IN(SELECT movieid FROM casting JOIN actor ON actorid = actor.id
WHERE name = ‘Julie Andrews’)
AND ord=1

Obtain a list, in alphabetical order, of actors who’ve had at least 15 starring roles.

SELECT name FROM actor JOIN casting ON actor.id = actorid WHERE ord = 1
GROUP BY actorid
HAVING COUNT(1)>=15 ORDER BY name

List the films released in the year 1978 ordered by the number of actors in the cast, then by title.

SELECT title,COUNT(actorid) FROM movie JOIN casting ON movie.id = movieid
JOIN actor ON actor.id = actorid
WHERE yr = 1978
GROUP BY title HAVING COUNT(actorid)>0
ORDER BY COUNT(actorid) DESC,title

List all the people who have worked with ‘Art Garfunkel’.
与12类似 关键在于你如何确定movie title

SELECT name FROM actor JOIN casting ON actor.id = casting.actorid
JOIN movie ON movie.id = movieid
WHERE movieid IN (SELECT movieid FROM casting JOIN actor
ON actor.id = actorid WHERE name = ‘Art Garfunkel’)
AND name != ‘Art Garfunkel’