[JOIN] SQL 고득점 키트 문제 풀이 모음

 

 

풀 때마다 업데이트 할 예정

 

---

 

[LV2]

 

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

SELECT BOOK_ID, AUTHOR_NAME, DATE_FORMAT(PUBLISHED_DATE,'%Y-%m-%d') as PUBLISHED_DATE
FROM BOOK b JOIN AUTHOR a ON b.AUTHOR_ID = a.AUTHOR_ID
Where b.CATEGORY like '경제'
ORDER BY b.PUBLISHED_DATE ASC;

 

• JOIN의 종류는 (JOIN(이너조인), LEFT OUTER JOIN(왼쪽 테이블 기준), RIGHT OUTER JOIN(오른쪽 테이블 기준), FULL OUTER JOIN(전체 테이블 대상)) 4가지이다.
• %Y = 2024
• %y = 24
• %M = January
• %m = 01
• %D = 22th
• %d = 22

 

 

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

SELECT PRODUCT_CODE, SUM(sales_amount)*price as SALES
FROM PRODUCT pro JOIN OFFLINE_SALE off ON pro.PRODUCT_ID = off.PRODUCT_ID
GROUP BY pro.product_id
ORDER BY SUM(sales_amount)*price DESC, PRODUCT_CODE ASC;

 

 

---

 

[LV3]

 

1. 없어진 기록 찾기

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

 

• 보호소에 입양되어 다른 입양처로 나간 기록은 있는데, 보호소로 들어온 기록이 없다.
• 그럼 들어온 기록이 NULL인 데이터를 찾으면 된다. 그럴 때 사용하는 것이 IS NULL이다.

 

 

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

SELECT i.ANIMAL_ID, i.NAME
FROM ANIMAL_INS i JOIN ANIMAL_OUTS o ON i.ANIMAL_ID = o.ANIMAL_ID
WHERE i.DATETIME>o.DATETIME
ORDER BY i.DATETIME;

 

• YEAR(컬럼명), MONTH(컬럼명), DAYOFMONTH(컬럼명)으로 년도, 월, 일을 뽑아내서 각각 비교해야하나라는 생각이 들었다.
• 하지만 DATETIME 타입은 컬럼명으로 서로 비교할 수 있다. (타입과 컬럼명 모두 DATETIME이었음)

 

 

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

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

 

• 가장 오랜 시간 있었던 동물 3마리를 도출하면 된다.
• 정렬을 DATETIME 순으로 했기 때문에 LIMIT 3로 데이터를 앞에서 3개만 도출해주면 된다.

 

 

---

 

[LV4]

 

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

SELECT f.FLAVOR
FROM FIRST_HALF f JOIN JULY j ON f.FLAVOR=j.FLAVOR
GROUP BY f.FLAVOR
ORDER BY SUM(f.TOTAL_ORDER+j.TOTAL_ORDER) DESC
LIMIT 3;

 

• FIRST_HALF에 JULY의 SHIPMENT_ID가 외래키로 들어가 이로 join하면 될 줄 알았다.
• 하지만  FLAVOR 망고가 SHIPMENT_ID 108과 208에 존재한다면 망고의 총합은 둘로 나뉘게 될 것이다.
• 이런 이유로 FLAVOR로 join을 해야 SHIPMENT_ID로 떨어져있는 FLAVOR들을 서로 합칠 수 있다.
• 그럼 GROUP BY는 왜 해주는 거냐? GROUP BY없이 SELECT에 SUM을 해주면 전체 판매량의 총합이 하나 나온다.
• 이런 이유로 GROUP BY FLAVOR로 영역을 나눠준 다음에 SUM을 적용해야 각 맛 별로 판매량의 총합이 나온다.

 

 

2. 특정 기간동안 대여 가능한 자동차들의 대여비용 구하기 (어려움)

SELECT CAR_ID, car.CAR_TYPE, TRUNCATE((30 * car.DAILY_FEE * (1-(discount.DISCOUNT_RATE/100))),0) as FEE
FROM CAR_RENTAL_COMPANY_CAR car JOIN (
    SELECT CAR_TYPE, DURATION_TYPE, DISCOUNT_RATE
    FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN discount
    WHERE DURATION_TYPE = '30일 이상') as discount
ON car.CAR_TYPE = discount.CAR_TYPE
WHERE car.CAR_TYPE IN ('세단', 'SUV')
AND car.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'
)
AND (car.DAILY_FEE * (1-discount.DISCOUNT_RATE/100) * 30) BETWEEN 500000 AND 2000000
ORDER BY FEE DESC, CAR_TYPE ASC, CAR_ID DESC;

 

• car 목록과 discount 목록을 type으로 join
• discount 목록 중 '30일 이상' 혜택만 join (인라인 뷰 사용)
• IN 명령어를 이용해 type이 세단과 SUV인 것만 도출
• daily_fee * (1-discount_rate/100) * 30의 값이 50만 ~ 200만 사이인 것을 도출
• NOT IN , BETWEEN 명령어를 이용해 렌탈 기록에서 2022-11-01 ~ 2022-11-30 사이의 렌탈 기록은 포함하지 않는다. (서브쿼리 사용)
• 총 요금 높은순, 자동차 타입 낮은순, 자동차 아이디 높은순으로 차례대로 정렬한다.

 

배운 것

- select 서브쿼리: 스칼라

- from 서브쿼리: 인라인 뷰

- where 서브쿼리: 서브쿼리

- 뷰는 실제로 테이블을 가지고 있지 않음. 가상 테이블이라고도 함

- 정수/정수 = 실수, 이 결과를 정수로 표현하고 싶다면 3가지 방법이 있다.

• 정수 DIV 정수 : 소수점 뿐만이 아니라 일반 정수도 모조리 올림해서 잘 안쓸 것 같음
• TRUNCATE(정수/정수, 0) : 소수점을 버려준다. 0은 소수점 자리수를 말한다. 1이라고 하면 소수점 1자리까지 표현
• FLOOR(정수/정수) : 소수점을 모두 버려준다.

 

 

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

# 생산일자(PRODUCE_DATE)가 2022년 5월인 식품들의 식품 ID, 식품 이름, 총매출을 조회
# 총매출 내림차순 -> 식품 ID 오름차순

SELECT f.PRODUCT_ID, f.PRODUCT_NAME, f.PRICE*SUM(o.AMOUNT) AS TOTAL_SALES
FROM FOOD_PRODUCT f JOIN FOOD_ORDER o ON f.PRODUCT_ID = o.PRODUCT_ID
WHERE o.PRODUCE_DATE BETWEEN '2022.05.01' AND '2022.05.31'
GROUP BY f.PRODUCT_ID
ORDER BY f.PRICE*SUM(o.AMOUNT) DESC, PRODUCT_ID ASC;

 

 

4. 그룹별 조건에 맞는 식당 목록 출력하기 (어려움)

SELECT MEMBER_NAME, REVIEW_TEXT, DATE_FORMAT(REVIEW_DATE,'%Y-%m-%d') AS REVIEW_DATE
FROM REST_REVIEW r JOIN MEMBER_PROFILE m ON r.MEMBER_ID = m.MEMBER_ID
WHERE r.MEMBER_ID IN (
    SELECT r.MEMBER_ID
    FROM REST_REVIEW r JOIN MEMBER_PROFILE m ON r.MEMBER_ID = m.MEMBER_ID
    GROUP BY r.MEMBER_ID
    HAVING COUNT(r.REVIEW_ID) = (
        SELECT COUNT(r.REVIEW_ID) AS C
        FROM REST_REVIEW r
        GROUP BY r.MEMBER_ID
        ORDER BY COUNT(r.REVIEW_ID) DESC
        LIMIT 1
    )
)
ORDER BY r.REVIEW_DATE, r.REVIEW_TEXT;

 

풀이 링크

[프로그래머스] 그룹별 조건에 맞는 식당 목록 출력하기 [#SQL]

 

배운 것

- PARTITION BY를 사용하여 그룹으로 묶어서 연산을 할 수 있다.

 - GROUP BY 절을 사용하지 않고, 조회된 각 행에 그룹으로 집계된 값을 표시할 때 OVER 절과 함께 PARTITION BY 절을 사용하면 된다.

- OVER 함수는 ORDER BY, GROUP BY 서브쿼리를 개선하기 위해 나온 함수라고 할 수 있다.

• COUNT(*)OVER() : 전체행 카운트
• COUNT(*)OVER(PARTITION BY 컬럼) : 그룹단위로 나누어 카운트
• MAX(컬럼)OVER() : 전체행 중에 최고값
• MAX(컬럼)OVER(PARTITION BY 컬럼) : 그룹내 최고값
• MIN(컬럼)OVER() : 전체행 중에 최소값
• MIN(컬럼)OVER(PARTITION BY 컬럼) : 그룹내 최소값
• SUM(컬럼)OVER() : 전체행 합
• SUM(컬럼)OVER(PARTITION BY 컬럼) : 그룹내 합
• AVG(컬럼)OVER() : 전체행 평균
• AVG(컬럼)OVER(PARTITION BY 컬럼) : 그룹내 평균
• STDDEV(컬럼)OVER() : 전체행 표준편차
• STDDEV(컬럼)OVER(PARTITION BY 컬럼) : 그룹내 표준편차
• RATIO_TO_REPORT(컬럼)OVER() : 현재행값/SUM(전체행값) 퍼센테이지로 나타낼경우 100곱하면 됩니다.
• RATIO_TO_REPORT(컬럼)OVER(PARTITION BY 컬럼) : 현재행값 / SUM(그룹행값) 퍼센테이지로 나타낼경우 100곱하면 됩니다.

 

 

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

# 보호소에 들어올 당시에는 중성화되지 않았지만, 보호소를 나갈 당시에는 중성화된 동물
# 아이디와 생물 종, 이름 출력
# 아이디 순 정렬

SELECT i.ANIMAL_ID, i.ANIMAL_TYPE, i.NAME
FROM ANIMAL_INS i JOIN ANIMAL_OUTS o ON i.ANIMAL_ID = o.ANIMAL_ID
WHERE i.SEX_UPON_INTAKE like 'Intact%'
AND o.SEX_UPON_OUTCOME NOT like 'Intact%'
ORDER BY i.ANIMAL_ID;

 

배운 것

• LIKE는 포함되는 문자열이고, NOT LIKE는 미포함 문자열이다.
• 와일드 카드의 종류는 다음과 같다.
  • % : 전체
  • _ : 하나의 단어
  • [acd]% : a or c or d로 시작하는 모든 단어, 주로 단어들이 이어진 경우가 아닐 때
  • [!acd]% : a or c or d로 시작하지 않는 모든 단어, 주로 단어들이 이어진 경우가 아닐 때
  • [a-c]% :  a  ~ c로 시작하는 모든 단어, 주로 단어들이 이어진 경우일 때

 

 

6. FrontEnd 개발자 찾기

#1. FROM | 이용
SELECT DISTINCT d.ID, d.EMAIL, d.FIRST_NAME, d.LAST_NAME
FROM DEVELOPERS d JOIN SKILLCODES s ON d.SKILL_CODE = d.SKILL_CODE|s.CODE
WHERE s.CATEGORY LIKE 'Front End'
ORDER BY d.ID;

#2. FROM & 이용
SELECT DISTINCT d.ID, d.EMAIL, d.FIRST_NAME, d.LAST_NAME
FROM DEVELOPERS d JOIN SKILLCODES s ON s.CODE = d.SKILL_CODE&s.CODE
WHERE s.CATEGORY LIKE 'Front End'
ORDER BY d.ID;


#3. WHERE | 이용
SELECT DISTINCT d.ID, d.EMAIL, d.FIRST_NAME, d.LAST_NAME
FROM DEVELOPERS d JOIN SKILLCODES s
WHERE d.SKILL_CODE = d.SKILL_CODE|s.CODE AND s.CATEGORY LIKE 'Front End'
ORDER BY d.ID;

#4. WHERE & 이용
SELECT DISTINCT d.ID, d.EMAIL, d.FIRST_NAME, d.LAST_NAME
FROM DEVELOPERS d JOIN SKILLCODES s
WHERE s.CODE = d.SKILL_CODE&s.CODE AND s.CATEGORY LIKE 'Front End'
ORDER BY d.ID;

 

 

 

풀이 링크

[프로그래머스] FrontEnd 개발자 찾기 [#SQL]

 

 

배운 것

- & 비트 연산은 비트로 두 수를 표현했을 때 각 자리가 모두 1인 경우에만 1(true)를 도출한다.

- | 비트 연산은 비트로 두 수를 표현했을 때 각 자리가 모두 0인 경우에만 1(false)를 도출한다.

 

---

 

[LV5]

 

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

SELECT YEAR(o.SALES_DATE) AS YEAR, MONTH(o.SALES_DATE) AS MONTH, COUNT(DISTINCT u.USER_ID) AS PUCHASED_USERS, ROUND(COUNT(DISTINCT u.USER_ID)/(
        SELECT COUNT(DISTINCT u.USER_ID)
        FROM USER_INFO u
        WHERE u.JOINED BETWEEN '2021-01-01' AND '2021-12-31'
    ),1) AS PUCHASED_RATIO
FROM ONLINE_SALE o JOIN USER_INFO u ON o.USER_ID=u.USER_ID
WHERE u.JOINED BETWEEN '2021-01-01' AND '2021-12-31'
GROUP BY YEAR(o.SALES_DATE), MONTH(o.SALES_DATE)
ORDER BY YEAR(o.SALES_DATE), MONTH(o.SALES_DATE)

 

 

 

풀이 링크

 

[프로그래머스] 상품을 구매한 회원 비율 구하기 [#SQL]