Solutions to the 'JOINS' exercises on SQLZOO.net

joins.sql

/*
 * joins.sql
 * 2/21/19
 * Aditya Jhanwar
 */

-- 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 id=matchid
where player like 'Mario%'

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

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

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

-- 8. 
select distinct player
from (select * from game where team1='GER' or team2='GER') A join goal on id=matchid
where teamid <> 'GER'

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

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

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

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

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