7.Find the largest country (by area) in each continent, show the continent, the name and the area:
The above example is known as a correlated or synchronized sub-query.
SELECT
c1.continent,
c1.name,
c1.area
FROM
world c1
JOIN (
SELECT
continent,
MAX(area) AS max_area
FROM
world
WHERE
area IS NOT NULL
GROUP BY
continent
) c2
ON
c1.continent = c2.continent
AND c1.area = c2.max_area;
8.List each continent and the name of the country that comes first alphabetically.
SELECT
c1.continent,c1.name
FROM
world c1
JOIN (
SELECT
name
FROM
world
GROUP BY
continent
) c2
ON
c1.name = c2.name
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 a.name,a.continent,a.population
FROM world a
join (SELECT continent,max(population) p
FROM world
WHERE population> 0 group by continent having p<=25000000) b
on a.continent=b.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 c1.name, c1.continent
FROM world c1
WHERE c1.population >= 3 * (
SELECT max(c2.population)
FROM world c2
WHERE c2.continent = c1.continent AND c2.name <> c1.name
);
