Skip to content

Instantly share code, notes, and snippets.

@ajhanwar

ajhanwar/joins.sql

Last active February 22, 2019 08:01
Show Gist options
  • Save ajhanwar/c443a74cf4ba428d90fb3accf0821bdd to your computer and use it in GitHub Desktop.
Save ajhanwar/c443a74cf4ba428d90fb3accf0821bdd to your computer and use it in GitHub Desktop.
Download ZIP
Solutions to the 'JOINS' exercises on SQLZOO.net
Raw
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
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment