SQLZOO习题详解（04）——SELECT within SELECT Tutorial

练习地址

---

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 continent = 'Europe' AND 
       gdp/population >(SELECT gdp/population FROM world WHERE name = 'United Kingdom');

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

SELECT name, continent FROM world 
  WHERE continent = (SELECT continent FROM world WHERE name ='Argentina ')
  OR continent = (SELECT continent FROM world WHERE name = 'Australia')
  	ORDER BY name;

4.Which country has a population that is more than United Kingom but less than Germany? Show the name and the population.(这里不能用BETWEEN…AND… 因为它们是左闭右闭的）

SELECT name, population FROM world
  WHERE population > (SELECT population FROM world 
  						WHERE name ='United Kingdom')
        AND population < (SELECT population FROM world 
        					WHERE name ='Germany');

5.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),'%')  FROM world
          WHERE continent = 'Europe';

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

SELECT name FROM world
  WHERE gdp > ALL(SELECT gdp FROM world WHERE continent = 'Europe' 
  AND gdp > 0);

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

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

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

SELECT continent, name FROM world x
   WHERE name = (SELECT name FROM world y WHERE x.continent=y.continent limit 1);

9.Find the continents where all countries have a population <= 25000000. Then find the names of the countries associated with these continents. Show name, continent and population.

SELECT name, continent, population FROM world x
 WHERE 25000000>=  ALL(SELECT population FROM world y WHERE x.continent = y.continent);

10.Some countries have populations more than three times that of all of their neighbours (in the same continent). Give the countries and continents.

SELECT name, continent FROM world x
 WHERE x.population/3 >= ALL(SELECT population FROM world y WHERE x.continent = y.continent
  AND x.name!=y.name);

笔记

百分号使用

-- 舍掉小数点后几位
select concat(truncate(0.8799*100,2),'%');

结果 87.00%

-- 四舍五入
select concat(round(0.8799*100,2),'%');

结果 88.00%

-- 舍掉小数点后几位
select concat(floor(0.8799*100),'%');

结果 87%

-- 直接拼接
select concat(0.8799*100,'%');

结果 87.9900%

（ps:感觉有些不好刷啊，有些题目完全是新的知识点，但是又不给你提示，直接上手属实难顶。）