감으로 코딩하던 내가 알고 코딩할 때까지

문제

위와 같은 STATION 테이블에서, $P_{1}(a, b), P_{2}(c, d)$를 정의

- a: LAT_N의 최소값

- b: LONG_W의 최소값

- c: LAT_N의 최대값

- d: LONG_W의 최대값

일 때,

1번 문제: $P_{1}$과 $P_{2}$ 사이의 맨하탄 거리를 구하고, 소숫점 4자리로 반올림

2번 문제:  $P_{1}$과 $P_{2}$ 사이의 유클리디안 거리를 구하고, 소숫점 4자리로 반올림

※ 맨하탄 거리 $d(P_{1}, P_{2}) = (|a - c|) + (|b - d|)$

※ 유클리디안 거리 $d(P_{1}, P_{2}) = \sqrt{(a - c)^{2} + (b - d)^{2}}$

풀이

-- 1번. 맨하탄 거리
-- a: MIN(LAT_N)
-- b: MIN(LONG_W)
-- c: MAX(LAT_N)
-- d: MAX(LONG_W)
-- ABS(): 절댓값(| |)
-- ROUND(a, n): a를 소숫점 n자리로 반올림
SELECT ROUND(ABS(MIN(LAT_N) - MAX(LAT_N)) + ABS(MIN(LONG_W) - MAX(LONG_W)), 4) FROM STATION

-- 출력
259.6859

-- 2번. 유클리디안 거리
-- a: MIN(LAT_N)
-- b: MIN(LONG_W)
-- c: MAX(LAT_N)
-- d: MAX(LONG_W)
-- SQRT(): 제곱근
-- POWER(a, n): a의 n제곱
-- ROUND(a, n): a를 소숫점 n자리로 반올림
SELECT ROUND(SQRT(POWER(MIN(LAT_N) - MAX(LAT_N), 2) + POWER(MIN(LONG_W) - MAX(LONG_W), 2)), 4) FROM STATION

-- 출력
184.1616

문제

테이블로부터
회사 코드 | 설립자 | Lead Manager 총원 |  Senior Manager 총원 |  Manager 총원 | Employee 총원
을 계산한 테이블을 출력하고, 회사 코드 기준으로 오름차순 정렬

풀이

1. Employee 테이블에 설립자를 제외한 각 직급별 데이터가 다 나와 있으므로, Employee 테이블과 Company 테이블만 JOIN하여 COUNT

SELECT 
    C.company_code,
    C.founder,
    COUNT(DISTINCT E.lead_manager_code),
    COUNT(DISTINCT E.senior_manager_code),
    COUNT(DISTINCT E.manager_code),
    COUNT(DISTINCT E.employee_code)
FROM Company C
    LEFT JOIN Employee E ON C.company_code = E.company_code
GROUP BY
    C.company_code,
    C.founder
ORDER BY C.company_code

