--The JOIN operation/zh


 --1
select 
matchid 
,player 
from goal
where teamid = 'GER';


 --2
select 
id 
,stadium 
,team1 
,team2 
from game
where id = '1012';


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


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


 --5
select 
goal.player 
,goal.teamid 
,eteam.coach 
,goal.gtime 
from goal join game on game.id = goal.matchid
          join eteam on goal.teamid = eteam.id 
where gtime <= 10;


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


 --7
select goal.player
from game join goal on game.id = goal.matchid
where stadium = 'National Stadium, Warsaw';


 --8
select distinct goal.player
from goal join game on goal.matchid = game.id 
where teamid != 'GER'
and (game.team1 = 'GER' 
  or game.team2 = 'GER');
/*这道题不要忘记排除进球的GER本身*/


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


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


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


 --12
select 
goal.matchid 
,game.mdate 
,count(goal.gtime) 
from game join goal on game.id = goal.matchid
where goal.teamid = 'GER'
group by goal.matchid,game.mdate;


 --13
select 
game.mdate 
,game.team1 
,sum(case when goal.teamid = game.team1 then 1 else 0 end) as score1 
,game.team2 
,sum(case when goal.teamid = game.team2 then 1 else 0 end) as score2
from game left join goal on game.id = goal.matchid
group by game.mdate,goal.matchid,game.team1,game.team2
order by game.mdate,goal.matchid,game.team1,game.team2;
/*这道题注意题干中Sort和Notice in the query given every goal is listed的要求，因此使用了group、order、left join*/


--Music Tutorial/zh


 --1
select 
album.title
,album.artist 
from album join track on album.asin = track.album 
where track.song = 'Alison';


 --2
select album.artist
from album join track on album.asin = track.album 
where track.song = 'Exodus';


 --3
select track.song
from album join track on album.asin = track.album 
where album.title = 'Blur';
/*读了题干几遍才明白Blur指的是title*/


 --4
select
album.title 
,count(track.song) 
from album join track on album.asin = track.album 
group by album.title;


 --5
select
album.title 
,count(track.song) 
from album join track on album.asin = track.album 
where song like '%Heart%'
group by album.title;


 --6
select track.song 
from album join track on album.asin = track.album 
where album.title = track.song;


 --7
select title
from album
where title = artist;


 --8
select 
track.song 
,count(distinct album)
from album join track on album.asin = track.album 
group by track.song
having count(distinct album) > 2;


 --9
select 
album.title 
,album.price 
,count(track.song) 
from album join track on album.asin = track.album 
group by album.title,album.price 
having album.price/count(track.song) < 0.5;


 --10
select 
album.title 
,count(track.song) as num
from album join track on album.asin = track.album 
group by album.title
order by num DESC;
/*这道题有报错，原因未知*/


--More JOIN operations/zh


 --1
select 
id 
,title 
from movie 
where yr = 1962;


 --2
select yr 
from movie
where title = 'Citizen Kane';


 --3
select 
id 
,title 
,yr 
from movie 
where title like 'Star Trek%'
order by yr;


 --4
select title
from movie 
where id in ('11768','11955','21191');


 --5 
select id 
from actor
where name = 'Glenn Close';


 --6
select id
from movie
where title = 'Casablanca';


 --7
select 
actor.name 
from actor join casting on actor.id = casting.actorid
where movieid = 11768;


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


 --9
select movie.title
from casting join movie on casting.movieid = movie.id
             join actor on casting.actorid = actor.id
where actor.name = 'Harrison Ford';


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


 --11
select 
movie.yr 
,count(movie.title)
from casting join movie on casting.movieid = movie.id
             join actor on casting.actorid = actor.id
where actor.name = 'John Travolta'
group by yr
having count(title) >= all(select count(title) 
                         from casting join movie on casting.movieid = movie.id
                                        join actor on casting.actorid = actor.id
                           where actor.name = 'John Travolta'
                           group by yr);
/*第二种解法*/
select 
movie.yr 
,count(movie.title)
from casting join movie on casting.movieid = movie.id
             join actor on casting.actorid = actor.id
where actor.name = 'John Travolta'
group by yr
having count(movie.title) = (select max(c)
                            from (select yr, count(movie.title) as c
                                   from casting join movie on casting.movieid = movie.id
                                               join actor on casting.actorid = actor.id 
                                  where actor.name = 'John Travolta'
                                  group by yr) as ps);
/*注意每一个派生出来的表都必须有一个自己的别名*/


 --13
select 
movie.title 
,actor.name 
from casting join movie on casting.movieid = movie.id
             join actor on casting.actorid = actor.id
where casting.movieid in (select casting.movieid
                          from casting join actor on casting.actorid = actor.id
                          where actor.name = 'Julie Andrews')
and ord = 1;


 --14
select actor.name
from actor join casting on casting.actorid = actor.id
where casting.ord = 1
group by actor.name 
having count(actor.name) >= 30
order by actor.name;


 --15
select 
movie.title 
,count(*) 
from casting join movie on casting.movieid = movie.id
where movie.yr = 1978 
group by movie.title
order by count(*) desc,title;
/*count(*)和count(字段名)的区别：前者对行的数目进行计算，包含null，后者对特定的列的值具有的行数进行计算，不包含null，得到的结果将是除去值为null和重复数据后的结果。*/


 --16  
select distinct actor.name
from actor join casting on casting.actorid = actor.id
where casting.movieid in (select casting.movieid
                         from actor join casting on casting.actorid = actor.id
                         where actor.name = 'Art Garfunkel')
and actor.name != 'Art Garfunkel';
 /*思路：首先求出Art Garfunkel演过的所有电影，其次查询所有演员，最后排除Art Garfunkel*/