[프로그래머스/SQL] String, Date (MySQL)

https://programmers.co.kr/learn/courses/30/parts/17047

프로그래머스

 

루시와 엘라 찾기

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
ORDER BY ANIMAL_ID;

 

이름에 el이 들어가는 동물 찾기

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE NAME LIKE '%EL%' AND ANIMAL_TYPE = 'DOG'
ORDER BY NAME;

 

중성화 여부 파악하기

# SELECT ANIMAL_ID, NAME, 
# IF(SUBSTR(SEX_UPON_INTAKE, 1, 8) = 'Neutered' 
#    OR SUBSTR(SEX_UPON_INTAKE, 1, 7) = 'Spayed', 'O', 'X') AS 중성화 
# FROM ANIMAL_INS;

# SELECT ANIMAL_ID, NAME, 
# IF(SEX_UPON_INTAKE LIKE 'Neutered%' OR SEX_UPON_INTAKE LIKE 'Spayed%', 'O', 'X') AS 중성화
# FROM ANIMAL_INS;

SELECT ANIMAL_ID, NAME, 
CASE 
    WHEN SEX_UPON_INTAKE LIKE 'Neutered%' OR SEX_UPON_INTAKE LIKE 'Spayed%' THEN 'O'
    ELSE 'X'
END AS 중성화
FROM ANIMAL_INS;

 

오랜 기간 보호한 동물(2)

SELECT AI.ANIMAL_ID, AI.NAME
FROM ANIMAL_INS AI
JOIN ANIMAL_OUTS AO ON AI.ANIMAL_ID = AO.ANIMAL_ID
ORDER BY AO.DATETIME - AI.DATETIME DESC
LIMIT 2;

 

 

DATETIME에서 DATE로 형 변환

SELECT ANIMAL_ID, NAME, SUBSTR(DATETIME, 1, 10) AS 날짜
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;