-- 출력
C1 Angela 1 2 5 13 
C10 Earl 1 1 2 3 
C100 Aaron 1 2 4 10 
C11 Robert 1 1 1 1 
C12 Amy 1 2 6 14 
C13 Pamela 1 2 5 14 
C14 Maria 1 1 3 5 
C15 Joe 1 1 2 3 
C16 Linda 1 1 3 5 
C17 Melissa 1 2 3 7 
C18 Carol 1 2 5 6 
C19 Paula 1 2 4 7 
C2 Frank 1 1 1 3 
C20 Marilyn 1 1 2 2 
C21 Jennifer 1 1 3 7 
C22 Harry 1 1 3 6 
C23 David 1 1 1 2 
C24 Julia 1 1 2 6 
C25 Kevin 1 1 2 5 
C26 Paul 1 1 1 3 
C27 James 1 1 1 3 
C28 Kelly 1 2 5 9 
C29 Robin 1 2 4 9 
C3 Patrick 1 2 2 5 
C30 Ralph 1 1 2 5 
C31 Gloria 1 1 1 3 
C32 Victor 1 2 4 8 
C33 David 1 2 5 12 
C34 Joyce 1 2 6 10 
C35 Donna 1 2 6 12 
C36 Michelle 1 2 5 11 
C37 Stephanie 1 1 2 5 
C38 Gerald 1 2 4 6 
C39 Walter 1 1 3 7 
C4 Lisa 1 1 1 1 
C40 Christina 1 1 3 6 
C41 Brandon 1 2 3 7 
C42 Elizabeth 1 2 4 8 
C43 Joseph 1 2 4 6 
C44 Lawrence 1 1 3 4 
C45 Marilyn 1 1 1 3 
C46 Lori 1 2 3 9 
C47 Matthew 1 2 3 4 
C48 Jesse 1 1 3 3 
C49 John 1 1 3 8 
C5 Kimberly 1 2 3 9 
C50 Martha 1 1 2 5 
C51 Timothy 1 2 5 12 
C52 Christine 1 1 2 2 
C53 Anthony 1 1 1 1 
C54 Paula 1 2 4 7 
C55 Kimberly 1 2 2 3 
C56 Louise 1 1 1 3 
C57 Martin 1 1 2 5 
C58 Paul 1 2 4 8 
C59 Antonio 1 1 2 4 
C6 Bonnie 1 1 2 6 
C60 Jacqueline 1 1 1 2 
C61 Diana 1 1 1 1 
C62 John 1 2 5 11 
C63 Dorothy 1 2 5 7 
C64 Evelyn 1 1 1 2 
C65 Phillip 1 2 4 8 
C66 Evelyn 1 2 4 11 
C67 Debra 1 1 1 3 
C68 David 1 2 5 9 
C69 Willie 1 1 1 3 
C7 Michael 1 1 1 2 
C70 Brandon 1 2 4 7 
C71 Ann 1 2 5 10 
C72 Emily 1 2 3 7 
C73 Dorothy 1 1 1 2 
C74 Jonathan 1 2 4 7 
C75 Dorothy 1 1 2 4 
C76 Marilyn 1 2 5 12 
C77 Norma 1 2 5 10 
C78 Nancy 1 2 3 7 
C79 Andrew 1 1 2 2 
C8 Todd 1 1 1 3 
C80 Keith 1 1 1 2 
C81 Benjamin 1 1 3 9 
C82 Charles 1 1 2 3 
C83 Alan 1 2 3 4 
C84 Tammy 1 1 1 3 
C85 Anna 1 2 4 8 
C86 James 1 1 3 5 
C87 Robin 1 2 3 5 
C88 Jean 1 1 2 3 
C89 Andrew 1 2 4 7 
C9 Joe 1 1 3 6 
C90 Roy 1 1 2 3 
C91 Diana 1 2 2 2 
C92 Christina 1 1 1 3 
C93 Jesse 1 1 2 2 
C94 Joyce 1 2 5 13 
C95 Patricia 1 1 3 5 
C96 Gregory 1 1 2 2 
C97 Brian 1 1 1 1 
C98 Christine 1 1 2 5 
C99 Lillian 1 1 2 6

 2. Employee 테이블에는 Lead Manager나 Senior Manager, Manager 중 하위 직급이 없는 사람에 대한 정보가 없으므로 완벽한 정보를 가지지 않을 수 있으므로, 상위 직급의 테이블도 연쇄적으로 JOIN하여 누락을 방지할 필요가 있음

SELECT 
    C.company_code,
    C.founder,
    COUNT(DISTINCT L.lead_manager_code),
    COUNT(DISTINCT S.senior_manager_code),
    COUNT(DISTINCT M.manager_code),
    COUNT(DISTINCT E.employee_code)
FROM Company C
    LEFT JOIN Lead_Manager L ON C.company_code = L.company_code
    LEFT JOIN Senior_Manager S ON L.company_code = S.company_code
    LEFT JOIN Manager M ON L.company_code = M.company_code
    LEFT JOIN Employee E ON L.company_code = E.company_code
GROUP BY
    C.company_code,
    C.founder
ORDER BY C.company_code

