SQLZOO习题答案

1.Bigger than Russia

SELECT name FROM world
WHERE population >
(SELECT population FROM world
WHERE name=‘Russia’)

2.Richer than UK

SELECT name FROM world WHERE continent = ‘Europe’ and gdp/population >
(SELECT gdp/population FROM world WHERE name = ‘United Kingdom’)

3.Neighbours of Argentina and Australia

SELECT name, continent FROM world WHERE continent in
(SELECT continent FROM world WHERE name in (‘Argentina’, ‘Australia’))
ORDER BY name

4.Between Canada and Poland

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

5.Percentages of Germany

SELECT name,
concat(ROUND(population/(SELECT population FROM world WHERE name = ‘Germany’)*100, 0), ‘%’) as percentage
FROM world WHERE continent = ‘Europe’

6.Bigger than every country in Europe

SELECT name FROM world
WHERE gdp > ALL(SELECT gdp FROM world WHERE gdp>0 AND continent = ‘Europe’)

7.Largest in each continent

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

8.First country of each continent (alphabetically)

SELECT continent, name FROM world a WHERE name =
(SELECT name FROM world b
WHERE b.continent = a.continent ORDER BY name LIMIT 1)

9.Difficult Questions That Utilize Techniques Not Covered In Prior Sections

SELECT name, continent, population FROM world
WHERE continent IN
(SELECT continent FROM world a
WHERE population >= ALL(SELECT population FROM world b where b.continent = a.continent AND population > 0)
AND population <= 25000000)

optimised:
SELECT name, continent, population FROM world x
WHERE 25000000>=ALL (SELECT population FROM world y
WHERE x.continent=y.continent
AND population>0)

10.

SELECT name, continent FROM world a
WHERE population/3 > ALL(SELECT population FROM world b
WHERE a.continent = b.continent AND a.name <> b.name AND population > 0)