프로그래머스 SQL 고득점 Kit 풀이 - JOIN 편

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

https://school.programmers.co.kr/learn/courses/30/lessons/144854

프로그래머스

풀이

SELECT B.BOOK_ID , A.AUTHOR_NAME, DATE_FORMAT(B.PUBLISHED_DATE,"%Y-%m-%d") AS PUBLISHED_DATE
FROM BOOK B INNER JOIN AUTHOR A USING (AUTHOR_ID)
WHERE B.CATEGORY = "경제"
ORDER BY B.PUBLISHED_DATE

---

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

https://school.programmers.co.kr/learn/courses/30/lessons/131533

프로그래머스

풀이(정답)

--PRODUCT 테이블과 OFFLINE_SALE 테이블에서 상품코드 별 매출액(판매가 * 판매량) 합계를 출력하는 SQL문을 작성해주세요. 
-- 결과는 매출액을 기준으로 내림차순 정렬해주시고 매출액이 같다면 상품코드를 기준으로 오름차순 정렬해주세요.

SELECT PRODUCT_CODE, SUM(PRICE)
FROM  PRODUCT INNER JOIN  OFFLINE_SALE USING (PRODUCT_ID)
GROUP BY PRODUCT_CODE
ORDER BY SUM(PRICE) DESC, PRODUCT_CODE

과정

 

먼저 PRODUCT 테이블과 OFFLINE_SALE을 INNER JOIN을 해준다. 그래야 해당 상품이 몇개가 팔렸는지 알 수 있기 때문이다.  USING을 통해서 두 테이블의 PRODUCT_ID가 같은 경우만 JOIN을 시킨다.

 

이렇게 해당 PRODUCT가 몇개 팔렸는지 알 수 있게 되었으니 매출액(판매가 - PRICE * SALES_AMOUNT) 를 구해야한다. 

 

먼저 일단 INNER JOIN 만 한 결과를 봐보자.

 

SELECT *
FROM  PRODUCT INNER JOIN OFFLINE_SALE USING (PRODUCT_ID)

SELECT * FROM PRODUCT INNER JOIN OFFLINE_SALE USING (PRODUCT_ID)

 

RPODUCT_ID에 대해 판매 기록이 여러개 있기 때문에 이러한 결과가 나오게 된다. 문제에서는  상품코드 별 매출액(판매가 * 판매량) 합계를 구라하고 했기 때문에 해당 PRODUCT_CODE의 총 판매 개수를 알아야한다. 

 GROUP BY 명령어는 동일한 값을 가진 컬럼을 그룹으로 묶어서 요약하기 위해서 사용한다. 따라서 PRODUCT_CODE 기준으로 GROUP BY를 시킨다. 그리고 SUM이라는 집계함수를 이용하여 해당 PRODUCT_CODE의 총 판매 개수를 구해보자.

 

SELECT PRODUCT_CODE ,SUM(SALES_AMOUNT)
FROM  PRODUCT INNER JOIN OFFLINE_SALE USING (PRODUCT_ID)
GROUP BY  PRODUCT_CODE

이렇게 하면 PRODUCT_CODE에 대해서 그룹으로 묶었고 , SUM(SALES_AMOUNT)를 해서 총 판매 계수를 출력한다.

 

문제에서는 판매가 * 총 판매개수 = 매출액을 출력해야하니 SUM(SALES_AMOUNT) * PRICE를 곱한다. 그 후 매출액 내림차순으로 정렬, 같다면 상품 코드를 기준으로 오름차순 정렬을 하면 문제에서 요구하는 정답을 출력할 수 있다.

---

Lv3. 없어진 기록 찾기

https://school.programmers.co.kr/learn/courses/30/lessons/59042

프로그래머스

 

풀이

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

 

---

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

https://school.programmers.co.kr/learn/courses/30/lessons/59043

프로그래머스

 

풀이

