오늘도 데이터분석

Average Population of Each Continent 문제 바로가기

문제해석

Given the CITY and COUNTRY tables, query the names of all the continents (COUNTRY.Continent) and their respective average city populations (CITY.Population) rounded down to the nearest integer.
Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

city와 country 테이블에서 모든 대륙(COUNTRY.Continent)의 이름과 대륙별 도시인구수(CITY.Population)의 평균을 구하는 쿼리 입력. 도시인구수의 평균값은 가장 가까운 정수로 내림처리.

The CITY and COUNTRY tables are described as follows: 

문제풀이

SELECT country.continent, FLOOR(AVG(city.population))
FROM city
         INNER JOIN country ON city.countrycode = country.code
GROUP BY country.continent

1. country code로 city와 country 조인

2. 대륙(continent)으로 그룹화

3. population을 AVG함수로 평균값 구하기

4. FLOOR 함수로 소수점 내림처리

   (* 올림 : CEIL , 반올림: ROUND)

Tip

-- GROUP BY 함수에서 그룹화의 기준이 되는 컬럼은 반드시 SELECT 에 적어주기

-- population 컬럼은 두 테이블에 중복되어 테이블명을 명시해줘야 함. 명시하지 않으면 에러 발생

-- continent 컬럼은 country테이블에만 있어 테이블명을 명시하지 않아도 추출 가능, 쿼리 결과 차이없음

   그러나 다른 사람이 읽는 경우 테이블명을 명시하는 것이 좋고, 쿼리 성능면에서도 전체 테이블이 아닌 해당 테이블 내에서만 컬럼 검색을 하기 때문에 연산속도에 영향을 줄 수 있음

해커랭크 Population Census 문제 바로가기 

문제해석

Given the CITY and COUNTRY tables, query the sum of the populations of all cities where the CONTINENT is 'Asia'.
Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

city, country 테이블에서 continent가 'Asia'인 도시들의 인구수 합을 구하는 쿼리입력 

The CITY and COUNTRY tables are described as follows: 

문제풀이

SELECT SUM(city.population)
FROM city
         INNER JOIN country ON city.countrycode = country.code
WHERE country.continent = 'Asia'

1. countrycode를 중심으로 INNER JOIN으로 city와 country 두 테이블을 조인

2. continent가 'Asia'인 데이터 WHERE 조건문으로 필터링

3. population의 합 SUM함수로 구하기 

Tip

-- population 컬럼은 두 테이블에 중복되어 테이블명을 명시해줘야 함. 명시하지 않으면 에러 발생

-- continent 컬럼은 country테이블에만 있어 테이블명을 명시하지 않아도 추출 가능, 쿼리 결과 차이없음

   그러나 다른 사람이 읽는 경우 테이블명을 명시하는 것이 좋고, 쿼리 성능면에서도 전체 테이블이 아닌 해당 테이블 내에서만 컬럼 검색을 하기 때문에 연산속도에 영향을 줄 수 있음

해커랭크 African Cities 문제 바로가기 

<문제 해석>

Given the CITY and COUNTRY tables, query the names of all cities where the CONTINENT is 'Africa'.
Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

주어진 CITY, COUNTRY 테이블에서 CONTINENT가 'Africa'인 모든 도시이름이 나오는 쿼리를 입력 
CITY테이블에서 CountryCode 와 Country테이블에서 Code가 매칭되는 컬럼. 

The CITY and COUNTRY tables are described as follows: 

<문제 풀이>

WHERE 사용 풀이

1. 두 테이블을 매칭되는 countrycode로 조인

2. continent 가 'Africa'인 WHERE 조건문

3. name 컬럼이 두 테이블 모두 있기 때문에 도시이름이 적힌 city테이블의 name을 추출

SELECT city.name
FROM city
         INNER JOIN country ON city.countrycode = country.code
WHERE country.continent = 'Africa'

AND 사용 풀이

1. 두 테이블을 country code와 continent =  'Africa' 기준으로 조인

2. name 컬럼이 두 테이블 모두 있기 때문에 도시이름이 적힌 city테이블의 name을 추출

SELECT city.name
FROM city
         INNER JOIN country ON city.countrycode = country.code AND country.continent = 'Africa'

해커랭크 Weather Observation Station 18 문제 바로가기 

문제 해석

Consider  P1(a,b) and P2(c,d) to be two points on a 2D plane.
a happens to equal the minimum value in Northern Latitude (LAT_N in STATION).
b happens to equal the minimum value in Western Longitude (LONG_W in STATION).
c happens to equal the maximum value in Northern Latitude (LAT_N in STATION).
d happens to equal the maximum value in Western Longitude (LONG_W in STATION).
Query the Manhattan Distance between points  P1 and P2 and round it to a scale of 4 decimal places.

