[Programmers][MySQL] 어린 동물 찾기

Lv.4 🔗어린 동물 찾기

📝문제 요약

문제 설명

SKILLCODES 테이블은 개발자들이 사용하는 프로그래밍 언어에 대한 정보를 담은 테이블입니다. SKILLCODES 테이블의 구조는 다음과 같으며, NAME, CATEGORY, CODE는 각각 스킬의 이름, 스킬의 범주, 스킬의 코드를 의미합니다. 스킬의 코드는 2진수로 표현했을 때 각 bit로 구분될 수 있도록 2의 제곱수로 구성되어 있습니다.

NAME	TYPE	UNIQUE	NULLABLE
NAME	VARCHAR(N)	Y	N
CATEGORY	VARCHAR(N)	N	N
CODE	INTEGER	Y	N

DEVELOPERS 테이블은 개발자들의 프로그래밍 스킬 정보를 담은 테이블입니다. DEVELOPERS 테이블의 구조는 다음과 같으며, ID, FIRST_NAME, LAST_NAME, EMAIL, SKILL_CODE는 각각 개발자의 ID, 이름, 성, 이메일, 스킬 코드를 의미합니다. SKILL_CODE 컬럼은 INTEGER 타입이고, 2진수로 표현했을 때 각 bit는 SKILLCODES 테이블의 코드를 의미합니다.

NAME	TYPE	UNIQUE	NULLABLE
ID	VARCHAR(N)	Y	N
FIRST_NAME	VARCHAR(N)	N	Y
LAST_NAME	VARCHAR(N)	N	Y
EMAIL	VARCHAR(N)	Y	N
SKILL_CODE	INTEGER	N	N

예를 들어 어떤 개발자의 SKILL_CODE가 400 (=b’110010000’)이라면, 이는 SKILLCODES 테이블에서 CODE가 256 (=b’100000000’), 128 (=b’10000000’), 16 (=b’10000’) 에 해당하는 스킬을 가졌다는 것을 의미합니다.

---

문제

DEVELOPERS 테이블에서 GRADE별 개발자의 정보를 조회하려 합니다. GRADE는 다음과 같이 정해집니다.

• A : Front End 스킬과 Python 스킬을 함께 가지고 있는 개발자
• B : C# 스킬을 가진 개발자
• C : 그 외의 Front End 개발자

GRADE가 존재하는 개발자의 GRADE, ID, EMAIL을 조회하는 SQL 문을 작성해 주세요.

결과는 GRADE와 ID를 기준으로 오름차순 정렬해 주세요.

---

예시

예를 들어 SKILLCODES 테이블이 다음과 같고,

NAME	CATEGORY	CODE
C++	Back End	4
JavaScript	Front End	16
Java	Back End	128
Python	Back End	256
C#	Back End	1024
React	Front End	2048
Vue	Front End	8192
Node.js	Back End	16384

DEVELOPERS 테이블이 다음과 같다면

ID	FIRST_NAME	LAST_NAME	EMAIL	SKILL_CODE
D165	Jerami	Edwards	jerami_edwards@grepp.co	400
D161	Carsen	Garza	carsen_garza@grepp.co	2048
D164	Kelly	Grant	kelly_grant@grepp.co	1024
D163	Luka	Cory	luka_cory@grepp.co	16384
D162	Cade	Cunningham	cade_cunningham@grepp.co	8452

다음과 같이 DEVELOPERS 테이블에 포함된 개발자들의 GRADE 및 정보가 결과에 나와야 합니다.

GRADE	ID	EMAIL
A	D162	cade_cunningham@grepp.co
A	D165	jerami_edwards@grepp.co
B	D164	kelly_grant@grepp.co
C	D161	carsen_garza@grepp.co

✏️문제 풀이

SELECT

SELECT CASE WHEN MAX(CASE WHEN NAME = 'Python' THEN 1 ELSE 0 END) = 1
            AND MAX(CASE WHEN CATEGORY = 'Front End' THEN 1 ELSE 0 END) = 1 THEN 'A'
            WHEN MAX(CASE WHEN NAME = 'C#' THEN 1 ELSE 0 END) = 1 THEN 'B'
            WHEN MAX(CASE WHEN CATEGORY = 'Front End' THEN 1 ELSE 0 END) = 1 THEN 'C'
            ELSE NULL END AS GRADE, 
            ID, EMAIL

• MAX(CASE WHEN 조건 THEN 1 ELSE 0 END)
  • 해당 조건을 만족하는 스킬이 하나라도 있으면 1, 없으면 0
  • MAX를 써서 여러 스킬 중에 하나라도 만족하는지 판단

  GRADE	조건
  A	Python 스킬이 있고 AND Front End 스킬도 있음
  B	C# 스킬이 있음
  C	Front End 스킬만 있음
  NULL	아무 조건에도 해당되지 않음 (출력되지 않음)

FROM

FROM DEVELOPERS D JOIN SKILLCODES S ON (D.SKILL_CODE & S.CODE) = S.CODE

• DEVELOPERS 테이블과 SKILLCODES 테이블을 JOIN
  • 개발자(SKILL_CODE)가 해당 스킬(S.CODE)을 포함하고 있을 경우

GROUP BY

GROUP BY 2, 3

• 2번 컬럼(ID), 3번 컬럼(EMAIL)을 기준으로 그룹화

HAVING

HAVING GRADE IS NOT NULL

• GRADE가 NULL이 아닌 개발자만 결과에 포함

ORDER BY

ORDER BY 1, 2

• 1번 컬럼(GRADE)을 기준으로 오름차순 정렬
• 2번 컬럼(ID)를 기준으로 오름차순 정렬

💯제출 코드

SELECT CASE WHEN MAX(CASE WHEN NAME = 'Python' THEN 1 ELSE 0 END) = 1
            AND MAX(CASE WHEN CATEGORY = 'Front End' THEN 1 ELSE 0 END) = 1 THEN 'A'
            WHEN MAX(CASE WHEN NAME = 'C#' THEN 1 ELSE 0 END) = 1 THEN 'B'
            WHEN MAX(CASE WHEN CATEGORY = 'Front End' THEN 1 ELSE 0 END) = 1 THEN 'C'
            ELSE NULL END AS GRADE, 
            ID, EMAIL
FROM DEVELOPERS D JOIN SKILLCODES S ON (D.SKILL_CODE & S.CODE) = S.CODE
GROUP BY 2, 3
HAVING GRADE IS NOT NULL
ORDER BY 1, 2