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

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

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

 

 

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

SELECT A.ID, IF(PCT <= 0.25, 'CRITICAL',
             IF(PCT <= 0.5, 'HIGH',
               IF(PCT <= 0.75, 'MEDIUM','LOW'))) AS COLONY_NAME
FROM ECOLI_DATA A
JOIN (SELECT ID, PERCENT_RANK() OVER (ORDER BY SIZE_OF_COLONY DESC) AS PCT
FROM ECOLI_DATA
) B
ON A.ID = B.ID
ORDER BY A.ID

 

 

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

SELECT ID, IF(SIZE_OF_COLONY <= 100, 'LOW',
              IF(SIZE_OF_COLONY <= 1000, 'MEDIUM', 'HIGH')) AS SIZE
FROM ECOLI_DATA
ORDER BY ID

 

 

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

SELECT A.ID, IF(ISNULL(B.CHILD_COUNT), 0, B.CHILD_COUNT) AS CHILD_COUNT
FROM ECOLI_DATA A
LEFT JOIN (
SELECT PARENT_ID AS ID, COUNT(ID) AS CHILD_COUNT
FROM ECOLI_DATA
GROUP BY PARENT_ID) B
ON A.ID = B.ID
ORDER BY A.ID

 

 

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

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

 

 

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

SELECT A.ID, B.FISH_NAME, A.LENGTH
FROM FISH_INFO A
JOIN FISH_NAME_INFO B
ON A.FISH_TYPE = B.FISH_TYPE
WHERE (A.FISH_TYPE, A.LENGTH) IN (
    SELECT FISH_TYPE, MAX(LENGTH) AS M_LENGTH
FROM FISH_INFO
GROUP BY FISH_TYPE
)

 

 

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

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

 

 

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

SELECT A.ITEM_ID, A.ITEM_NAME, A.RARITY
FROM ITEM_INFO A
LEFT JOIN ITEM_TREE B
ON A.ITEM_ID = B.PARENT_ITEM_ID
WHERE ISNULL(B.ITEM_ID) 
ORDER BY A.ITEM_ID DESC

 

 

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

SELECT CONCAT('/home/grep/src/',BOARD_ID,'/',FILE_ID,FILE_NAME,FILE_EXT) AS FILE_PATH
FROM USED_GOODS_FILE 
WHERE BOARD_ID = (SELECT BOARD_ID
FROM USED_GOODS_BOARD 
ORDER BY VIEWS DESC
LIMIT 1)
ORDER BY FILE_ID DESC

 

 

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

SELECT USER_ID, NICKNAME, CONCAT(CITY,' ', STREET_ADDRESS1,' ', STREET_ADDRESS2) AS 전체주소,
    CONCAT(LEFT(TLNO,3),'-',MID(TLNO,4,4),'-',RIGHT(TLNO,4)) AS 전화번호
FROM USED_GOODS_USER
WHERE USER_ID IN (SELECT WRITER_ID
FROM USED_GOODS_BOARD
GROUP BY WRITER_ID
HAVING COUNT(BOARD_ID) >= 3)
ORDER BY USER_ID DESC

 

 

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

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