[프로그래머스] SQL LEVEL 2 전체 문제 /Oracle /오라클

 

 

 

 

🚩최솟값 구하기

SELECT MIN(DATETIME)
    FROM ANIMAL_INS

🚩동물 수 구하기

SELECT COUNT(ANIMAL_ID)
    FROM ANIMAL_INS

🚩중복 제거하기

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

🚩NULL 처리하기

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

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

SELECT ANIMAL_TYPE, COUNT(*) AS count
FROM ANIMAL_INS
GROUP BY ANIMAL_TYPE
ORDER BY ANIMAL_TYPE

🚩동명 동물 수 찾기

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

🚩입양 시각 구하기

SELECT HOUR, COUNT(*) COUNT
FROM (SELECT TO_CHAR(DATETIME, 'HH24') HOUR
     FROM ANIMAL_OUTS)
WHERE HOUR >= 9 AND HOUR < 20
GROUP BY HOUR
ORDER BY HOUR

🚩루시와 엘라 찾기

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 (NAME LIKE '%El%' OR NAME LIKE '%el%') AND ANIMAL_TYPE = 'Dog'
ORDER BY NAME

🚩중성화 여부 파악하기

SELECT ANIMAL_ID, NAME,
CASE WHEN SEX_UPON_INTAKE LIKE 'Spayed%' THEN 'O'
WHEN SEX_UPON_INTAKE LIKE 'Neutered%' THEN 'O'
ELSE 'X'
END
AS 중성화
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

🚩DATETIME에서 DATE로 형 변환

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