-- 출력
C1 Angela 1 2 5 13 
C10 Earl 1 1 2 3 
C100 Aaron 1 2 4 10 
C11 Robert 1 1 1 1 
C12 Amy 1 2 6 14 
C13 Pamela 1 2 5 14 
C14 Maria 1 1 3 5 
C15 Joe 1 1 2 3 
C16 Linda 1 1 3 5 
C17 Melissa 1 2 3 7 
C18 Carol 1 2 5 6 
C19 Paula 1 2 4 7 
C2 Frank 1 1 1 3 
C20 Marilyn 1 1 2 2 
C21 Jennifer 1 1 3 7 
C22 Harry 1 1 3 6 
C23 David 1 1 1 2 
C24 Julia 1 1 2 6 
C25 Kevin 1 1 2 5 
C26 Paul 1 1 1 3 
C27 James 1 1 1 3 
C28 Kelly 1 2 5 9 
C29 Robin 1 2 4 9 
C3 Patrick 1 2 2 5 
C30 Ralph 1 1 2 5 
C31 Gloria 1 1 1 3 
C32 Victor 1 2 4 8 
C33 David 1 2 5 12 
C34 Joyce 1 2 6 10 
C35 Donna 1 2 6 12 
C36 Michelle 1 2 5 11 
C37 Stephanie 1 1 2 5 
C38 Gerald 1 2 4 6 
C39 Walter 1 1 3 7 
C4 Lisa 1 1 1 1 
C40 Christina 1 1 3 6 
C41 Brandon 1 2 3 7 
C42 Elizabeth 1 2 4 8 
C43 Joseph 1 2 4 6 
C44 Lawrence 1 1 3 4 
C45 Marilyn 1 1 1 3 
C46 Lori 1 2 3 9 
C47 Matthew 1 2 3 4 
C48 Jesse 1 1 3 3 
C49 John 1 1 3 8 
C5 Kimberly 1 2 3 9 
C50 Martha 1 1 2 5 
C51 Timothy 1 2 5 12 
C52 Christine 1 1 2 2 
C53 Anthony 1 1 1 1 
C54 Paula 1 2 4 7 
C55 Kimberly 1 2 2 3 
C56 Louise 1 1 1 3 
C57 Martin 1 1 2 5 
C58 Paul 1 2 4 8 
C59 Antonio 1 1 2 4 
C6 Bonnie 1 1 2 6 
C60 Jacqueline 1 1 1 2 
C61 Diana 1 1 1 1 
C62 John 1 2 5 11 
C63 Dorothy 1 2 5 7 
C64 Evelyn 1 1 1 2 
C65 Phillip 1 2 4 8 
C66 Evelyn 1 2 4 11 
C67 Debra 1 1 1 3 
C68 David 1 2 5 9 
C69 Willie 1 1 1 3 
C7 Michael 1 1 1 2 
C70 Brandon 1 2 4 7 
C71 Ann 1 2 5 10 
C72 Emily 1 2 3 7 
C73 Dorothy 1 1 1 2 
C74 Jonathan 1 2 4 7 
C75 Dorothy 1 1 2 4 
C76 Marilyn 1 2 5 12 
C77 Norma 1 2 5 10 
C78 Nancy 1 2 3 7 
C79 Andrew 1 1 2 2 
C8 Todd 1 1 1 3 
C80 Keith 1 1 1 2 
C81 Benjamin 1 1 3 9 
C82 Charles 1 1 2 3 
C83 Alan 1 2 3 4 
C84 Tammy 1 1 1 3 
C85 Anna 1 2 4 8 
C86 James 1 1 3 5 
C87 Robin 1 2 3 5 
C88 Jean 1 1 2 3 
C89 Andrew 1 2 4 7 
C9 Joe 1 1 3 6 
C90 Roy 1 1 2 3 
C91 Diana 1 2 2 2 
C92 Christina 1 1 1 3 
C93 Jesse 1 1 2 2 
C94 Joyce 1 2 5 13 
C95 Patricia 1 1 3 5 
C96 Gregory 1 1 2 2 
C97 Brian 1 1 1 1 
C98 Christine 1 1 2 5 
C99 Lillian 1 1 2 6

