SQLZOO刷题记录2——SELECT from WORLD Tutorial

题目5：France, Germany, Italy

Show the name and population for France, Germany, Italy

解题5：

SELECT name, population
FROM world
WHERE name IN ('France', 'Germany', 'Italy')

 注意：函数IN的用法，括号()，()内部字符串加引号。

题目8：

Exclusive OR (XOR). Show the countries that are big by area (more than 3 million) or big by population (more than 250 million) but not both. Show name, population and area.

• Australia has a big area but a small population, it should be included.
• Indonesia has a big population but a small area, it should be included.
• China has a big population and big area, it should be excluded.
• United Kingdom has a small population and a small area, it should be excluded.

解题8：

SELECT name, population, area 
FROM world 
WHERE (area > 3000000) ^ (population > 250000000) 

注意：异或属于位运算符，条件语句的值为true或false，符合要求。

题目9：

Show the name and population in millions and the GDP in billions for the countries of the continent 'South America'. Use the ROUND function to show the values to two decimal places.

For South America show population in millions and GDP in billions both to 2 decimal places.

Millions and billions

Divide by 1000000 (6 zeros) for millions. Divide by 1000000000 (9 zeros) for billions.

解题9：

SELECT name, ROUND(population/1000000,2) AS population, ROUND(gdp/1000000000,2) AS GDP 
FROM world 
WHERE continent='South America' 

注意：ROUND(f,p) returns f rounded to p decimal places.

题目12：

The capital of Sweden is Stockholm. Both words start with the letter 'S'.

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.

• You can use the function LEFT to isolate the first character.
• You can use <> as the NOT EQUALS operator.

解题12：

SELECT name, capital 
FROM world 
WHERE LEFT(name,1) = LEFT(capital,1) 
AND name <> capital

题目13：

Equatorial Guinea and Dominican Republic have all of the vowels (a e i o u) in the name. They don't count because they have more than one word in the name.

Find the country that has all the vowels and no spaces in its name.

• You can use the phrase name NOT LIKE '%a%' to exclude characters from your results.
• The query shown misses countries like Bahamas and Belarus because they contain at least one 'a'

解题13：

SELECT name
FROM world
WHERE name LIKE '%a%' 
AND name LIKE '%e%' 
AND name LIKE '%i%' 
AND name LIKE '%o%' 
AND name LIKE '%u%' 
AND name NOT LIKE '% %' 

 注意：这道题目提交的结果是正确的，但是感觉不够严谨，应该可以做到更加优化。