Most of the answers to SQLZOO

I’ve recently got more involved with SQL at work. We mainly use SQL-Server, and I’m very rusty at anything SQL related… Fortunately a colleague suggested I go through the tutorials on SQLZOO, which proved to be a truly excellent resource.

Here are my answers to most of the questions. I made this file about six months ago, but hopefully it should still be fairly current.

It has proved quite useful as a reference to basic SQL. Especially considering I do not write SQL every day.

Anyway, to my interpretation of the answers!

Note: I haven’t been able to answer the last question in the self join quiz – and that’s probably what you’re looking for!

SELECT from WORLD Tutorial:

1.

SELECT name, continent, population 
FROM world

2.

SELECT name 
FROM world
WHERE population>=200000000

3.

SELECT name, gdp/population 
FROM world
WHERE population>=200000000

4.

SELECT name, population/1000000 
FROM world
WHERE continent = 'South America'

5.

SELECT name, population 
FROM world
WHERE name IN ( 'France', 'Germany', 'Italy')

6.

SELECT name 
FROM world 
WHERE name LIKE '%United%'

SELECT from NOBEL Tutorial:

1.

SELECT yr, subject, winner
FROM nobel
WHERE yr = 1950

2.

SELECT winner
FROM nobel
WHERE yr = 1962 AND subject = 'Literature'

3.

SELECT yr, subject 
FROM nobel 
WHERE winner='Albert Einstein'

4.

SELECT winner 
FROM nobel
WHERE subject='Peace' AND yr>=2000

5.

SELECT yr, subject, winner 
FROM nobel
WHERE subject='Literature' 
 AND yr BETWEEN 1980 AND 1989

6.

SELECT * FROM nobel
WHERE winner IN ('Theodore Roosevelt', 'Woodrow Wilson'
 , 'Jed Bartlet', 'Jimmy Carter')

7.

SELECT winner 
FROM nobel 
WHERE winner LIKE 'John%'

8.

SELECT DISTINCT nobel.yr FROM nobel LEFT JOIN 
  (SELECT DISTINCT yr, subject FROM nobel 
   WHERE subject='Chemistry') AS x ON nobel.yr=x.yr
WHERE nobel.subject='Physics' AND x.yr IS NULL

SELECT Within SELECT Tutorial:

1.

SELECT name FROM world
WHERE population > 
 (SELECT population FROM world WHERE name='Russia')

2.

SELECT name FROM world
WHERE continent = 'Europe' AND
gdp/population > 
 (SELECT gdp/population FROM world WHERE name='United Kingdom')

3.

SELECT name, continent FROM world
WHERE continent IN 
(SELECT continent FROM world WHERE
 name IN ('Belize', 'Belgium')
)

4.

SELECT name, population FROM world
WHERE population >
(SELECT population FROM world WHERE name='Canada')
AND population <
(SELECT population FROM world WHERE name='Poland')

5.

SELECT name FROM world WHERE gdp > ALL 
  (SELECT gdp FROM world WHERE continent='Europe' 
   AND gdp IS NOT NULL)

6.

SELECT continent, name, area FROM world x
  WHERE area >= ALL
    (SELECT area FROM world y
     WHERE y.continent=x.continent)

7.

SELECT name, world.continent, population 
FROM world LEFT JOIN 
(SELECT DISTINCT continent FROM world
WHERE population>=25000000) x
ON world.continent=x.continent
WHERE x.continent IS NULL

8.

SELECT name, continent from world x
WHERE population > ALL
(SELECT y.population*3 FROM world y
 WHERE y.continent=x.continent
 AND y.name <> x.name)

SUM And COUNT:

1.

SELECT SUM(population)
FROM world

2.

SELECT continent 
FROM world 
GROUP BY continent

3.

SELECT SUM(gdp) 
FROM world 
WHERE continent='Africa'

4.

SELECT COUNT(*) 
FROM world
WHERE area>=1000000

5.

SELECT SUM(population) 
FROM world
WHERE name IN ('France','Germany','Spain')

6.

SELECT continent, COUNT(name) 
FROM world
GROUP BY continent

7.

SELECT continent, COUNT(name) FROM world
WHERE population>=10000000 
GROUP BY continent

