[코딩테스트] 프로그래머스 SQL String Date 11 ~ 19

제가 공부한 내용을 정리하는 블로그입니다.
아직 많이 부족하고 배울게 너무나도 많습니다. 틀린내용이 있으면 언제나 가감없이 말씀해주시면 감사하겠습니다😁
Programmers SQL 고득점 Kit(String, Date)입니다.

루시와 엘라 찾기

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

 

• 이름이 'Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty'인 동물을 조회하기 위해 조건을 걸어줍니다.
  WHERE NAME IN ('Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty')
• 동물 ID를 기준으로 정렬합니다.

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

SELECT 
    ANIMAL_ID, 
    NAME
FROM ANIMAL_INS
WHERE UPPER(NAME) LIKE '%EL%' AND ANIMAL_TYPE = 'Dog'
ORDER BY NAME;

• 이름에 EL이 들어가는 개를 찾기 위해 UPPER(또는 LOWER)를 사용하여 Like절을 통해 조회합니다.
  WHERE UPPER(NAME) LIKE '%EL%' AND ANIMAL_TYPE = 'Dog'
• 이름을 통해 정렬합니다.

중성화 여부 파악하기

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

• 중성화 여부를 위한 CASE 절을 이용합니다.
  CASE
          WHEN SEX_UPON_INTAKE LIKE 'Neutered%' Then 'O'
          WHEN SEX_UPON_INTAKE LIKE 'Spayed%' Then 'O'
          WHEN SEX_UPON_INTAKE LIKE 'Intact%' THEN 'X'
      END AS "중성화"
• 동물 ID를 통해 정렬합니다.

오랜기간 보호한 동물(2)

SELECT ANIMAL_ID, NAME
FROM (
    SELECT
        INS.ANIMAL_ID,
        INS.NAME,
        (OUTS.DATETIME - INS.DATETIME) AS PROTECTION_DAYS
    FROM
        ANIMAL_INS INS
        JOIN ANIMAL_OUTS OUTS ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
    ORDER BY
        PROTECTION_DAYS DESC
)
WHERE ROWNUM <= 2;

• 입양간 동물을 구하기 위해 JOIN 합니다.
  FROM ANIMAL_INS INS JOIN ANIMAL_OUTS OUTS ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
• 보호기간을 구하기 위해 PROTECTION_DAYS 컬럼을 생성하고 해당 컬럼을 통해 내림차순 정렬합니다.
  (OUTS.DATETIME - INS.DATETIME) AS PROTECTION_DAYS
• 상위 두개의 레코드를 조회하기 위해 WHERE절을 이용합니다.
  WHERE ROWNUM <= 2;

카테고리 별 상품 개수 구하기

SELECT 
    SUBSTR(PRODUCT_CODE, 0, 2) AS CATEGORY, 
    COUNT(*)
FROM PRODUCT
GROUP BY SUBSTR(PRODUCT_CODE, 0, 2)
ORDER BY CATEGORY

• 상품 카테고리 앞 두자리를 기준으로 그룹화를 진행합니다.
  GROUP BY SUBSTR(PRODUCT_CODE, 0, 2)
• 카테고리를 기준으로 오름차순 정렬합니다.

DATETIME에서 DATE로 형변환

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

• DATETIME을 YYYY-MM-DD로 형변환합니다.
• 동물 ID를 기준으로 오름차순 정렬합니다.

연도 별 평균 미세먼지 농도 조회하기

SELECT
    YEAR(YM) AS YEAR, 
    ROUND(AVG(PM_VAL1),2) AS PM10, 
    ROUND(AVG(PM_VAL2),2) AS 'PM2.5'
FROM AIR_POLLUTION
WHERE LOCATION2 = '수원'
GROUP BY YEAR
ORDER BY YEAR;

• 지역이 수원인 동네에 연도별 미세먼지 농도를 구하기 위해 GROUP BY를 사용합니다.
  WHERE LOCATION2 = '수원'
  GROUP BY YEAR
• 연도를 기준으로 오름차순 정렬합니다.

한 해에 잡은 물고기 수 구하기

SELECT
    COUNT(*) AS FISH_COUNT
FROM FISH_INFO
WHERE YEAR(TIME) = 2021

• 2021년에 잡은 물고기 수를 출력하기 위해 조건을 걸어줍니다.
  WHERE YEAR(TIME) = 2021

분기별 분화된 대장균의 개체수 구하기

SELECT
    E.QUARTER,
    COUNT(*) AS ECOLI_COUNT
FROM (
    SELECT 
        ID,
        CASE 
            WHEN MONTH(DIFFERENTIATION_DATE) <= 3 THEN "1Q"
            WHEN MONTH(DIFFERENTIATION_DATE) <= 6 THEN "2Q"
            WHEN MONTH(DIFFERENTIATION_DATE) <= 9 THEN "3Q"
            WHEN MONTH(DIFFERENTIATION_DATE) <= 12 THEN "4Q"
        END AS QUARTER
    FROM ECOLI_DATA
) E
GROUP BY QUARTER
ORDER BY E.QUARTER

• 월을 기준으로 분기를 구하기 위해 CASE 절을 이용한 서브 테이블 E를 생성합니다.
  (
      SELECT 
          ID,
          CASE 
              WHEN MONTH(DIFFERENTIATION_DATE) <= 3 THEN "1Q"
              WHEN MONTH(DIFFERENTIATION_DATE) <= 6 THEN "2Q"
              WHEN MONTH(DIFFERENTIATION_DATE) <= 9 THEN "3Q"
              WHEN MONTH(DIFFERENTIATION_DATE) <= 12 THEN "4Q"
          END AS QUARTER
      FROM ECOLI_DATA
  ) E
• QUARTER를 기준으로 그룹화와 정렬을 진행합니다
  GROUP BY QUARTER
  ORDER BY E.QUARTER