[SQL 연습 문제] 코딩 테스트 대비 해커랭크 easy 문제 풀이(4)

사용한 키워드

- SELECT, FROM, WHERE, GROUP BY, ORDER BY, LIMIT, MAX, MIN, SUM, AVG, ROUND, FLOOR, TRUNCATE, INNER JOIN, SET, REPEAT

 

 

1. Weather Observation Station 13

Weather Observation Station 13 | HackerRank

SELECT TRUNCATE(SUM(lat_n),4)
FROM station
WHERE lat_n > 38.7880
AND lat_n <137.2345;

숫자 함수

TRUNCATE(숫자, 자릿수): 자릿수를 기준으로 숫자를 버림

ex) TRUNCATE(13.56789, 2) -> 13.56

 

 

2. Weather Observation Station 14

Weather Observation Station 14 | HackerRank

SELECT TRUNCATE(MAX(lat_n),4)
FROM station
WHERE lat_n <137.2345;

 

 

3. Weather Observation Station 15

Weather Observation Station 15 | HackerRank

SELECT ROUND(long_w,4)
FROM station
WHERE lat_n <137.2345
ORDER BY lat_n DESC
LIMIT 1;

 

 

4. Weather Observation Station 16

Weather Observation Station 16 | HackerRank

SELECT ROUND(MIN(lat_n), 4)
FROM station
WHERE lat_n > 38.7780;

 

 

5. Weather Observation Station 17

Weather Observation Station 17 | HackerRank

SELECT ROUND(long_w,4)
FROM station
WHERE lat_n > 38.7780
ORDER BY lat_n
LIMIT 1;

 

 

6. Population Census

Population Census | HackerRank

SELECT SUM(city.population)
FROM city
    INNER JOIN country ON CITY.CountryCode = COUNTRY.Code
WHERE country.Continent = 'Asia';

 

 

7. African Cities

African Cities | HackerRank

SELECT city.name
FROM city
    INNER JOIN country ON CITY.CountryCode = COUNTRY.Code
WHERE country.Continent = 'Africa';

 

 

8. Average Population of Each Continent

Average Population of Each Continent | HackerRank

SELECT country.continent, FLOOR(AVG(city.population))
FROM city
    INNER JOIN country ON CITY.CountryCode = COUNTRY.Code
GROUP BY country.continent;

 

 

9. Draw The Triangle 1

Draw The Triangle 1 | HackerRank

SET @number = 21;
SELECT REPEAT('* ', @number := @number - 1) 
FROM information_schema.tables 
LIMIT 20;

 

 

10. Draw The Triangle 2

Draw The Triangle 2 | HackerRank

SET @number = 0;
SELECT REPEAT('* ', @number := @number + 1) 
FROM information_schema.tables 
LIMIT 20;