[중급 SQL 문제풀이] HackerRank 문제 및 풀이(3)

1. Weather Observation Station 3 : https://www.hackerrank.com/challenges/weather-observation-station-3/problem
2. Weather Observation Station 19 : https://www.hackerrank.com/challenges/weather-observation-station-19/problem
3. Placements: https://www.hackerrank.com/challenges/placements/problem
4. Binary Tree Nodes: https://www.hackerrank.com/challenges/binary-search-tree-1/problem

 

 

 

 

 

 

 

 

 

 

 

 

답안

1. Weather Observation Station 3

   -ID가 짝수 인 결과 출력

/*
Query a list of CITY names from STATION for cities that have an even ID number
*/
SELECT DISTINCT(CITY) FROM STATION WHERE MOD(ID,2) = 0

 

2. 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.
*/

-- a: minimum(lat_n), b: maximum(lat_n), c: minimum(long_w), d: maximum(long_w)
-- p1(minimum(lat_n),minimum(long_w)) , p2(maximum(lat_n), maximum(long_w))

SELECT ROUND(SQRT(POW(max(lat_n)- min(lat_n),2) + POW(max(long_w)- min(long_w),2)),4)
FROM STATION

3. Placements

/*
Write a query to output the names of those students whose best friends got offered a higher salary than them. Names must be ordered by the salary amount offered to the best friends. It is guaranteed that no two students got same salary offer.
*/

SELECT (SELECT S.NAME FROM Students S WHERE S.ID = F.ID)  
FROM  Friends F
inner join Packages P1 on F.ID = P1.ID
inner join Packages P2 on F.Friend_ID = P2.ID
WHERE P1.salary <  P2.salary
ORDER BY P2.salary

 

4. Binary Tree Nodes - 어려운 문제

Root : 부모 x

Leaf : 자식 x, 부모가 아님( P컬럼에서 등장 x) 

 

/*
N and P, where N represents the value of a node in Binary Tree, and P is the parent of N.
*/

SELECT DISTINCT B1.N ,
       (CASE 
            WHEN B1.P IS NULL THEN 'Root'   
            WHEN  B2.N IS NULL THEN 'Leaf' 
            else 'Inner'    
        end
        ) AS result
FROM BST B1 
LEFT JOIN BST B2 ON B1.N = B2.P
ORDER BY B1.N