8.

SELECT continent
FROM world 
GROUP BY continent 
HAVING SUM(population)>=100000000

The NOBEL Table Can Be Used To Practice More SUM And COUNT Functions:

1.

SELECT COUNT(winner) 
FROM nobel

2.

SELECT DISTINCT subject 
FROM nobel

3.

SELECT COUNT(subject) 
FROM nobel 
WHERE subject='Physics'

4.

SELECT subject, COUNT(*) 
FROM nobel
GROUP BY subject

5.

SELECT subject, MIN(yr) 
FROM nobel
GROUP BY subject

6.

SELECT subject, COUNT(*) 
FROM nobel 
WHERE yr=2000
GROUP BY subject

7.

SELECT subject, COUNT(DISTINCT winner)
FROM nobel
GROUP BY subject

8.

SELECT subject, COUNT(DISTINCT yr) 
FROM nobel
GROUP BY subject

9.

SELECT yr 
FROM nobel 
WHERE subject='Physics'
GROUP BY yr 
HAVING COUNT(yr)=3

10.

SELECT winner 
FROM nobel 
GROUP BY winner 
HAVING COUNT(*)>1

11.

SELECT winner
FROM nobel 
GROUP BY winner 
HAVING COUNT(DISTINCT subject)>1

12.

SELECT yr, subject 
FROM nobel
WHERE yr>=2000
GROUP BY yr, subject 
HAVING COUNT(subject)=3

The JOIN Operation:

1.

SELECT matchid, player 
FROM goal 
WHERE teamid = 'GER'

2.

SELECT id,stadium,team1,team2
FROM game 
WHERE id = 1012

3.

SELECT player, teamid, mdate
FROM game 
JOIN goal ON (id=matchid)
WHERE teamid='GER'

4.

SELECT team1, team2, player 
FROM game JOIN goal ON game.id=matchid 
WHERE player LIKE 'Mario%'

5.

SELECT player, teamid, gtime
FROM goal 
WHERE gtime<=10

6.

SELECT mdate, teamname
FROM game JOIN eteam ON team1=eteam.id
WHERE coach='Fernando Santos'

7.

SELECT player 
FROM goal JOIN game ON matchid=id
WHERE stadium='National Stadium, Warsaw'

8.

SELECT DISTINCT player
FROM game JOIN goal ON id=matchid
JOIN eteam t1 ON game.team1=t1.id
JOIN eteam t2 ON game.team2=t2.id
JOIN eteam ON goal.teamid=eteam.id
WHERE (t1.teamname = 'Germany' OR t2.teamname = 'Germany') 
 AND eteam.teamname <> 'Germany'

9.

SELECT teamname, COUNT(*)
FROM eteam 
JOIN goal ON eteam.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 game.id=matchid
WHERE team1='POL' OR team2='POL'
GROUP BY id

12.

SELECT matchid, mdate, COUNT(*)
FROM goal 
JOIN game ON matchid=id
WHERE teamid='GER'
GROUP BY matchid

13.

SELECT mdate, team1, 
SUM(CASE WHEN teamid=team1 THEN 1 ELSE 0 END) AS score1,
team2,
SUM(CASE WHEN teamid=team2 THEN 1 ELSE 0 END) AS score2
FROM game LEFT 
JOIN goal ON id=matchid
GROUP BY id
ORDER BY mdate, matchid, team1, team2

Old JOIN Tutorial:

1.

SELECT who, country.name
FROM ttms 
JOIN country ON (ttms.country=country.id)
WHERE games = 2000

2.

SELECT who, color
FROM ttms 
JOIN country ON country=id
WHERE name='Sweden'

3.

SELECT games
FROM ttms 
JOIN country ON id=country
WHERE name='China' AND color='gold'

4.

SELECT who
FROM ttws 
JOIN games ON (ttws.games=games.yr)
WHERE city = 'Barcelona'

5.

SELECT city, color
FROM ttws 
JOIN games ON games=yr
WHERE who='Jing Chen'

6.

SELECT who, city
FROM ttws 
JOIN games ON games=yr
WHERE color='gold'

7.

SELECT games, color
FROM ttmd 
JOIN 
  (SELECT * FROM team WHERE name='Yan Sen') As x
