프로그래머스 4~6 : IS NULL, JOIN, STRING, DATE

 

IS NULL

 

이름이 없는 동물의 아이디

SELECT NVL(NAME, ANIMAL_ID)  FROM ANIMAL_INS 
WHERE NAME IS NULL
ORDER BY ANIMAL_ID;

 

이름이 있는 동물의 아이디

SELECT ANIMAL_ID  FROM ANIMAL_INS 
WHERE NAME IS NOT NULL
ORDER BY ANIMAL_ID;

 

NULL 처리하기

SELECT
    ANIMAL_TYPE,
    NVL(NAME, 'No name') "NAME",
    SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

 

JOIN

 

없어진 기록 찾기

SELECT O.ANIMAL_ID, O.NAME 
FROM ANIMAL_INS I
    RIGHT JOIN ANIMAL_OUTS O
    ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE I.ANIMAL_ID IS NULL
ORDER BY O.ANIMAL_ID;

 

있었는데요 없었습니다

-- 보호 시작일보다 입양일이 더 빠른 동물
-- 1.3 > 1.1
-- INS.DATE >OUTS.DATE

SELECT I.ANIMAL_ID, I.NAME 
FROM ANIMAL_OUTS O
    INNER JOIN ANIMAL_INS I
    ON O.ANIMAL_ID = I.ANIMAL_ID 
WHERE 
    I.DATETIME > O.DATETIME
ORDER BY I.DATETIME;

 

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

WITH T1 AS(
SELECT I.* FROM ANIMAL_INS I
    LEFT JOIN ANIMAL_OUTS O
    ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE
    O.ANIMAL_ID IS NULL
ORDER BY I.DATETIME
)
SELECT NAME, DATETIME FROM T1 WHERE ROWNUM <= 3;

 

차집합은 잘 안먹는다.. 뚁땅함. ㅠ

 

 

보호소에서 중성화한 동물

SELECT 
    O.ANIMAL_ID, O.ANIMAL_TYPE, O.NAME --, SEX_UPON_INTAKE, SEX_UPON_OUTCOME
FROM ANIMAL_INS I
    INNER JOIN ANIMAL_OUTS O
    ON I.ANIMAL_ID = O.ANIMAL_ID
WHERE
    SEX_UPON_INTAKE NOT LIKE 'Spayed%' 
    AND SEX_UPON_INTAKE NOT LIKE 'Neutered%'
    AND (SEX_UPON_OUTCOME LIKE 'Spayed%'
         OR SEX_UPON_OUTCOME LIKE 'Neutered%'
    )
ORDER BY ANIMAL_ID;

 

String, Date

루시와 엘라 찾기

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
    UPPER(NAME) LIKE UPPER('%EL%')
    AND ANIMAL_TYPE = 'Dog'
ORDER BY NAME;

 

중성화 여부 파악하기

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

 

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

SELECT ANIMAL_ID, NAME FROM (
    SELECT O.ANIMAL_ID, O.NAME, O.DATETIME - I.DATETIME FROM ANIMAL_INS I
        INNER JOIN ANIMAL_OUTS O
        ON I.ANIMAL_ID = O.ANIMAL_ID
    ORDER BY O.DATETIME - I.DATETIME DESC
)
WHERE ROWNUM <= 2;

 

DATETIME에서 DATE로 형 변환

SELECT 
    ANIMAL_ID, 
    NAME,
    TO_CHAR(DATETIME, 'YYYY-MM-DD') AS "날짜" 
    FROM ANIMAL_INS
ORDER BY ANIMAL_ID;