SQL 스터디 한달 차!
프로그래머스에 있는 문제들을 이번주까지 다 풀어오기로 했다.
이번주 문제 풀이는 IS NULL, JOIN, String, Date!

🙌 1. 학습 내용(함수 및 문법구조) 정리

IF 함수 : IF(조건, 조건 참인 경우 실행, 조건 거짓인 경우 실행)
DATEDIFF 함수 : DATEDIFF(A.COLUMN, B.COLUMN) A-B 날짜 출력

🙌 2. 문제 풀이

💯 경기도에 위치한 식품창고 목록 출력하기

SELECT WAREHOUSE_ID, WAREHOUSE_NAME, ADDRESS, IF(FREEZER_YN IS NULL, 'N', FREEZER_YN) AS FREEZER_YN
FROM FOOD_WAREHOUSE
WHERE ADDRESS LIKE '경기도%'

< 풀이 과정 >
SQL에서의 IF 문을 알면 쉬운 풀이

💯 이름이 없는 동물의 아이디

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NULL
ORDER BY ANIMAL_ID

< 풀이 과정 >
이름이 null이며 ID 기준 오름차순 정렬 진행하기

💯 이름이 있는 동물의 아이디

SELECT ANIMAL_ID
FROM ANIMAL_INS
WHERE NAME IS NOT NULL
ORDER BY ANIMAL_ID

< 풀이 과정 >
위 문제에서 이름이 null이 아닌 기준만 바꿔서 적용!

💯 NULL 처리하기

SELECT ANIMAL_TYPE, IFNULL(NAME, 'No name') AS NAME, SEX_UPON_INTAKE
FROM ANIMAL_INS

< 풀이 과정 >
이름이 NULL인 애들은 No name으로 변환하여 출력해주는 문제
지난 주차 때 배웠던 IFNULL 적용하여 해결!

💯 나이 정보가 없는 회원 수 구하기

SELECT COUNT(USER_ID) AS USERS 
FROM USER_INFO
WHERE AGE IS NULL

< 풀이 과정 >
나이 정보가 NULL인 유저의 수 세는 문제!
출력 정보가 USERS 데이터 1개이므로 COUNT 적용

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

SELECT ORDER_ID, PRODUCT_ID, DATE_FORMAT(OUT_DATE, '%Y-%m-%d'), 
    CASE 
        WHEN OUT_DATE <= '2022-05-01'
            THEN '출고완료'
        WHEN OUT_DATE > '2022-05-01'
            THEN '출고대기'
        ELSE '출고미정'
    END AS 출고여부
FROM FOOD_ORDER
ORDER BY ORDER_ID

< 풀이 과정 >
지난 주차 때 학습했던 CASE ~ WHEN ~ THEN ~ END 이용하여 문제 해결
22년 5월 1일 이전에 출고된 경우 출고완료 처리
22년 5월 1일 이후에 출고될 경우 출고대기 처리
이외의 케이스는 출고미정 처리

💯 취소되지 않은 진료 예약 조회하기

SELECT A.APNT_NO, P.PT_NAME, A.PT_NO, D.MCDP_CD, D.DR_NAME, A.APNT_YMD
FROM APPOINTMENT AS A
LEFT JOIN DOCTOR AS D ON D.DR_ID = A.MDDR_ID
LEFT JOIN PATIENT AS P ON P.PT_NO = A.PT_NO
WHERE A.APNT_YMD LIKE '2022-04-13%' AND D.MCDP_CD = 'CS' AND A.APNT_CNCL_YN = 'N'
ORDER BY A.APNT_YMD 

< 풀이 과정 >

• APPOINTMENT 테이블을 기준으로 DOCTOR와 PATIENT 테이블을 LEFT JOIN 진행!
• 이후 2022년 4월 13일 날짜 필터 걸어주고 MCDP_CD가 CS인 경우, 취소되지 않는 조건인 APNT_CNCL_YN 이 N인 경우 조건 걸어주기

💯 루시와 엘라 찾기

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

< 풀이 과정 >
문제 그대로 이름이 'Lucy', 'Ella', 'Pickle', 'Rogan', 'Sabrina', 'Mitty' 얘네인 케이스만 출력해주기

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

SELECT ANIMAL_ID, NAME
FROM ANIMAL_INS
WHERE ANIMAL_TYPE = 'Dog' AND NAME LIKE '%el%'
ORDER BY NAME

< 풀이 과정 >
종류가 Dog이면서 이름에 el이 들어가야하므로 WHERE 조건 절에 해당 조건 걸어주고 출력

💯 중성화 여부 파악하기

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

< 풀이 과정 >
지난 시간 배운 case ~ when ~ then ~ end 사용하여 Neutered, Spayed로 시작하는 데이터면 O처리 아니면 X 처리 하여 출력

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

SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_INS AS INS, ANIMAL_OUTS AS OUTS
WHERE INS.ANIMAL_ID = OUTS.ANIMAL_ID
ORDER BY DATEDIFF(OUTS.DATETIME, INS.DATETIME) desc
LIMIT 2