2차원 평면상에서 P1(a,b),  P2(c,d) 두 점이 있음
a는 LAT_N의 최솟값
b는 LONG_W의 최솟값
c는 LAT_N의 최댓값
d는 LONG_W의 최댓값

두 점의 맨해튼 거리를 구하는 쿼리 입력, 그리고 결과는 소수점 4자리까지 반올림.

*맨해튼 거리(Manhattan Distance)
: 축을 따라 직각으로 측정된 두 점 사이의 거리  --> | x1 - x2 | +| y1 - y2 |
즉 이 문제에서는 | a - c | + | b - d |

The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.

문제 풀이

SELECT ROUND(ABS(MIN(LAT_N) - MAX(LAT_N)) + ABS(MIN(LONG_W) - MAX(LONG_W)),4)
FROM station

풀이 순서

1. a = MIN(LAT_N)

   b = MAX(LAT_N)

   c = MIN(LONG_W)

   d = MAX(LONG_W)

2. | a - c | + | b - d |  계산

   절대값은 ABS 함수 사용

3. 반올림 ROUND  함수 사용

해커랭크 Weather Observation Station 17 문제 바로가기 

문제 해석

Query the Western Longitude(LONG_W)where the smallest Northern Latitude(LAT_N) in STATION is greater than 38.7780.
station 테이블에서 북위(LAT_N) 38.7780보다 큰 값 중에서 가장 작은 북위일 때, 서부경도의 값을 나타내는 쿼리를 입력

Round your answer to 4 decimal places.
반올림해서 소수점 4자리까지 나타내기.

The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.

문제 풀이

SELECT ROUND(LONG_W, 4)
FROM station 
WHERE LAT_N > 38.7780
ORDER BY LAT_N ASC
LIMIT 1

SELECT ROUND(LONG_W, 4) -- LONG_W을 반올림해서 소수점4자리까지 나타냄
FROM station                       
WHERE LAT_N > 38.7780     -- 비교연산자와 WHERE 조건문 사용, 38.7780보다 큰 LAT_N
ORDER BY LAT_N ASC         -- LAT_N을 오름차순 정렬, 조건에 맞는 가장 낮은 값이 맨 위에 나타남
                                         (ASC는 생략 가능)
LIMIT 1                            -- 첫번째 데이터만 보기

해커랭크 Weather Observation Station 13 문제 바로가기

문제 해석

Query the sum of Northern Latitudes(LAT_N) from STATION having values greater than 38.7880 and less than 137.2345.
STATION 테이블에서 38.7880보다 크고 137.2345 보다 작은 북위(LAT_N)의 합을 구하는 쿼리를 입력.

Truncate your answer to 4 decimal places.
결과는 소수점 4자리까지 자르기. 

The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.

문제 풀이

SELECT ROUND(SUM(LAT_N),4)
FROM station
WHERE LAT_N > 38.7880 AND LAT_N < 137.2345

-- 소수점 4자리까지 나타내기 위해 ROUND 반올림함수 사용. 
-- 조건문 WHERE 사용
-- 논리연산자 AND와 비교연산자로 조건 나타냄 (LAT_N > 38.7880 AND LAT_N < 137.2345)

해커랭크 Weather Observation Station 7 바로가기

문제 해석

Query the list of CITY names ending with vowels (a, e, i, o, u) from STATION.
--> STATION 테이블에서 모음으로 끝나는 도시(CITY)이름의 리스트를 뽑는 쿼리를 작성하라.

Your result cannot contain duplicates.
--> 결과에 중복된 것이 있으면 안된다. 

The STATION table is described as follows:

where LAT_N is the northern latitude and LONG_W is the western longitude.

문제 풀이

LIKE 사용 풀이

SELECT DISTINCT city
FROM station 
WHERE city LIKE '%a'
   OR city LIKE '%e'
   OR city LIKE '%i'
   OR city LIKE '%o'
   OR city LIKE '%u'

SELECT DISTINCT city   -- city 컬럼을 불러오되 중복을 피하기 위해 DISTINCT 사용
FROM station             
WHERE city LIKE '%a'    -- 조건문 WHERE 사용, '와일드카드 %'로 모음으로 끝나는 이름 조건, 
     OR city LIKE '%e'        논리연산자 OR사용으로 각 a,e,i,o,u 으로 끝나는 도시이름 모두 추출
     OR city LIKE '%i'
     OR city LIKE '%o'
     OR city LIKE '%u'