260104) 주말 SQL 코드카타

SQL 코드카타

Q31. 오랜 기간 보호한 동물(1)

1. 문제 링크: https://school.programmers.co.kr/learn/courses/30/lessons/59044

2. 정답 코드:

SELECT
    i.NAME,
    i.DATETIME
FROM ANIMAL_INS AS i
LEFT JOIN ANIMAL_OUTS AS o
  ON i.ANIMAL_ID = o.ANIMAL_ID
WHERE o.ANIMAL_ID IS NULL
GROUP BY i.ANIMAL_ID
ORDER BY i.DATETIME
LIMIT 3;

 

Q32. 카테고리 별 도서 판매량 집계하기

1. 문제 링크: https://school.programmers.co.kr/learn/courses/30/lessons/144855

2. 정답 코드:

SELECT
    b.CATEGORY,
    SUM(bs.SALES) AS TOTAL_SALES
FROM BOOK AS b
JOIN BOOK_SALES AS bs
  ON b.BOOK_ID = bs.BOOK_ID
  AND SUBSTR(bs.SALES_DATE, 1, 7) = '2022-01'
GROUP BY b.CATEGORY
ORDER BY b.CATEGORY ASC;

 

Q33. 상품 별 오프라인 매출 구하기

1. 문제 링크: https://school.programmers.co.kr/learn/courses/30/lessons/131533

2. 정답 코드:

SELECT
    p.PRODUCT_CODE,
    IFNULL(SUM(p.PRICE * o.SALES_AMOUNT), 0) AS SALES
FROM PRODUCT AS p
LEFT JOIN OFFLINE_SALE AS o
ON p.PRODUCT_ID = o.PRODUCT_ID
GROUP BY p.PRODUCT_CODE
ORDER BY SALES DESC, p.PRODUCT_CODE ASC;

 

Q34. 있었는데요 없었습니다

1. 문제 링크: https://school.programmers.co.kr/learn/courses/30/lessons/59043

2. 정답 코드:

SELECT
    i.ANIMAL_ID,
    i.NAME
FROM ANIMAL_INS AS i
JOIN ANIMAL_OUTS AS o
  ON i.ANIMAL_ID = o.ANIMAL_ID
  AND i.DATETIME > o.DATETIME
GROUP BY i.ANIMAL_ID
ORDER BY i.DATETIME ASC;

 

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

1. 문제 링크: https://school.programmers.co.kr/learn/courses/30/lessons/59411

2. 정답 코드:

SELECT
    i.ANIMAL_ID,
    i.NAME
FROM ANIMAL_INS AS i
JOIN ANIMAL_OUTS AS o
  ON i.ANIMAL_ID = o.ANIMAL_ID
ORDER BY (i.DATETIME - o.DATETIME) ASC
LIMIT 2;

3. 오류 상황: 결과 화면은 예시와 비슷하게 나오는데 정답이 아님

4. 시도 방법: 놓친 조건이 없는지 확인함

5. 최종 문제 해결 방법: 보호기간을 구해야 하므로, (i.DATETIME - o.DATETIME) 로 ASC 정렬함 

 

Q36. 보호소에서 중성화한 동물

1. 문제 링크: https://school.programmers.co.kr/learn/courses/30/lessons/59045

2. 정답 코드:

SELECT
    i.ANIMAL_ID,
    i.ANIMAL_TYPE,
    i.NAME
FROM ANIMAL_INS AS i
JOIN ANIMAL_OUTS AS o
  ON i.ANIMAL_ID = o.ANIMAL_ID
  AND i.SEX_UPON_INTAKE <> o.SEX_UPON_OUTCOME
ORDER BY i.ANIMAL_ID ASC;

Q37. 조건에 맞는 도서와 저자 리스트 출력하기

1. 문제 링크: https://school.programmers.co.kr/learn/courses/30/lessons/144854

2. 정답 코드:

SELECT
    b.BOOK_ID,
    a.AUTHOR_NAME,
    SUBSTR(b.PUBLISHED_DATE, 1, 10) AS PUBLISHED_DATE
FROM BOOK AS b
JOIN AUTHOR AS a
  ON b.AUTHOR_ID = a.AUTHOR_ID
  AND b.CATEGORY = '경제'
ORDER BY b.PUBLISHED_DATE ASC;

Q38. 조건별로 분류하여 주문상태 출력하기

1. 문제 링크: https://school.programmers.co.kr/learn/courses/30/lessons/131113

2. 정답 코드:

SELECT
    ORDER_ID,
    PRODUCT_ID,
    SUBSTR(OUT_DATE, 1, 10) AS OUT_DATE,
    CASE
        WHEN OUT_DATE <= '2022-05-01' THEN '출고완료'
        WHEN OUT_DATE > '2022-05-01' THEN '출고대기'
        WHEN OUT_DATE IS NULL THEN '출고미정'        
    END AS 출고여부
FROM FOOD_ORDER
ORDER BY ORDER_ID ASC;

 

Q39. 성분으로 구분한 아이스크림 총 주문량

1. 문제 링크: https://school.programmers.co.kr/learn/courses/30/lessons/133026

2. 정답 코드:

SELECT
    i.INGREDIENT_TYPE,
    SUM(TOTAL_ORDER) AS TOTAL_ORDER
FROM FIRST_HALF AS f
JOIN ICECREAM_INFO AS i
  ON f.FLAVOR = i.FLAVOR
GROUP BY i.INGREDIENT_TYPE
ORDER BY TOTAL_ORDER ASC;

Q40. 루시와 엘라 찾기

1. 문제 링크: https://school.programmers.co.kr/learn/courses/30/lessons/59046

2. 정답 코드:

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