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

■ 출처

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

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

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

 

 

 

■ 연습문제

1. 대장균의 크기에 따라 분류하기 2

NTILE(N) OVER(ORDER BY 컬럼) : 컬럼 값을 기준으로 N개 등급으로 분류하여 조회하는 함수

#설명: 대장균 개체의 크기를 4개 등급으로 분류하여, 대장균 개체의 ID와 분류된 이름을 조회
#참고: 데이터의 수는 4의 배수이며, 같은 사이즈의 대장균 개체가 서류 다른 이름으로 분류됨을 전제

SELECT
    ID,
    (CASE WHEN NTILE(4) OVER(ORDER BY SIZE_OF_COLONY DESC)=1 THEN 'CRITICAL'
          WHEN NTILE(4) OVER(ORDER BY SIZE_OF_COLONY DESC)=2 THEN 'HIGH'
          WHEN NTILE(4) OVER(ORDER BY SIZE_OF_COLONY DESC)=3 THEN 'MEDIUM' ELSE 'LOW' END) AS COLONY_NAME
FROM
    ECOLI_DATA 
ORDER BY
    ID

 

2. 대장균의 크기에 따라 분류하기 1

#설명: 대장균 개체의 크기를 3개로 분류하여, 대장균 개체의 ID와 분류 상태로 조회

SELECT
    ID,
    (CASE WHEN SIZE_OF_COLONY<=100 THEN 'LOW'
         WHEN SIZE_OF_COLONY<=1000 THEN 'MEDIUM' ELSE 'HIGH' END) AS SIZE
FROM
    ECOLI_DATA 
ORDER BY
    ID ASC

 

3. 대장균들의 자식의 수 구하기

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

스칼라 서브쿼리(Scalar Suquery) : 
- SELECT 구문에 위치하는 서브쿼리로 메인쿼리의 각 행에 대한 단일값 반환
- 출력되는 하나의 값이 없다면 NULL 값을 반환
- 메인쿼리의 각 행에 대한 서브쿼리가 실행

#설명: 대장균 개체의 ID와 자식 수를 조회

SELECT
    ID, 
    #개체ID별 자식 수를 측정하기 위해, 'PARENT_ID = ID' 조건이 일치되는 레코드 수 조회(단, 자식 없으면 0 출력)
    IFNULL((SELECT COUNT(*) 
            FROM ECOLI_DATA  
            GROUP BY PARENT_ID 
            HAVING PARENT_ID = ID), 0) AS CHILD_COUNT
FROM
    ECOLI_DATA 
ORDER BY
     ID ASC

 

4. 특정 조건을 만족하는 물고기별 수와 최대 길이 구하기

#설명: 평균 길이가 33cm 이상인 물고기들을 종류별로 분류하여, 잡은 수, 최대 길이, 물고기의 종류를 조회

SELECT
    COUNT(FISH_TYPE) AS FISH_COUNT,
    MAX(LENGTH) AS MAX_LENGTH,
    FISH_TYPE 
FROM
    FISH_INFO
GROUP BY
    FISH_TYPE
HAVING
    AVG(IFNULL(LENGTH, 10)) >= 33
ORDER BY
    FISH_TYPE ASC

 

5. 물고기 종류 별 대어 찾기

중첩 서브쿼리(Nested Subquery) :   
- WHERE/HAVING 구문과 같은 조건절에 사용하는 서브쿼리
- 메인쿼리의 조건을 결정하기 위해 서브쿼리의 결과를 비교
- 서브쿼리는 메인쿼리의 조건을 평가하는데 사용되며, 서브쿼리의 결과는 단일 값, 다중 값, 혹은 테이블이 될 수 있음
- 비연관 서브쿼리일 경우, 메인쿼리보다 먼저 실행
- 연관 서브쿼리는 메인쿼리의 각 행에 대해 반본적으로 실행

#설명: 물고기 종류별로 가장 큰 물고기의 ID, 이름, 길이를 조회

SELECT
    A_Table.ID,
    B_Table.FISH_NAME,
    A_Table.LENGTH
FROM
    FISH_INFO AS A_Table 
    LEFT JOIN FISH_NAME_INFO AS B_Table  
    ON A_Table.FISH_TYPE = B_Table.FISH_TYPE
WHERE 
    #두 테이블을 결합한 후, 물고기 종류별 최대 크기를 갖는 데이터만 남도록 조건문 설정
    A_Table.FISH_TYPE IN (SELECT FISH_TYPE FROM FISH_INFO GROUP BY FISH_TYPE HAVING LENGTH = MAX(LENGTH))
