SQLZOO题目小结备忘

SQLZOO小结

做了SQLZOO的题目，写一些小结帖以便查看；以下内容部分参考这里

SUM and COUNT

6.For each continent show the continent and number of countries.

select continent,count(*) from world
group by continent;

7.For each continent show the continent and number of countries with populations of at least 10 million.

select continent,count(*)
from world
where population>10000000
group by continent

8.List the continents that have a total population of at least 100 million.

select continent from world
group by continent
having sum(population)>100000000;

SUM and COUNT Quiz

7. Select the statement that shows the name and population density of the country with the largest population

 SELECT name, population/area AS density 
 FROM bbc 
 WHERE population = 
 (SELECT MAX(population) FROM bbc)

SELECT within SELECT Tutorial

5.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.[遗留问题]

select name,
concat(round(population/(select population from world where name='Germany')*100,0),'%')
as percentage
from world 
where continent='Europe';#不会去除结果中的多余的0；查了cast后使用，报错不会修改

6.Which countries have a GDP greater than every country in Europe? [Give the name only.] (Some countries may have NULL gdp values)

#法1：
select name from world 
where gdp>