TIL_76. 프로그래머스 SQL 코딩 테스트

소고기는레어·2021년 2월 24일

TIL sql

TIL 📝

목록 보기

78/96

2021. 02. 24 수요일

프로그래머스에서 SQL 코딩 테스트 문제를 풀어보았다.

요즘 파이썬과 태블로에 집중하다보니 SQL에 소홀해진 것 같아서 배운 내용을 잊지 않기 위해 프로그래머스에서 제공하는 SQL 코딩 테스트 문제를 풀어보았다.
프로그래머스에서 제공하는 SQL 코딩 문제는 ANIMAL_INS 와 ANIMAL_OUTS라는 동물 보호소 데이터 테이블을 사용한다.
ANIMAL_ID, ANIMAL_TYPE, DATETIME, NAME, SEX_UPON_OUTCOME 는 각각
동물의 아이디, 생물 종, 보호 시작일(OUTS의 경우 입양일), 이름, 성별 및 중성화 여부를 나타낸다.

SELECT

모든 레코드 조회하기

모든 데이터를 ID 순으로 조회하기

SELECT
    *
FROM
    ANIMAL_INS
ORDER BY
    ANIMAL_ID
;

역순 정렬하기

모든 동물의 이름과 보호 시작일을 ID 역순으로 조회하기

SELECT
    NAME,
    DATETIME
FROM
    ANIMAL_INS
ORDER BY
    ANIMAL_ID DESC
;

아픈 동물 찾기

아픈 동물의 ID와 이름을 ID 순으로 조회하기
(INTAKE_CONDITION이 Sick 인 경우)

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

어린 동물 찾기

젊은 동물의 ID와 이름을 ID순으로 조회하기
(INTAKE_CONDITION이 Aged가 아닌 경우)

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

동물의 아이디와 이름

모든 동물의 ID와 이름을 ID 순으로 조회하기

SELECT
    ANIMAL_ID,
    NAME
FROM
    ANIMAL_INS
ORDER BY
    ANIMAL_ID
;

여러 기준으로 정렬하기

모든 동물의 ID와 이름, 보호 시작일을 이름 순으로 조회하기
(이름이 같을 경우 보호를 나중에 시작한 동물 먼저 불러오기)

SELECT
    ANIMAL_ID,
    NAME,
    DATETIME
FROM
    ANIMAL_INS
ORDER BY
    NAME,
    DATETIME DESC
;

상위 n개 레코드

동물 보호소에 가장 먼저 들어온 동물의 이름을 조회하기

WITH sub AS
    (
    SELECT
        MIN(DATETIME) AS min_datetime
    FROM
        ANIMAL_INS
    )
SELECT
    NAME
FROM
    ANIMAL_INS as A
RIGHT JOIN
    sub
ON
    sub.min_datetime = A.DATETIME
;

혹은

SELECT
	NAME
FROM
	ANIMAL_INS
ORDER BY
	DATE
LIMIT
	1
;

SUM, MAX, MIN

최댓값 구하기

가장 최근에 들어온 동물이 언제 들어왔는지 조회하기

SELECT
    MAX(DATETIME)
FROM
    ANIMAL_INS
;

최솟값 구하기

가장 먼저 들어온 동물은 언제 들어왔는지 조회하기

SELECT
    MIN(DATETIME)
FROM
    ANIMAL_INS
;

동물 수 구하기

동물 보호소에 동물이 몇 마리 들어왔는지 조회하기

SELECT
    COUNT(*)
FROM
    ANIMAL_INS
;

중복 제거하기

보호소에 있는 동물의 이름이 몇가지인지 조회하기
(NAME = NULL 은 조회하지 않으며 중복일 경우 한 번만 집계)

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

GROUP BY

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

동물 보호소의 동물 중 고양이와 강아지가 각각 몇 마리인지 조회하기
(고양이를 강아지보다 먼저 조회)

SELECT
    ANIMAL_TYPE,
    COUNT(*) AS count
FROM
    ANIMAL_INS
WHERE
    ANIMAL_TYPE IN ('Dog', 'Cat')
GROUP BY
    ANIMAL_TYPE
ORDER BY
    1
;

동명 동물 수 찾기

동물 보호소에 들어온 동물 이름 중 두 번 이상 쓰인 이름과 해당 이름이 쓰인 횟수를 이름 순으로 조회하기
(NAME = NULL 은 제외)

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

입양 시각 구하기(1)

09:00 부터 19:59까지 각 시간대별로 입양이 몇 건 발생했는지 시간대 순으로 조회하기

SELECT
    EXTRACT(HOUR FROM DATETIME) AS HOUR,
    COUNT(*) AS COUNT
FROM
    ANIMAL_OUTS
GROUP BY   
    HOUR
HAVING
    HOUR BETWEEN 9 AND 19
ORDER BY
    1
;

입양 시각 구하기(2)

0시부터 23시까지 각 시간대별로 입양이 몇건이나 발생했는지 시간대 순으로 조회하기

SET @HOUR = -1;

WITH TIME AS
    (
    SELECT
        @HOUR := @HOUR + 1 AS HOUR,
        ANIMAL_ID
    FROM
        ANIMAL_OUTS
    )
    
SELECT
    TIME.HOUR AS HOUR,
    COUNT(OUTS.ANIMAL_ID) AS COUNT
FROM
    TIME
LEFT JOIN
    (
    SELECT
        EXTRACT(HOUR FROM DATETIME) AS HOUR,
        ANIMAL_ID
    FROM
        ANIMAL_OUTS
    ) AS OUTS
ON
    TIME.HOUR = OUTS.HOUR
WHERE
    TIME.HOUR BETWEEN 0 AND 23
