[SQL] 프로그래머스 SQL 고득점 KIT 문제 풀이 정답 -2

JOIN 풀이

없어진 기록 찾기

-- 코드를 입력하세요
SELECT outs.ANIMAL_ID, outs.NAME
from ANIMAL_OUTS as outs
left join ANIMAL_INS as ins on outs.ANIMAL_ID=ins.ANIMAL_ID
where ins.ANIMAL_ID is null
order by outs.ANIMAL_ID

 

있었는데요 없었습니다

SELECT ins.ANIMAL_ID, ins.NAME 
FROM ANIMAL_INS as ins
JOIN ANIMAL_OUTS as outs
ON ins.ANIMAL_ID = outs.ANIMAL_ID 
WHERE ins.DATETIME > outs.DATETIME 
ORDER BY ins.DATETIME

 

오랜 기간 보호한 동물 1 - LEFT OUTER JOIN

-- 코드를 입력하세요
SELECT ins.NAME, ins.DATETIME
FROM ANIMAL_INS as ins
LEFT JOIN ANIMAL_OUTS as outs
ON ins.ANIMAL_ID = outs.ANIMAL_ID
WHERE outs.ANIMAL_ID is null
ORDER BY ins.DATETIME
LIMIT 3;

 

보호소에서 중성화한 동물

-- 코드를 입력하세요
SELECT ins.ANIMAL_ID, ins.ANIMAL_TYPE, ins.NAME
FROM ANIMAL_INS as ins
JOIN ANIMAL_OUTS as outs
ON ins.ANIMAL_ID = outs.ANIMAL_ID
WHERE ins.SEX_UPON_INTAKE LIKE "Intact%" 
AND (outs.SEX_UPON_OUTCOME LIKE "Neutered%" 
     OR outs.SEX_UPON_OUTCOME LIKE "Spayed%")
ORDER BY ins.ANIMAL_ID

 

String, Date

루시와 엘라 찾기

-- 코드를 입력하세요
SELECT ins.ANIMAL_ID, ins.NAME, ins.SEX_UPON_INTAKE
FROM ANIMAL_INS as ins
WHERE ins.NAME LIKE "Lucy%" OR
        ins.NAME LIKE "Ella%" OR
        ins.NAME LIKE "Pickle%" OR
        ins.NAME LIKE "Rogan%" OR
        ins.NAME LIKE "Sabrina%" OR
        ins.NAME LIKE "Mitty%"
ORDER BY ins.ANIMAL_ID;

 

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

-- 코드를 입력하세요
SELECT ins.ANIMAL_ID, ins.NAME
FROM ANIMAL_INS as ins
WHERE ins.NAME LIKE "%el%"
    AND ins.ANIMAL_TYPE = "Dog"
ORDER BY ins.NAME;

 

중성화 여부 파악하기

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 
ORDER BY ANIMAL_ID

 

오랜 기간 보호한 동물 2 

-- 코드를 입력하세요
SELECT outs.ANIMAL_ID, outs.NAME
FROM ANIMAL_OUTS as outs
LEFT JOIN ANIMAL_INS as ins
ON ins.ANIMAL_ID = outs.ANIMAL_ID
ORDER BY outs.DATETIME-ins.DATETIME DESC
LIMIT 2;

 

DATETIME에서 DATE로 형 변환

%Y 4자리 연도 %y 2자리 연소

-- 코드를 입력하세요
SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, '%Y-%m-%d') AS 날짜
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;