문제

링크

풀이

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

SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE) AS count 
FROM ANIMAL_INS 
GROUP BY ANIMAL_TYPE 
ORDER BY ANIMAL_TYPE;

2. 루시와 엘라 찾기

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

3. 최솟값 구하기

SELECT MIN(DATETIME) FROM ANIMAL_INS;

4. 동명 동물 수 찾기

SELECT NAME, COUNT(NAME) AS COUNT 
FROM ANIMAL_INS
GROUP BY NAME
HAVING COUNT(NAME) > 1
ORDER BY NAME;

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

SELECT ANIMAL_ID, NAME 
FROM ANIMAL_INS 
WHERE NAME LIKE '%el%' AND ANIMAL_TYPE = 'Dog' 
ORDER BY NAME;

6. 동물 수 구하기

SELECT COUNT(ANIMAL_ID) AS count FROM ANIMAL_INS;

7. 입양 시각 구하기(1)

SELECT date_format(DATETIME, '%k') AS HOUR, COUNT(date_format(DATETIME, '%k')) AS COUNT
FROM ANIMAL_OUTS
GROUP BY HOUR
HAVING(HOUR > 8) AND (HOUR < 20)
ORDER BY HOUR * 1;

8. NULL 처리하기

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

9. 중성화 여부 파악하기

SELECT ANIMAL_ID, NAME, 
CASE 
    WHEN SEX_UPON_INTAKE LIKE 'Neutered%' THEN 'O'
    WHEN SEX_UPON_INTAKE LIKE 'Spayed%' THEN 'O'
    ELSE 'X'
END AS '중성화'
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

10. 중복 제거하기

SELECT COUNT(DISTINCT NAME) AS count FROM ANIMAL_INS;

11. DATETIME에서 DATE로 형 변환

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