SQL Zoo 练习题答案—SELECT within SELE

SELECT within SELECT

1.List each country name where the population is larger than that of 'Russia'.

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

2.Show the countries in Europe with a per capita GDP greater than 'United Kingdom'.

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

3.List the name and continent of countries in the continents containing either Argentina or Australia. Order by name of the country.

SECLECT name,continent FROM world WHERE continent IN (SELECT continent FROM world WHERE name IN('Argentina','Australia')) ORDER BY name

4.Which country has a population that is more than Canada but less than Poland? Show the name and the population.

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

这一题用 BETWEEN AND 不行，因为BETWEEN AND 是包含首尾的。

5.Germany (population 80 million) has the largest population of the countries in Europe. Austria (population 8.5 million) has 11% of the population of Germany.

Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.

SELECT name, CONCAT(ROUND(population/(SELECT population FROM world WHERE name = 'Germany')*100,0),'%') FROM world WHERE continent = 'Europe'

6.Which countries have a GDP greater than every country in Europe? [Give the nameonly.] (Some countries may have NULL gdp values)

select name from world where gdp > ALL(select gdp from world where gdp > 0 and continent='Europe')

ALL/SOME/ANY 的用法，学习URL

7.Find the largest country (by area) in each continent, show the continent, the nameand the area:

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

8.List each continent and the name of the country that comes first alphabetically.

SUM and COUNT

1.Show the total population of the world.

SELECT SUM(population)FROM world

2.List all the continents - just once each.

SELECT DISTINCT(continent) FROM world

3.Give the total GDP of Africa

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

4.How many countries have an area of at least 1000000

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

5.What is the total population of ('Estonia', 'Latvia', 'Lithuania')

SELECT SUM(population) FROM world WHERE name IN ('Estonia', 'Latvia', 'Lithuania')

6.For each continent show the continent and number of countries.

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

7.For each continent show the continent and number of countries with populations of at least 10 million.

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

8.List the continents that have a total population of at least 100 million.

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