< 풀이 과정 >
DATETIME 형태의 컬럼 간의 DATEDIFF 함수로 날짜 차이 계산 가능!

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

SELECT LEFT(PRODUCT_CODE, 2) AS CATEGORY, COUNT(PRODUCT_ID) AS PRODUCTS
FROM PRODUCT
GROUP BY CATEGORY
ORDER BY CATEGORY

< 풀이 과정 >
PRODUCT_CODE의 왼쪽 2개가 카테고리 코드이고, GROUP BY로 카테고리 별 항목이 몇갠지 출력

💯 DATETIME에서 DATE로 형 변환

SELECT ANIMAL_ID, NAME, DATE_FORMAT(DATETIME, '%Y-%m-%d') AS 날짜
FROM ANIMAL_INS
ORDER BY ANIMAL_ID

< 풀이 과정 >
이전 주차에서 학습한 DATE_FORMAT을 통해 DATETIME > DATE 형 변환이 가능함을 알고 있었기에 바로 변환하여 출력!
추가로 확인해보니 LEFT(DATETIME, 10)도 가능한 것으로 확인됌.
알아서 형 변환을 해주는걸로 보아 SQL은 참.. 함수를 어디까지 허용해주는지 어려운 부분이 있다.

💯 주문량이 많은 아이스크림들 조회하기

SELECT F.FLAVOR
FROM FIRST_HALF AS F
LEFT JOIN (SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_ORDER FROM JULY GROUP BY FLAVOR) J
ON F.FLAVOR = J.FLAVOR
ORDER BY F.TOTAL_ORDER + J.TOTAL_ORDER DESC
LIMIT 3

< 풀이 과정 >
서브쿼리로 7월 달 FLAVOR 별 TOTAL_ORDER 전부 합쳐준 후
이후 상반기 매출 테이블과 합친 것 기준으로 상위 3개 뽑아내기!

💯 5월 식품들의 총매출 조회하기

1.
SELECT O.PRODUCT_ID, P.PRODUCT_NAME, SUM(P.PRICE*O.AMOUNT) AS TOTAL_SALES
FROM FOOD_ORDER O 
JOIN (SELECT * FROM FOOD_PRODUCT) P ON P.PRODUCT_ID = O.PRODUCT_ID
WHERE O.PRODUCE_DATE LIKE '2022-05%'
GROUP BY O.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, PRODUCT_ID

2.
SELECT P.PRODUCT_ID, P.PRODUCT_NAME, (P.PRICE*O.AMOUNT) AS TOTAL_SALES
FROM FOOD_PRODUCT P
INNER JOIN (SELECT PRODUCT_ID, SUM(AMOUNT) AS AMOUNT 
           FROM FOOD_ORDER
           WHERE PRODUCE_DATE LIKE '2022-05%'
           GROUP BY PRODUCT_ID) O
ON O.PRODUCT_ID = P.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, PRODUCT_ID

< 풀이 과정 >

• 총매출은 FOOD_ORDER 테이블의 AMOUNT * PRODUCT테이블의 PRICE의 합으로 계산
• JOIN문으로 ORDER 테이블을 PRODUCT_ID 그룹핑 기준 SUM(AMOUNT)한 값들 출력

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

SELECT B.BOOK_ID, A.AUTHOR_NAME, LEFT(B.PUBLISHED_DATE,10) AS PUBLISHED_DATE
FROM BOOK B
INNER JOIN AUTHOR A ON A.AUTHOR_ID = B.AUTHOR_ID
WHERE CATEGORY = '경제'
ORDER BY PUBLISHED_DATE 

< 풀이 과정 >

• INNER JOIN 사용하여 AUTHOR_ID 겹치면 조인 진행
• 이때 카테고리가 경제여야 하고 DATE형으로 변환시켜주기

💯💯 그룹별 조건에 맞는 식당 목록 출력하기

SELECT MP.MEMBER_NAME, RR.REVIEW_TEXT, LEFT(RR.REVIEW_DATE,10) AS REVIEW_DATE
FROM MEMBER_PROFILE MP
INNER JOIN REST_REVIEW RR ON MP.MEMBER_ID = RR.MEMBER_ID
WHERE RR.MEMBER_ID = (SELECT MEMBER_ID 
                     FROM REST_REVIEW
                     GROUP BY MEMBER_ID 
                     ORDER BY COUNT(*) DESC 
                     LIMIT 1)
ORDER BY REVIEW_DATE, REVIEW_TEXT 

< 풀이 과정 >
MEMBER_ID 기준으로 리뷰를 가장 많이 작성한 사람의 회원 이름, 리뷰 텍스트, 리뷰 작성일을 출력하는 문제
만일 동일인물이 2명 이상인 경우 LIMIT의 문제를 해결하기 위해 서브쿼리를 이후 하나 더 작성해서 해보았지만 GROUP FUNCTION INVALID가 떠서 해결하진 못한 상황..
스터디 이후 추가 공부 필요!

