SQL_5. SQL 문제 (프로그래머스)

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

Data Analyst 👨🏻‍💻

목록 보기
13/57
post-thumbnail

프로그래머스 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
;
profile
소고기는레어
https://www.rarebeef.co.kr/
이전 포스트

SQL_4. JOIN

다음 포스트

Rainbow Six Siege 매치 데이터 분석

0개의 댓글