SQL kit

SELECT

모든 레코드 조회하기

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 animal_id;

어린 동물 찾기

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;

상위 n개 레코드

SELECT name
FROM animal_ins
WHERE datetime = (
    SELECT min(datetime)
    FROM animal_ins);

max 와 min 은 그룹함수이므로 WHERE 에서 사용할 수 없다. 만약 사용하려면 서브쿼리를 활용해야 한다.

SUM, MAX, MIN

최댓값 구하기

SELECT max(datetime)
FROM animal_ins;

최솟값 구하기

SELECT min(datetime)
FROM animal_ins;

동물 수 구하기

SELECT count(*)
FROM Animal_ins;

중복 제거하기

SELECT count(DISTINCT name) 
FROM animal_ins;

DISTINCT 함수

1. SELECT DISTINCT
2. SELECT count(DISTINCT)

GROUP BY

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

SELECT animal_type
    ,count(*)
FROM animal_ins
GROUP BY animal_type
ORDER BY animal_type;

동명 동물 수 찾기

SELECT name
    , count(*)
FROM animal_ins
GROUP BY name
HAVING count(*) >= 2
    AND name IS NOT NULL
ORDER BY name;

GROUP BY 는 null 을 제외하지 않는다.

입양 시각 구하기(1)

보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다. 09:00부터 19:59까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요. 이때 결과는 시간대 순으로 정렬해야 합니다.

HOUR COUNT
----------
09    1
10    2
11    13
12    10
13    14
14    9
15    7
16    10
17    12
18    16
19    2

코드

SELECT to_char(datetime, 'HH24') AS hour
    , count(*)
FROM animal_outs
GROUP BY to_char(datetime, 'HH24')
HAVING to_char(datetime, 'HH24') BETWEEN 9 AND 19
ORDER BY hour;

출력을 09 가 아닌 9 로 하고싶다면 SELECT 에서 0 을 빼면 된다.

FMHH24 를 해버리면 정렬이 이상하게 되버린다.

서브쿼리 활용 ALIAS

SELECT hour, count(*)
FROM (
    SELECT to_char(datetime, 'HH24') AS hour
    FROM animal_outs)
GROUP BY hour
HAVING hour BETWEEN 9 AND 19
ORDER BY hour;

[입양 시각 구하기(2)]

H 테이블

HOUR
----
0
1
2
3
:
:
21
22
23

A 테이블

HOUR   COUNT
--------------
09        1
10        2
11        13
12        10
13        14
14        9
15        7
16        10
17        12
18        16
19        2

WITH h AS (
    SELECT level - 1 AS hour
    FROM dual
    CONNECT By level <= 24
)
, a AS (
    SELECT to_char(datetime, 'HH24') AS hour
        , count(*) AS count
    FROM animal_outs
    GROUP BY to_char(datetime, 'HH24')
)

SELECT hour, nvl(count, 0)
FROM h LEFT JOIN a USING (hour)
ORDER BY hour;

IS NULL

이름이 없는 동물의 아이디

SELECT animal_id
FROM animal_ins
WHERE name IS NULL
ORDER BY animal_id;

이름이 있는 동물의 아이디

aSELECT animal_id
FROM animal_ins
WHERE name IS NOT NULL
ORDER BY animal_id;

NULL 처리하기

SELECT animal_type, nvl(name, 'No name'), sex_upon_intake
FROM animal_ins
ORDER BY animal_id;

JOIN

없어진 기록 찾기

animal_id    name
-----------------
A349480        Daisy
A349733        Allie
A349990        Spice
A362137        *Darcy

SELECT o.animal_id, o.name
FROM animal_ins i RIGHT JOIN animal_outs o 
    ON o.animal_id = i.animal_id
WHERE i.datetime IS NULL
ORDER BY o.animal_id;

있었는데요 없었습니다

관리자의 실수로 일부 동물의 입양일이 잘못 입력되었습니다. 보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 시작일이 빠른 순으로 조회해야합니다.

animal_id    name
-----------------
A362383    *Morado
A381217    Cherokee

SELECT i.animal_id, i.name
FROM animal_ins i, animal_outs o
WHERE o.datetime - i.datetime < 0
    AND i.animal_id = o.animal_id
ORDER BY i.datetime;

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

name    datetime
----------------------------
Shelly    2015-01-29 15:01:00
Jackie    2016-01-03 16:25:00
Benji    2016-04-19 13:28:00

SELECT name, datetime
FROM (
    SELECT i.name, i.datetime
        , rank() OVER(ORDER BY i.datetime) AS rank
    FROM animal_ins i LEFT JOIN animal_outs o
        ON i.animal_id = o.animal_id
    WHERE o.animal_id IS NULL
) 
WHERE rank <= 3;

보호소에서 중성화한 동물

animal_id    animal_type        name
-----------------------------------
A382192        Dog            Maxwell 2
A410330        Dog            Chewy

SELECT i.animal_id, i.animal_type, i.name
FROM animal_ins i JOIN animal_outs o
    ON i.animal_id = o.animal_id
WHERE i.sex_upon_intake LIKE 'Intact%'
    AND (o.sex_upon_outcome LIKE 'Spayed%'
        OR o.sex_upon_outcome LIKE 'Neutered%')
ORDER BY i.animal_id;

String, Date

루시와 엘라 찾기

SELECT animal_id, name, sex_upon_intake
FROM animal_ins
WHERE name IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
ORDER BY animal_id;

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

SELECT animal_id, name
FROM animal_ins
WHERE regexp_like(name, 'EL', 'i')
    AND animal_type = 'Dog'
ORDER BY name;

중성화 여부 파악하기

보호소의 동물이 중성화되었는지 아닌지 파악하려 합니다. 중성화된 동물은 SEX_UPON_INTAKE 컬럼에 'Neutered' 또는 'Spayed'라는 단어가 들어있습니다. 동물의 아이디와 이름, 중성화 여부를 아이디 순으로 조회하는 SQL문을 작성해주세요. 이때 중성화가 되어있다면 'O', 아니라면 'X'라고 표시해주세요.

ANIMAL_ID    NAME    중성화
--------------------------
A355753        Elijah        O
A373219        Ella        O
A382192        Maxwell 2    X

SELECT animal_id, name
    , decode(regexp_replace(sex_upon_intake, ' .*')
    , 'Neutered', 'O'
    , 'Spayed', 'O'
    , 'X') AS 중성화
FROM animal_ins
ORDER BY animal_id;

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

ANIMAL_OUTS 테이블의 ANIMAL_ID는 ANIMAL_INS의 ANIMAL_ID의 외래 키입니다.

입양을 간 동물 중, 보호 기간이 가장 길었던 동물 두 마리의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 이때 결과는 보호 기간이 긴 순으로 조회해야 합니다.

animal_id    name
-----------------
A362707        Girly Girl
A370507        Emily

SELECT animal_id, name
FROM (SELECT o.animal_id, o.name
        , rank() OVER(ORDER BY o.datetime - i.datetime DESC) AS rank 
     FROM animal_outs o, animal_ins i   
     WHERE o.animal_id = i.animal_id)
WHERE rank <= 2;

DATETIME 에서 DATE 로 형변환

animal_id    name    날짜
---------------------------
A349996    Sugar    2018-01-22
A350276    Jewel    2017-08-13
A350375    Meo    2017-03-06
A352555    Harley    2014-08-08
A352713    Gia    2017-04-13

SELECT animal_id, name
    , to_char(datetime, 'YYYY-MM-DD') AS 날짜
FROM animal_ins
ORDER BY animal_id;

DATETIME 과 DATE 의 차이점?