SQL ZOO
My solutions to the the tutorials and quizzes of SQL Zoo. Uses MySQL engine.
Table of Contents
- SELECT basics
- SELECT names
- SELECT Quiz
- SELECT from WORLD Tutorial
- BBC QUIZ
- SELECT from Nobel Tutorial
- Nobel Quiz
- SELECT within SELECT Tutorial
- Nested SELECT Quiz
- SUM and COUNT
- SUM and COUNT Quiz
- Nobel Prizes Aggregate functions
- The JOIN operation
- JOIN Quiz
- Old JOIN Tutorial
- More JOIN operations
- JOIN Quiz 2
- Using Null
- Numeric Examples
- Window function
- Self join
- Self join Quiz
Select Basics
SELECT population
FROM world
WHERE name = 'Germany';
SELECT name, population
FROM world
WHERE name IN ('Sweden', 'Norway', 'Denmark');
SELECT name, area
FROM world
WHERE area BETWEEN 200000 AND 250000;
SELECT Quiz
SELECT name, population
FROM world
WHERE population BETWEEN 1000000 AND 1250000
Table-E
SELECT name FROM world
WHERE name LIKE '%a' OR name LIKE '%l'
3rd Table
4th Table
SELECT name, area, population
FROM world
WHERE area > 50000 AND population < 10000000
SELECT name, population/area
FROM world
WHERE name IN ('China', 'Nigeria', 'France', 'Australia')
SELECT names
SELECT name
FROM world
WHERE name LIKE 'Y%';
SELECT name
FROM world
WHERE name LIKE '%Y';
SELECT name
FROM world
WHERE name LIKE '%x%';
SELECT name
FROM world
WHERE name LIKE '%land';
SELECT name
FROM world
WHERE name LIKE 'C%ia';
SELECT name
FROM world
WHERE name LIKE '%oo%';
SELECT name
FROM world
WHERE name LIKE '%a%a%a%';
SELECT name
FROM world
WHERE name LIKE '_t%'
ORDER BY name;
SELECT name
FROM world
WHERE name LIKE '%o__o%';
SELECT name
FROM world
WHERE name LIKE '____';
SELECT name
FROM world
WHERE name = capital;
SELECT name
FROM world
WHERE capital = CONCAT(name, ' City');
SELECT capital, name
FROM world
WHERE capital LIKE CONCAT('%', name, '%');
SELECT capital, name
FROM world
WHERE capital LIKE CONCAT(name, '%')
AND capital <> name;
SELECT name,
SUBSTRING(capital, LENGTH(name) + 1, LENGTH(capital)) as ext
FROM world
WHERE capital LIKE CONCAT(name, '%')
AND capital <> name;
SELECT from WORLD Tutorial
SELECT name, continent, population FROM world
SELECT name FROM world
WHERE population >= 200000000;
SELECT name, (gdp / population)
FROM world
WHERE population >= 200000000
SELECT name, (population / 1000000)
FROM world
WHERE continent = 'South America';
SELECT name, population
FROM world
WHERE name in ('France', 'Germany', 'Italy')
SELECT name
FROM world
WHERE name LIKE 'United%';
SELECT name, population, area
FROM world
WHERE area > 3000000 OR population > 250000000;
SELECT name, population, area
FROM world
WHERE area > 3000000 XOR population > 250000000;
SELECT name, ROUND(population / 1000000, 2), ROUND(gdp / 1000000000, 2)
FROM world
WHERE continent = 'South America';
SELECT name, ROUND(gdp / population, -3) as per_capita_gdp
FROM world
WHERE gdp >= 1000000000000;
SELECT name, capital
FROM world
WHERE LENGTH(name) = LENGTH(capital);
SELECT name, capital
FROM world
WHERE LEFT(name, 1) = LEFT(capital, 1) AND name <> capital;
SELECT name
FROM world
WHERE
name LIKE '%a%' AND
name LIKE '%e%' AND
name LIKE '%i%' AND
name LIKE '%o%' AND
name LIKE '%u%' AND
name NOT LIKE '% %';
BBC QUIZ
SELECT name
FROM world
WHERE name LIKE 'U%'
SELECT population
FROM world
WHERE name = 'United Kingdom'
'name' should be name
Nauru | 990
SELECT name, population
FROM world
WHERE continent IN ('Europe', 'Asia')
SELECT name FROM world
WHERE name IN ('Cuba', 'Togo')
Brazil Colombia
SELECT from Nobel Tutorial
SELECT yr, subject, winner
FROM nobel
WHERE yr = 1950;
SELECT winner
FROM nobel
WHERE yr = 1962
AND subject = 'Literature'
SELECT yr, subject
FROM nobel
WHERE winner = 'Albert Einstein';
SELECT winner
FROM nobel
WHERE subject = 'Peace'
AND yr >= 2000;
SELECT *
FROM nobel
WHERE subject = 'Literature'
AND yr BETWEEN 1980 AND 1989;
SELECT *
FROM nobel
WHERE winner IN ('Theodore Roosevelt',
'Woodrow Wilson',
'Jimmy Carter',
'Barack Obama');
SELECT winner
FROM nobel
WHERE winner LIKE 'John%';
SELECT *
FROM nobel
WHERE (subject = 'Physics' AND yr = 1980) OR
(subject = 'Chemistry' AND yr = 1984);
SELECT *
FROM nobel
WHERE yr = 1980 AND
subject NOT IN ('Chemistry', 'Medicine');
Harder Questions
SELECT *
FROM nobel
WHERE (subject = 'Medicine' AND yr < 1910) OR
(subject = 'Literature' AND yr > 2003);
SELECT *
FROM nobel
WHERE winner = 'PETER GRÜNBERG';
SELECT *
FROM nobel
WHERE winner = "EUGENE O'NEILL";
SELECT winner, yr, subject
FROM nobel
WHERE winner LIKE 'Sir%'
ORDER BY yr DESC;
SELECT winner, subject
FROM nobel
WHERE yr = 1984
ORDER BY CASE WHEN subject IN ('Physics', 'Chemistry') THEN 1 ELSE 0 END,
subject, winner;
Nobel Quiz
SELECT winner FROM nobel
WHERE winner LIKE 'C%' AND winner LIKE '%n'
SELECT COUNT(subject) FROM nobel
WHERE subject = 'Chemistry'
AND yr BETWEEN 1950 and 1960
SELECT COUNT(DISTINCT yr) FROM nobel
WHERE yr NOT IN (SELECT DISTINCT yr FROM nobel WHERE subject = 'Medicine')
Medicine | Sir John Eccles
Medicine | Sir Frank Macfarlane Burnet
SELECT yr FROM nobel
WHERE yr NOT IN(SELECT yr
FROM nobel
WHERE subject IN ('Chemistry','Physics'))
SELECT DISTINCT yr
FROM nobel
WHERE subject='Medicine'
AND yr NOT IN(SELECT yr FROM nobel
WHERE subject='Literature')
AND yr NOT IN (SELECT yr FROM nobel
WHERE subject='Peace')
Chemistry | 1
Literature | 1
Medicine | 2
Peace | 1
Physics | 1
SELECT within SELECT Tutorial
SELECT name
FROM world
WHERE population > (
SELECT population from world WHERE name = 'Russia');
SELECT name
FROM world
WHERE continent = 'Europe' AND
(gdp / population) > (SELECT (gdp / population)
from world
WHERE name = 'United Kingdom');
SELECT name, continent
FROM world
WHERE continent IN (SELECT continent
FROM world
WHERE name = 'Argentina' OR name = 'Australia')
ORDER BY name;
SELECT name, population
FROM world
WHERE population > (
SELECT population
FROM world
WHERE name = 'Canada')
AND population < (
SELECT population
FROM world
WHERE name = 'Poland');
SELECT
name,
CONCAT(ROUND((population / (SELECT population FROM world WHERE name = 'Germany') * 100)), '%')
FROM world
WHERE continent = 'Europe';
SELECT name
FROM world
WHERE gdp > ALL(SELECT gdp
FROM world
WHERE (continent = 'Europe') AND (gdp > 0));
SELECT continent, name, area
FROM world AS x
WHERE area >= ALL
(SELECT area
FROM world AS y
WHERE y.continent = x.continent AND
area > 0);
SELECT continent, name
FROM world
GROUP BY continent;
SELECT name, continent, population
FROM world AS x
WHERE 25000000 > ALL (
SELECT population
FROM world AS y
WHERE y.continent = x.continent
AND population > 0
);
SELECT name, continent
FROM world AS x
WHERE population > ALL (
SELECT population * 3
FROM world as y
WHERE y.continent = x.continent
AND y.name <> x.name
);
Nested SELECT Quiz
SELECT region, name, population FROM bbc x WHERE population <= ALL (SELECT population FROM bbc y WHERE y.region=x.region AND population>0)
SELECT name,region,population FROM bbc x WHERE 50000 < ALL (SELECT population FROM bbc y WHERE x.region=y.region AND y.population>0)
SELECT name, region FROM bbc x
WHERE population < ALL (SELECT population/3 FROM bbc y WHERE y.region = x.region AND y.name != x.name)
Table-D
SELECT name FROM bbc
WHERE gdp > (SELECT MAX(gdp) FROM bbc WHERE region = 'Africa')
SELECT name FROM bbc
WHERE population < (SELECT population FROM bbc WHERE name='Russia')
AND population > (SELECT population FROM bbc WHERE name='Denmark')
Table-B
SUM and COUNT
SELECT SUM(population)
FROM world;
SELECT DISTINCT continent
FROM world;
SELECT SUM(gdp)
FROM world
WHERE continent = 'Africa';
SELECT COUNT(area)
FROM world
WHERE area >= 1000000;
SELECT SUM(population)
FROM world
WHERE name IN ('Estonia', 'Latvia', 'Lithuania');
SELECT continent, COUNT(name)
FROM world
GROUP BY continent;
SELECT continent, COUNT(name)
FROM world
WHERE population > 10000000
GROUP BY continent;
SELECT continent
FROM world
GROUP BY continent
HAVING SUM(population) >= 100000000;
SUM and COUNT Quiz
SELECT SUM(population) FROM bbc WHERE region = 'Europe'
SELECT COUNT(name) FROM bbc WHERE population < 150000
AVG(), COUNT(), MAX(), MIN(), SUM()
No result due to invalid use of the WHERE function
SELECT AVG(population) FROM bbc WHERE name IN ('Poland', 'Germany', 'Denmark')
SELECT region, SUM(population)/SUM(area) AS density FROM bbc GROUP BY region
SELECT name, population/area AS density FROM bbc WHERE population = (SELECT MAX(population) FROM bbc)
Table-D
Nobel Prizes Aggregate functions
SELECT COUNT(winner) FROM nobel;
SELECT DISTINCT subject
FROM nobel;
SELECT COUNT(subject)
FROM nobel
WHERE subject = 'Physics';
SELECT subject, COUNT(subject) as prices
FROM nobel
GROUP BY subject;
SELECT subject, MIN(yr) as first_year
FROM nobel
GROUP BY subject;
SELECT subject, COUNT(subject) as awards_in_2000
FROM nobel
WHERE yr = 2000
GROUP BY subject;
SELECT subject, COUNT(DISTINCT winner) as distinct_winners
FROM nobel
GROUP BY subject;
SELECT subject, COUNT(DISTINCT yr) as distinct_yr
FROM nobel
GROUP BY subject;
SELECT yr
FROM nobel
WHERE subject = 'Physics'
GROUP BY yr
HAVING COUNT(yr) = 3;
SELECT winner
FROM nobel
GROUP BY winner
HAVING COUNT(winner) > 1;
SELECT winner
FROM nobel
GROUP BY winner
HAVING COUNT(DISTINCT subject) > 1
SELECT yr, subject
FROM nobel
WHERE yr > 1999
GROUP BY yr, subject
HAVING COUNT(yr) > 2;
The JOIN operation
SELECT matchid, player
FROM goal
WHERE teamid = 'GER';
SELECT id,stadium,team1,team2
FROM game
WHERE id = 1012;
SELECT goal.player, goal.teamid, game.stadium, game.mdate
FROM game
INNER JOIN goal
ON game.id = goal.matchid
WHERE goal.teamid = 'GER';
SELECT game.team1, game.team2, goal.player
FROM game
INNER JOIN goal
ON game.id = goal.matchid
WHERE goal.player LIKE 'Mario%';
SELECT goal.player, goal.teamid, eteam.coach, goal.gtime
FROM goal
INNER JOIN eteam
ON goal.teamid = eteam.id
WHERE goal.gtime <= 10;
SELECT game.mdate, eteam.teamname
FROM game
INNER JOIN eteam
ON game.team1 = eteam.id
WHERE eteam.coach = 'Fernando Santos';
SELECT goal.player
FROM game
INNER JOIN goal
ON goal.matchid = game.id
WHERE game.stadium = 'National Stadium, Warsaw';
SELECT DISTINCT goal.player
FROM game
INNER JOIN goal
ON goal.matchid = game.id
WHERE goal.teamid <> 'GER'
AND (game.team1 = 'GER' OR game.team2 = 'GER');
SELECT eteam.teamname, COUNT(goal.teamid)
FROM goal
INNER JOIN eteam
ON eteam.id = goal.teamid
GROUP BY eteam.teamname;
SELECT game.stadium, COUNT(goal.matchid)
FROM game
INNER JOIN goal
ON game.id = goal.matchid
GROUP BY game.stadium;
SELECT goal.matchid, game.mdate, COUNT(goal.matchid)
FROM game
INNER JOIN goal
ON goal.matchid = game.id
WHERE game.team1 = 'POL' OR game.team2 = 'POL'
GROUP BY goal.matchid;
SELECT goal.matchid, game.mdate, COUNT(goal.matchid) AS german_goals
FROM game
INNER JOIN goal
ON game.id = goal.matchid
WHERE goal.teamid = 'GER'
GROUP BY goal.matchid;
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.id, game.mdate
ORDER BY game.mdate, goal.matchid, game.team1, game.team2
JOIN Quiz
game JOIN goal ON (id=matchid)
matchid, teamid, player, gtime, id, teamname, coach
SELECT player, teamid, COUNT(*)
FROM game JOIN goal ON matchid = id
WHERE (team1 = "GRE" OR team2 = "GRE")
AND teamid != 'GRE'
GROUP BY player, teamid
DEN | 9 June 2012
GER | 9 June 2012
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'
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'))
Netherlands | 2
Poland | 2
Republic of Ireland | 1
Ukraine | 2
Old JOIN Tutorial
SELECT who, country.name
FROM ttms JOIN country
ON (ttms.country=country.id)
WHERE games = 2000
SELECT ttms.who, ttms.color
FROM ttms
INNER JOIN country
ON ttms.country = country.id
WHERE country.name = 'Sweden';
SELECT ttms.games
FROM ttms
INNER JOIN country
ON ttms.country = country.id
WHERE country.name = 'China' AND ttms.color = 'gold';
SELECT ttws.who AS 'barcelona_winners'
FROM ttws
INNER JOIN games
ON ttws.games = games.yr
WHERE games.city = 'Barcelona';
SELECT games.city, ttws.color
FROM ttws
INNER JOIN games
ON ttws.games = games.yr
WHERE ttws.who = 'Jing Chen';
SELECT ttws.who, games.city
FROM ttws
INNER JOIN games
ON ttws.games = games.yr
WHERE ttws.color = 'gold';
SELECT ttmd.games, ttmd.color
FROM ttmd
INNER JOIN team
ON ttmd.team = team.id
WHERE team.name LIKE '%Yan Sen%';
SELECT team.name
FROM ttmd
INNER JOIN team
ON ttmd.team = team.id
WHERE ttmd.color = 'gold' AND ttmd.games = 2004;
SELECT team.name
FROM ttmd
INNER JOIN team
ON ttmd.team = team.id
WHERE ttmd.country = 'FRA';
More JOIN operations
SELECT id, title
FROM movie
WHERE yr = 1962;
SELECT yr
FROM movie
WHERE title = 'Citizen Kane';
SELECT id, title, yr
FROM movie
WHERE title LIKE '%Star Trek%'
ORDER BY yr;
SELECT id
FROM actor
WHERE name = 'Glenn Close';
SELECT id
FROM movie
WHERE title = 'Casablanca';
SELECT actor.name AS casablanca_cast_list
FROM movie
INNER JOIN casting
ON movie.id = casting.movieid
INNER JOIN actor
ON casting.actorid = actor.id
WHERE casting.movieid = (
SELECT id
FROM movie
WHERE title = 'Casablanca'
);
SELECT actor.name AS alien_cast_list
FROM movie
INNER JOIN casting
ON movie.id = casting.movieid
INNER JOIN actor
ON casting.actorid = actor.id
WHERE casting.movieid = (
SELECT id
FROM movie
WHERE title = 'Alien'
);
SELECT movie.title
FROM movie
INNER JOIN casting
ON movie.id = casting.movieid
INNER JOIN actor
ON casting.actorid = actor.id
WHERE casting.actorid = (
SELECT id
FROM actor
WHERE name = 'Harrison Ford'
);
SELECT movie.title
FROM movie
INNER JOIN casting
ON movie.id = casting.movieid
INNER JOIN actor
ON casting.actorid = actor.id
WHERE casting.actorid = (
SELECT id
FROM actor
WHERE name = 'Harrison Ford'
)
AND casting.ord <> 1;
SELECT
movie.title,
(CASE WHEN casting.ord = 1 THEN actor.name END) AS name
FROM movie
INNER JOIN casting
ON movie.id = casting.movieid
INNER JOIN actor
ON casting.actorid = actor.id
WHERE movie.yr = 1962
AND (CASE WHEN casting.ord = 1 THEN actor.name END) IS NOT NULL;
SELECT yr, COUNT(movie.title)
FROM movie
INNER JOIN casting
ON movie.id = casting.movieid
INNER JOIN actor
ON casting.actorid = actor.id
WHERE actor.name = 'Rock Hudson'
GROUP BY movie.yr
HAVING COUNT(movie.title) > 2;
SELECT
movie.title,
(CASE WHEN casting.ord = 1 THEN actor.name END) as leading_actor
FROM movie
INNER JOIN casting
ON movie.id = casting.movieid
INNER JOIN actor
ON casting.actorid = actor.id
WHERE (CASE WHEN casting.ord = 1 THEN actor.name END) IS NOT NULL
AND casting.movieid IN (
SELECT casting.movieid
FROM casting
INNER JOIN actor
ON casting.actorid = actor.id
WHERE actor.name = 'Julie Andrews'
);
SELECT actor.name
FROM movie
INNER JOIN casting
ON movie.id = casting.movieid
INNER JOIN actor
ON casting.actorid = actor.id
GROUP BY actor.name
HAVING SUM(CASE WHEN casting.ord = 1 THEN 1 ELSE 0 END) >= 15;
SELECT movie.title, COUNT(actor.id) as actors
FROM movie
INNER JOIN casting
ON movie.id = casting.movieid
INNER JOIN actor
ON casting.actorid = actor.id
WHERE movie.yr = 1978
GROUP BY movie.title
ORDER BY actors DESC, movie.title;
SELECT DISTINCT actor.name
FROM movie
INNER JOIN casting
ON movie.id = casting.movieid
INNER JOIN actor
ON casting.actorid = actor.id
WHERE casting.movieid IN (
SELECT casting.movieid
FROM casting
INNER JOIN actor
ON casting.actorid = actor.id
WHERE actor.name = 'Art Garfunkel'
)
AND actor.name <> 'Art Garfunkel';
JOIN Quiz 2
SELECT name
FROM actor INNER JOIN movie ON actor.id = director
WHERE gross < budget
SELECT *
FROM actor JOIN casting ON actor.id = actorid
JOIN movie ON movie.id = movieid
SELECT name, COUNT(movieid)
FROM casting JOIN actor ON actorid=actor.id
WHERE name LIKE 'John %'
GROUP BY name ORDER BY 2 DESC
Table-B
SELECT name
FROM movie JOIN casting ON movie.id = movieid
JOIN actor ON actor.id = actorid
WHERE ord = 1 AND director = 351
link the director column in movies with the primary key in actor connect the primary keys of movie and actor via the casting table
Table-B
Using Null
SELECT name
FROM teacher
WHERE dept IS NULL;
SELECT teacher.name, dept.name
FROM teacher
INNER JOIN dept
ON teacher.dept = dept.id;
SELECT teacher.name, dept.name
FROM teacher
LEFT JOIN dept
ON teacher.dept = dept.id;
SELECT teacher.name, dept.name
FROM teacher
RIGHT JOIN dept
ON teacher.dept = dept.id;
SELECT
name,
COALESCE(mobile, '07986 444 2266') as mobile
FROM teacher;
SELECT
teacher.name,
COALESCE(dept.name, 'None') as dept
FROM teacher
LEFT JOIN dept
ON teacher.dept = dept.id;
SELECT Count(name), Count(mobile)
FROM teacher;
SELECT dept.name, COUNT(teacher.name) as number_of_teacher
FROM teacher
RIGHT JOIN dept
ON teacher.dept = dept.id
GROUP BY dept.name;
SELECT
name,
(CASE WHEN dept IN (1, 2) THEN 'Sci' ELSE 'Art' END)
FROM teacher;
SELECT
name,
(CASE WHEN dept IN (1, 2) THEN 'Sci'
WHEN dept = 3 THEN 'Art'
ELSE 'None' END)
FROM teacher;
Using Null Quiz
SELECT teacher.name, dept.name FROM teacher LEFT OUTER JOIN dept ON (teacher.dept = dept.id)
SELECT dept.name FROM teacher JOIN dept ON (dept.id = teacher.dept) WHERE teacher.name = 'Cutflower'
SELECT dept.name, COUNT(teacher.name) FROM teacher RIGHT JOIN dept ON dept.id = teacher.dept GROUP BY dept.name
display 0 in result column for all teachers without department
'four' for Throd
Table-A
Numeric Examples
SELECT A_STRONGLY_AGREE
FROM nss
WHERE question='Q01'
AND institution = 'Edinburgh Napier University'
AND subject = '(8) Computer Science'
SELECT institution, subject
FROM nss
WHERE score >= 100 AND question = 'Q15';
SELECT institution, score
FROM nss
WHERE subject = '(8) Computer Science'
AND score < 50
AND question = 'Q15';
SELECT subject, SUM(response)
FROM nss
WHERE question = 'Q22'
AND subject IN ('(8) Computer Science',
'(H) Creative Arts and Design')
GROUP BY subject;
SELECT subject,
SUM((A_STRONGLY_AGREE / 100) * response)
FROM nss
WHERE question = 'Q22'
AND subject IN ('(8) Computer Science',
'(H) Creative Arts and Design')
GROUP BY subject;
SELECT subject,
ROUND(SUM(A_STRONGLY_AGREE * response) / SUM(response))
FROM nss
WHERE question = 'Q22'
AND subject IN ('(8) Computer Science',
'(H) Creative Arts and Design')
GROUP BY subject;
SELECT institution,
ROUND(SUM((score * response)) / SUM(response))
FROM nss
WHERE question = 'Q22' AND institution LIKE '%Manchester%'
GROUP BY institution;
SELECT
institution,
SUM(sample) AS sample_size,
SUM(CASE WHEN subject = '(8) Computer Science' THEN sample ELSE 0 END) AS comp
FROM nss
WHERE question = 'Q01' AND institution LIKE '%Manchester%'
GROUP BY institution;
Window function
SELECT lastName, party, votes
FROM ge
WHERE constituency = 'S14000024' AND yr = 2017
ORDER BY votes DESC;
SELECT
party,
votes,
RANK() OVER (ORDER BY votes DESC) as posn
FROM ge
WHERE constituency = 'S14000024' AND yr = 2017
ORDER BY party;
SELECT
yr,
party,
votes,
RANK() OVER (PARTITION BY yr ORDER BY votes DESC) as posn
FROM ge
WHERE constituency = 'S14000021'
ORDER BY party, yr;
SELECT
constituency,
party,
votes,
RANK() OVER (PARTITION BY constituency ORDER BY votes DESC) AS posn
FROM ge
WHERE yr = 2017
AND constituency BETWEEN 'S14000021' AND 'S14000026'
ORDER BY posn, constituency;
SELECT
constituency,
party,
votes,
RANK() OVER (PARTITION BY constituency ORDER BY votes DESC) AS posn
FROM ge
WHERE yr = 2017
AND constituency BETWEEN 'S14000021' AND 'S14000026'
ORDER BY posn, constituency;
SELECT party, COUNT(party)
FROM (
SELECT constituency,
party,
RANK() OVER (PARTITION BY constituency ORDER BY votes DESC) as posn
FROM ge
WHERE yr = 2017 AND constituency LIKE 'S%'
) AS party_ranking
WHERE party_ranking.posn = 1
GROUP BY party;
Self join
SELECT COUNT(*)
FROM stops;
SELECT id
FROM stops
WHERE name = 'Craiglockhart';
SELECT stops.id, stops.name
FROM stops
INNER JOIN route
ON stops.id = route.stop
WHERE route.num = 4 AND route.company = 'LRT'
ORDER BY route.pos;
SELECT company, num, COUNT(*)
FROM route
WHERE stop = 149 OR stop = 53
GROUP BY company, num
HAVING COUNT(*) = 2;
SELECT a.company, a.num, a.stop, b.stop
FROM route a
INNER 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'
);
SELECT a.company, a.num, stopa.name, stopb.name
FROM route a
INNER JOIN route b
ON (a.company = b.company AND a.num = b.num)
INNER JOIN stops stopa
ON a.stop = stopa.id
INNER JOIN stops stopb
ON b.stop = stopb.id
WHERE stopa.name = 'Craiglockhart' AND stopb.name = 'London Road';
SELECT DISTINCT a.company, a.num
FROM route a
INNER JOIN route b
ON (a.company = b.company AND a.num = b.num)
WHERE a.stop = 115 AND b.stop = 137;
SELECT a.company, a.num
FROM route a
INNER JOIN route b
ON (a.company = b.company AND a.num = b.num)
INNER JOIN stops stopa
ON (a.stop = stopa.id)
INNER JOIN stops stopb
ON (b.stop = stopb.id)
WHERE stopa.name = 'Craiglockhart' AND stopb.name = 'Tollcross';
SELECT stopb.name, a.company, a.num
FROM route a
INNER JOIN route b
ON (a.company = b.company AND a.num = b.num)
INNER JOIN stops stopa
ON (a.stop = stopa.id)
INNER JOIN stops stopb
ON (b.stop = stopb.id)
WHERE stopa.name = 'Craiglockhart' AND a.company = 'LRT';
SELECT DISTINCT a.num, a.company, stops.name, b.num, b.company
FROM (
SELECT x.company, x.num, y.stop
FROM route x
INNER JOIN route y
ON (x.company = y.company AND x.num = y.num)
INNER JOIN stops stopx
ON (x.stop = stopx.id)
INNER JOIN stops stopy
ON (y.stop = stopy.id)
WHERE stopx.name = 'Craiglockhart'
) AS a
INNER JOIN (
SELECT x.company, x.num, y.stop
FROM route x
INNER JOIN route y
ON (x.company = y.company AND x.num = y.num)
INNER JOIN stops stopx
ON (x.stop = stopx.id)
INNER JOIN stops stopy
ON (y.stop = stopy.id)
WHERE stopx.name = 'Lochend'
) AS b
ON (a.stop = b.stop)
INNER JOIN stops
ON (a.stop = stops.id)
ORDER BY a.num, stops.name, b.num
Self join Quiz
SELECT DISTINCT a.name, b.name
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'
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'
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'