Sqlzoo SELECT within SELECT Tutorial #5

select within select tutorial answers
sqlzoo answers
list each continent and the name of the country that comes first alphabetically.
sqlzoo sum and count answers
find the largest country (by area) in each continent, show the continent, the name and the area:
list the continents that have a total population of at least 100 million.
select the code that shows the name, region and population of the smallest country in each region
select select

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.

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.

SELECT within SELECT Tutorial, https://github.com › jisaw › sqlzoo-solutions › blob › master › select-in-select A correlated subquery works like a nested loop: the subquery only has access to rows related to a single record at a time in the outer query. The technique relies on table aliases to identify two different uses of the same table, one in the outer query and the other in the subquery.

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')

select-in-select.sql almost 5 years ago, This tutorial looks at how we can use the SELECT statements within 5. Germany (population million) has the largest population of the  3 SELECT from Nobel Additional practice of the basic features using a table of Nobel Prize winners. 4 SELECT within SELECT In which we form queries using other queries. 5 SUM and COUNT In which we apply aggregate functions. more the same 6 JOIN In which we join two tables; game and goals. previously music tutorial 7 More JOIN operations

    select name, CONCAT(ROUND((population/(select population from world where name = "Germany"))*100),"%") 
    from world
    where continent= "Europe"

sqlzoo select in select 5, I'm supposed to create a nested select statement that will deliver the names of all countries with a The tutorial question is #5 on this page: http://sqlzoo.net/wiki/  sql,select , Sqlzoo SELECT within SELECT Tutorial #5. 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).

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

Sqlzoo practice Answer--select within SELECT Tutorial, Fortunately a colleague suggested I go through the tutorials on SQLZOO, which proved 5. SELECT name, population FROM world WHERE name IN ( 'France',​  SELECT x.name, x.continent FROM world x WHERE x.population > ALL( SELECT (y.population*3) FROM world y WHERE x.continent=y.continent AND x.name<>y.name ) Note the last condition in the inner query where I'm excluding the "x" country from the list of "y" countries by doing x.name<>y.name. If that is not done, no rows will be returned in the result.

sub query should be return multiple data so you can use in function like this

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

[SQL] SQL Zoo tutorial is wrong? Working on nested Select, Say for example you wanted to search for country name with population larger than Russia. Without the subquery you would perform a search on  You may use a SELECT statement in the FROM line.. In this case the derived table X has columns name and gdp_per_capita.The calculated values in the inner SELECT can be used in the outer SELECT.

Most of the answers to SQL-Zoo! – Frutbunn Blog, 3 SELECT from Nobel Additional practice of the basic features using a table of Nobel Prize winners. 4 SELECT within SELECT In which we form queries using other queries. 5 SUM and COUNT In which we apply aggregate functions. more the same 6 JOIN In which we join two tables; game and goals. previously music tutorial 7 More JOIN operations

SQL ZOO (part 3/8) – SELECT IN SELECT – Daniels Coding Blog, We would like to show you a description here but the site won’t allow us.

SELECT name, capital FROM world WHERE LEFT (name, 1) = LEFT (capital, 1) AND name <> capital All the vowels Equatorial Guinea and Dominican Republic have all of the vowels (a e i o u) in the name.

Comments
  • The last part of the SQL, WHERE population ... is redundant. You can just use WHERE continent = "Europe" instead of that blob.
  • Thanks for answering! Code-only answers are discouraged on Stack Overflow, because a code dump with no context doesn't explain how or why the solution will work, making it impossible for the original poster (or any future readers) to understand the logic behind it. Please, edit your question and include an explanation of your code so that others can benefit from your answer!
  • If you want percentage then you will be change division 80000000 to 800
  • You can edit question and add example of data and accepted result