-> CASE WHEN RAW LIKE '%법' THEN ' '||'1' ELSE '2' ||
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
무슨 서브쿼리를 써야할까?
1. 선수들 중 포지션이 미드필더인 선수들의 소속팀명 및 선수 정보 출력
2. 선수 정보와 해당 선수가 속한 팀의 평균 키 함께 출력
3. 평균키가 삼성 블루윙즈팀의 평균키보다 작은 팀의 이름과 해당 팀의 평균키 출력
1. 인라인뷰 (FROM절 서브쿼리)
2. 스칼라 서브쿼리 (SELECT절 서브쿼리)
3. HAVING 서브쿼리
1. SELECT절 서브쿼리 -> 스칼라 서브쿼리 (1로우 1칼럼)
2. FROM절 서브쿼리 -> 인라인뷰 (임시로 생성되는 동적 뷰로 조인과 같은기능)
SELECT PLAYER_NAME 선수명, POSITION 포지션, BACK_NO 백넘버, HEIGHT 키
FROM (SELECT PLAYER_NAME, POSITION, BACK_NO, HEGIHT)
FROM PLAYER
WHERE HEIGHT IS NOT NULL
ORDER BY HEIGHT DESC)
WHERE ROWNUM<=5;
-> 같은 키인 선수가 존재해도 5명이 넘어가면 미출력 -> RANK() 함수 이용
3. HAVING 서브쿼리 -> 그룹핑된 결과에 대한 부가적인 조건
SELECT P.TEAM_ID 팀코드, T.TEAM_NAME 팀명, AVG(P.HEIGHT) 평균키
FROM PLAYER P, TEAM T
WHERE P.TEAM_ID = T.TEAM_ID
GROUP BY P.TEAM_ID, T.TEAM_NAME
HAVING AVG(P.HEGIHT) < (SELECT AVG(HEIGHT)
FROM PLAYER
WHERE TEAM_ID='K02');
1. 고양이와 개는 몇 마리 있을까
-- 코드를 입력하세요
SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE)
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE ASC
2. 동명 동물 수 찾기
-- 코드를 입력하세요
SELECT NAME, COUNT(NAME)
FROM ANIMAL_INS
GROUP BY NAME
HAVING COUNT(NAME)>=2
ORDER BY NAME
4. 입양 시각 구하기 (1)
(1) 인라인뷰에서 처리 후, 해당 조건의 출력만 수행
SELECT hour, COUNT(*)
FROM
(
SELECT TO_NUMBER(TO_CHAR(datetime,'HH24')) as hour
FROM ANIMAL_OUTS
)
WHERE hour BETWEEN 9 AND 19
GROUP BY hour
ORDER BY hour;
(2) SELECT문이 가장 마지막으로 처리하므로, ALIAS사용 못해서 각각 시간변환 필요
SELECT to_char(datetime, 'HH24') as hour, count(*)
FROM ANIMAL_OUTS
where to_char(datetime, 'HH24') between 09 and 19
group by to_char(datetime, 'HH24')
order by hour
5. 입양 시각 구하기 (2)
보호소에서는 몇 시에 입양이 가장 활발하게 일어나는지 알아보려 합니다.
0시부터 23시까지, 각 시간대별로 입양이 몇 건이나 발생했는지 조회하는 SQL문을 작성해주세요.
이때 결과는 시간대 순으로 정렬
-> 계층형 쿼리 필요 [0시부터 23시를 만들어야 한다]
HOUR
COUNT
0
0
1
0
2
0
3
0
4
0
5
0
6
0
7
3
8
1
9
1
10
2
11
13
12
10
13
14
14
9
15
7
16
10
17
12
18
16
19
2
20
0
21
0
22
0
23
0
정답
SELECT HOUR, COUNT(*)-1 COUNT
FROM
(
(SELECT TO_NUMBER(TO_CHAR(DATETIME, 'HH24')) HOUR FROM ANIMAL_OUTS)
UNION ALL
(SELECT LEVEL-1 FROM DUAL CONNECT BY LEVEL <=24)
)
GROUP BY HOUR
ORDER BY HOUR
(0) 계층형쿼리 + 외부조인
SELECT A.HOUR, COUNT(B.DATETIME) AS COUNT
FROM (SELECT LEVEL-1 AS HOUR
FROM DUAL
CONNECT BY LEVEL <=24) A
LEFT JOIN
ANIMAL_OUTS B
ON A.HOUR = TO_NUMBER(TO_CHAR(B.DATETIME,'HH24'))
GROUP BY A.HOUR
ORDER BY A.HOUR
다른 풀이들
(1) WITH문으로 계층형쿼리를 만들고, TO_CHAR로 시간을 가져온다.
WITH A AS(
SELECT LEVEL-1 AS HOUR
FROM DUAL
CONNECT BY LEVEL <= 24
),
B AS(
SELECT TO_CHAR(DATETIME, 'HH24') AS HOUR
FROM ANIMAL_OUTS
)
SELECT A.HOUR AS HOUR, COUNT(B.HOUR) AS COUNT
FROM A,B
WHERE A.HOUR = B.HOUR(+)
GROUP BY A.HOUR
ORDER BY A.HOUR
(2) 가상테이블 이용
SELECT T.HOUR,
(SELECT COUNT(*)
FROM ANIMAL_OUTS
WHERE T.HOUR = TO_CHAR(DATETIME, 'HH24'))AS COUNT
FROM
(SELECT LEVEL-1 AS HOUR
FROM DUAL
CONNECT BY LEVEL <= 24)T
(3) DECODE 이용
SELECT LVL,
SUM(DECODE(SUBSTR(COUNT,0,2),LVL,SUBSTR(COUNT,3),0)) COUNT
FROM
(SELECT TO_CHAR(DATETIME,'HH24')||COUNT(*) COUNT
FROM ANIMAL_OUTS
GROUP BY TO_CHAR(DATETIME,'HH24')) A,
(SELECT LEVEL-1 LVL
FROM DUAL
CONNECT BY LEVEL <= 24) B
GROUP BY LVL
ORDER BY LVL;
(4) WITH문과 외부 조인
with tmp as (
select level-1 as hour
from dual
connect by level < 25
)
select a.hour
, nvl(b.count, 0)
from tmp a
left outer join
(
select to_char(datetime, 'HH24') as hour
, count(*) as count
from animal_outs
group by to_char(datetime, 'HH24')
) b
on a.hour = b.hour
order by a.hour
;
(5) CASE문 이용
SELECT VT.HOUR, CASE WHEN CT.COUNT IS NULL THEN 0 WHEN CT.COUNT IS NOT NULL THEN CT.COUNT END COUNT
FROM (--카운팅
SELECT TO_CHAR(DATETIME, 'HH24') HOUR, COUNT(TO_CHAR(DATETIME, 'HH24')) COUNT
FROM ANIMAL_OUTS
GROUP BY TO_CHAR(DATETIME, 'HH24')
) CT,
(--가상테이블
SELECT LEVEL-1 HOUR
FROM DUAL
CONNECT BY LEVEL <= 24
) VT
WHERE VT.HOUR = CT.HOUR(+)
ORDER BY VT.HOUR;
