Geek Answers Handbook

Sqlzoo SELECT in SELECT Tutorial # 5

My question is:

In Germany (population 80 million people) the largest population of European countries. Austria (population 8.5 million people) has 11% of the population of Germany.

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

My answer:

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

What am I doing wrong? Thanks.

+5
source share
5 answers

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 curious about the sub-query, as from the OP question this was not clear (at least to me). The reason is that the table "peace" (as the name implies, I must admit) contains the entire country of the world, while we are only interested in the European one. Moreover, the German population must be extracted from the database, since it does not exceed 80,000,000; if you use this number, you will get back 101% as a German population.

+5
source

When using SQL Server in SQL Zoo, do not use CONCAT :

I think SQL Zoo uses a version of SQL Server that does not support CONCAT , and what's more, it looks like you need to do CAST . Instead, combine using the "+". Also see this post .

I believe that the script should be something like below (although I did not get it at will, because I want the result to look like 3%; 0%; 4%, etc. instead of 3.000000000000000% ; 0.000000000000000%; 4.000000000000000%, etc. And I am starting a new topic for this 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')

+2
source 
  select name, CONCAT(ROUND((population/(select population from world where name = "Germany"))*100),"%") from world where continent= "Europe"
+1
source 
 SELECT name, CONCAT(ROUND(population/(SELECT population FROM world WHERE name = 'Germany')*100,0), '%') FROM world WHERE continent = 'Europe'
+1
source

The sub request should return some data, so you can use it as a function

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

More articles:


All Articles