[SQL] 코테 준비

상위 N개 레코드

SELECT NAME
FROM ANIMAL_INS
ORDER BY DATETIME
LIMIT 1

-> 가장 먼저 들어온 동물(Order by datetime) limit 1로 두어 하나만 출력!

최댓값 구하기

1. limit 활용

SELECT DATETIME AS '시간'
FROM ANIMAL_INS
ORDER BY DATETIME DESC
LIMIT 1

2. max 활용

SELECT MAX(DATETIME) AS '시간'
FROM ANIMAL_INS

중복 제거하기

SELECT COUNT(DISTINCT NAME)
FROM ANIMAL_INS
WHERE NAME IS NOT NULL

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

SELECT ANIMAL_TYPE, COUNT(ANIMAL_ID) AS 'COUNT'
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE

1) 프-웨-그-해-설-오 순서에 따라
group by animal_type : 애니멀 타입에 따라 분류를 한 다음,
count(animal_id) : 각 type에 따른 animal_id를 세아려준다.
2) animal_type을 기준으로 animal_id별로 count

동명 동물 수 찾기

SELECT NAME, COUNT(NAME) AS 'COUNT'
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
GROUP BY NAME
HAVING COUNT(NAME)>=2
ORDER BY NAME

입양 시각 구하기(1)

SELECT HOUR(DATETIME) AS HOUR, COUNT(DATETIME) AS 'COUNT'
FROM ANIMAL_OUTS
WHERE HOUR(DATETIME)>=9 AND HOUR(DATETIME) <=19
GROUP BY HOUR(DATETIME)
ORDER BY HOUR

• HOUR(DATETIME) : 시간을 추출하는 함수 HOUR 활용

입양 시각 구하기(2) -> 이거까지,,나올까?

SET @HOUR := -1; # 변수선언
SELECT (@HOUR := @HOUR +1) AS HOUR,
(SELECT COUNT(*) FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = @HOUR) AS COUNT 
FROM ANIMAL_OUTS
WHERE @HOUR < 23

• SET 함수 활용
• HOUR 에 -1을 대입(SET @HOUR = -1;)

NULL 처리하기

SELECT ANIMAL_TYPE, IFNULL(NAME, 'No name') AS NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS

• IFNULL(컬럼 명, '대체값')

없어진 기록찾기

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

• INS 테이블에 없는 것을 찾아주어야 한다.
• LEFT OUTER JOIN 을 통해 OUTS 를 기준으로 OUTS의 모든 데이터가 나올 수 있도록 한다!

있었는데요 없었습니다

SELECT A.ANIMAL_ID,A.NAME
FROM ANIMAL_OUTS A LEFT OUTER JOIN ANIMAL_INS B ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE A.DATETIME<B.DATETIME
ORDER BY B.DATETIME

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

SELECT A.NAME, A.DATETIME
FROM ANIMAL_INS A LEFT OUTER JOIN ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID
WHERE B.ANIMAL_ID IS NULL
ORDER BY A.DATETIME
LIMIT 3

보호소에서 중성화한 동물

SELECT B.ANIMAL_ID, B.ANIMAL_TYPE, B.NAME
FROM ANIMAL_INS A LEFT OUTER JOIN ANIMAL_OUTS B ON A.ANIMAL_ID=B.ANIMAL_ID
WHERE A.SEX_UPON_INTAKE LIKE 'Intact%' AND (B.SEX_UPON_OUTCOME LIKE 'Spayed%' OR B.SEX_UPON_OUTCOME LIKE 'Neutered%')
ORDER BY B.ANIMAL_ID

루시와 엘라 찾기

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

WHERE 컬럼명 IN('', '', ...)

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

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE NAME LIKE '%el%' AND ANIMAL_TYPE ='Dog'
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

• case when 조건 then 참일때 표기 else 거짓일때 표기 end as 출력될 컬럼명

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

SELECT A.ANIMAL_ID, A.NAME
FROM ANIMAL_INS A LEFT OUTER JOIN ANIMAL_OUTS B ON A.ANIMAL_ID = B.ANIMAL_ID
ORDER BY B.DATETIME - A.DATETIME DESC
LIMIT 2

• Order by 에 뺄셈 연산을 넣어서 주기를 계산할 수 있다
• Limit를 통해 상위 2개만 출력되도록 지정(order by - limit)

DATETIME에서 DATE로 형 변환

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

• DATE_FORMAT(DATETIME, '%Y-%m-%d')