Comments and questions from readers...

Bob Brooks writes...

I am learning Sql using your tutorial and I thank you for it. It is extremely easy to use. I believe your answer to 2b is wrong. You only count countries with a population of 10 million. If a region has no countries of 10 million, that region is not listed. It should be listed with a count of zero. I'm having trouble coming up with that answer.Bob BrooksSan Diego, Ca

Andrew replies... Thanks Bob,You are absolutely right about the country count. If a region has no countries that fit the bill that region will not show up at all. I can't think of an easy way to fix this.

A hard way to fix this is to use the functions floor and sign . We divide the population by 10 million and get the integer component using floor , we then take the sign which delivers 1, 0 or -1 if its input is positive, zero or negative.

select region, name, population/10000000,
floor(population/10000000), sign(floor(population/10000000))
  from cia;

This final value may now be summed to get a count by region.

select region, sum(sign(floor(population/10000000)))
  from cia
  group by region;

Note that the functions floor and sign are common but not universal. In MS Access we can achieve something similar using the function CInt and CBool.

It is probably possible to achieve the same answer using outer joins - any suggestions would be gratefully received.

Yours Andrew