-- 관리자의 실수로 일부 동물의 입양일이 잘못 입력되었습니다. 
--보호 시작일보다 입양일이 더 빠른 동물의 아이디와 이름을 조회하는 SQL문을 작성해주세요. 
--이때 결과는 보호 시작일이 빠른 순으로 조회해야합니다.

SELECT I.ANIMAL_ID , I.NAME
FROM ANIMAL_INS I INNER JOIN ANIMAL_OUTS O USING (ANIMAL_ID)
WHERE O.DATETIME < I.DATETIME
ORDER BY I.DATETIME

 

SQL에서의 날짜, 시간 등에서의 대소 비교는 시간순으로 더 빠를 수록 더 작은 값으로 판단한다.

 

---

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

https://school.programmers.co.kr/learn/courses/30/lessons/59044

프로그래머스

 

풀이

-- 아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 동물 3마리의 이름과 보호 시작일을 조회하는 SQL문을 작성해주세요. 
-- 이때 결과는 보호 시작일 순으로 조회해야 합니다.

SELECT I.NAME , I.DATETIME
FROM ANIMAL_INS I LEFT JOIN ANIMAL_OUTS O USING(ANIMAL_ID)
WHERE O.DATETIME IS NULL
ORDER BY I.DATETIME
LIMIT 3

 

입양을 못 간 동물을 알기 위해서 ANIMAL_INS 와 ANIMAL_OUTS를 LEFT JOIN을 시킨다. ANIMAL_ID이 같은 것들만 값이 있게 들어오고 입양된 기록이 없다면 전부 NULL로 되서 들어올 것이다. 이를 O.DATETIME IS NULL 하여 입양을 아직 못간 동물을 찾으면 된다. 

SELECT * FROM ANIMAL_INS I LEFT JOIN ANIMAL_OUTS O USING(ANIMAL_ID)

 

그리고 주어진 조건에 맞게 결과는 보호 시작일 순으로 조회하고 (ORDER BY I.DATETIME  -> 기본으로 오름차순이다. ) 동물 3마리만 출력한다. (LIMIT 3)

---

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

https://school.programmers.co.kr/learn/courses/30/lessons/59045

프로그래머스

풀이(1)

SELECT I.ANIMAL_ID , I.ANIMAL_TYPE,  I.NAME
FROM ANIMAL_INS I INNER JOIN ANIMAL_OUTS  O
USING (ANIMAL_ID)
WHERE SUBSTRING_INDEX(SEX_UPON_INTAKE,' ',1) = "Intact" AND (SUBSTRING_INDEX(SEX_UPON_OUTCOME,' ',1) = "Neutered" or SUBSTRING_INDEX(SEX_UPON_OUTCOME,' ',1) = "Spayed")
ORDER BY 1

보호소에 들어왔을때는 중성화가 되어있지 않았고 , 입양보내졌을때는 중성화가 된 필드를 출력하는게 문제였다.

Intact 가 중성화가 되어있지 않음을 의미했고 , 성별에 따라서 "Spayed" 또는 "Neutered" 가 중성화를 의미하는 키워드였다. 

 

따라서 ANIMAL_ID가 같은 것으로 두 테이블을 먼저 Join한다. 그리고  SEX_UPON_INTAKE의 맨 앞의 split값이 "Intact"이면서  SEX_UPON_OUTCOME가 "Spayed" 또는 "Neutered" 일 때를 출력하도록 풀었다.

 

다른 사람들의 정답을 보면 WHERE 절의 조건을 

 

- SEX_UPON_INTAKE ! = SEX_UPON_OUTCOME

- LIKE 연산자를 이용하여  %Intact% 

 

두 경우로 많이들 풀었다. 나 같은 경우는 공백 한칸이 주어지고 앞에 중성화 여부의 값이 들어있으니 Split을 해주고 싶어 SUBSTRING_INDEX(필드명 , 나눌 기준이 될 문자 , 몇번째를 의미하는지에 대한 숫자)를 이용하여 중성화 여부 값을 가져오려고 하였다.

 

