sqlzoo.net 답 (SELECT from WORLD Tutorial)

https://sqlzoo.net/wiki/SELECT_from_WORLD_Tutorial

SELECT from WORLD Tutorial - SQLZOO

SELECT from WORLD Tutorial

-- no1
-- 기본으로 들어가 있는 쿼리 실행하면 답

-- no2

SELECT name FROM world
WHERE population >= 200000000

-- no3

select name, gdp/population as 'per capita GDP'
from world
where population >= 200000000;

-- no4

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

-- no5

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

-- no6

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

-- no7

select name, population, area
from world
where area >= 3000000 or population >= 250000000;

-- no8

select name, population, area
from world
where area >= 3000000 XOR population >= 250000000;

select name, population, area
from world
where
(area >= 3000000 and population < 250000000) or
(area < 3000000 and population >= 250000000);

select name, population, area
from world
where
(area > 3000000) <> (population > 250000000);

-- no9

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

 

function ROUND(number, decimals)

숫자를 소수점 이하 자릿수를 남기고 반올림하는 함수
round(12.99) -> 13
round(12.5, 0) -> 13
round(12.5, 1) -> 12.5
round(12.5, 2) -> 12.50

소숫점 이하 2자리수를 남기고 반올림을 하는 것이니, 12.50 의 0을 반올림하여 12.5가 되는데,

2자리수를 남겨야 하니 12.50이 됨.
음수가 되면 소숫점 이상 지정한 자릿수에서 반올림하고 밑의 수는 0으로 처리

 

 

-- no10

select name, ROUND(gdp/population, -3) as 'per-capita GDP'
from world
where gdp >= 1000000000000

-- no11

SELECT name, capital
from world
where length(name) = length(capital);

-- no12

SELECT name, capital
from world
where (LEFT(name, 1) = LEFT(capital, 1)) and name != capital;

-- no13

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