Database

프로그래머스 SQL 1~3 - SELECT, SUM, MAX, MIN, GROUP BY

philo0407 2021. 3. 22. 23:58

SELECT

 

모든 레코드 조회하기
SELECT *
FROM ANIMAL_INS
ORDER BY ANIMAL_ID ASC;

 

역순 정렬하기
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 ASC;

 

어린 동물 찾기
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE INTAKE_CONDITION <> 'Aged'
ORDER BY ANIMAL_ID ASC;

 

동물의 아이디와 이름
SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

 

 

여러 기준으로 정렬하기
SELECT ANIMAL_ID, NAME, DATETIME
FROM ANIMAL_INS
ORDER BY NAME ASC, DATETIME DESC;

 

 

상위 n개 레코드
WITH T AS (
SELECT ROWNUM, NAME, DATETIME FROM ANIMAL_INS
ORDER BY DATETIME ASC
)
SELECT NAME FROM T
WHERE ROWNUM = 1;

 

참고로 이렇게 하면 정답이 되질 않는다 ㅋㅋ

 

SUM, MAX, MIN

 

최댓값 구하기
SELECT MAX(DATETIME) FROM ANIMAL_INS
ORDER BY DATETIME DESC;

 

최솟값 구하기
SELECT MIN(DATETIME) 
FROM ANIMAL_INS
ORDER BY DATETIME;

 

동물 수 구하기
SELECT COUNT(*) AS "count" FROM ANIMAL_INS;

 

중복 제거하기
SELECT COUNT(DISTINCT NAME) FROM ANIMAL_INS WHERE NAME IS NOT NULL;

 

GROUP BY

 

고양이와 개는 몇 마리 있을까
SELECT ANIMAL_TYPE , COUNT(1) "count"
FROM ANIMAL_INS 
GROUP BY ANIMAL_TYPE
ORDER BY DECODE(ANIMAL_TYPE, 'Cat', 1, 'Doc', 2, 3);

 

동명 동물 수 찾기
SELECT NAME, COUNT(1) AS "COUNT"
FROM ANIMAL_INS
GROUP BY NAME
HAVING COUNT(1) > 1 AND NAME IS NOT NULL
ORDER BY NAME;

 

 

 

입양 시각 구하기(1)
WITH HOUR_TABLE AS (
	SELECT TO_NUMBER(TO_CHAR(DATETIME, 'HH24')) AS HH FROM ANIMAL_OUTS
)
SELECT HH, COUNT(1) 
FROM HOUR_TABLE
GROUP BY HH
ORDER BY HH
WHERE HH BETWEEN 9 AND 20;

 

 

어떤 명제가 참인지를 테스트하는 방법..

아래 처럼 WHERE 조건에 문장을 건다.

SELECT 'TRUE' FROM DUAL
WHERE TO_NUMBER('1') = 1;

 

입양 시각 구하기(1)
WITH HOUR_TABLE AS (
	SELECT TO_CHAR(DATETIME, 'HH24') "HH" FROM ANIMAL_OUTS
)
SELECT HH, COUNT(1) AS "COUNT"
FROM HOUR_TABLE
GROUP BY HH
HAVING HH BETWEEN 9 AND 20
ORDER BY HH;

 

입양 시각 구하기(2)
WITH REALT AS ( 
    SELECT TO_CHAR(DATETIME, 'HH24') "HOUR" FROM ANIMAL_OUTS
),
ENUM_HOUR AS (
    SELECT 0 "HOUR" FROM DUAL
    UNION
    SELECT 1 FROM DUAL
    UNION
    SELECT 2 FROM DUAL
    UNION
    SELECT 3 FROM DUAL
    UNION
    SELECT 4 FROM DUAL
    UNION
    SELECT 5 FROM DUAL
    UNION
    SELECT 6 FROM DUAL
    UNION
    SELECT 7 FROM DUAL
    UNION
    SELECT 8 FROM DUAL
    UNION
    SELECT 9 FROM DUAL
    UNION
    SELECT 10 FROM DUAL
    UNION
    SELECT 11 FROM DUAL
    UNION
    SELECT 12 FROM DUAL
    UNION
    SELECT 13 FROM DUAL
    UNION
    SELECT 14 FROM DUAL
    UNION
    SELECT 15 FROM DUAL
    UNION
    SELECT 16 FROM DUAL
    UNION
    SELECT 17 FROM DUAL
    UNION
    SELECT 18 FROM DUAL
    UNION
    SELECT 19 FROM DUAL
    UNION
    SELECT 20 FROM DUAL
    UNION
    SELECT 21 FROM DUAL
    UNION
    SELECT 22 FROM DUAL
    UNION
    SELECT 23 FROM DUAL
)
SELECT E.HOUR, COUNT(R.HOUR) "COUNT" 
FROM REALT R
    RIGHT OUTER JOIN ENUM_HOUR E
    ON R.HOUR = E.HOUR
GROUP BY E.HOUR
ORDER BY HOUR;

정말 운이 좋았다.

하지만.. 원래 저렇게 푸나? ㅋㅋ

 

 

