SQLZOO SELECT BASIC 문제 풀이

2. name,population을 보여주고 Sweden,Norway,Denmark를 선택한다.

SELECT name, population 
FROM world
WHERE name IN ('Sweden', 'Norway', 'Denmark');

3. 200000과 250000 area를 구하라

SELECT name, area 
FROM world
WHERE area BETWEEN 200000 AND 250000

4. Show the name and population in millions for the countries of the continent 'South America'. Divide the population by 1000000 to get population in millions.

select name,population/1000000
from world
where continent='South America'

5. Show the name and population for France, Germany, Italy

select name,population
from world
where name IN('France','Germany','Italy');

6. Show the countries which have a name that includes the word 'United'

select name
from world
where name like'%United%';

7.  Two ways to be big: A country is big if it has an area of more than 3 million sq km or it has a population of more than 250 million. Show the countries that are big by area or big by population. Show name, population and area.

SELECT name,population,area
FROM world
WHERE area>3000000
OR population>250000000

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.
• XOR은 둘 중에 하나를 만족할 때 출력하지만 두 조건이 만족할 때는 X

SELECT name,population,area
FROM world
WHERE area>3000000
XOR population>250000000

9. round 함수 사용 For South America show population in millions and GDP in billions both to 2 decimal places. 소수점 2째자리 까지

select name, ROUND(population/1000000,2),ROUND(gdp/1000000000,2)
from world
where continent='South America'

10. Show per-capita GDP for the trillion dollar countries to the nearest $1000

SELECT name, ROUND(gdp/population, -3)
FROM world
WHERE gdp > 1000000000000

11. Show the name and capital where the name and the capital have the same number of characters

name과 capital의 length가 같은 것들을 출력해라.

SELECT name, capital
FROM world
WHERE LENGTH(name) =LENGTH(capital);

12. 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.

name과 capital에서 첫 글자가 같지만 name,capital가 전부 같은 것은 제외한다.

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

13. a,e,i,o,u 모음을 모두 포함하고 빈 칸은 포함하지 않는다. 

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

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 '% %';

14. Weather Observation Station 3 - ID가 짝수인 city를 중복없이 검색해야 합니다.

select Distinct city
from station
where MOD(ID,2) = 0