[Programmers] SQL level2

#최솟값 구하기
SELECT MIN(DATETIME) FROM ANIMAL_INS;

#동물 수 구하기
SELECT COUNT(*) AS COUNT FROM ANIMAL_INS;

#중복 제거하기
SELECT COUNT(DISTINCT NAME) AS COUNT FROM ANIMAL_INS;

#고양이와 개는 몇 마리 있을까
SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE) AS count FROM ANIMAL_INS GROUP BY(ANIMAL_TYPE) ORDER BY ANIMAL_TYPE;

#동명 동물 수 찾기
SELECT NAME, COUNT(NAME) AS COUNT FROM ANIMAL_INS GROUP BY(NAME) HAVING COUNT(NAME) > 1 ORDER BY NAME;

#NULL 처리하기
SELECT ANIMAL_TYPE, 
CASE WHEN NAME IS NULL THEN 'No name'
ELSE NAME
END AS NAME, 
SEX_UPON_INTAKE 
FROM ANIMAL_INS 
ORDER BY ANIMAL_ID;

#입양 시각 구하기(1)
SELECT HOUR(DATETIME) AS HOUR, COUNT(*) AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) BETWEEN 9 AND 19 GROUP BY(HOUR) ORDER BY HOUR;

#루시와 엘라 찾기
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 ANIMAL_TYPE = 'Dog' AND NAME LIKE "%el%" ORDER BY 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;

#DATETIME에서 DATE로 형 변환
SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME,"%Y-%m-%d") AS 날짜 FROM ANIMAL_INS ORDER BY ANIMAL_ID;

# 중복제거하기
SELECT COUNT(DISTINCT NAME) AS COUNT FROM ANIMAL_INS;
-> DISTINCT를 COUNT괄호 안에 넣어야함., COUNT(*)는 NULL 까지 카운트하지만 COUNT(속성)은 NULL을 카운트하지않음

# NULL 처리하기
(NVL 함수 활용(Oracle) OR CASE-WHEN-THEN-WHEN-THEN...-ELSE-END 활용(MySQL))
A) - SELECT ANIMAL_TYPE, NVL(NAME,'No name') AS NAME, SEX_UPON_INTAKE FROM ANIMAL_INS ORDER BY ANIMAL_ID;
B) - SELECT ANIMAL_TYPE,
CASE WHEN NAME IS NULL THEN 'No name'
ELSE NAME
END AS NAME,
SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

# 이름에 el이 들어가는 동물 찾기
SELECT ANIMAL_ID, NAME FROM ANIMAL_INS WHERE ANIMAL_TYPE = 'Dog' AND NAME LIKE "%el%" ORDER BY NAME;
-> LIKE 문법 주의, WHERE절에 LIKE 보다 Dog 먼저 걸러줘야 효율적인 SQL이 된다.

# 입양 시각 구하기(1) (SUBSTRING 함수 활용 OR HOUR 함수 활용)
A) - SELECT SUBSTRING(DATETIME,12,2) AS HOUR, COUNT() AS COUNT FROM ANIMAL_OUTS WHERE SUBSTRING(DATETIME,12,2) BETWEEN 9 AND 19 GROUP BY(HOUR) ORDER BY HOUR;
-> SELECT 절에서 AS HOUR로 ALIAS 한 경우 GROUP BY, ORDER BY절 등에서 ALIAS명을 사용할 수 있음
B) - SELECT HOUR(DATETIME) AS HOUR, COUNT() AS COUNT FROM ANIMAL_OUTS WHERE HOUR(DATETIME) BETWEEN 9 AND 19 GROUP BY(HOUR) ORDER BY HOUR;
-> MySQL 날짜 함수 참고 (HOUR, MINUTE, SECOND 등) : https://jang8584.tistory.com/7

# 중성화 여부 파악하기 (CASE-THEN-ELSE-END 활용)