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

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

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

 
 
1. 특정 형질을 가지는 대장균 찾기 (비트 연산)

SELECT COUNT(ID) AS COUNT
FROM ECOLI_DATA
WHERE (GENOTYPE&2 = 0) AND (GENOTYPE&1 = 1 OR GENOTYPE&4 = 4)

 
 
2. 가장 큰 물고기 10마리 구하기 (ORDER BY, LIMIT)

SELECT ID, LENGTH
FROM FISH_INFO
ORDER BY LENGTH desc, ID asc
LIMIT 10

 
 
3. 한 해에 잡은 물고기 수 구하기 (YEAR)

SELECT COUNT(ID) AS FISH_COUNT
FROM FISH_INFO
WHERE YEAR(TIME) = 2021

 
 
4. 가장 큰 물고기의 길이 구하기 (DISTINCT, CONCAT, MAX)

SELECT DISTINCT(CONCAT(LENGTH,'cm')) AS MAX_LENGTH
FROM FISH_INFO
WHERE LENGTH IN (SELECT MAX(LENGTH)
                FROM FISH_INFO)

 
 

5. 잡은 물고기 평균 길이 구하기 (ROUND, AVG, ISNULL)

SELECT ROUND(AVG(IF(ISNULL(LENGTH),10,LENGTH)),2) AS AVERAGE_LENGTH
FROM FISH_INFO

 
 

6. 잔챙이 잡은 수 구하기 (ISNULL)

SELECT COUNT(ID) AS FISH_COUNT
FROM FISH_INFO
WHERE ISNULL(LENGTH)

 

 
7. Python 개발자 찾기 (WHERE)

SELECT ID, EMAIL, FIRST_NAME, LAST_NAME
FROM DEVELOPER_INFOS
WHERE SKILL_1 = 'Python' OR SKILL_2 = 'Python' OR SKILL_3 = 'Python'
ORDER BY ID

 
 
8. 조건에 부합하는 댓글 조회하기 (DATE_FORMAT)

SELECT A.TITLE, A.BOARD_ID, B.REPLY_ID, B.WRITER_ID, B.CONTENTS, DATE_FORMAT(B.CREATED_DATE,'%Y-%m-%d') AS CREATED_DATE
FROM USED_GOODS_BOARD A
JOIN USED_GOODS_REPLY B
ON A.BOARD_ID = B.BOARD_ID
WHERE YEAR(A.CREATED_DATE) = 2022 AND MONTH(A.CREATED_DATE) = 10
ORDER BY B.CREATED_DATE, A.TITLE

 
 
9. 특정 옵션이 포함된 자동차 리스트 구하기 (LIKE)

SELECT CAR_ID, CAR_TYPE, DAILY_FEE, OPTIONS
FROM CAR_RENTAL_COMPANY_CAR
WHERE OPTIONS LIKE '%네비게이션%'
ORDER BY CAR_ID DESC

 
 
10. 자동차 대여 기록에서 장기/단기 대여 구분하기 (DATE_FORMAT, DATE_DIFF)

SELECT HISTORY_ID, CAR_ID, 
    DATE_FORMAT(START_DATE,'%Y-%m-%d') AS START_DATE, 
    DATE_FORMAT(END_DATE,'%Y-%m-%d') AS END_DATE, 
    IF(DATEDIFF(END_DATE, START_DATE) >= 29, '장기 대여','단기 대여') AS RENT_TYPE
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
WHERE YEAR(START_DATE) = 2022 AND MONTH(START_DATE) = 9
ORDER BY HISTORY_ID DESC