SQLZOO

join：

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 join goal on (id=matchid)
where teamid='GER'

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

5.
SELECT player, teamid, coach, gtime
  FROM goal join eteam on goal.teamid=eteam.id
 WHERE gtime<=10
 
6.
select mdate, teamname
from game join eteam on team1=eteam.id
where coach='Fernando Santos'

7.
select distinct(player)
from game join goal on id=matchid
where stadium  like 'National Stadium%'

8.
SELECT distinct(player)
  FROM game JOIN goal ON matchid = id 
    WHERE (team1='GER' or team2='GER') and teamid<>'GER'
    
9.
SELECT teamname,count(teamid)
  FROM eteam JOIN goal on id=teamid
group BY teamname

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

11.
SELECT matchid,mdate, count(id)
FROM game JOIN goal ON matchid = id 
WHERE (team1 = 'POL' OR team2 = 'POL')
group by matchid

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

13.
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 id=matchid
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

more join：

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 id
from actor
where name='Glenn Close';

5.
select id
from movie
where title='Casablanca';
 
6.
select name
from casting left join actor on id=actorid
where movieid=11768;

7.
(1)
select name
from (
select actorid 
from casting left join movie on id=movieid
where title ='Alien'
)  as aid left join actor on actorid=id;

(2)
select name
from  actor
where id in(
select actorid
from movie left join casting on id=movieid
where title='Alien');
(出来一样的结果，但是顺序不对所以导致结果不对，不知道为什么顺序不一样)

8.
(1)
with temp as
(
select movieid 
from 
(select id
from actor
where name='Harrison Ford') as temp left join casting on id=actorid
)
select title
from  temp inner join movie on movieid=id;

(2)
select title
from  movie
where id in
(select movieid 
from 
(select id
from actor
where name='Harrison Ford') as temp left join casting on id=actorid
);

9.
(1)
with actid as (
select id
from actor
where name='Harrison Ford'),
movid as (
select movieid
from actid left join casting on id=actorid
where ord!=1 )
select title
from movid left join movie on movid.movieid=movie.id;

(2)
select title
from (
(select id
from actor
where name='Harrison Ford') as actid left join casting on id=actorid) 
left join movie on movieid=movie.id
where ord!=1;

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

10.
with film1962 as(
select id,title
from movie
where yr=1962),
actid as (
select actorid,title
from film1962 left join casting on id=movieid
where ord=1)
select title,name
from actid left join actor on actorid=id;

11.
(1)
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

(2)
with acid as (
select id
from actor
where name='Rock Hudson'),
acmovie as(
select movieid
from acid join casting on id=actorid)
select yr,count(title)
from acmovie left join movie on movieid=id
group by yr
having count(title)>2;

12.
with mia as (
select movieid 
from casting left join actor on id=actorid
where name='Julie Andrews'),
ma as(
select mia.movieid, name
from mia left join casting on mia.movieid=casting.movieid left join actor on id=actorid
where ord=1),
mm as (
select title,movieid
from mia left join movie on mia.movieid=movie.id)
select distinct(title),name
from ma left join mm on ma.movieid=mm.movieid
order by title;

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

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

15.
with wmovieid as(
select movieid
from casting inner join actor on id=actorid
where name='Art Garfunkel')
select name
from (wmovieid left join casting on wmovieid.movieid=casting.movieid) left join actor on actorid=actor.id 
where name!='Art Garfunkel';

join quiz 2:

1.
SELECT name
  FROM actor INNER JOIN movie ON actor.id = director
 WHERE gross < budget

2.
SELECT *
  FROM actor JOIN casting ON actor.id = actorid
  JOIN movie ON movie.id = movieid

3.
SELECT name, COUNT(movieid)
  FROM casting JOIN actor ON actorid=actor.id
 WHERE name LIKE 'John %'
 GROUP BY name ORDER BY 2 DESC
 
4.
Table-B
"Crocodile" Dundee
Crocodile Dundee in Los Angeles
Flipper
Lightning Jack

5.
SELECT name
  FROM movie JOIN casting ON movie.id = movieid
  JOIN actor ON actor.id = actorid
WHERE ord = 1 AND director = 351
   
6.
link the director column in movies with the primary key in actor
connect the primary keys of movie and actor via the casting table
   
7.
A Bronx Tale	1993
Bang the Drum Slowly	1973
Limitless	2011

Using Null：

1.
select name 
from teacher
where dept is null;

2.
SELECT teacher.name, dept.name
 FROM teacher INNER JOIN dept
           ON (teacher.dept=dept.id)

