[MySQL] SQL 문제풀이 (8문제)

SQL 고득점 Kit의 문제를 다 푼지 어언 2개월..

8문제가 새로 추가되었다.. (2024/02/25 기준)

이 8문제를 풀어보고자 합니다.

 

1, 4, 6, 7번은 아이템과 관련된 문제 시리즈이고,

2, 3, 5, 8번은 개발자 찾기 관련된 문제 시리즈입니다.

 

SELECT

1. 업그레이드 된 아이템 구하기

테이블을 삼중 조인하여 parent 아이템의 데이터를 select 했다

이때, 첫번째 join 시 left join을 하지 않았다.

왜냐하면 parent 아이템이 없는 경우도 있기 때문이다.

(left join을 할 경우, parent 아이템이 null로 출력되므로)

SELECT P.ITEM_ID, P.ITEM_NAME, P.RARITY
FROM ITEM_INFO AS I JOIN ITEM_TREE AS T
ON I.ITEM_ID = T.PARENT_ITEM_ID
LEFT JOIN ITEM_INFO AS P
ON T.ITEM_ID = P.ITEM_ID
WHERE I.RARITY = 'RARE'
ORDER BY P.ITEM_ID DESC;

 

2. Python 개발자 찾기

SELECT ID, EMAIL, FIRST_NAME, LAST_NAME
FROM DEVELOPER_INFOS
WHERE SKILL_1 = 'Python' OR SKILL_2 = 'Python' OR SKILL_3 = 'Python'
ORDER BY ID;

 

3. 조건에 맞는 개발자 찾기

여태까지 AND, OR을 사용한 논리 연산자는 많이 사용했는데,

&, | 와 같은 비트 논리 연산자는 사용해 본 적이 없어서 이 문제가 신기했다.

&는 AND와 같지만, 비트끼리 비교한 것이며

|는 OR과 같지만, 마찬가지로 비트끼리 비교한 것이다.

 

예를 들어 Python 의 이진수 코드가 1000이고 C#의 코드가 0100일 경우,

1*** 이거나 *1**이라는 코드를 가지고 있는 사람은

Python이나 C# 스킬을 가진 사람 인 것이다.

그래서 두 코드를 더해서 1100을 만든 다음, 내가 가진 스킬코드와 비교하고자 한다.

 

두 스킬을 더한 1100 코드와, 내가 가진 스킬 코드에

비트 논리 연산자인 & 를 사용했을 경우,

0보다 크면 하나 이상의 스킬이 겹친다는 뜻이고,

조건에 맞는 개발자를 고를 수 있게 되는 것이다.

SELECT ID, EMAIL, FIRST_NAME, LAST_NAME
FROM DEVELOPERS
WHERE SKILL_CODE & (
    SELECT SUM(CODE)
    FROM SKILLCODES
    WHERE NAME = 'Python' OR NAME = 'C#') > 0
ORDER BY ID;

 

SUM, MIN, MAX

4. 조건에 맞는 아이템들의 가격의 총합 구하기

SELECT SUM(PRICE) AS TOTAL_PRICE
FROM ITEM_INFO
WHERE RARITY = "LEGEND";

 

GROUP BY

5. 언어별 개발자 구분하기

이 문제를 풀기 위해서는 with 절을 썼다.

이전에 recursive with 을 사용한 적이 있는데, with은 쉽게 말해 임시 테이블을 만드는 것이다.

python의 스킬코드를 저장한 py 테이블,

c#의 스킬코드를 저장한 cp 테이블,

프론트엔드 스킬코드를 모두 더한 값을 저장한 fe 테이블로 세개를 만들었다.

여러개의 임시 테이블을 만드려면 with을 한 번만 사용한 뒤 콤마(,) 로 구분하면 된다.

 

이후 if 문을 사용해 등급을 나누었다.

1. 현재의 스킬코드가 python 스킬코드를 포함하고, fe 스킬코드와 하나라도 일치하다면 A 등급