---

 

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

https://school.programmers.co.kr/learn/courses/30/lessons/131124

프로그래머스

풀이

SELECT MEMBER_NAME, REVIEW_TEXT, DATE_FORMAT(REVIEW_DATE, "%Y-%m-%d") AS REVIEW_DATE
FROM MEMBER_PROFILE MP 
INNER JOIN (
    SELECT  *
    FROM REST_REVIEW 
    WHERE MEMBER_ID IN (
                SELECT  MEMBER_ID
                FROM REST_REVIEW 
                GROUP BY MEMBER_ID
                HAVING COUNT(REVIEW_TEXT) = (
                       -- 1번 쿼리
                        SELECT COUNT(*)
                        FROM REST_REVIEW
                        GROUP BY MEMBER_ID
                        ORDER BY COUNT(*) DESC 
                        LIMIT 1
                        )
                    )
                 ) RR
USING(MEMBER_ID)
ORDER BY REVIEW_DATE ASC, REVIEW_TEXT ASC

 

조건

 

1. 리뷰를 가장 많이 작성한 회원의 리뷰를 조회

2. 회원 이름, 리뷰 텍스트, 리뷰 작성일이 출력되도록 작성

3. 결과는 리뷰 작성일을 기준으로 오름차순, 리뷰작성일이 같다면 리뷰 텍스트를 기준으로 오름차순 정렬

 

풀이과정

 

1. 먼저 리뷰를 많이 쓴 회원의 리뷰개수는 몇 개인지 알아야한다. 주의할 점이 있는데 4개가 최대 개수였다고 하면 4개를 쓴 회원이 1명또는 여러명일 수 있다는 점이다. 

 SELECT COUNT(*)
 FROM REST_REVIEW
 GROUP BY MEMBER_ID
 ORDER BY COUNT(*) DESC 
 LIMIT 1
 )

따라서 REST_REVIEW에서 MEMBER_ID를 기준으로 GROUP BY를 해주고 리뷰를 몇개 작성했는지에 대한 COUNT(*)를 해준다. 최대 개수를 구할꺼지 내림차순 후 맨 위에 있는 행의 값을 출력하는 쿼리를 작성하였다.

 

2. 예를 들어 최대 리뷰 많이쓴 개수가 4개라고 하면 4개를 쓴 모든 회원의 리뷰를 보여 주어야한다. 따라서 어떤 회원들이 존재하는지에 대한 쿼리를 작성했다.

SELECT  MEMBER_ID
                FROM REST_REVIEW 
                GROUP BY MEMBER_ID
                HAVING COUNT(REVIEW_TEXT) = (
                       -- 1번 쿼리
                        SELECT COUNT(*)
                        FROM REST_REVIEW
                        GROUP BY MEMBER_ID
                        ORDER BY COUNT(*) DESC 
                        LIMIT 1
                        )
                    )

3. 2번 쿼리에서 출력되는 MEMBER_ID를 가지고 해당 회원들의 리뷰를 출력하는 쿼리이다.

 SELECT  *
    FROM REST_REVIEW 
    WHERE MEMBER_ID IN (
                SELECT  MEMBER_ID
                FROM REST_REVIEW 
                GROUP BY MEMBER_ID
                HAVING COUNT(REVIEW_TEXT) = (
                       -- 1번 쿼리
                        SELECT COUNT(*)
                        FROM REST_REVIEW
                        GROUP BY MEMBER_ID
                        ORDER BY COUNT(*) DESC 
                        LIMIT 1
                        )
                    )
                 )

4. 문제에서 회원의 이름 (MEMBER_NAME) 도 함께 출력해야되니 MEMBER_ID 가 같은 행끼리 MEMBER_PROFILE JOIN 시킨 후 조건에 맞게 나머지 정렬이나, 출력 필드를 적어 정답을 출력한다.