GROUP BY
    HOUR

;

IS NULL

이름이 없는 동물의 아이디

이름이 없는 동물의 ID를 ID 오름차순으로 조회하기

SELECT
    ANIMAL_ID
FROM
    ANIMAL_INS
WHERE
    NAME IS NULL
ORDER BY
    ANIMAL_ID
;

이름이 있는 동물의 아이디

이름이 있는 동물의 ID를 ID 오름차순으로 조회하기

-- 코드를 입력하세요
SELECT
    ANIMAL_ID
FROM
    ANIMAL_INS
WHERE
    NAME IS NOT NULL
ORDER BY
    ANIMAL_ID
;

NULL 처리하기

동물의 생물 종, 이름, 성별 및 중성화 여부를 ID 순으로 조회하기
(이름이 NULL일 경우 'No name' 으로 불러오기)

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
;

혹은

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

JOIN

없어진 기록 찾기

입양을 간 기록은 있는데 보호소에 들어온 기록이 없는 동물의 ID와 이름을 ID 순으로 조회하기

SELECT
	ANIMAL_ID,
    O.NAME
FROM
	ANIMAL_OUTS AS O
LEFT JOIN
	ANIMAL_INS AS I
USING(ANIMAL_ID)
WHERE
	I.ANIMAL_ID IS NULL
;

있었는데요 없었습니다.

보호 시작일보다 입양일이 더 빠른 동물의 ID와 이름을 보호 시작일이 빠른 순으로 조회하기

SELECT
	ANIMAL_ID,
    O.NAME
FROM
	ANIMAL_OUTS AS O
INNER JOIN
	ANIMAL_INS AS I
USING(ANIMAL_ID)
WHERE
	O.DATETIME < I.DATETIME
ORDER BY
	I.DATETIME
;

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

아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 보호 시작일 순으로 조회하기

SELECT
    NAME,
    DATETIME
FROM
    ANIMAL_INS
WHERE
    ANIMAL_ID NOT IN
    (SELECT
        ANIMAL_ID
    FROM
        ANIMAL_OUTS)
ORDER BY
    DATETIME
LIMIT
    3
;

혹은

SELECT
    I.NAME,
    I.DATETIME
FROM
    ANIMAL_INS AS I
LEFT JOIN
    ANIMAL_OUTS AS O
USING(ANIMAL_ID)
WHERE
    O.ANIMAL_ID IS NULL
ORDER BY
    I.DATETIME
LIMIT
    3
;

보호소에서 중성화한 동물

보호소에 들어올 당시는 중성화 되지 않았지만, 입양갈 때는 중성화 된 동물의 ID와 생물 종, 이름을 ID 순으로 조회하기
(중성화 아닌 경우 Intact, 중성화일 경우 Spayed 또는 Neutered)

SELECT
	O.ANIMAL_ID,
    O.ANIMAL_TYPE,
    O.NAME
FROM
	ANIMAL_OUTS AS O
INNER JOIN
	ANIMAL_INS AS I
USING(ANIMAL_ID)
WHERE
	SEX_UPON_INTAKE LIKE ('Intact%')
    AND SEX_UPON_OUTCOME NOT LIKE ('Intact%')
ORDER BY
	O.ANIMAL_ID
;

String, Date

루시와 엘라 찾기

이름이 Lucy, Ella, Pickle, Rogan, Sabrina, Mitty인 동물의 ID와 이름, 성별 및 중성화 여부를 ID 순으로 조회하기

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

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

이름에 'el' 이 들어가는 개의 아이디와 이름을 이름 순으로 조회하기
(대소문자 구분 x)

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

중성화 여부 파악하기

동물의 ID와 이름, 중성화 여부를 O,X 로 표시하여 ID 순으로 조회하기

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로 형 변환

동물의 ID, 이름, 보호 시작 날짜(시각은 제외)를 ID 순으로 조회하기

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

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

입양을 간 동물 중, 보호 기간이 가장 길었던 동물 두 마리의 ID와 이름을 보호기간이 긴 순으로 조회하기

SELECT
    OUTS.ANIMAL_ID,
    OUTS.NAME
FROM
    ANIMAL_OUTS AS OUTS
INNER JOIN
    ANIMAL_INS AS INS
USING(ANIMAL_ID)
ORDER BY
    OUTS.DATETIME - INS.DATETIME DESC 
    # DATEDIFF(OUTS.DATETIME, INS.DATETIME) DESC 로 대체 가능
LIMIT
    2
;

소고기는레어

https://www.rarebeef.co.kr/

이전 포스트

TIL_75. Tableau 강의

다음 포스트

TIL_76. 프로그래머스 SQL 코딩 테스트

TIL 📝

2021. 02. 24 수요일

프로그래머스에서 SQL 코딩 테스트 문제를 풀어보았다.

SELECT

모든 레코드 조회하기

역순 정렬하기

아픈 동물 찾기

어린 동물 찾기

동물의 아이디와 이름

여러 기준으로 정렬하기

상위 n개 레코드

SUM, MAX, MIN

최댓값 구하기

최솟값 구하기

동물 수 구하기

중복 제거하기

GROUP BY

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

동명 동물 수 찾기

입양 시각 구하기(1)

입양 시각 구하기(2)

IS NULL

이름이 없는 동물의 아이디

이름이 있는 동물의 아이디

NULL 처리하기

JOIN

없어진 기록 찾기

있었는데요 없었습니다.

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

보호소에서 중성화한 동물

String, Date

루시와 엘라 찾기

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

중성화 여부 파악하기

DATETIME에서 DATE로 형 변환

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

TIL_75. Tableau 강의

TIL.77_Intermediate Python (1)

0개의 댓글