Weather Observation Station 16,17,18,19 | Population Census

 

Weather Observation Station 16 📝 

https://www.hackerrank.com/challenges/weather-observation-station-16/problem?isFullScreen=true 

➡️ station 테이블 

• 38.7780보다 큰 lat_n 중 가장 작은 값 ( 단, 소숫점 5자리에서 반올림) => min,round 함수 사용
  

 

 

Query the smallest Northern Latitude (LAT_N) from STATION that is greater than 38.7780. Round your answer to 4 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 round(min(lat_n),4) 
FROM station 
WHERE lat_n>38.7780;

 

---

 

Weather Observation Station 17📝 

https://www.hackerrank.com/challenges/weather-observation-station-17/problem?isFullScreen=true 

➡️ station 테이블 

• 출력 : long_w 소수점 5자리에서 반올림
• =>조건 1 : lat_n이 38.7780보다 큰 값중에서 가장 작은 값

 

 

Query the Western Longitude (LONG_W)where the smallest Northern Latitude (LAT_N) in STATION is greater than 38.7780. Round your answer to 4 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 ROUND(long_w,4)
FROM station
WHERE lat_n = (SELECT min(lat_n) FROM station WHERE lat_n>38.7780);

 

---

 

Weather Observation Station 18📝 

https://www.hackerrank.com/challenges/weather-observation-station-18/problem?isFullScreen=true 

➡️ station 테이블 

• 출력 : Manhattan Distance P1(a,b) P2(c,d) 사이의 거리 구하기
• => 조건 1 : Manhattan Distance : |a-c| + |b-d| 
• => 조건 2: la-c| = lat_n의 최댓값 - lat_n의 최솟값 
• => 조건 3: |b-d| = long_w의 최댓값 - long_w의 최솟값 

 

Consider  P₁(a,b) and  P₂(c,b) 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 P₁ and P₂ and round it to a scale of 4 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 ROUND(max(lat_n)-min(lat_n)+max(long_w)-min(long_w),4)
FROM station;

 

---

 

Weather Observation Station 19📝 

https://www.hackerrank.com/challenges/weather-observation-station-19/problem?isFullScreen=true 

➡️ station 테이블 

• 출력 : Euclidean Distance P1(a,c) P2(b,d) 사이의 거리 구하기
• => 조건 1 : Euclidean Distance  : 루트( (a-b)^2 + (c-d)^2 )
• => 조건 2: a-b = lat_n의 최댓값 - lat_n의 최솟값 
• => 조건 3: c-d = long_w의 최댓값 - long_w의 최솟값 

 

Consider  P₁(a,c) and P₂(b,d) to be two points on a 2D plane where (a,b)  are the respective minimum and maximum values of Northern Latitude (LAT_N) and (c,d)  are the respective minimum and maximum values of Western Longitude (LONG_W) in STATION. 

Query the Euclidean Distance between points P₁ and P₂ and format your answer to display 4 decimal digits.

Input Format

The STATION table is described as follows:

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

SELECT ROUND(SQRT(POWER(max(lat_n)-min(lat_n),2)+POWER(max(long_w)-min(long_w),2)),4)
FROM station;

 

---

 

Population Census📝 

https://www.hackerrank.com/challenges/asian-population/problem?isFullScreen=true 

➡️ city 테이블,country 테이블 

• 출력 : SUM(city.population)
•  조건 1 : country.continent='Asia'
•  조건 2 : 테이블 JOIN city.countrycode = country.code

 

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.

Input Format

The CITY and COUNTRY tables are described as follows:

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