문제

"노드" || "노드의 부모"로 구성된 테이블에서

- Root: root node

- Leaf: leaf node

- inner: neither root node nor leaf node

구분하여 node 오름차순으로 정렬

Sample Output

1 Leaf
2 Inner
3 Leaf
5 Root
6 Leaf
8 Inner
9 Leaf

풀이

1. P가 null이면 root node 이다.

CASE WHEN P IS NULL THEN 'Root'

2. N 중에 P에도 값이 있는 node는 누군가의 부모이므로 leaf node가 될 수 없다. root node도 아니기에 inner node이다.

CASE WHEN N IN (SELECT DISTINCT P FROM BST) THEN 'Inner'

3. 그 외에는 전부 leaf node이다.

CASE ELSE 'Leaf'

4. 종합하여 N과 함께 출력하고 N을 기준으로 오름차순으로 정렬한다.

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

문제

"이름" || "직업"으로 구성된 테이블에서

1열: 직업이 Doctor인 사람 이름 (오름차순 정렬)

2열: 직업이 Professor인 사람 이름 (오름차순 정렬)

3열: 직업이 Singer인 사람 이름 (오름차순 정렬)

4열: 직업이 Actor인 사람 이름 (오름차순 정렬)

직업 별 이름의 최대 개수 이하인 열에서 빈 셀은 Null로 출력

Sample Output

Jenny    Ashley     Meera  Jane
Samantha Christeen  Priya  Julia
NULL     Ketty      NULL   Maria

풀이

1. 직업 별 이름들을 서브쿼리로 추출해서 JOIN

    - row_number() over(ORDER BY Name) 열 추가: 이름 오름차순 정렬 + JOIN 기준으로 하여, 빈 셀에 Null 삽입

    - JOIN 방향은 이름 개수가 가장 많은 Professor 열 기준: 다른 열 기준 시 Professor의 이름이 잘리기 때문

SELECT d.Name, p.Name, s.Name, a.Name
FROM
(SELECT Name, row_number() over(ORDER BY Name) as r
FROM OCCUPATIONS
WHERE Occupation = 'Doctor') AS d
RIGHT JOIN
(SELECT Name, row_number() over(ORDER BY Name) as r
FROM OCCUPATIONS
WHERE Occupation = 'Professor') AS p
ON d.r = p.r
LEFT JOIN
(SELECT Name, row_number() over(ORDER BY Name) as r
FROM OCCUPATIONS
WHERE Occupation = 'Singer') AS s
ON p.r = s.r
LEFT JOIN
(SELECT Name, row_number() over(ORDER BY Name) as r
FROM OCCUPATIONS
WHERE Occupation = 'Actor') AS a
ON s.r = a.r

-- 출력
Aamina Ashley Christeen Eve 
Julia Belvet Jane Jennifer 
Priya Britney Jenny Ketty 
NULL Maria Kristeen Samantha 
NULL Meera NULL NULL 
NULL Naomi NULL NULL 
NULL Priyanka NULL NULL

문제

"이름" || "직업" 으로 구성된 테이블에서

1. "이름 (직업의 첫 글자)" 형태로 추출(이름 오름차순)

2. 각 직업의 수를 세서 "There are a total of 3 doctors"와 같이 출력(직업 count 오름차순, 직업명 오름차순)

Sample Output

Ashely(P)
Christeen(P)
Jane(A)
Jenny(D)
Julia(A)
Ketty(P)
Maria(A)
Meera(S)
Priya(S)
Samantha(D)
There are a total of 2 doctors.
There are a total of 2 singers.
There are a total of 3 actors.
There are a total of 3 professors.

풀이

1. "이름 (직업의 첫 글자)"를 이름 기준 오름차순으로 출력

SELECT CONCAT(Name, '(', SUBSTR(Occupation,1,1),')') 
FROM OCCUPATIONS 
ORDER BY Name;