SELECT MEMBER_NAME, REVIEW_TEXT, DATE_FORMAT(REVIEW_DATE, "%Y-%m-%d") AS REVIEW_DATE
FROM MEMBER_PROFILE MP 
INNER JOIN (
    SELECT  *
    FROM REST_REVIEW 
    WHERE MEMBER_ID IN (
                SELECT  MEMBER_ID
                FROM REST_REVIEW 
                GROUP BY MEMBER_ID
                HAVING COUNT(REVIEW_TEXT) = (
                       -- 1번 쿼리
                        SELECT COUNT(*)
                        FROM REST_REVIEW
                        GROUP BY MEMBER_ID
                        ORDER BY COUNT(*) DESC 
                        LIMIT 1
                        )
                    )
                 ) RR
USING(MEMBER_ID)
ORDER BY REVIEW_DATE ASC, REVIEW_TEXT ASC

---

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

https://school.programmers.co.kr/learn/courses/30/lessons/133027

프로그래머스

풀이

SELECT FLAVOR
FROM FIRST_HALF FH  JOIN 
(
    SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_ORDER
    FROM JULY
    GROUP BY FLAVOR
) J
USING(FLAVOR)
ORDER BY J.TOTAL_ORDER + FH.TOTAL_ORDER DESC
LIMIT 3

 

조건

 

1. 7월(JULY) 아이스크림 총 주문량과 상반기(FIRST_HALF) 아이스크림 총 주문량을 더한 값이 큰 순서대로 상위 3개의 맛을 조회

2. 7월에는 주문량이 많아 같은 맛 아이스크림에 대하여 서로 다른 두 공장에서 아이스크림 가게로 출하하는 경우가 있음. 같은 맛의 아이스크림이더라도 다른 출하 번호를 갖음.

 

 

풀이

 

1. 먼저 7월에는 주문량이 많아 다른 출하 번호를 갖기만 같은 맛 아이스크림이 있을 수 있으므로 JULY 테이블에서  FALVOR에 대해 GROUP BY를 하고 같은 맛에 대한 주문 총합을 먼저 구한다.

2. 1번으로 나온 테이블과 FIRST_HALF의 FALVOR의 같은 경우로 테이블 JOIN을 한다.

3.  1번 테이블의 총 주문량과  FIRST_HALF 의 총 주문량을 더하기 연산하여 출력한다.

4.  총 주문량이 큰 순서대로 3개의 맛을 출력해야하므로 총 주문량에 대하여 ORDER BY 를 통해 DESC 내림차순을 한다.

5. 마지막으로 LIMIT 3을 통해 3개만 출력하도록 한다.

 

---

Lv4. 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기

https://school.programmers.co.kr/learn/courses/30/lessons/157339

프로그래머스

풀이 

SELECT  CAR_ID , CAR_TYPE, ROUND(DAILY_FEE * 30 * (100 - DISCOUNT_RATE)/100) AS FEE
FROM CAR_RENTAL_COMPANY_CAR INNER JOIN CAR_RENTAL_COMPANY_DISCOUNT_PLAN
USING(CAR_TYPE)
WHERE DURATION_TYPE ="30일 이상" 
      AND CAR_TYPE IN ("SUV", "세단") 
      AND CAR_ID NOT IN (
        SELECT CAR_ID 
        FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY
         WHERE END_DATE >= '2022-11-01' AND START_DATE <= '2022-11-30'
      )
      
HAVING FEE >= 500000 AND FEE < 2000000
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC;

조건

 

1. 자동차 종류가 세단 또는 suv인 자동차 중
2. 2022년 11월 1일 부터 2022년 11월 30일까지 대여 가능하고
3. 30일 간의 대여 금액이 50만원 이상 200만원 미만인 자동차에 대해서 
4. 자동차 ID , 자동차 종류 , 대여 금액: (FEE) 리스트를 출력
5. 결과는 대여금액을 기준으로 내림차순 , 자동차 종류를 기준으로 오름차순 정렬 ,자동차 ID를 기준으로 내림차순

 

