프로그래머스 SQL 고득점 Kit 문제 모음 2

IS NULL

이름이 없는 동물의 ID : https://programmers.co.kr/learn/courses/30/lessons/59039

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

이름이 있는 동물의 ID : https://programmers.co.kr/learn/courses/30/lessons/59407

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

NULL 처리하기 : https://programmers.co.kr/learn/courses/30/lessons/59410

[ORACLE]

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

[MYSQL]

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

JOIN

없어진 기록 찾기 : https://programmers.co.kr/learn/courses/30/lessons/59042

SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_OUTS OUTS LEFT JOIN ANIMAL_INS INS
ON OUTS.ANIMAL_ID = INS.ANIMAL_ID
WHERE INS.ANIMAL_ID IS NULL
ORDER BY OUTS.ANIMAL_ID ASC;

있었는데요 없었습니다 : https://programmers.co.kr/learn/courses/30/lessons/59043

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

오랜 기간 보호한 동물(1) : https://programmers.co.kr/learn/courses/30/lessons/59044

[MySQL]

SELECT  I.NAME, I.DATETIME
    FROM ANIMAL_INS I 
    WHERE NOT EXISTS (SELECT 1 FROM ANIMAL_OUTS O WHERE I.ANIMAL_ID = O.ANIMAL_ID)
    ORDER BY I.DATETIME
LIMIT 3
;

[ORACLE]

SELECT * FROM
    (SELECT  I.NAME, I.DATETIME
    FROM ANIMAL_INS I 
    WHERE NOT EXISTS (SELECT 1 FROM ANIMAL_OUTS O WHERE I.ANIMAL_ID = O.ANIMAL_ID)
    ORDER BY I.DATETIME
    ) T
WHERE ROWNUM < 4
;

보호소에서 중성화한 동물 : https://programmers.co.kr/learn/courses/30/lessons/59045

SELECT OUTS.ANIMAL_ID, OUTS.ANIMAL_TYPE, OUTS.NAME 
FROM
    (SELECT ANIMAL_ID, ANIMAL_TYPE, NAME
        FROM ANIMAL_OUTS
        WHERE ( SEX_UPON_OUTCOME LIKE 'Neutered%' OR SEX_UPON_OUTCOME LIKE 'Spayed%' )
    ) OUTS
INNER JOIN
    (SELECT ANIMAL_ID, ANIMAL_TYPE, NAME
        FROM ANIMAL_INS 
        WHERE SEX_UPON_INTAKE LIKE 'Intact%'  
    ) INS
ON OUTS.ANIMAL_ID = INS.ANIMAL_ID
ORDER BY OUTS.ANIMAL_ID
;

String, Date

루시와 엘라 찾기 : https://programmers.co.kr/learn/courses/30/lessons/59046

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

이름에 el이 들어가는 동물 찾기 : https://programmers.co.kr/learn/courses/30/lessons/59047

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE UPPER(NAME) LIKE '%EL%'
AND ANIMAL_TYPE = 'Dog'
ORDER BY NAME

중성화 여부 파악하기 : https://programmers.co.kr/learn/courses/30/lessons/59409

SELECT ANIMAL_ID
    , NAME
    , CASE SUBSTR(SEX_UPON_INTAKE, 0, INSTR(SEX_UPON_INTAKE, ' ')-1) 
      WHEN 'Neutered' THEN 'O' 
      WHEN 'Spayed' THEN 'O'
      ELSE 'X' END AS 중성화
FROM ANIMAL_INS
ORDER BY ANIMAL_ID
;

오랜 기간 보호한 동물(2) : https://programmers.co.kr/learn/courses/30/lessons/59411

[ORACLE]

SELECT * FROM
(SELECT I.ANIMAL_ID, I.NAME
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 < 3

[MYSQL]

SELECT I.ANIMAL_ID, I.NAME
FROM   ANIMAL_INS I INNER JOIN ANIMAL_OUTS O
ON     I.ANIMAL_ID =  O.ANIMAL_ID
ORDER BY O.DATETIME - I.DATETIME DESC
LIMIT 2

DATETIME에서 DATE로 형 변환 : https://programmers.co.kr/learn/courses/30/lessons/59414

[ORACLE]

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

[MySQL]

SELECT ANIMAL_ID
    , NAME
    , DATE_FORMAT(DATETIME, '%Y-%m-%d') AS 날짜 
FROM ANIMAL_INS
ORDER BY ANIMAL_ID