ORDER BY
    A_Table.ID

 

6. 부서별 평균 연봉 조회하기

#설명: 부서별 평균 연봉을 확인하고자 부서ID, 영문 부서명, 평균 연봉을 조회

SELECT
    A_Table.DEPT_ID,
    A_Table.DEPT_NAME_EN,
    ROUND(AVG(B_Table.SAL), 0) AS AVG_SAL
FROM
    HR_DEPARTMENT AS A_Table
    RIGHT JOIN HR_EMPLOYEES AS B_Table
    ON A_Table.DEPT_ID = B_Table.DEPT_ID
GROUP BY
    A_Table.DEPT_ID
ORDER BY
    AVG_SAL DESC

 

 

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

#설명: 더 이상 업그레이드를 할 수 없는 아이템의 ID, 이름, 회귀도를 출력

SELECT
    A_Table.ITEM_ID,
    A_Table.ITEM_NAME,
    A_Table.RARITY
FROM
    #아이템 ID와 부모 아이템 ID가 일치하는 테이블을 구축
    ITEM_INFO AS A_Table
    LEFT JOIN ITEM_TREE AS B_Table
    ON A_Table.ITEM_ID = B_Table.PARENT_ITEM_ID
WHERE
    #PARENT_ITEM_ID이 NULL 값을 가지는 결과를 출력(즉, 최대 업그레이드 아이템)
    B_Table.PARENT_ITEM_ID IS NULL
ORDER BY
    A_Table.ITEM_ID DESC

 

8. 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기

#설명: 조회수가 가장 높은 중고거래 게시물에 대한 첨부파일 경로를 조회

SELECT
    CONCAT('/home/grep/src/', B_Table.BOARD_ID, '/', 
           B_Table.FILE_ID, B_Table.FILE_NAME, B_Table.FILE_EXT) AS FILE_PATH
FROM
    USED_GOODS_BOARD AS A_Table
    LEFT JOIN USED_GOODS_FILE  AS B_Table
    ON A_Table.BOARD_ID = B_Table.BOARD_ID
WHERE
    A_Table.VIEWS IN (SELECT MAX(VIEWS) FROM USED_GOODS_BOARD)

 

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

SUBSTRING(문자열, 시작위치, 길이) : 문자열에서 시작 위치부터 길이만큼 조회하는 함수
LEFT(문자열, 길이) : 문자열에서 왼쪽부터 길이만큼 조회하는 함수
RIGHT(문자열, 길이) : 문자열에서 오른쪽부터 길이만큼 조회하는 함수

#설명: 중고거래 게시물을 3건 이상 등록한 사용자의 ID, 닉네임, 전체주소, 전화번호 조회

SELECT
    B_Table.USER_ID,
    B_Table.NICKNAME,
    CONCAT(B_Table.CITY, ' ', B_Table.STREET_ADDRESS1, ' ', B_Table.STREET_ADDRESS2) AS '전체주소',
    CONCAT(SUBSTRING(B_Table.TLNO, 1, 3), 
           '-', SUBSTRING(B_Table.TLNO, 4, 4), 
           '-', SUBSTRING(B_Table.TLNO, 8, 4)) AS '전화번호'
    
FROM
    USED_GOODS_BOARD AS A_Table
    LEFT JOIN USED_GOODS_USER  AS B_Table
    ON A_Table.WRITER_ID = B_Table.USER_ID
GROUP BY
    B_Table.USER_ID
HAVING
    COUNT(A_Table.WRITER_ID) >= 3
ORDER BY 
    B_Table.USER_ID DESC

 

10. 조건에 맞는 사용자와 총 거래금액 조회하기

#설명: 완료된 중고거래의 총금액이 70만원 이상인 회원ID, 닉네임, 총거래금액을 조회

SELECT
    B_Table.USER_ID,
    B_Table.NICKNAME,
    SUM(A_Table.PRICE) AS TOTAL_SALES
FROM
    USED_GOODS_BOARD AS A_Table
    LEFT JOIN USED_GOODS_USER  AS B_Table
    ON A_Table.WRITER_ID = B_Table.USER_ID
WHERE 
    A_Table.STATUS = 'DONE'
GROUP BY
    B_Table.USER_ID
HAVING
    TOTAL_SALES >= 700000
ORDER BY
    TOTAL_SALES ASC