2. 현재의 스킬코드에 C#이 포함되어 있다면 B 등급

3. 아무 프론트엔드 스킬코드를 하나라도 가지고 있다면 C등급

 

마지막으로, GRADE가 NULL인 부분을 필터링 했는데

다른 문제에서도 봤다시피, WHERE 절에서 alias 사용이 안되므로 HANING 절을 사용했다.

WITH PY AS (
    SELECT CODE
    FROM SKILLCODES
    WHERE NAME = 'Python'
),
CP AS(
    SELECT CODE
    FROM SKILLCODES
    WHERE NAME = 'C#'
),
FE AS (
    SELECT SUM(CODE) AS CODE
    FROM SKILLCODES 
    WHERE CATEGORY = 'Front End'
)

SELECT IF(SKILL_CODE & PY.CODE AND SKILL_CODE & FE.CODE, 'A',
          IF(SKILL_CODE & CP.CODE, 'B',
            IF(SKILL_CODE & FE.CODE, 'C', NULL))) AS GRADE,
    ID, EMAIL
FROM DEVELOPERS, PY, CP, FE
HAVING GRADE IS NOT NULL
ORDER BY GRADE, ID;

 

IS NULL

6. ROOT 아이템 구하기

1번 문제와 유사한데 훨씬 쉽다.

두 테이블을 조인한 뒤, 부모 노드가 null인 데이터만 조회하면 된다.

SELECT I.ITEM_ID, I.ITEM_NAME
FROM ITEM_INFO AS I LEFT JOIN ITEM_TREE AS T
ON I.ITEM_ID = T.ITEM_ID
WHERE T.PARENT_ITEM_ID IS NULL
ORDER BY I.ITEM_ID;

 

7. 업그레이드 할 수 없는 아이템 구하기

PARENT_ITEM_ID에 해당하는 아이템은, ITEM_TREE의 ITEM_ID로 업그레이드 할 수 있다.

즉, PARENT_ITEM_ID에 해당하는 아이템은 모두 업그레이드 할 수 있으므로

이에 속하지 않는 (= NOT IN) 아이템이, 우리가 구하고자 하는 '업그레이드 할 수 없는 아이템' 이다.

그래서 NOT IN을 사용해서 다음과 같이 풀었다.

하지만, 서브쿼리를 보면 WHERE PARENT_ITEM_ID IS NOT NULL 이라는 절이 들어있는데.

이는 왜 들어있을까?

 

바로, NULL값은 비교시 Unknown (= FALSE) 을 반환하기 때문이다.

프로그래머스 예제를 보면, 서브쿼리의 결과 [null, 0, 0, 1, 1] 를 반환한다.

이를 ITEM_INFO의 ITEM_ID인 [0, 1, 2, 3, 4, 5]와 비교했을 때,

서브쿼리의 결과 중 하나인 null과도 비교하게 되므로 전부 false 처리가 되고,

실행 결과에 아무것도 남지 않게 되는 것이다.

 

따라서 서브쿼리에 WHERE PARENT_ITEM_ID IS NOT NULL 라는 조건을 추가해야 원하는 결과를 얻을 수 있다.

SELECT ITEM_ID, ITEM_NAME, RARITY
FROM ITEM_INFO
WHERE ITEM_ID NOT IN (
    SELECT PARENT_ITEM_ID 
    FROM ITEM_TREE 
    WHERE PARENT_ITEM_ID IS NOT NULL)
ORDER BY ITEM_ID DESC;

 

JOIN

8. FrontEnd 개발자 찾기

이 문제도 3번 문제와 같다.

다만 조건이, Python, C# 개발자를 찾는 것이 아니라,

카테고리가 Front End 인 개발자를 찾으면 된다.

SELECT ID, EMAIL, FIRST_NAME, LAST_NAME
FROM DEVELOPERS 
WHERE SKILL_CODE & (
    SELECT SUM(CODE)
    FROM SKILLCODES
    WHERE CATEGORY = 'Front End') > 0
ORDER BY ID;