※ The STATION table is described as follows:
( where LAT_N is the northern latitude and LONG_W is the western longitude. )
■ [Aggregation] Weather Observation Station 13
https://www.hackerrank.com/challenges/weather-observation-station-13/problem?isFullScreen=true
Q.
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 4 decimal places.
A.
SELECT TRUNC(SUM(LAT_N),4) FROM STATION WHERE LAT_N > 38.7880 AND LAT_N < 137.2345;
■ [Aggregation] Weather Observation Station 14
https://www.hackerrank.com/challenges/weather-observation-station-14/problem?isFullScreen=true
Q.
Query the greatest value of the Northern Latitudes (LAT_N) from STATION that is less than 137.2345. Truncate your answer to 4 decimal places.
A.
SELECT TRUNC(MAX(LAT_N),4) FROM STATION WHERE LAT_N < 137.2345;
■ [Aggregation] Weather Observation Station 15
https://www.hackerrank.com/challenges/weather-observation-station-15/problem?isFullScreen=true
Q.
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.
A.
SELECT ROUND(LONG_W,4) FROM STATION WHERE LAT_N = (SELECT MAX(LAT_N) FROM STATION WHERE LAT_N < 137.2345);SELECT ROUND(LONG_W,4) FROM STATION GROUP BY LONG_W, LAT_N HAVING LAT_N = (SELECT MAX(LAT_N) FROM STATION WHERE LAT_N < 137.2345);SELECT ROUND(LONG_W,4) FROM (SELECT LONG_W FROM STATION WHERE LAT_N < 137.2345 ORDER BY LAT_N DESC) WHERE ROWNUM = 1;SELECT ROUND(LONG_W,4) FROM (SELECT ROW_NUMBER() OVER(ORDER BY LAT_N DESC) AS RN, LONG_W FROM STATION WHERE LAT_N < 137.2345) WHERE RN = 1;
-- RANK()SELECT ROUND(LONG_W,4)
FROM STATION S
,(SELECT MAX(LAT_N) AS MAX_LAT_N FROM STATION WHERE LAT_N < 137.2345) S_FL
WHERE S.LAT_N = S_FL.MAX_LAT_N;( https://stackoverflow.com/questions/3680254/t-sql-selecting-column-based-on-maxother-column )
■ [Aggregation] Weather Observation Station 16
https://www.hackerrank.com/challenges/weather-observation-station-16/problem?isFullScreen=true
Q.
Query the smallest Northern Latitude (LAT_N) from STATION that is greater than 38.7780. Round your answer to 4 decimal places.
A.
SELECT ROUND(MIN(LAT_N),4) FROM STATION WHERE LAT_N > 38.7780;
■ [Aggregation] Weather Observation Station 17
https://www.hackerrank.com/challenges/weather-observation-station-17/problem?isFullScreen=true
Q.
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.
A.
SELECT ROUND(LONG_W,4) FROM STATION WHERE LAT_N = (SELECT MIN(LAT_N) FROM STATION WHERE LAT_N > 38.7780);
■ [Aggregation] Weather Observation Station 18
https://www.hackerrank.com/challenges/weather-observation-station-18/problem?isFullScreen=true
Q.
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.
A.
-- (https://xlinux.nist.gov/dads/HTML/manhattanDistance.html)
-- ■ Manhattan Distance :
-- The distance between two points measured along axes at right angles.
-- In a plane with p1 at (x1, y1) and p2 at (x2, y2), it is |x1 - x2| + |y1 - y2|.
-- A = MIN(LAT_N)
-- B = MIN(LONG_W)
-- C = MAX(LAT_N)
-- D = MAX(LONG_W)
-- SELECT ROUND( ABS(MIN(LAT_N)-MAX(LAT_N)) + ABS(MIN(LONG_W)-MAX(LONG_W)) , 4 ) FROM STATION;
SELECT ROUND( ( MAX(LAT_N) - MIN(LAT_N) ) + ( MAX(LONG_W) - MIN(LONG_W) ) , 4 ) FROM STATION;
■ [Aggregation] Weather Observation Station 19
https://www.hackerrank.com/challenges/weather-observation-station-19/problem?isFullScreen=true
Q.
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.
A.
-- (https://en.wikipedia.org/wiki/Euclidean_distance)
-- ■ Euclidean distance :
-- the Euclidean distance between two points in Euclidean space
-- is the length of a line segment between the two points.
-- It can be calculated from the Cartesian coordinates of the points using the Pythagorean theorem,
-- therefore occasionally being called the Pythagorean distance.
-- P( p1, p2 ), Q( q1, q2 )
-- D( P, Q ) = Math.sqrt( Math.pow( q1 - p1, 2 ) + Math.pow( q2 - p2, 2 ) )
-- A = MIN(LAT_N)
-- B = MAX(LAT_N)
-- C = MIN(LONG_W)
-- D = MAX(LONG_W)
SELECT ROUND( SQRT( POWER( MAX(LAT_N) - MIN(LAT_N), 2 ) + POWER( MAX(LONG_W) - MIN(LONG_W), 2 ) ) , 4 ) FROM STATION;
■ [Aggregation] Weather Observation Station 20
https://www.hackerrank.com/challenges/weather-observation-station-20/problem?isFullScreen=true
Q.
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.
A.
SELECT ROUND(MEDIAN(LAT_N),4) FROM STATION;