[SQL] 프로그래머스 코딩테스트 : Lv2. (4)

 

Lv.2 분기별 분화된 대장균의 개체 수 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/299308

SELECT CONCAT(QUARTER(DIFFERENTIATION_DATE),'Q') AS QUARTER
    , COUNT(*) AS ECOLI_COUNT
FROM ECOLI_DATA
GROUP BY CONCAT(QUARTER(DIFFERENTIATION_DATE),'Q')
ORDER BY CONCAT(QUARTER(DIFFERENTIATION_DATE),'Q')

❗QUARTER() 사용해서 분기 나타내기
❗CONCAT() 사용해서 뒤에 'Q' 붙이기

 

Lv.2 노선별 평균 역 사이 거리 조회하기

https://school.programmers.co.kr/learn/courses/30/lessons/284531

SELECT ROUTE
    , CONCAT(ROUND(SUM(D_BETWEEN_DIST),1),'km') AS TOTAL_DISTANCE
    , CONCAT(ROUND(AVG(D_BETWEEN_DIST),2),'km') AS AVERAGE_DISTANCE
FROM SUBWAY_DISTANCE
GROUP BY ROUTE
ORDER BY SUM(D_BETWEEN_DIST) DESC

❗CONCAT(), ROUND() 함수 써서 포맷 맞추기
❗ORDER BY 절에 SUM(D_BETWEEN_DIST) 사용
- 칼럼 명을 쓰면 문자열 순으로 정렬이 된다
- CONCAT() 으로 km 붙이면서 문자열이 됨

 

-- 실패
SELECT ROUTE
    , CONCAT(ROUND(SUM(D_BETWEEN_DIST),1),'km') AS TOTAL_DISTANCE
    , CONCAT(ROUND(AVG(D_BETWEEN_DIST),2),'km') AS AVERAGE_DISTANCE
FROM SUBWAY_DISTANCE
GROUP BY ROUTE
ORDER BY TOTAL_DISTANCE DESC

❗ORDER BY 절에 칼럼 명을 쓴 구문은 최종 제출에서 에러가 난다
❗ 참고 : https://school.programmers.co.kr/questions/79570

 

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

https://school.programmers.co.kr/learn/courses/30/lessons/273711

SELECT II.ITEM_ID, II.ITEM_NAME, II.RARITY
FROM ITEM_TREE AS IT
    LEFT JOIN ITEM_INFO AS II ON II.ITEM_ID = IT.ITEM_ID
WHERE IT.PARENT_ITEM_ID IN (SELECT ITEM_ID
                            FROM ITEM_INFO
                            WHERE RARITY = 'RARE')
ORDER BY II.ITEM_ID DESC

❗WHERE 절에 이전 단계 (PARENT_ITEM)에 지금 단계가 RARE (WHERE 절 서브 쿼리) 인 ID 만 조건 걸기
❗ORDER BY 절에 내림차순
❗참고 : https://school.programmers.co.kr/questions/79576

+) LEFT JOIN 절

❗주어진 데이터 JOIN 해서 전체 정보 확인 및 추출할 데이터 준비

+) WHERE 절 서브 쿼리

❗RARITY 가 RARE 인 것을 확인 및 ITEM_ID 추출

 

Lv.2 조건에 맞는 개발자 찾기

https://school.programmers.co.kr/learn/courses/30/lessons/276034

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

❗WHERE 절에 Python, C# 에 해당하는 CODE 추출

 

-- 실패 (케이스 6번만 통과)
SELECT ID, EMAIL, FIRST_NAME, LAST_NAME
FROM DEVELOPERS
WHERE SKILL_CODE & 256 != 0
    OR SKILL_CODE & 1024 != 0
ORDER BY ID ASC

❗케이스 6번만 통과한다
- 최종 제출에서는 SKILLCODE가 바뀌기 때문이라는 것을 알게됨
- 참고 : https://school.programmers.co.kr/questions/78068

 

Lv.2 연도별 대장균 크기의 편차 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/299310

WITH T1 AS (
    SELECT YEAR(DIFFERENTIATION_DATE) AS YEAR
        , MAX(SIZE_OF_COLONY) AS YEAR_MAX
    FROM ECOLI_DATA
    GROUP BY YEAR(DIFFERENTIATION_DATE)
), T2 AS (
    SELECT *, YEAR(DIFFERENTIATION_DATE) AS YEAR
    FROM ECOLI_DATA
)

SELECT T2.YEAR
    , ABS(T2.SIZE_OF_COLONY - T1.YEAR_MAX) AS YEAR_DEV
    , T2.ID
FROM T2 
    LEFT JOIN T1 ON T2.YEAR = T1.YEAR
ORDER BY T2.YEAR ASC, YEAR_DEV ASC

❗YEAR_DEV 계산

+) WITH 절 T1

❗GROUP BY 절에 YEAR() 사용해서 YEAR_MAX 값 구하기

+) WITH 절 T2

❗기존 ECOLI_DATA에 YEAR 변수 추가

+) JOIN 절 T1, T2

❗T1, T2 JOIN 해서 테이블 연결

 

Lv.2 부모의 형질을 모두 가지는 대장균 찾기

https://school.programmers.co.kr/learn/courses/30/lessons/301647

SELECT T1.ID, T1.GENOTYPE, T1.GENOTYPE & T2.GENOTYPE AS PARENT_GENOTYPE
FROM ECOLI_DATA AS T1
    LEFT JOIN ECOLI_DATA AS T2 ON T1.PARENT_ID = T2.ID
WHERE T2.GENOTYPE = T1.GENOTYPE & T2.GENOTYPE
ORDER BY ID ASC

❗WHERE 절에 부모형질과 비트연산 결과가 같은 것만 추출

• T2.GENOTYPE = T1.GENOTYPE & T2.GENOTYPE

+) LEFT JOIN 절

❗ON T1.PARENT_ID = T2.ID 으로 왼쪽은 자식, 오른쪽은 부모의 정보 넣기
❗ID, PARENT_ID, GENOTYPE(자식 형질), P_GENOTYPE(부모 형질) 순서로 테이블이 결합 된 것을 확인
❗T1.GENOTYPE & T2.GENOTYPE : 자식 형질을 부모형질로 비트 연산