ORACLE_LEVEL_2

Programmers.co.kr 코딩테스트 연습

ORACLE Level 2 모든 문제

최소값 구하기

SELECT MIN(DATETIME)
FROM ANIMAL_INS;

-Sources :
https://programmers.co.kr/learn/courses/30/lessons/59038

동물 수 구하기

SELECT COUNT(*)
FROM ANIMAL_INS;

-Sources :
https://programmers.co.kr/learn/courses/30/lessons/59406

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

SELECT ANIMAL_TYPE, COUNT(ANIMAL_TYPE)
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE;

-Sources :
https://programmers.co.kr/learn/courses/30/lessons/59040

중복 제거하기

SELECT COUNT(DISTINCT NAME)
FROM ANIMAL_INS;

-Sources :
https://programmers.co.kr/learn/courses/30/lessons/59408

동명 동물 수 찾기

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

-Sources :
https://programmers.co.kr/learn/courses/30/lessons/59041

NULL 처리하기

SELECT ANIMAL_TYPE, NVL(NAME, 'No name'), SEX_UPON_INTAKE
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

-Sources :
https://programmers.co.kr/learn/courses/30/lessons/59410

입양 시각 구하기(1)

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

-Sources :
https://programmers.co.kr/learn/courses/30/lessons/59412

루시와 엘라 찾기

SELECT ANIMAL_ID, NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS
WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
ORDER BY ANIMAL_ID;

-Sources :
https://programmers.co.kr/learn/courses/30/lessons/59046

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

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE (NAME LIKE 'El%' OR name like '%el%')
AND ANIMAL_TYPE = 'Dog'
ORDER BY NAME;

-Sources :
https://programmers.co.kr/learn/courses/30/lessons/59047

중성화 여부 파악하기

SELECT ANIMAL_ID, NAME, DECODE(SUBSTR(SEX_UPON_INTAKE, 1, 3), 'Neu', 'O', 'Spa', 'O', 'Int', 'X') "중성화"
FROM ANIMAL_INS
order by animal_id;

-Sources :
https://programmers.co.kr/learn/courses/30/lessons/59409

DATETIME에서 DATE로 형 변환

SELECT ANIMAL_ID, NAME, TO_CHAR(DATETIME, 'YYYY-MM-DD')
FROM ANIMAL_INS
ORDER BY ANIMAL_ID;

-Sources :
https://programmers.co.kr/learn/courses/30/lessons/59414