[SQL 쿼리테스트] 4일차

프로그래머스 MySQL 레벨 2 문제풀이

1) 부모의 형질을 모두 가지는 대장균 찾기 (풀이 완료)

• 틀린 이유: 비트 연산 사용 방법을 잘못 알고 있었음
  • 비트 연산은 숫자와 숫자 사이에 작동하며 이진수로 변환해주지 않아도 됨
  • CONV(숫자, 10, 2) 와 BIN(숫자) 함수는 문자열을 반환하기 때문에 CONV & CONV = CONV 연산은 문자열 간의 비트 연산으로 간주되어 일반적인 숫자간 비트연산간 다르게 작동함

# JOIN (기본 테이블 + 부모 GENOTYPE)
SELECT d.ID, d.GENOTYPE, p.GENOTYPE AS PARENT_GENOTYPE
FROM ECOLI_DATA d LEFT JOIN ECOLI_DATA p ON d.PARENT_ID = p.ID

# 부모 타입과 비교
WHERE d.GENOTYPE & p.GENOTYPE = p.GENOTYPE
ORDER BY d.ID ASC;

 

2) 연도별 대장균 크기의 편차 구하기 (풀이 완료)

• YEAR: 날짜형 칼럼에서 년도만 추출 → 숫자형으로 반환
• DATEFORMAT: 날짜형 칼럼에서 원하는 부분 추출 → 문자열로 반환
• 년도 추출시 DATEFORMAT 을 사용하면 문자열이 반환되어 ORDER BY 처리 시 숫자와는 정렬 기준이 달라 오답 처리 됨
• 감으로 풀어서 재풀이 필요
• DATEFORMAT 함수 활용 방법 알아두기
• 서브쿼리(GROUP BY)를 활용해서 푸는 방법 알아두기 [참고]

SELECT YEAR(DIFFERENTIATION_DATE) AS YEAR,
(SELECT MAX(SIZE_OF_COLONY) FROM ECOLI_DATA t1 WHERE YEAR(t1.DIFFERENTIATION_DATE) = YEAR(t2.DIFFERENTIATION_DATE)) - SIZE_OF_COLONY AS YEAR_DEV, ID
FROM ECOLI_DATA t2
ORDER BY YEAR ASC, YEAR_DEV ASC;

 

3) 분기별 분화된 대장균의 개체 수 구하기 (풀이 실패)

• 날짜 컬럼에서 분기 추출: QUARTER(컬럼명)
• 문자열 이어붙이기: CONCAT("문자1", "문자2", "문자3")
• 틀린 이유: SELECT 절에서 AS 로 별칭 지정한 변수 기준으로 GROUP BY할 때는 GROUP BY 절에도 별칭으로 써야 하는데 GROUP BY 절에 별칭으로 안 씀 [참고]

# 첫 코드 (서브쿼리 사용)
SELECT CONCAT(t2.QUARTER, "Q") AS QUARTER, COUNT(*) AS ECOLI_COUNT
FROM (SELECT QUARTER(DIFFERENTIATION_DATE) AS QUARTER, ID FROM ECOLI_DATA t1) t2
GROUP BY t2.QUARTER
ORDER BY t2.QUARTER ASC;

# 수정한 코드 (SELECT 절 별칭 지정 및 GROUP BY 절 그룹핑 기준 변수 호칭시 별칭 사용)
SELECT CONCAT(QUARTER(DIFFERENTIATION_DATE), "Q") AS QUARTER, COUNT(*) AS ECOLI_COUNT
FROM ECOLI_DATA
GROUP BY QUARTER
ORDER BY QUARTER ASC;

 

4) 특정 물고기를 잡은 총 수 구하기 (풀이 완료)

SELECT COUNT(*) AS FISH_COUNT
FROM FISH_INFO
WHERE FISH_TYPE IN (SELECT FISH_TYPE FROM FISH_NAME_INFO WHERE FISH_NAME IN ("BASS", "SNAPPER"));

 

5) 월별 잡은 물고기 수 구하기 (풀이 완료)

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