• What is the total surface area of all the countries on the planet?
SELECT SUM(SurfaceArea ) FROM Country;

148956306.90

  • What the total surface area of each region on the planet?
SELECT Region, SUM(SurfaceArea ) FROM Country GROUP BY Region;

  • Region SUM(SurfaceArea)
  • Antarctica 13132101.00
  • Australia and New Zealand 8011939.00
  • Baltic Countries 175117.00
  • British Islands 313173.00
  • Caribbean 234423.00

  • List each region in the Europe, along with it's total population.
SELECT Region, SUM( Population ) FROM Country WHERE Continent = 'Europe' GROUP BY Region

  • Region SUM(Population)
  • Baltic Countries 7561900
  • British Islands63398500
  • Eastern Europe 307026000
  • Nordic Countries 24166400
  • Southern Europe 144674200
  • Western Europe 183247600

  • (More challenging, but possible) List the all the regions, along with the percentage of the total surface area they encompass.
SELECT Region, SUM( SurfaceArea ) AS Area, SUM( SurfaceArea )/(SELECT SUM(SurfaceArea ) FROM Country) AS Percent FROM Country GROUP BY Region

  • Region Area Percent
  • Antarctica 13132101.00 8.816076
  • Australia and New Zealand 8011939.00 5.378718
  • Baltic Countries 175117.00 0.117563
  • British Islands 313173.00 0.210245
  • Caribbean 234423.00 0.157377
  • Central Africa 6612667.00 4.439333
  • Central America 2479532.00 1.664604
  • Eastern Africa 6299891.00 4.229355
  • Eastern Asia 11774482.00 7.904655
  • Eastern Europe 18814094.00 12.630613
  • Melanesia 540774.00 0.363042
  • Micronesia 3102.00 0.002082
  • Micronesia/Caribbean 16.00 0.000011
  • Middle East 4820592.00 3.236246
  • Nordic Countries 1321901.00 0.887442
  • North America 21500515.00 14.434109
  • Northern Africa 8524703.00 5.722955
  • Polynesia 8463.00 0.005682
  • South America 17864926.00 11.993400
  • Southeast Asia 4494801.00 3.017530
  • Southern Africa 2674778.00 1.795680
  • Southern and Central Asia 10791130.00 7.244494
  • Southern Europe 1316392.40 0.883744
  • Western Africa 6138338.00 4.120898
  • Western Europe 1108456.50 0.744149
Topic revision: r1 - 2013-10-21 - JimSkon
 
This site is powered by the TWiki collaboration platformCopyright &© by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Ideas, requests, problems regarding TWiki? Send feedback