SELECT within SELECT Tutorial

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 gdp > 0 and continent=‘Europe‘)

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

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

select continent,name,area from world where area in(SELECT max(area) FROM world group by continent)

8.List each continent and the name of the country that comes first alphabetically.分组后，每组数据中的第一行

select continent,name 
from world as x 
where x.name=(
select y.name from world as y where y.continent=x.continent order by name 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 and population>0)

sqlzoo转

标签：