링크

SELECT

모든 레코드 조회하기

1
SELECT * FROM ANIMAL_INS ORDER BY ANIMAL_ID ASC

역순 정렬하기

1
SELECT NAME, DATETIME FROM ANIMAL_INS ORDER BY ANIMAL_ID DESC

아픈 동물 찾기

1
SELECT ANIMAL_ID, NAME FROM ANIMAL_INS WHERE INTAKE_CONDITION = 'Sick' ORDER BY ANIMAL_ID ASC

어린 동물 찾기

1
SELECT ANIMAL_ID, NAME FROM ANIMAL_INS WHERE INTAKE_CONDITION != 'Aged' ORDER BY ANIMAL_ID ASC

동물의 아이디와 이름

1
SELECT ANIMAL_ID, NAME FROM ANIMAL_INS ORDER BY ANIMAL_ID ASC

여러 기준으로 정렬하기

1
SELECT ANIMAL_ID, NAME, DATETIME FROM ANIMAL_INS ORDER BY NAME ASC, DATETIME DESC

상위 n개 레코드

1
SELECT NAME FROM ANIMAL_INS ORDER BY DATETIME ASC LIMIT 1

SUM, MAX, MIN

최댓값 구하기

1
SELECT MAX(DATETIME) FROM ANIMAL_INS

최솟값 구하기

1
SELECT MIN(DATETIME) FROM ANIMAL_INS

동물 수 구하기

1
SELECT COUNT(ANIMAL_ID) FROM ANIMAL_INS

중복 제거하기

1
SELECT COUNT(DISTINCT NAME) FROM ANIMAL_INS

GROUP BY

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

1
SELECT ANIMAL_TYPE, COUNT(*) FROM ANIMAL_INS WHERE ANIMAL_TYPE = 'Cat' or ANIMAL_TYPE = 'Dog' GROUP BY ANIMAL_TYPE ORDER BY ANIMAL_TYPE ASC

동명 동물 수 찾기

1
SELECT NAME, COUNT(NAME) FROM ANIMAL_INS GROUP BY NAME HAVING COUNT(NAME) >= 2 ORDER BY NAME ASC

입양 시각 구하기(1)

1
2
-- 코드를 입력하세요
SELECT HOUR(DATETIME), COUNT(HOUR(DATETIME)) FROM ANIMAL_OUTS GROUP BY HOUR(DATETIME) ORDER BY HOUR(DATETIME)

입양 시각 구하기(2)

1
2
3
4
5
6
7
8
9
10
WITH RECURSIVE CTE AS (
    SELECT 0 AS N
    UNION ALL
    SELECT N + 1 FROM CTE
    WHERE N < 23
)

SELECT N,
(SELECT COUNT(*) FROM ANIMAL_OUTS WHERE HOUR(DATETIME) = N)
FROM CTE

ISNULL

이름이 없는 동물의 아이디

1
SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME IS NULL ORDER BY ANIMAL_ID ASC

이름이 있는 동물의 아이디

1
SELECT ANIMAL_ID FROM ANIMAL_INS WHERE NAME IS NOT NULL ORDER BY ANIMAL_ID ASC

NULL 처리하기

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

JOIN

없어진 기록 찾기

1
2
3
4
5
SELECT ANIMAL_ID, ANIMAL_OUTS.NAME
FROM ANIMAL_OUTS LEFT OUTER JOIN ANIMAL_INS
USING (ANIMAL_ID)
WHERE ANIMAL_INS.NAME IS NULL AND ANIMAL_OUTS.NAME IS NOT NULL
ORDER BY ANIMAL_ID ASC

있었는데요 없었습니다

1
2
3
4
5
SELECT ANIMAL_ID, ANIMAL_INS.NAME 
FROM ANIMAL_INS JOIN ANIMAL_OUTS 
USING(ANIMAL_ID) 
WHERE ANIMAL_INS.DATETIME > ANIMAL_OUTS.DATETIME 
ORDER BY ANIMAL_INS.DATETIME ASC

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

1
2
3
4
5
SELECT ANIMAL_INS.NAME, ANIMAL_INS.DATETIME 
FROM ANIMAL_INS LEFT OUTER JOIN ANIMAL_OUTS USING(ANIMAL_ID)
WHERE ANIMAL_OUTS.DATETIME IS NULL
ORDER BY ANIMAL_INS.DATETIME ASC
LIMIT 3

보호소에서 중성화한 동물

1
2
3
4
5
6
SELECT ANIMAL_ID, ANIMAL_INS.ANIMAL_TYPE, ANIMAL_INS.NAME 
FROM ANIMAL_INS JOIN ANIMAL_OUTS USING(ANIMAL_ID)
WHERE ANIMAL_INS.SEX_UPON_INTAKE LIKE 'Intact%' 
AND (ANIMAL_OUTS.SEX_UPON_OUTCOME LIKE 'Spayed%'
OR ANIMAL_OUTS.SEX_UPON_OUTCOME LIKE 'Neutered%')
ORDER BY ANIMAL_ID ASC

STRING, DATE

루시와 엘라 찾기

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

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

1
SELECT ANIMAL_ID, NAME FROM ANIMAL_INS WHERE NAME LIKE '%el%' and ANIMAL_TYPE = 'Dog' ORDER BY NAME ASC

중성화 여부 파악하기

1
SELECT ANIMAL_ID, NAME, IF(SEX_UPON_INTAKE LIKE 'Neutered%' or SEX_UPON_INTAKE LIKE 'Spayed%', 'O', 'X') FROM ANIMAL_INS ORDER BY ANIMAL_ID ASC

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

1
SELECT ANIMAL_ID, ANIMAL_INS.NAME FROM ANIMAL_INS JOIN ANIMAL_OUTS USING(ANIMAL_ID) ORDER BY (ANIMAL_OUTS.DATETIME - ANIMAL_INS.DATETIME) DESC LIMIT 2

DATETIME에서 DATE로 형 변환

1
SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, '%Y-%m-%d') FROM ANIMAL_INS ORDER BY ANIMAL_ID ASC