[실무]프로그래머스 MySQL 기출문제 모음 Lv.2.1

■ 출처

- 코딩테스트 : MySQL / Oracle / Lv.2

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

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

 

 

 

■ 연습문제

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

#설명: 부모 형질을 모두 보유한 대장균의 ID, 형질, 부모 형질을 조회
#참고: [자식의 개체 형질] & [부모의 개체 형질] = [부모의 개체 형질]

SELECT
    A_Table.ID, 
    A_Table.GENOTYPE, 
    B_Table.GENOTYPE AS PARENT_GENOTYPE
FROM
    ECOLI_DATA AS A_Table               -- 자식 테이블
    INNER JOIN ECOLI_DATA AS B_Table    -- 부모 테이블
    ON A_Table.PARENT_ID = B_Table.ID   -- 자식 테이블의 부모ID와 부모 테이블의 ID 조건으로 결합
WHERE
    A_Table.GENOTYPE & B_Table.GENOTYPE = B_Table.GENOTYPE
ORDER BY
    ID ASC

 

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

YEAR() : 날짜 컬럼에서 '년' 일자만 출력하는 함수

#설명: 분화된 연도, 분화된 연도별 대장균 크기의 편차, 대장균 개체ID를 조회
#참고: 셀프 조인(SELF JOIN) 문제

SELECT
    YEAR(A_Table.DIFFERENTIATION_DATE) AS YEAR,
    (B_Table.MAX_IZE - A_Table.SIZE_OF_COLONY) AS YEAR_DEV,
    A_Table.ID
FROM
    ECOLI_DATA AS A_Table                        -- 배양한 대장균 정보
    LEFT JOIN (
        SELECT
            YEAR(DIFFERENTIATION_DATE) AS YEAR,
            MAX(SIZE_OF_COLONY) AS MAX_IZE       -- 연동별 최대 대장균 크기 정보
        FROM
            ECOLI_DATA
        GROUP BY
            YEAR(DIFFERENTIATION_DATE)) AS B_Table
    ON YEAR(A_Table.DIFFERENTIATION_DATE) = B_Table.YEAR
ORDER BY
    YEAR ASC, YEAR_DEV ASC

 

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

#설명: 각 분기별 분화된 대장균의 개체 총 수 조회
#참고: CONCAT(CEIL(MONTH(DIFFERENTIATION_DATE)/3), 'Q') AS QUARTER 대체 가능

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

 

4. 특정 물고기를 잡은 총 수 구하기

OUTER JOIN 구문 : 두 테이블을 조인할 때 1개 테이블에만 데이터가 있도록 결과 조회
- LEFT JOIN 구문 : OUTER JOIN 구문의 한 종류로써, 왼쪽 테이블의 모든 컬럼을 출력하도록 조회

#설명: 그 동안 잡은 물고기 중에서 BASS와 SNAPPER의 수를 조회

SELECT
    COUNT(A_Table.ID) AS FISH_COUNT
FROM
    FISH_INFO AS A_Table
    LEFT JOIN FISH_NAME_INFO AS B_Table
    ON A_Table.FISH_TYPE = B_Table.FISH_TYPE
WHERE
    B_Table.FISH_NAME IN ('BASS', 'SNAPPER')

 

5. 월별 잡은 물고기 수 구하기

IFNULL() : 컬럼의 값이 NULL 값을 반환할 때 다른 값으로 출력되도록 하는 함수

COUNT(*) : NULL 포함한 행 수를 출력하는 함수

#설명: 월별 잡은 물고기 수, 월을 조회

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

 

6. 물고기 종류 별 잡은 수 구하기

#설명: 물고기의 종류별 물고기 이름과 잡은 수를 조회

SELECT
    COUNT(A_Table.ID) AS FISH_COUNT,
    B_Table.FISH_NAME
FROM
    FISH_INFO AS A_Table
    INNER JOIN FISH_NAME_INFO AS B_Table
    ON A_Table.FISH_TYPE = B_Table.FISH_TYPE
GROUP BY
    B_Table.FISH_NAME
ORDER BY
    FISH_COUNT	DESC

※ 테이블 간 결합을 위해 사용하는 INNER JOIN은 'INNER'을 생략할 수 있으므로 'JOIN' 구문만 입력해서 사용 가능

 

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

#설명: 노선별, 노선, 총 누계거리, 평균 역 사이 거리를 조회
#참고: 총 누계 거리는 테이블에 존재하는 역들의 역 사이거리의 총합을 의미

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

 

8. 연도 별 평균 미세먼지 농도 조회하기

GROUP BY 구문 : 특정 컬럼 값을 기준으로 결과 값을 조회하고 싶을 때 사용하는 구문
HAVING 구문 : 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
    LOCATION2 = '수원'
GROUP BY
    YEAR(YM)
ORDER BY
    YEAR ASC

 

 

9. 조건에 맞는 사원 정보 조회하기

LIMIT N : N만큼의 결과를 출력 제한하여 조회하는 함수

#설명: 22년 평가 점수가 가장 높은 사원들의 점수, 사번, 성명, 직책, 이메일 조회

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

 

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

#설명: Python, c# 스킬을 가진 개발자의 ID, 이메일, 이름, 성을 조회

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