풀이 과정

 

일단 테이블이 3개기도 하고 테이블명도 길고,,, 조건도 많다. 하나씩 차근히 풀어보자!

테이블 명이 조금 길기 때문에 축약해서 부르겠다.

 

- 대여 중인 자동차들 정보 CAR_RENTAL_COMPANY_CAR => A테이블
- 자동차 대여 기록 정보 CAR_RENTAL_COMPANY_RENTAL_HISTORY => B 테이블
- 자동차 종류 별 대여 기간 종류 별 할인 정책 정보 CAR_RENTAL_COMPANY_DISCOUNT_PLAN => C 테이블

 

1.  A테이블과 C테이블 사이에 CAR_TYPE이 같은 경우만을 JOIN 한다. 

2. WHERE절을 이용하여 CAR_TYPE 이 "세단" 이거나 "SUV" 인 경우의 데이터만 뽑는다. 그리고 DURATION_TYPE = "30일 이상" 인 것만 선택한다. 2022-11-01 부터 2022-11-30일까지 대여료를 구해야하니 저렇게 조건을 설정한 것이다.

3. B 테이블에서 2022-11-01 부터 2022-11-30일 까지 대여할 수 있는 차량만 골라야한다. 따라서 NOT IN을 이용하여 대여 종료날짜가 END_DATE >= '2022-11-01 & 대여 시작 날짜 START_DATE <= "2022-11-30" 인 경우는 대여를 할 수 없는 경우이니 해당 CAR_ID에 대해선 출력하지 않도록 만든다.

4. 이제 1,2,3 과정에서 얻은 테이블에서 조건에 맞게 출력해보자. 대여료 같은 경우에는 A테이블의 DAILY_FEE * 30 (일) 을 하고 여기서 또 (100 - DISCOUNT_RATE)/100 을 곱한다. 만약 할인율이 5% 였다고 한다면 100-5는 95고 이를 100으로 나누면 0.95다 이를 총 금액에서 곱하면 할인 후 총 대여료가 된다. 문제에서는 대여료를 출력할때 정수까지만 보여달라고 했으니 ROUND()를 이용하여 소숫점을 제거한다.

5. 구한 FEE의 값이 50만원 이상 200만원미만 이어야 하기때문에 HAVING 절을 이용하여 조건을 처리한다.

6. 마지막으로 ORDER BY 를 이용하여 조건에 맞게 내림차순과 오름차순을 설정한다. 

---

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

https://school.programmers.co.kr/learn/courses/30/lessons/131117

프로그래머스

풀이

SELECT  FP.PRODUCT_ID, FP.PRODUCT_NAME , SUM(FO.AMOUNT) * FP.PRICE AS TOTAL_SALES
FROM FOOD_ORDER FO INNER JOIN FOOD_PRODUCT FP
USING (PRODUCT_ID)
WHERE DATE_FORMAT(FO.PRODUCE_DATE,"%Y-%m") = "2022-05"
GROUP BY FP.PRODUCT_ID
ORDER BY 3 DESC , 1

 

조건 

 

1.  식품 ID , 식품 이름 , 총매출 조회

2.  생산일자가 2022년 5월 식품들만 조회

3.  총매출 기준으로 내림차순 정렬 , 총매출이 같다면 식품 ID를 기준으로 오름차순 정렬

 

풀이 과정

 

1.   식품의 주문 정보가 있는 상품만 계산하기 위해 PRODUCT_ID로 JOIN

2.  DATE_FORMAT을 이용하여 2022년 5월의 행만 조회

3. GROUP BY 와 집계 함수를 이용하여 PRODUCT_ID에 대한 주문 총량 * 식품 개수 의 합을 출력