Sqlzoo SELECT within SELECT Tutorial #5

Question

My Question is:

Germany (population 80 million) has the largest population of the countries in Europe. Austria (population 8.5 million) has 11% of the population of Germany.

Show the name and the population of each country in Europe. Show the population as a percentage of the population of Germany.

My answer:

SELECT name,CONCAT(ROUND(population/80000000,-2),'%')
FROM world
WHERE population = (SELECT population
                    FROM world
                      WHERE continent='Europe')

What I am doing wrong? Thanks.

Answer 1

The question was incomplete and was taken from here

This is the answer

SELECT 
  name, 
  CONCAT(ROUND((population*100)/(SELECT population 
                                 FROM world WHERE name='Germany'), 0), '%')
FROM world
WHERE population IN (SELECT population
                     FROM world
                     WHERE continent='Europe')

I was wondering about sub-query as from OP'S question it wasn't clear (at least to me). The reason is that "world" table (as the name suggest, I have to admit) contains all world country whereas we're interested only into european one. Moreover, the population of Germany has to be retrieved from DB because it's not extacly 80.000.000; if you use that number you receive back 101% as Germany population.

Answer 2

When using sql server in SQL Zoo, then don't use CONCAT:

I think SQL Zoo uses a version of SQL Server that doesn't support CONCAT and furthermore it looks like you have to do a CAST. Instead concatenate with the use of '+'. Also see this post.

I figure the script should be something like beneath (though I haven't got it to my desired stated, because of the fact I want to result to look like 3%;0%;4%;etc. instead of 3.000000000000000%;0.000000000000000%;4.000000000000000%;etc.. And I start a new topic for that one here).

SELECT name, CAST(ROUND(population*100/(SELECT population FROM world WHERE name='Germany'), 0) as varchar(20)) +'%' FROM world WHERE population IN (SELECT population FROM world WHERE continent='Europe')