SELECT Name, Capital, Region, SurfaceArea AS "Surface Area", Population FROM
world.country ORDER BY Population;
SELECT Name, Capital, Region, SurfaceArea AS "Surface Area", Population FROM
world.country WHERE Population > 50000000 ORDER BY Population DESC;
SELECT Name, Capital, Region, SurfaceArea AS "Surface Area", Population FROM
world.country WHERE Population > 50000000 AND Population < 100000000 ORDER BY
Population DESC;
SELECT SUM(SurfaceArea) as "N. America Surface Area", SUM(Population) as "N.
America Population" FROM world.country WHERE Region = "North America";
In some cases, you might need to split a string. The following query uses
SUBSTRING_FUNCTION() to spilt a string where a space occurs. Run the following
query.
SELECT Region, substring_index(Region, " ", 1) FROM world.country;
SELECT Region FROM world.country WHERE LENGTH(TRIM(Region)) < 10;
SELECT Name, Region from world.country WHERE substring_index(Region, " ", 1) =
"Southern";
SELECT Region, substring_index(Region, " ", 1) FROM world.country;
SELECT Region, SUM(Population) FROM world.country WHERE Region = 'Australia and New
Zealand' GROUP By Region ORDER By SUM(Population) desc;
SELECT Region, Name, Population, SUM(Population) OVER(partition by Region ORDER BY
Population) as 'Running Total' FROM world.country WHERE Region = 'Australia and New
Zealand';
SELECT Region, Name, Population, RANK() OVER(partition by Region ORDER BY
Population desc) as 'Ranked' FROM world.country order by Region, Ranked;