ON team=id

8.

SELECT name
FROM ttmd 
JOIN team ON team=id
WHERE color='gold' AND games=2004

9.

SELECT name
FROM ttmd 
JOIN team ON team=id
WHERE country='FRA'

More JOIN Operations:

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 name 
FROM casting 
JOIN actor ON actorid=actor.id 
JOIN movie ON movie.id=movieid
WHERE title= 'Casablanca'

8.

SELECT name 
FROM casting 
JOIN actor ON actorid=actor.id 
JOIN movie ON movie.id=movieid
WHERE title= 'Alien'

9.

SELECT title 
FROM movie 
JOIN casting ON movie.id=movieid 
JOIN actor ON actor.id=actorid
WHERE name='Harrison Ford'

10.

SELECT title 
FROM movie 
JOIN casting ON movie.id=movieid 
JOIN actor ON actor.id=actorid
WHERE name='Harrison Ford' AND ord!=1

11.

SELECT title, name 
FROM movie 
JOIN casting ON movie.id=movieid 
JOIN actor ON actor.id=actorid
WHERE yr=1962 AND ord=1

12.

SELECT yr, COUNT(title)
FROM movie 
JOIN casting ON movie.id=movieid
JOIN actor ON actor.id=actorid
WHERE name='John Travolta'
GROUP BY yr 
HAVING COUNT(yr)>2

13.

SELECT title, name 
FROM movie
JOIN casting ON movie.id=casting.movieid
JOIN actor ON actor.id=actorid JOIN
  (SELECT DISTINCT movieid FROM casting JOIN
   actor ON actorid=actor.id WHERE 
   name='Julie Andrews') x
ON x.movieid=casting.movieid
WHERE ord=1

14.

SELECT name FROM actor 
JOIN casting ON actor.id=actorid
WHERE ord=1
GROUP BY actorid 
HAVING COUNT(*)>=30
ORDER BY name

15.

SELECT title, COUNT(actorid) AS a
FROM casting 
JOIN movie ON movie.id=movieid
WHERE yr=1978
GROUP BY movieid
ORDER BY a DESC

16.

SELECT actor.name
FROM casting 
JOIN actor ON actorid=actor.id
JOIN
  (SELECT movieid, name FROM casting JOIN actor
   ON actor.id=actorid WHERE
   name='Art Garfunkel') AS x
ON x.movieid=casting.movieid
WHERE actor.name<>x.name

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 (teacher.dept=dept.id)

4.

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

5.

SELECT name, COALESCE(mobile, '07986 444 2266')
FROM teacher

6.

SELECT teacher.name, COALESCE(dept.name, 'None')
FROM teacher
LEFT JOIN dept ON dept=dept.id

7.

SELECT COUNT(name), COUNT(mobile)
FROM teacher

8.

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

9.

SELECT name,
CASE WHEN dept=1 OR 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

Self JOIN:

1.

SELECT COUNT(id) 
FROM stops

2.

SELECT id 
FROM stops 
WHERE name='Craiglockhart'

3.

SELECT id, name
FROM route 
JOIN stops ON stop=id
WHERE num='4' AND company='LRT'

4.

SELECT company, num, COUNT(*)
FROM route WHERE stop=149 OR stop=53
GROUP BY company, num
HAVING COUNT(*)>1

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=149

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.num=b.num AND a.company=b.company)
WHERE a.stop=115 AND b.stop=137

8.

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

9.

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

10.

*** I WAS UNABLE TO ANSWER THIS ONE... ***

2 thoughts on “Most of the answers to SQLZOO”

  1. HI The Answer 10 in Self JOIN is

    SELECT distinct c3,c2,c1,s3,s2
    FROM
    (SELECT stopa.name as c1,a.company as c2,a.num as c3
    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 stopb.name=’Craiglockhart’ ) table1
    JOIN
    (

    SELECT stopa.name as s1,a.company as s2 ,a.num as s3
    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 stopb.name=’Sighthill’
    ) table2 ON (table1.c1 = table2.s1)
    ORDER BY CAST(c3 AS UNSIGNED)

    You can see the smile face.

Leave a Reply

Your email address will not be published. Required fields are marked *