[SQL] 프로그래머스 MySQL - Lv.2 (1~10)

https://school.programmers.co.kr/learn/challenges?order=recent&levels=2&languages=mysql

코딩테스트 연습 | 프로그래머스 스쿨

 

1. 부모의 형질을 모두 가지는 대장균 찾기 (비트 연산)

SELECT A.ID, A.GENOTYPE, B.GENOTYPE AS PARENT_GENOTYPE
FROM ECOLI_DATA A
JOIN ECOLI_DATA B
ON A.PARENT_ID = B.ID
WHERE A.GENOTYPE & B.GENOTYPE = B.GENOTYPE
ORDER BY A.ID

 

 

2. 연도별 대장균 크기의 편차 구하기  (JOIN, SUBQUERY)

SELECT B.YEAR, (B.MAX_SIZE - A.SIZE_OF_COLONY) AS YEAR_DEV, A.ID
FROM ECOLI_DATA A
JOIN (SELECT YEAR(DIFFERENTIATION_DATE) AS YEAR, MAX(SIZE_OF_COLONY) AS MAX_SIZE
FROM ECOLI_DATA
GROUP BY YEAR) B
ON YEAR(A.DIFFERENTIATION_DATE) = B.YEAR
ORDER BY B.YEAR, YEAR_DEV

 

* JOIN의 ON에서 A.YEAR = B.YEAR으로 하면 에러. SELECT문에서 정의한 A.YEAR에 대해 JOIN에서는 알 수 없기 때문

 

3. 분기별 분화된 대장균의 개체 수 구하기 (CONCAT, GROUP BY)

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

 

* 아래 코드는 돌아가지 않음, GROUP BY에 사용되는 식과 SELECT에 사용되는 식이 일치해야함

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

 

 

4. 특정 물고기를 잡은 총 수 구하기 (JOIN, IN)

SELECT COUNT(*) AS FISH_COUNT
FROM FISH_INFO A
JOIN FISH_NAME_INFO B
ON A.FISH_TYPE = B.FISH_TYPE
WHERE B.FISH_NAME IN ('BASS','SNAPPER')

 

 

5. 월별 잡은 물고기 수 구하기 (GROUP BY, ORDER BY)

SELECT COUNT(ID) AS FISH_COUNT, MONTH(TIME) AS MONTH
FROM FISH_INFO
GROUP BY MONTH
ORDER BY MONTH

 

 

6. 물고기 종류별 잡은 수 구하기 (JOIN, GROUP BY, ORDER BY)

SELECT COUNT(*) AS FISH_COUNT, B.FISH_NAME
FROM FISH_INFO A
JOIN FISH_NAME_INFO B
ON A.FISH_TYPE = B.FISH_TYPE
GROUP BY B.FISH_NAME
ORDER BY FISH_COUNT DESC

 

 

7. 노선별 평균 역 사이 거리 조회하기 (CONCAT, ORDER BY)

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

* ORDER BY를 TOTAL_DISTANCE로 하면 문자열 순으로 정렬되기 때문에 오류! 숫자로 정렬해야함.

 

 

8. 연도별 평균 미세먼지 농도 구하기 (ROUND, AVG, GROUP BY)

SELECT YEAR(YM) AS YEAR, ROUND(AVG(PM_VAL1),2) AS PM10, ROUND(AVG(PM_VAL2),2) AS 'PM2.5'
FROM AIR_POLLUTION
WHERE LOCATION1 = '경기도' AND LOCATION2 = '수원'
GROUP BY YEAR
ORDER BY YEAR

 

 

9. 조건에 맞는 사원 정보 조회하기 (SUBQUERY, LIMIT)

SELECT B.SCORE, A.EMP_NO, A.EMP_NAME, A.POSITION, A.EMAIL
FROM HR_EMPLOYEES A
JOIN (SELECT EMP_NO, SUM(SCORE) AS SCORE
FROM HR_GRADE
GROUP BY EMP_NO, YEAR) B
ON A.EMP_NO = B.EMP_NO
ORDER BY B.SCORE DESC
LIMIT 1

 

 

10. 조건에 맞는 개발자 찾기 (비트 연산)

SELECT ID, EMAIL, FIRST_NAME, LAST_NAME
FROM DEVELOPERS
WHERE ID IN (SELECT DISTINCT D.ID
FROM DEVELOPERS D
JOIN SKILLCODES S
ON D.SKILL_CODE & S.CODE = S.CODE
WHERE S.NAME = 'C#' OR S.NAME = 'Python')
ORDER BY ID