SQL学习记录（二）SQLZOO答案

SQL学习记录（二）

包含SELECT from World、BBC QUIZ

2.1、SELECT from World

#找出有至少200百萬(2億)人口的國家名稱，及人均國內生產總值。
SELECT name,GDP/population
FROM world
WHERE population>200000000
#顯示'South America'南美洲大陸的國家名字和以百萬為單位人口數。 將人口population 除以一百萬(1000000)得可得到以百萬為單位人口數。
SELECT name,population/1000000
FROM world
WHERE continent LIKE 'South America'
#顯示法國，德國，意大利(France, Germany, Italy)的國家名稱和人口。
SELECT name,population FROM world
WHERE name IN('France','Germany','Italy')
#顯示包含單詞“United”為名稱的國家。
SELECT name FROM world
WHERE name LIKE concat('%','United','%')
#展示大國的名稱，人口和面積。
SELECT name,population,area FROM world
WHERE area>3000000 OR population>250000000
#顯示以人口或面積為大國的國家，但不能同時兩者。顯示國家名稱，人口和面積。
SELECT name,population,area FROM world
WHERE (area>3000000 AND population<=250000000)
OR (area<=3000000 AND population>250000000)
【或者更简便的用XOR异或条件】
SELECT name,population,area FROM world
WHERE area>3000000 XOR population>250000000
#對於南美顯示以百萬計人口，以十億計2位小數GDP。
SELECT name,ROUND(population/1000000,2),ROUND(GDP/1000000000,2)
FROM world
WHERE continent LIKE 'South America'
#Show the name and per-capita GDP for those countries with a GDP of at least one trillion (1000000000000; that is 12 zeros). Round this value to the nearest 1000.
#Show per-capita GDP for the trillion dollar countries to the nearest $1000.
SELECT name,ROUND(gdp/population,-3)
FROM world
WHERE gdp>=1000000000000
#Show the name and capital where the name and the capital have the same number of characters.
SELECT name,capital FROM world
 WHERE LENGTH(name)=LENGTH(capital)
#Show the name and the capital where the first letters of each match. Don't include countries where the name and the capital are the same word.
SELECT name,capital FROM world
WHERE LEFT(name,1)=LEFT(capital,1)
AND name<>capital
#Find the country that has all the vowels and no spaces in its name.
SELECT name
   FROM world
WHERE name NOT LIKE '% %'
  AND name LIKE '%a%'
AND name LIKE '%e%'
AND name LIKE '%i%'
AND name LIKE '%o%'
AND name LIKE '%u%'

2.2、BBC QUIZ

1. Select the code which gives the name of countries beginning with U
   

2. Select the code which shows just the population of United Kingdom?
   

3. Select the answer which shows the problem with this SQL code - the intended result should be the continent of France:
   

4. Select the result that would be obtained from the following code:
   
   

5. Select the code which would reveal the name and population of countries in Europe and Asia
   

6. Select the code which would give two rows
   

7. Select the result that would be obtained from this code:
   
   
   得分：
   

SQL学习记录（三）