프로그래머스에서 제공하는 SQL 코딩 문제는 ANIMAL_INS 와 ANIMAL_OUTS라는 동물 보호소 데이터 테이블을 사용한다.
ANIMAL_ID
, ANIMAL_TYPE
, DATETIME
, NAME
, SEX_UPON_OUTCOME
는 각각
동물의 아이디, 생물 종, 보호 시작일(OUTS의 경우 입양일), 이름, 성별 및 중성화 여부를 나타낸다.
제목이 파란색으로 강조된 문제는 풀면서 개인적으로 흥미롭거나 재밌었다고 느낀 문제이다.
SELECT
*
FROM
ANIMAL_INS
ORDER BY
ANIMAL_ID
;
SELECT
NAME,
DATETIME
FROM
ANIMAL_INS
ORDER BY
ANIMAL_ID DESC
;
SELECT
ANIMAL_ID,
NAME
FROM
ANIMAL_INS
WHERE
INTAKE_CONDITION = 'Sick'
ORDER BY
1
;
SELECT
ANIMAL_ID,
NAME
FROM
ANIMAL_INS
WHERE
INTAKE_CONDITION != 'Aged'
ORDER BY
ANIMAL_ID
;
SELECT
ANIMAL_ID,
NAME
FROM
ANIMAL_INS
ORDER BY
ANIMAL_ID
;
SELECT
ANIMAL_ID,
NAME,
DATETIME
FROM
ANIMAL_INS
ORDER BY
NAME,
DATETIME DESC
;
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
;
SELECT
MAX(DATETIME)
FROM
ANIMAL_INS
;
SELECT
MIN(DATETIME)
FROM
ANIMAL_INS
;
SELECT
COUNT(*)
FROM
ANIMAL_INS
;
SELECT
COUNT(DISTINCT NAME)
FROM
ANIMAL_INS
WHERE
NAME IS NOT NULL
;
SELECT
ANIMAL_TYPE,
COUNT(*) AS count
FROM
ANIMAL_INS
WHERE
ANIMAL_TYPE IN ('Dog', 'Cat')
GROUP BY
ANIMAL_TYPE
ORDER BY
1
;
SELECT
NAME,
COUNT(*) AS name_count
FROM
ANIMAL_INS
WHERE
NAME IS NOT NULL
GROUP BY
NAME
HAVING
name_count >= 2
ORDER BY
NAME
;
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
;
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
;
SELECT
ANIMAL_ID
FROM
ANIMAL_INS
WHERE
NAME IS NULL
ORDER BY
ANIMAL_ID
;
-- 코드를 입력하세요
SELECT
ANIMAL_ID
FROM
ANIMAL_INS
WHERE
NAME IS NOT NULL
ORDER BY
ANIMAL_ID
;
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
;
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
;
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
;
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
;
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
;
SELECT
ANIMAL_ID,
NAME,
SEX_UPON_INTAKE
FROM
ANIMAL_INS
WHERE
NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
ORDER BY
ANIMAL_ID
;
SELECT
ANIMAL_ID,
NAME
FROM
ANIMAL_INS
WHERE
ANIMAL_TYPE = 'Dog'
AND NAME LIKE ('%el%')
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
;
SELECT
ANIMAL_ID,
NAME,
DATE_FORMAT(DATETIME, '%Y-%m-%d')
FROM
ANIMAL_INS
ORDER BY
ANIMAL_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
;