Lv.2 mysql

🔒 고양이와 개는 몇 마리 있을까

🗝️ my solution

SELECT ANIMAL_TYPE, count(ANIMAL_TYPE) as count
from ANIMAL_INS
group by ANIMAL_TYPE
order by ANIMAL_TYPE

🧩 group by

🔒 최솟값 구하기

🗝️ my solution

SELECT DATETIME as 시간
from ANIMAL_INS
where DATETIME = (select min(DATETIME) from ANIMAL_INS)

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

🗝️ my solution

SELECT ANIMAL_ID, NAME
from ANIMAL_INS
where NAME LIKE '%EL%' and ANIMAL_TYPE = 'Dog'
order by NAME;

🧩 LIKE

🔒 동물 수 구하기

🗝️ my solution

SELECT count(ANIMAL_ID) as count
from ANIMAL_INS;

🔒 중복 제거하기

🗝️ my solution

SELECT count(distinct NAME) as count
from ANIMAL_INS
where NAME is not NULL;

🧩 중복 제거

🔒 동명 동물 수 찾기

🗝️ my solution

SELECT NAME, COUNT(NAME) AS count
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME
HAVING count > 1
ORDER BY NAME;

🧩 GROUP BY + HAVING

🔒 NULL 처리하기

🗝️ my solution

SELECT ANIMAL_TYPE, 
    IF(NAME IS NULL, 'No name', NAME) AS 'NAME',
    SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

🧩 IF문

🔒 DATETIME에서 DATE로 형 변환

🗝️ my solution

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

🧩 DATE_FORMAT()

🔒 중성화 여부 파악하기

🗝️ my solution

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;

🧩 CASE WHEN, IF ELSE