-- 출력
Aamina(D)
Ashley(P)
Belvet(P)
Britney(P)
Christeen(S)
Eve(A)
Jane(S)
Jennifer(A)
Jenny(S)
Julia(D)
Ketty(A)
Kristeen(S)
Maria(P)
Meera(P)
Naomi(P)
Priya(D)
Priyanka(P)
Samantha(A)

2. 각 직업의 수를 세서 "There are a total of 3 doctors"를 직업 수 오름차순, 직업명 오름차순으로 출력

SELECT CONCAT('There are a total of ', COUNT(Occupation), ' ', LOWER(Occupation), 's.') 
FROM OCCUPATIONS
GROUP BY Occupation
ORDER BY COUNT(Occupation), Occupation;

-- 출력
There are a total of 3 doctors.
There are a total of 4 actors.
There are a total of 4 singers.
There are a total of 7 professors.

3. 두 쿼리를 UNION으로 결합하여 한 줄로 출력하려고 했지만 MySQL에서 UNION을 사용할 때 ORDER BY가 적용되지 않아 오류 발생

SELECT CONCAT(Name, '(', SUBSTR(Occupation,1,1),')') 
FROM OCCUPATIONS 
ORDER BY Name
UNION
SELECT CONCAT('There are a total of ', COUNT(Occupation), ' ', LOWER(Occupation), 's.') 
FROM OCCUPATIONS
GROUP BY Occupation
ORDER BY COUNT(Occupation), Occupation

-- 출력
-- ERROR 1064 (42000) at line 1: You have an error in your SQL syntax; check the manual
-- that corresponds to your MySQL server version for the right syntax to use near 'UNION
-- SELECT CONCAT('There are a total of ', COUNT(Occupation), ' ', LOWER(Occup' at line 4

4. 각 쿼리를 서브쿼리에 넣어 UNION 시도했지만 MySQL에서 UNION하면 ORDER BY로 한 정렬이 풀려버림

SELECT a.*
FROM (SELECT CONCAT(Name, '(', SUBSTR(Occupation,1,1),')') 
      FROM OCCUPATIONS 
      ORDER BY Name) AS a
UNION
SELECT b.*
FROM (SELECT CONCAT('There are a total of ', COUNT(Occupation), ' ', LOWER(Occupation), 's.') 
      FROM OCCUPATIONS
      GROUP BY Occupation
      ORDER BY COUNT(Occupation), Occupation) AS b

-- 출력
Ashley(P)
Samantha(A)
Julia(D)
Britney(P)
Maria(P)
Meera(P)
Priya(D)
Priyanka(P)
Jennifer(A)
Ketty(A)
Belvet(P)
Naomi(P)
Jane(S)
Jenny(S)
Kristeen(S)
Christeen(S)
Eve(A)
Aamina(D)
There are a total of 3 doctors.
There are a total of 4 actors.
There are a total of 4 singers.
There are a total of 7 professors.

정답

5. 두 쿼리를 UNION으로 묶지않고 ";"로 각 쿼리를 끝내 두 번 출력하는 방법으로 변경

SELECT CONCAT(Name, '(', SUBSTR(Occupation,1,1),')') 
FROM OCCUPATIONS 
ORDER BY Name;

SELECT CONCAT('There are a total of ', COUNT(Occupation), ' ', LOWER(Occupation), 's.') 
FROM OCCUPATIONS
GROUP BY Occupation
ORDER BY COUNT(Occupation), Occupation;

-- 출력
Aamina(D)
Ashley(P)
Belvet(P)
Britney(P)
Christeen(S)
Eve(A)
Jane(S)
Jennifer(A)
Jenny(S)
Julia(D)
Ketty(A)
Kristeen(S)
Maria(P)
Meera(P)
Naomi(P)
Priya(D)
Priyanka(P)
Samantha(A)
There are a total of 3 doctors.
There are a total of 4 actors.
There are a total of 4 singers.
There are a total of 7 professors.