[코딩테스트 연습- SQL] String, Date

1. 루시와 엘라 찾기

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME="Lucy" OR NAME="Ella" OR NAME="Pickle" OR NAME="Rogan" OR NAME="Sabrina" OR NAME="Mitty"

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME in ("Lucy","Ella","Pickle","Rogan","Sabrina","Mitty")

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

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE ANIMAL_TYPE = "Dog" AND NAME LIKE "%el%"
ORDER BY NAME

3. 중성화 여부 파악하기

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

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

SELECT A.ANIMAL_ID, A.NAME
FROM ANIMAL_INS A, ANIMAL_OUTS B
WHERE A.ANIMAL_ID = B.ANIMAL_ID 
ORDER BY B.DATETIME - A.DATETIME DESC
LIMIT 2

5. DATETIME에서 DATE로 형 변환

SELECT 
    ANIMAL_ID, NAME, 
    /* Y(0000), M/D(영문), y(00), m,d(숫자) */
    DATE_FORMAT(DATETIME, "%Y-%m-%d") AS "날짜"
FROM ANIMAL_INS
ORDER BY ANIMAL_ID