[데이터분석 부트캠프] SQL 문제풀이 #1. 프로그래머스 SUM, MAX, MIN

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

문제: 물고기 종류 별로 가장 큰 물고기의 ID, 물고기 이름, 길이를 출력하는 SQL 문을 작성해주세요.

물고기의 ID 컬럼명은 ID, 이름 컬럼명은 FISH_NAME, 길이 컬럼명은 LENGTH로 해주세요.
결과는 물고기의 ID에 대해 오름차순 정렬해주세요.
단, 물고기 종류별 가장 큰 물고기는 1마리만 있으며 10cm 이하의 물고기가 가장 큰 경우는 없습니다. (이 부분은 뭐지? 라는 생각이 들었음)

SELECT a.ID,
       b.FISH_NAME,
       a.LENGTH
FROM FISH_INFO a
JOIN FISH_NAME_INFO b  #2개의 테이블에서 가져올 데이터가 있으므로 조인 진행
  ON a.FISH_TYPE = b.FISH_TYPE #FISH_TYPE 열이 겹치기 때문에 기준으로 함
WHERE a.LENGTH = (
    SELECT MAX(a2.LENGTH) #길이가 가장 긴 물고기 정보를 가져와야하므로 서브쿼리로 가장 긴 길이를 가져오도록 함
    FROM FISH_INFO a2  #테이블이 한번 더 필요하기 때문에 또다른 alias를 사용하여 기입
    WHERE a2.FISH_TYPE = a.FISH_TYPE #빠뜨리지 않도록 주의!!
) 
ORDER BY a.ID
;

 

2. 잡은 물고기 중 가장 큰 물고기의 길이 구하기

문제: FISH_INFO 테이블에서 잡은 물고기 중 가장 큰 물고기의 길이를 'cm' 를 붙여 출력하는 SQL 문을 작성해주세요.

이 때 컬럼명은 'MAX_LENGTH' 로 지정해주세요.

SELECT CONCAT(a.LENGTH,'cm') as MAX_LENGTH
FROM FISH_INFO a
WHERE a.LENGTH = (SELECT MAX(b.LENGTH)
                  FROM FISH_INFO b
                  WHERE a.FISH_TYPE = b.FISH_TYPE)
ORDER BY a.LENGTH DESC
LIMIT 1
;

* CONCAT: 문자 합치기

ex. CONCAT(NAME, '님') >>> 니카님

 

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

문제: 분화된 연도(YEAR), 분화된 연도별 대장균 크기의 편차(YEAR_DEV), 대장균 개체의 ID(ID) 를 출력하는 SQL 문을 작성해주세요. 분화된 연도별 대장균 크기의 편차는 분화된 연도별 가장 큰 대장균의 크기 - 각 대장균의 크기로 구하며 결과는 연도에 대해 오름차순으로 정렬하고 같은 연도에 대해서는 대장균 크기의 편차에 대해 오름차순으로 정렬해주세요.

SELECT YEAR(a.DIFFERENTIATION_DATE) AS 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(DIFFERENTIATION_DATE)
) AS b
ON YEAR(a.DIFFERENTIATION_DATE) = b.YEAR
ORDER BY 1,2
;

* 서브쿼리에 alias는 FROM 절에서만 사용 가능하다.
❌ WHERE 절 안의 서브쿼리에 alias를 붙이면 문법 오류 발생.