[MySQL/Hackerrank] Weather Observation Station 2~20

Weather Observation Station 2

Query the following two values from the STATION table:

1. The sum of all values in LAT_N rounded to a scale of 2 decimal places.
2. The sum of all values in LONG_W rounded to a scale of 2 decimal places.Input Format
3. The STATION table is described as follows:

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

Output Format

Your results must be in the form:

lat lon

where lat  is the sum of all values in LAT_N and lon is the sum of all values in LONG_W. Both results must be rounded to a scale of 2 decimal places.

SELECT ROUND(SUM(LAT_N),2)
    , ROUND(SUM(LONG_W),2)
FROM STATION

 

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. Truncate your answer to  decimal places.

Input Format

The STATION table is described as follows:

-- TRANCATE 하라고 해놓고선 ROUND로 안하면 에러나는거 이해안감

SELECT ROUND(SUM(LAT_N), 4)
FROM STATION 
WHERE LAT_N > 38.7880
    AND LAT_N < 137.2345;
    
SELECT ROUND(SUM(LAT_N), 4)
FROM STATION 
WHERE LAT_N BETWEEN 38.7880 AND 137.2345;

Weather Observation Station 14

Query the greatest value of the Northern Latitudes (LAT_N) from STATION that is less than 137.2345 . Truncate your answer to  decimal places.

Input Format

The STATION table is described as follows:

SELECT ROUND(MAX(LAT_N),4)
FROM STATION
WHERE LAT_N < 137.2345;

 

Weather Observation Station 15

Query the Western Longitude (LONG_W) for the largest Northern Latitude (LAT_N) in STATION that is less than 137.2345. Round your answer to 4 decimal places.

Input Format

The STATION table is described as follows:

SELECT ROUND(LONG_W,4)
FROM STATION
WHERE LAT_N IN (
    SELECT MAX(LAT_N)    
    FROM STATION
    WHERE LAT_N < 137.2345
);

Weather Observation Station 16

Query the smallest Northern Latitude (LAT_N) from STATION that is greater than 33.7780. Round your answer to 4 decimal places.

Input Format

The STATION table is described as follows:

SELECT ROUND(MIN(LAT_N),4)
FROM STATION
WHERE LAT_N > 38.7780

 

Weather Observation Station 17

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:

SELECT ROUND(LONG_W,4)
FROM STATION
WHERE LAT_N IN (
    SELECT MIN(LAT_N)
    FROM STATION 
    WHERE LAT_N > 38.7780
    );

 

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  decimal places.

Input Format

The STATION table is described as follows:

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

/*
 Manhattan Distance는 |x1 - x2| + |y1 - y2| 이거라고 한다
*/

Weather Observation Station 19

Consider P1(a,c) and P2(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 P1 and P2 and format your answer to display 4 decimal digits.

Input Format

The STATION table is described as follows:

SELECT
    ROUND(SQRT(
        POWER(MAX(LAT_N)  - MIN(LAT_N),  2)
      + POWER(MAX(LONG_W) - MIN(LONG_W), 2)
    ), 4)
FROM 
    STATION;

Weather Observation Station 20

A median is defined as a number separating the higher half of a data set from the lower half. Query the median of the Northern Latitudes (LAT_N) from STATION and round your answer to 4 decimal places.

Input Format

The STATION table is described as follows:

--너무 수학적이어서 포기 ㅡ.ㅡ;

SET @N := 0;
SELECT COUNT(*) FROM STATION INTO @TOTAL;

SELECT
    ROUND(AVG(A.LAT_N), 4)
FROM (SELECT @N := @N +1 AS ROW_ID, LAT_N FROM STATION ORDER BY LAT_N) A
WHERE
    CASE WHEN MOD(@TOTAL, 2) = 0 
            THEN A.ROW_ID IN (@TOTAL/2, (@TOTAL/2+1))
            ELSE A.ROW_ID = (@TOTAL+1)/2
    END
;