3.
select teacher.name,dept.name
from teacher left join dept on dept.id=teacher.dept;

4.
select teacher.name,dept.name
from teacher right join dept on dept.id=teacher.dept;

5.
select  name,coalesce(mobile,'07986 444 2266') as mobilenumber
from teacher;

6.
select teacher.name,coalesce(dept.name,'None')
from teacher left join dept on teacher.dept=dept.id;

7.(空是不算的,但是如果count(*)，就会计算总共项）
select count(id),count(mobile)
from teacher;

8.
select dept.name,count(teacher.id)
from teacher right join dept on teacher.dept=dept.id
group by dept.name;


9.
select teacher.name,
case when teacher.dept=1 or teacher.dept=2 then 'Sci'
     else 'Art'
end
from teacher;

10.
select name,
case when dept=1 or dept=2 then 'Sci'
     when dept=3 then 'Art'
     else 'None'
end
from teacher;

Using Null Quiz:

1.
  SELECT teacher.name, dept.name FROM teacher LEFT OUTER JOIN dept ON (teacher.dept = dept.id)

2.
SELECT dept.name FROM teacher JOIN dept ON (dept.id = teacher.dept) WHERE teacher.name = 'Cutflower'

3.
SELECT dept.name, COUNT(teacher.name) FROM teacher RIGHT JOIN dept ON dept.id = teacher.dept GROUP BY dept.name

4.
display 0 in result column for all teachers without department

5.
'four' for Throd

6.
 Shrivell	Computing
Throd	Computing
Splint	Computing
Spiregrain	Other
Cutflower	Other
Deadyawn	Other

self join：

1.
select count(id)
from stops;

2.
select id
from stops
where name='Craiglockhart';

3.
with temp as (
select stop 
from route
where num='4' and company='LRT'
) 
select id,name
from temp left join stops on id =stop;

4.
select company, num,count(*)
from route
where stop=149 or stop=53
group by company, num
having count(*)=2;

5.
select a.company,a.num,a.stop,b.stop
from route a join route b on 
(a.company=b.company and a.num=b.num)
where a.stop=53  and b.stop=(
select id
from stops
where name='London Road');
 
6.
SELECT a.company, a.num, stopa.name, stopb.name
FROM route a JOIN route b ON
  (a.company=b.company AND a.num=b.num)
  JOIN stops stopa ON (a.stop=stopa.id)
  JOIN stops stopb ON (b.stop=stopb.id)
WHERE stopa.name='Craiglockhart' and stopb.name='London Road';

7.
select distinct(a.company), a.num
from route a join route b on a.company=b.company and a.num=b.num
join stops stopsa on a.stop=stopsa.id
join stops stopsb on b.stop=stopsb.id
where a.stop=115 and b.stop=137;

8.
select a.company ,b.num
from route a join route b on a.company=b.company and a.num=b.num
where a.stop=( select id from stops where name='Craiglockhart') and 
b.stop =(select id from stops where name='Tollcross');


9.
select stopsb.name,a.company,a.num 
from (route a join route b on a.company=b.company and a.num=b.num)
join stops stopsa on a.stop=stopsa.id 
join stops stopsb on b.stop=stopsb.id 
where a.company='LRT' and b.company ='LRT'and stopsa.name='Craiglockhart';

10.(顺序不对，我是不知道为什么)
select count(*)
from (route a join route b on a.company=b.company and a.num=b.num) 
join (route c join route d on c.company=d.company and c.num=d.num) on b.stop=c.stop
join stops transfer on transfer.id=b.stop 
where a.stop != b.stop and 
a.stop=(select id from stops where name='Craiglockhart') and 
c.stop != d.stop and 
d.stop=(select id from stops where name='Lochend');

self join quiz:

1.
  FROM stops a JOIN route z ON a.id=z.stop
  JOIN route y ON y.num = z.num
  JOIN stops b ON y.stop=b.id
 WHERE a.name='Craiglockhart' AND b.name ='Haymarket'

2.
SELECT S2.id, S2.name, R2.company, R2.num
  FROM stops S1, stops S2, route R1, route R2
 WHERE S1.name='Haymarket' AND S1.id=R1.stop
   AND R1.company=R2.company AND R1.num=R2.num
   AND R2.stop=S2.id AND R2.num='2A'

3.
SELECT a.company, a.num, stopa.name, stopb.name
  FROM route a JOIN route b ON (a.company=b.company AND a.num=b.num)
  JOIN stops stopa ON (a.stop=stopa.id)
  JOIN stops stopb ON (b.stop=stopb.id)
 WHERE stopa.name='Tollcross'