💯 없어진 기록 찾기

SELECT OUTS.ANIMAL_ID, OUTS.NAME
FROM ANIMAL_OUTS OUTS
LEFT OUTER JOIN ANIMAL_INS INS ON OUTS.ANIMAL_ID = INS.ANIMAL_ID
WHERE INS.ANIMAL_ID IS NULL
ORDER BY ANIMAL_ID

< 풀이 과정 >
입양을 간 기록은 있는데 보호소에 들어온 데이터가 없는 경우는 즉 OUTS 테이블에는 ID 값이 존재하나 INS테이블에는 ID가 비어있는 상황이므로 ANIMAL_OUTS를 기준으로 ANIMAL_INS 테이블을 LEFT OUTER JOIN을 한다.
그렇게 되면 INS 테이블의 ID가 NULL로 채워지므로 이때 ID가 NULL인 애들만 출력하기

💯 있었는데요 없었습니다

SELECT INS.ANIMAL_ID, INS.NAME
FROM ANIMAL_INS INS
INNER JOIN ANIMAL_OUTS OUTS ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE INS.DATETIME > OUTS.DATETIME
ORDER BY INS.DATETIME

< 풀이 과정 >

• 보호소 들어온 INS테이블과 보호소를 나가는 OUTS 테이블의 ANIMAL_ID 가 동일한 조건으로 INNER JOIN 진행
• 이후 INS테이블의 입양일이 OUTS테이블의 보호 시작일보다 빠른 동물 출력해주기

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

SELECT INS.NAME, INS.DATETIME
FROM ANIMAL_INS INS LEFT JOIN ANIMAL_OUTS OUTS ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE OUTS.ANIMAL_ID IS NULL
ORDER BY INS.DATETIME
LIMIT 3

< 풀이 과정 >
INS 테이블에 OUTS 테이블 LEFT JOIN 하여 입양 나간 기록이 없는 즉, OUTS 테이블의 ID가 NULL이며 입양 들어온 기록이 가장 오래된 상위 3마리의 동물 출력하기

💯 보호소에서 중성화한 동물

SELECT OUTS.ANIMAL_ID, OUTS.ANIMAL_TYPE, OUTS.NAME
FROM ANIMAL_OUTS OUTS
INNER JOIN ANIMAL_INS INS ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE INS.SEX_UPON_INTAKE != OUTS.SEX_UPON_OUTCOME
ORDER BY ANIMAL_ID

< 풀이 과정 >
INNER JOIN 진행하여 성별이 다르면 출력하기
(중성화한 경우 제외하면 성별이 바뀔 일이 없으므로 간단하게 != 처리!)

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

SELECT P.PRODUCT_CODE, SUM(P.PRICE * O.SALES_AMOUNT) AS SALES
FROM PRODUCT P
JOIN OFFLINE_SALE O ON P.PRODUCT_ID = O.PRODUCT_ID
GROUP BY P.PRODUCT_CODE
ORDER BY SALES DESC, PRODUCT_CODE

< 풀이 과정 >

• OFFLINE 데이터와 PRODUCT 테이블 조인하기.
• 조인 이전 PRICE * SALES_AMOUNT의 합 (PRODUCT_CODE) 기준으로 처리

💯 상품을 구매한 회원 비율 구하기

SELECT YEAR(O.SALES_DATE) AS YEAR, MONTH(O.SALES_DATE) AS MONTH, COUNT(DISTINCT O.USER_ID) AS PUCHASED_USERS, 
ROUND((COUNT(DISTINCT O.USER_ID) / (SELECT COUNT(DISTINCT USER_ID) FROM USER_INFO WHERE YEAR(JOINED) = 2021)), 1) AS PUCHASED_RATIO
FROM ONLINE_SALE O
INNER JOIN (SELECT USER_ID, JOINED FROM USER_INFO WHERE YEAR(JOINED) = 2021) U
ON U.USER_ID = O.USER_ID
GROUP BY YEAR, MONTH
ORDER BY YEAR, MONTH  

< 풀이 과정 >
복잡한 절차가 상당히 많았던 문제..

• ONLINE 테이블에 판매 날짜를 기준으로 YEAR와 MONTH SELECT
• DISTINCT 사용하여 USER_ID 뽑아서 회원수 체크하는 USERS SELECT
• 소수점 첫째자리까지 뽑아내기 위해 ROUND사용, 분자/분모 나누어서 분모는 이후 JOIN으로 2021년 뽑기에 COUNT(DISTINCT())만 하고 분자의 경우 2021년 가입한 USER를 뽑기 위해 서브쿼리 사용
• 이후 앞서 USERS 필드와 RATIO필드의 분자를 위해 2021년만 INNER JOIN
• 이후 YEAR, MONTH 기준 GROUP BY 및 ORDER BY 진행