계층 형 쿼리를 이용하는 방법도 있는 것 같다. (날짜 레코드 만들기 CONNECT BY 등SELECT

 

 

모든 레코드 조회하기

SELECT *

FROM ANIMAL_INS

ORDER BY ANIMAL_ID ASC;

 

 

역순 정렬하기

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

 

 

어린 동물 찾기

SELECT ANIMAL_ID, NAME

FROM ANIMAL_INS

WHERE INTAKE_CONDITION <> 'Aged'

ORDER BY ANIMAL_ID ASC;

 

 

동물의 아이디와 이름

SELECT ANIMAL_ID, NAME

FROM ANIMAL_INS

ORDER BY ANIMAL_ID;

 

 

 

 

여러 기준으로 정렬하기

SELECT ANIMAL_ID, NAME, DATETIME

FROM ANIMAL_INS

ORDER BY NAME ASC, DATETIME DESC;

 

 

 

 

상위 n개 레코드

WITH T AS (

SELECT ROWNUM, NAME, DATETIME FROM ANIMAL_INS

ORDER BY DATETIME ASC

)

SELECT NAME FROM T

WHERE ROWNUM = 1;



 

 



참고로 이렇게 하면 정답이 되질 않는다 ㅋㅋ

 

 

SUM, MAX, MIN

 

 

최댓값 구하기

SELECT MAX(DATETIME) FROM ANIMAL_INS

ORDER BY DATETIME DESC;

 

 

최솟값 구하기

SELECT MIN(DATETIME) 

FROM ANIMAL_INS

ORDER BY DATETIME;

 

 

동물 수 구하기

SELECT COUNT(*) AS "count" FROM ANIMAL_INS;

 

 

중복 제거하기

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

 

 

GROUP BY

 

 

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

SELECT ANIMAL_TYPE , COUNT(1) "count"

FROM ANIMAL_INS 

GROUP BY ANIMAL_TYPE

ORDER BY DECODE(ANIMAL_TYPE, 'Cat', 1, 'Doc', 2, 3);

 

 

동명 동물 수 찾기

SELECT NAME, COUNT(1) AS "COUNT"

FROM ANIMAL_INS

GROUP BY NAME

HAVING COUNT(1) > 1 AND NAME IS NOT NULL

ORDER BY NAME;

 

 

 

 

 

 

입양 시각 구하기(1)

WITH HOUR_TABLE AS (

 SELECT TO_NUMBER(TO_CHAR(DATETIME, 'HH24')) AS HH FROM ANIMAL_OUTS

)

SELECT HH, COUNT(1) 

FROM HOUR_TABLE

GROUP BY HH

ORDER BY HH

WHERE HH BETWEEN 9 AND 20;

 

 

 

 

어떤 명제가 참인지를 테스트하는 방법..

 

아래 처럼 WHERE 조건에 문장을 건다.

 

SELECT 'TRUE' FROM DUAL

WHERE TO_NUMBER('1') = 1;

 

 

입양 시각 구하기(1)

WITH HOUR_TABLE AS (

 SELECT TO_CHAR(DATETIME, 'HH24') "HH" FROM ANIMAL_OUTS

)

SELECT HH, COUNT(1) AS "COUNT"

FROM HOUR_TABLE

GROUP BY HH

HAVING HH BETWEEN 9 AND 20

ORDER BY HH;

 

 

입양 시각 구하기(2)

WITH REALT AS ( 

    SELECT TO_CHAR(DATETIME, 'HH24') "HOUR" FROM ANIMAL_OUTS

),

ENUM_HOUR AS (

    SELECT 0 "HOUR" FROM DUAL

    UNION

    SELECT 1 FROM DUAL

    UNION

    SELECT 2 FROM DUAL

    UNION

    SELECT 3 FROM DUAL

    UNION

    SELECT 4 FROM DUAL

    UNION

    SELECT 5 FROM DUAL

    UNION

    SELECT 6 FROM DUAL

    UNION

    SELECT 7 FROM DUAL

    UNION

    SELECT 8 FROM DUAL

    UNION

    SELECT 9 FROM DUAL

    UNION

    SELECT 10 FROM DUAL

    UNION

    SELECT 11 FROM DUAL

    UNION

    SELECT 12 FROM DUAL

    UNION

    SELECT 13 FROM DUAL

    UNION

    SELECT 14 FROM DUAL

    UNION

    SELECT 15 FROM DUAL

    UNION

    SELECT 16 FROM DUAL

    UNION

    SELECT 17 FROM DUAL

    UNION

    SELECT 18 FROM DUAL

    UNION

    SELECT 19 FROM DUAL

    UNION

    SELECT 20 FROM DUAL

    UNION

    SELECT 21 FROM DUAL

    UNION

    SELECT 22 FROM DUAL

    UNION

    SELECT 23 FROM DUAL

)

SELECT E.HOUR, COUNT(R.HOUR) "COUNT" 

FROM REALT R

    RIGHT OUTER JOIN ENUM_HOUR E

    ON R.HOUR = E.HOUR

GROUP BY E.HOUR

ORDER BY HOUR;

정말 운이 좋았다.

 

하지만.. 원래 저렇게 푸나? ㅋㅋ

 

 

 

 

 

계층 형 쿼리를 이용하는 방법도 있는 것 같다. (날짜 레코드 만들기 CONNECT BY 등)

SELECT LEVEL - 1AS HOUR, 0 AS COUNT
FROM DUAL
CONNECT BY LEVEL<25

 

ORACLE 풀이 공유합니다 | 프로그래머스 (programmers.co.kr)