• 01. Weather Observation Station 3(해커랭크)
• 02. Weather Observation Station 19(해커랭크)
• 03. Placements(해커랭크)
• 04. Binary Tree Nodes(해커랭크)

01. Weather Observation Station 3(해커랭크)

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

-- 내 문제 풀이(정답)
SELECT DISTINCT CITY
FROM STATION
WHERE ID % 2 = 0

-- MOD() 활용
SELECT DISTINCT CITY
FROM STATION
WHERE MOD(ID, 2) = 0

02. 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 decimal digits.

-- 내 문제 풀이(정답)
SELECT ROUND(SQRT(POW(MAX(LAT_N)-MIN(LAT_N),2) + POW(MAX(LONG_W)-MIN(LONG_W),2)),4)
FROM STATION

03. Placements(해커랭크)

You are given three tables: Students, Friends and Packages. Students contains two columns: ID and Name. Friends contains two columns: ID and Friend_ID (ID of the ONLY best friend). Packages contains two columns: ID and Salary (offered salary in $ thousands per month).

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.

-- 내 문제 풀이(오답) => 문제해석실수 : 친구의 salary 기준으로 order by 해야!
SELECT NAME
FROM Packages P1 INNER JOIN Friends F ON P1.ID = F.ID
                INNER JOIN Students S ON P1.ID = S.ID
                INNER JOIN Packages P2 ON P2.ID = F.Friend_ID
WHERE P1.Salary < P2.Salary
ORDER BY P1.Salary

-- 정답 (Friends 테이블을 기준으로 INNER JOIN 하는 것이 더 깔끔하다.)
SELECT NAME
FROM Friends F INNER JOIN Students S ON F.ID = S.ID
               INNER JOIN Packages P1 ON P1.ID = F.ID
               INNER JOIN Packages P2 ON P2.ID = F.Friend_ID
WHERE P1.Salary < P2.Salary
ORDER BY P2.Salary

04. Binary Tree Nodes(해커랭크)

You are given a table, BST, containing two columns: N and P, where N represents the value of a node in Binary Tree, and P is the parent of N.

Write a query to find the node type of Binary Tree ordered by the value of the node. Output one of the following for each node:

• Root: If node is root node.
• Leaf: If node is leaf node.
• Inner: If node is neither root nor leaf node.

-- 내 문제 풀이(오답)
SELECT N
     , CASE WHEN ISNULL(P) THEN 'Root'
            WHEN N NOT IN (P) THEN 'Leaf'
            ELSE 'Inner'
       END
FROM BST
ORDER BY N

-- LEFT JOIN 활용 (좌측 테이블에 해당 값 없으면 NULL이 되는 성질 활용) => 서브쿼리 활용하면 더 깔끔히 풀이 가능!
SELECT DISTINCT BST.N
     , CASE WHEN BST.P IS NULL THEN 'Root'
            WHEN BST2.N IS NULL THEN 'Leaf'
            ELSE 'Inner'
       END
FROM BST LEFT JOIN BST AS BST2 ON BST.N = BST2.P
ORDER BY BST.N