【SQLZOO学习打卡】Tutorial：Section C

Part 6. The JOIN Operation

@ Game table: 

@ Goal table:

 

@ Eteam table:

 

1. JOIN:  use data from two or more tables

You can combine the two steps into a single query with a JOIN. 

SELECT *
  FROM game JOIN goal ON (id=matchid)

The FROM clause says to merge data from the goal table with that from the game table. The ON says how to figure out which rows in game go with which rows in goal - the matchid from goal must match id from game. (If we wanted to be more clear/specific we could say ON (game.id=goal.matchid)

select player, teamid, stadium, mdate from 
goal join game on goal.matchid=game.id
where teamid='GER'

select team1, team2, player from 
game join goal on game.id=goal.matchid
where player like 'Mario%'

select player, teamid, coach, gtime from
goal join eteam on goal.teamid=eteam.id
where gtime<=10

To JOIN game with eteam you could use either
game JOIN eteam ON (team1=eteam.id) or game JOIN eteam ON (team2=eteam.id)

Notice that because id is a column name in both game and eteam you must specify eteam.id instead of just id

select mdate, teamname from
game join eteam on game.team1=eteam.id
where coach='Fernando Santos' 

 2. DISTINCT : to stop players being listed twice

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

3. Group by and Count 

select teamname,count(teamid) from
goal join eteam on goal.teamid=eteam.id
group by teamname

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

select matchid, mdate, count(teamid) from
game join goal on game.id=goal.matchid
where (team1='POL' or team2='POL')
group by matchid, mdate

elect matchid, mdate, count(teamid) from
game join goal on game.id=goal.matchid
where (team1='GER' or team2='GER') and teamid='GER'
group by matchid, mdate

4. CASE WHEN 

select mdate, team1, sum(case when teamid=team1 then 1 else 0 end) score1, team2, sum(case when teamid=team2 then 1 else 0 end) score2 from
game left  join goal on game.id=goal.matchid
group by mdate, matchid, team1, team2

Part 7. More JOIN Operations

@ Movie table: 

 

@ Actor table:

@ Casting table:

 

select name from 
casting join movie on casting.movieid=movie.id
join actor on casting.actorid=actor.id
where title='Alien'

select title from
actor join casting on actor.id=casting.actorid
join movie on casting.movieid=movie.id
where actor.name= 'Harrison Ford' and casting.ord!=1

select yr, count(title) from
actor join casting on actor.id=casting.actorid
join movie on casting.movieid=movie.id
where actor.name= 'Rock Hudson'
group by yr
having count(title)>2

Title is not a unique field, create a table of IDs in your subquery 

select distinct(title), name from 
movie join casting on movie.id=casting.movieid
join actor on casting.actorid=actor.id 
and ord=1
and movieid in (select subcasting.movieid from 
        actor subactor  join casting subcasting on subactor.id=subcasting.actorid
        and name='Julie Andrews')

select name
from casting join  actor on casting.actorid = actor.id
and ord = 1
group by name
having count (movieid) >= 15

select title,count(actorid) 
from movie join casting on movie.id = casting.movieid
and movie.yr = 1978
group by title 
order by count(actorid) desc,title

select name from 
actor join casting on actor.id = casting.actorid
and name !=  'Art Garfunkel'
and movieid in (
    select movieid from 
         actor join casting on actor.id = casting.actorid
         and actor.name = 'Art Garfunkel')