프로그래머스 SQL 고득점 Kit 문제 모음 1

SELECT

모든 레코드 조회하기 : https://programmers.co.kr/learn/courses/30/lessons/59034

SELECT * FROM ANIMAL_INS ORDER BY ANIMAL_ID;

역순 정렬하기 : https://programmers.co.kr/learn/courses/30/lessons/59035

SELECT NAME, DATETIME FROM ANIMAL_INS ORDER BY ANIMAL_ID DESC;

아픈 동물 찾기 : https://programmers.co.kr/learn/courses/30/lessons/59036

SELECT animal_id, name 
    FROM animal_ins 
    WHERE intake_condition = 'sick' 
    ORDER BY animal_id;

어린 동물 찾기 : https://programmers.co.kr/learn/courses/30/lessons/59037

SELECT ANIMAL_ID, NAME
FROM   ANIMAL_INS
WHERE INTAKE_CONDITION <> 'Aged'
ORDER BY ANIMAL_ID;

동물의 아이디와 이름  : https://programmers.co.kr/learn/courses/30/lessons/59403   

SELECT ANIMAL_ID, NAME FROM ANIMAL_INS ORDER BY ANIMAL_ID;

여러 기준으로 정렬하기 https://programmers.co.kr/learn/courses/30/lessons/59404

SELECT ANIMAL_ID, NAME, DATETIME FROM ANIMAL_INS ORDER BY NAME, DATETIME DESC

상위 n개 레코드 : https://programmers.co.kr/learn/courses/30/lessons/59405

 

[MySQL]

SELECT NAME FROM ANIMAL_INS ORDER BY DATETIME ASC LIMIT 1

[Oracle]

SELECT NAME FROM
  (SELECT NAME FROM ANIMAL_INS ORDER BY DATETIME ASC ) T
WHERE ROWNUM <= 1

SUM, MAX, MIN

최대값 구하기 : https://programmers.co.kr/learn/courses/30/lessons/59415

SELECT DATETIME 
FROM ANIMAL_INS
WHERE DATETIME = (SELECT MAX(DATETIME) FROM ANIMAL_INS)

 최소값 구하기 : https://programmers.co.kr/learn/courses/30/lessons/59038

SELECT DATETIME
FROM ANIMAL_INS
WHERE DATETIME = (SELECT MIN(DATETIME) FROM ANIMAL_INS)

동물 수 구하기 : https://programmers.co.kr/learn/courses/30/lessons/59406

SELECT COUNT(1) FROM ANIMAL_INS;

중복 제거하기 : https://programmers.co.kr/learn/courses/30/lessons/59408

SELECT count(distinct name) from animal_ins;

GROUP BY

고양이와 개는 몇마리 있을까 : https://programmers.co.kr/learn/courses/30/lessons/59040

SELECT ANIMAL_TYPE, COUNT(1)
FROM ANIMAL_INS
WHERE ANIMAL_TYPE IN ('Cat','Dog')
GROUP BY ANIMAL_TYPE
order by ANIMAL_TYPE
;

동명 동물 수 찾기 : https://programmers.co.kr/learn/courses/30/lessons/59041

SELECT NAME, COUNT(NAME) AS COUNT
FROM ANIMAL_INS
GROUP BY NAME
HAVING COUNT > 1
ORDER BY NAME

입양시각 구하기 : https://programmers.co.kr/learn/courses/30/lessons/59412

[MySQL]

SELECT DATE_FORMAT(DATETIME, '%H') AS HOUR, COUNT(1) AS COUNT 
FROM ANIMAL_OUTS
WHERE DATE_FORMAT(DATETIME, '%H') BETWEEN '09' AND '19'
GROUP BY DATE_FORMAT(DATETIME, '%H') 
ORDER BY HOUR
;

[ORACLE]

SELECT TO_CHAR(DATETIME, 'HH24') AS HOUR, COUNT(1) AS COUNT 
FROM ANIMAL_OUTS
WHERE TO_CHAR(DATETIME, 'HH24') BETWEEN '09' AND '19'
GROUP BY TO_CHAR(DATETIME, 'HH24') 
ORDER BY HOUR
;

입양시각 구하기 2 : https://programmers.co.kr/learn/courses/30/lessons/59413

[ORACLE]

SELECT H.HOUR, NVL(T.COUNT, 0) AS COUNT
FROM        
    (SELECT LEVEL-1 AS HOUR 
     FROM DUAL 
     CONNECT BY LEVEL < 25 ) H
    LEFT JOIN
    (SELECT TO_NUMBER(TO_CHAR(DATETIME, 'HH24')) AS HOUR, COUNT(1) AS COUNT
     FROM ANIMAL_OUTS 
     GROUP BY TO_NUMBER(TO_CHAR(DATETIME, 'HH24')) ) T
    ON H.HOUR = T.HOUR
ORDER BY HOUR

;