[코드카타 연습하기]코드카타 SQL_(165-169)

코드카타 레벨 7 165-169

165. Given the CITY and COUNTRY tables, query the sum of the populations of all cities where the CONTINENT is 'Asia'.

Note: CITY.CountryCode and COUNTRY.Code are matching key columns

SELECT SUM(CITY.POPULATION)
FROM CITY INNER JOIN COUNTRY 
ON CITY.COUNTRYCODE= COUNTRY.CODE
WHERE CONTINENT = 'ASIA'

166. Given the CITY and COUNTRY tables, query the names of all cities where the CONTINENT is 'Africa'.

Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

SELECT CITY.NAME
FROM CITY INNER JOIN COUNTRY 
ON CITY.COUNTRYCODE= COUNTRY.CODE
WHERE CONTINENT = 'AFRICA'

167. Given the CITY and COUNTRY tables, query the names of all the continents (COUNTRY.Continent) and their respective average city populations (CITY.Population) rounded down to the nearest integer.

Note: CITY.CountryCode and COUNTRY.Code are matching key columns.

SELECT COUNTRY.Continent, FLOOR(AVG(CITY.Population))
FROM CITY INNER JOIN COUNTRY 
ON CITY.COUNTRYCODE= COUNTRY.CODE
GROUP BY 1;

168. 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 DISTINCT BT.N,
    CASE 
        WHEN BT.P IS NULL THEN 'Root'
        WHEN CHILD.P IS NULL THEN 'Leaf'
        ELSE 'Inner'
    END
FROM BST BT
    LEFT JOIN BST CHILD ON BT.N = CHILD.P
    LEFT JOIN BST PARENT ON BT.P = PARENT.N
ORDER BY BT.N;

if) n 의 값을 가지고 있는 p가 없다(null) → leaf
if) p가 없다(null) → root
else) Inner
->CHILD 가 Null 인것들을 검증해야함-> 즉,LEFT JOIN.

169. Amber's conglomerate corporation just acquired some new companies. Each of the companies follows this hierarchy:
     
     Given the table schemas below, write a query to print the company_code, founder name, total number of lead managers, total number of senior managers, total number of managers, and total number of employees. Order your output by ascending company_code.

Note:

The tables may contain duplicate records.
The company_code is string, so the sorting should not be numeric. For example, if the company_codes are C_1, C_2, and C_10, then the ascending company_codes will be C_1, C_10, and C_2.

SELECT C.company_code, FOUNDER, 
       COUNT(DISTINCT LM.lead_manager_code),
       COUNT(DISTINCT SM.senior_manager_code),
       COUNT(DISTINCT M.manager_code),
       COUNT(DISTINCT E.employee_code)
FROM Company C 
LEFT JOIN Lead_Manager LM 
ON C.company_code = LM.company_code
LEFT JOIN Senior_Manager SM
ON LM.lead_manager_code = SM.lead_manager_code
LEFT JOIN Manager M
ON SM.senior_manager_code = M.senior_manager_code
LEFT JOIN Employee E
ON M.manager_code = E.manager_code
GROUP BY 1,2
ORDER BY 1;

SENIOR MANAGER 의 케이스를 위해 이너 조인말고 LEFT JOIN이 필요!

인사이트

168번의 경우 자식노드와 부모노드의 관계를 잘 파악하는 것이 가장 중요한 문제였다.


먼저 가장 쉬운 부모노드를 찾으려면 p가 존재하지 않아야 가장 상위이다. 그러므로 표에서 보듯이 5번은 p의 값이 null이므로 최상위인 'root'가 된다.
조건으로 바꾸면, if( p is null, 'root')
그리고 n에는 있고 p가 null은 아니지만, 값이 없다면, leaf로 표현한다. 가장 아래에 있다고 보면된다.
마지막으로 그 나머지를 inner중간단계로 본다.

조건을 세우고 이를 테이블로 만드는것 역시 쉽지 않은데, 일단 null의 값을 확인하기 위해 left join을 해야했고, 주어진 테이블은 하나이지만 부모와 자식 테이블로 나눠야하기 때문에 하나의 테이블을 여러개로 생각해 조합을 해줘야하는 복잡한 문제였다.

SELECT *
FROM BST BT
    LEFT JOIN BST CHILD ON BT.N = CHILD.P
    LEFT JOIN BST PARENT ON BT.P = PARENT.N
ORDER BY BT.N;

->

관계 파악부터, 조인을 만드는것 까지 시간도 오래 걸리고, 어려운 문제였다.

169번의 문제는 조인의 과정이 복잡해보이지만 그렇게 어려운 문제는 아니었다. 하지만 시니어 매니저의 값을 구하려면 이너조인이 아니라 left조인을 사용해야했다!