[SQL : HackerRank] Population Density Difference , Weather Observation Station 11, Weather Observation Station 13

1] Population Density Difference

---

>> 문제 

Query the difference between the maximum and minimum populations in CITY.

Input Format

The CITY table is described as follows: 

 

>> 문제 푼 코드 

select max(population) - min(population)
from city

www.hackerrank.com/challenges/population-density-difference/problem

Population Density Difference | HackerRank

 

2]  Weather Observation Station 11

---

>> 문제 

Query the list of CITY names from STATION that either do not start with vowels or do not end with vowels. Your result cannot contain duplicates.

Input Format

The STATION table is described as follows:

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

 

>> 문제 해결 코드

select distinct city
from station
where ( city  NOT LIKE 'A%' and city NOT LIKE 'E%' and
    city NOT LIKE 'O%' and city NOT LIKE 'I%' and city NOT LIKE 'U%') 
    or ( city NOT LIKE '%a' and city NOT LIKE '%e' and
    city NOT LIKE '%o' and city NOT LIKE '%i' and city NOT LIKE '%u' ) 

>> 더 간단하게 표현할 수 있는 코드

select disticnt city
from staition
where left(city, 1) Not in ('A', 'E', 'I', 'O' , 'U')
	OR RIGHT(city, 1) Not in ('A', 'E', 'I', 'O' , 'U')

※ left / right 함수를 이용해서 풀 수 있다는 점을 기억하다. 

 

www.hackerrank.com/challenges/weather-observation-station-11/problem

Weather Observation Station 11 | HackerRank

 

 

3]  Weather Observation Station 13

---

>> 문제 

Query the sum of Northern Latitudes (LAT_N) from STATION having values greater than  and less than . Truncate your answer to  decimal places.

Input Format

The STATION table is described as follows:

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

 

>> 문제 푼 코드

select truncate(sum(lat_n), 4)
from station
where lat_n > 38.7880 and lat_n < 137.2345

 

: 집계함수가 있는 것은 굳이 groupby안해도된다! 없는 것은 해야한다! 잊지말자

 

www.hackerrank.com/challenges/weather-observation-station-13/problem

Weather Observation Station 13 | HackerRank