Mysql 문제풀이 강의 해설 SET2

📌 Weather Observation Station 5

https://www.hackerrank.com/challenges/weather-observation-station-5/problem

[내가 작성한 답] - 오답

SELECT T.CITY, T.L, S.CITY, LENGTH(S.CITY)
FROM (
    SELECT ID, CITY, MIN(LENGTH(CITY)) AS L
    FROM STATION
    GROUP BY ID, CITY
    ORDER BY L
    LIMIT 1) T
INNER JOIN STATION S ON T.ID = S.ID
WHERE LENGTH(S.CITY) = MAX(LENGTH(S.CITY))
GROUP BY T.CITY, T.L, S.CITY, LENGTH(S.CITY)


You can write two separate queries to get the desired output. It need not be a single query.
대박적.. 꼭 한개의 쿼리로 작성할 필요 없음 따라서 아래와 같이 작성도 가능

SELECT CITY, LENGTH(CITY) L
FROM STATION
ORDER BY L, CITY
LIMIT 1;

SELECT CITY, LENGTH(CITY) L
FROM STATION
ORDER BY L DESC, CITY
LIMIT 1;

📌 Binary Tree Nodes

https://www.hackerrank.com/challenges/binary-search-tree-1/problem

도저히 모르겠는데요..

[내가 작성한 답]-오답
SELECT N,
CASE WHEN P IS NULL THEN 'Root'
     ELSE N=P THEN 'Inner' ELSE 'leaf' END
FROM BST


[정답] 
SELECT N,
     CASE 
        WHEN P IS NULL THEN 'Root'
        WHEN N IN (P 컬럼에 들어있는 값이어야 한다) THEN 'Inner'
        ELSE 'Leaf'
        END
FROM BST
ORDER BY N


SELECT N,
     CASE 
        WHEN P IS NULL THEN 'Root'
        WHEN N IN (SELECT DISTINCT P FROM BST) THEN 'Inner'
        ELSE 'Leaf'
    END
FROM BST
ORDER BY N

📌 SQL Project Planning

https://www.hackerrank.com/challenges/sql-projects/problem

[내가 작성한 답] - 오답
SELECT Start_date, END_DATE
FROM Projects
WHere Start_date NOT IN (SELECT End_Date FROM projects)
AND END_DATE NOT IN (SELECT START_DATE FROM projects)

[정답]
1) 각 프로젝트별로 언제 시작했는지 쿼리 작성
2) 각 프로젝트별 언제 끝났는지 쿼리 작성
3) 두 테이블을 조인
4) 프로젝트 걸린 시간을 datediff 함수로 계산


SELECT start_date, end_date
FROM (
	SELECT Start_date, ROW_NUMBER() OVER (ORDER BY START_DATE) RNK
	FROM Projects
	WHere Start_date NOT IN (SELECT DISTINCT End_Date FROM projects)
) S_DATE 
INNER JOIN (
SELECT END_date, ROW_NUMBER() OVER (ORDER BY END_DATE) RNK
FROM Projects
WHere END_date NOT IN (SELECT DISTINCT START_Date FROM projects)
    ) E_DATE ON S_DATE.RNK = E_DATE.RNK
order by datediff(end_date,start_Date), start_Date