제가 공부한 내용을 정리하는 블로그입니다.
아직 많이 부족하고 배울게 너무나도 많습니다. 틀린내용이 있으면 언제나 가감없이 말씀해주시면 감사하겠습니다😁
Programmers SQL 고득점 Kit(JOIN)입니다.
특정 기간동안 대여 가능한 자동차들의 대여 비용 구하기
SELECT
H.CAR_ID,
C.CAR_TYPE,
C.DAILY_FEE * 30 * (1 - (DISCOUNT_RATE / 100)) AS FEE
FROM (
SELECT DISTINCT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
GROUP BY CAR_ID
HAVING MAX(TO_CHAR(END_DATE, 'YYYY-MM')) < '2022-11'
) H JOIN (
SELECT CAR_ID, CAR_TYPE, DAILY_FEE
FROM CAR_RENTAL_COMPANY_CAR C
WHERE CAR_TYPE = '세단' OR CAR_TYPE = 'SUV'
) C ON H.CAR_ID = C.CAR_ID JOIN (
SELECT CAR_TYPE, DURATION_TYPE, DISCOUNT_RATE
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
WHERE (CAR_TYPE = '세단' OR CAR_TYPE = 'SUV') AND (DURATION_TYPE = '30일 이상')
) P ON C.CAR_TYPE = P.CAR_TYPE
WHERE C.DAILY_FEE * 30 * (1 - (DISCOUNT_RATE / 100)) BETWEEN 500000 AND 2000000
ORDER BY FEE DESC, C.CAR_TYPE ASC, H.CAR_ID DESC
- 2022년 11월 대여가 가능한 차를 찾아야 하기에 CAR_RENTAL_COMPANY_RENTAL_HISTORY에서 반납일이 11월보다 작은 차를 조회하기 위한 서브 테이블 H를 생성합니다.
(
SELECT DISTINCT CAR_ID
FROM CAR_RENTAL_COMPANY_RENTAL_HISTORY H
GROUP BY CAR_ID
HAVING MAX(TO_CHAR(END_DATE, 'YYYY-MM')) < '2022-11'
) H
- 자동차의 종류가 세단이나 SUV여야 하므로 서브테이블 C를 생성합니다.
(
SELECT CAR_ID, CAR_TYPE, DAILY_FEE
FROM CAR_RENTAL_COMPANY_CAR C
WHERE CAR_TYPE = '세단' OR CAR_TYPE = 'SUV'
) C
- H테이블과 C 테이블을 CAR_ID를 기준으로 조인합니다
FROM (...) H JOIN (...)C ON H.CAR_ID = C.CAR_ID
- 세단과 SUV의 30일 이상 대여할 시의 할인율을 구하기 위한 서브 테이블 P를 생성합니다.
(
SELECT CAR_TYPE, DURATION_TYPE, DISCOUNT_RATE
FROM CAR_RENTAL_COMPANY_DISCOUNT_PLAN
WHERE (CAR_TYPE = '세단' OR CAR_TYPE = 'SUV') AND (DURATION_TYPE = '30일 이상')
) P
- P 테이블과 C테이블의 CAR_TYPE을 기준으로 조인합니다.
C.CAR_TYPE = P.CAR_TYPE
- 30일 간의 대여 금액이 50만원 이상 200만원 미만인 자동차를 구하기 위해 BETWEEN 함수를 이용하빈다.
DISCOUNT_RATE가 퍼센트로 표시되어있기에 해당 계산을 이용합니다.
WHERE C.DAILY_FEE * 30 * (1 - (DISCOUNT_RATE / 100)) BETWEEN 500000 AND 2000000
- 자동차 종류를 기준으로 오름차순 정렬, 자동차 ID를 기준으로 내림차순 정렬합니다.
5월 식품들의 총 매출 조회하기
SELECT
P.PRODUCT_ID,
P.PRODUCT_NAME,
P.PRICE * O.AMOUNT AS TOTAL_SALES
FROM FOOD_PRODUCT P JOIN (
SELECT PRODUCT_ID, SUM(AMOUNT) AS AMOUNT
FROM FOOD_ORDER O
WHERE TO_CHAR(PRODUCE_DATE, 'YYYY-MM') = '2022-05'
GROUP BY PRODUCT_ID
) O ON P.PRODUCT_ID = O.PRODUCT_ID
ORDER BY TOTAL_SALES DESC, P.PRODUCT_ID ASC
- 2022년 5월에 생산된 식품을 조회하기 위한 서브 테이블 O를 생성합니다.
총 매출(TOTAL_ORDER * PRICE)을 구해야하기 때문에 식품ID를 기준으로 그룹화를 하고 SUM 함수를 사용합니다.
(
SELECT PRODUCT_ID, SUM(AMOUNT) AS AMOUNT
FROM FOOD_ORDER O
WHERE TO_CHAR(PRODUCE_DATE, 'YYYY-MM') = '2022-05'
GROUP BY PRODUCT_ID
) O
- FOOD_PRODUCT 테이블과 PRODUCT_ID를 기준으로 JOIN합니다.
FROM FOOD_PRODUCT P JOIN (...) O ON P.PRODUCT_ID = O.PRODUCT_ID
- 총 매출을 구하기 위해 다음과 같이 TOTAL_SALES 컬럼을 구성합니다.
P.PRICE * O.AMOUNT AS TOTAL_SALES
- 총 매출을 기준으로 내림차순 정렬하고, 식품 ID를 기준으로 오름차순 정렬합니다.
주문량이 많은 아이스크림들 조회하기
SELECT
H.FLAVOR
FROM (
SELECT FLAVOR, TOTAL_ORDER
FROM FIRST_HALF
) H JOIN (
SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_ORDER
FROM JULY
GROUP BY FLAVOR
) J ON H.FLAVOR = J.FLAVOR
ORDER BY H.TOTAL_ORDER + J.TOTAL_ORDER DESC
FETCH NEXT 3 ROWS ONLY
- 상반기 아이스크림의 총 주문량을 구하기 위한 서브 테이블 H를 생성합니다.
(
SELECT FLAVOR, TOTAL_ORDER
FROM FIRST_HALF
) H
- 7월에는 주문량이 많아 다른 두 공장에서 같은 맛을 출하를 진행하였으므로 FLAVOR를 기준으로 그룹화를 진행하고 총 주문량을 조회하는 서브테이르 J를 생성합니다.
(
SELECT FLAVOR, SUM(TOTAL_ORDER) AS TOTAL_ORDER
FROM JULY
GROUP BY FLAVOR
) J
- H 테이블과 J 테이블을 FLAVOR을 기준으로 JOIN 합니다.
FROM (...) H JOIN (...) J ON H.FLAVOR = J.FLAVOR
- 총 주문량에 대해 내림차순 정렬 후 상위 3개의 레코드를 조회합니다.
ORDER BY H.TOTAL_ORDER + J.TOTAL_ORDER DESC
FETCH NEXT 3 ROWS ONLY
조건에 맞는 도서와 저자 리스트 출력하기
SELECT
B.BOOK_ID,
A.AUTHOR_NAME,
TO_CHAR(B.PUBLISHED_DATE, 'YYYY-MM-DD') AS PUBLISHED_DATE
FROM BOOK B JOIN AUTHOR A ON B.AUTHOR_ID = A.AUTHOR_ID
WHERE B.CATEGORY = '경제'
ORDER BY B.PUBLISHED_DATE
- 도서 테이블과 저자 테이블을 JOIN하기 위해 AUTHOR_ID를 기준으로 JOIN합니다.
FROM BOOK B JOIN AUTHOR A ON B.AUTHOR_ID = A.AUTHOR_ID
- 경제 카테고리에 해당하는 도서를 찾기 위해 조건을 걸어줍니다.
WHERE B.CATEGORY = '경제'
- 출판일을 기준으로 오름차순 정렬합니다.
그룹별 조건에 맞는 식당 목록 출력하기
WITH MemberReviewCount AS (
SELECT
MEMBER_ID,
COUNT(*) AS REVIEW_COUNT
FROM
REST_REVIEW
GROUP BY
MEMBER_ID
),
MaxReviewMember AS (
SELECT
MEMBER_ID
FROM
MemberReviewCount
WHERE
REVIEW_COUNT = (SELECT MAX(REVIEW_COUNT) FROM MemberReviewCount)
)
SELECT
MP.MEMBER_NAME,
RR.REVIEW_TEXT,
TO_CHAR(RR.REVIEW_DATE, 'YYYY-MM-DD')
FROM
REST_REVIEW RR
JOIN MEMBER_PROFILE MP ON RR.MEMBER_ID = MP.MEMBER_ID
JOIN MaxReviewMember MRM ON RR.MEMBER_ID = MRM.MEMBER_ID
ORDER BY
TO_CHAR(RR.REVIEW_DATE, 'YYYY-MM-DD') ASC,
RR.REVIEW_TEXT ASC;
- WITH 키워드를 이용하여 테이블을 미리 생성합니다.
첫번째 테이블 MemberReviewCount는 MEMBER_ID를 기준으로 리뷰 횟수를 조회하는 테이블을 생성합니다.
WITH MemberReviewCount AS (
SELECT
MEMBER_ID,
COUNT(*) AS REVIEW_COUNT
FROM
REST_REVIEW
GROUP BY
MEMBER_ID
),
- 두번째 테이블은 리뷰를 가장 많이 작성한 회원 ID를 조회하기 위한 테이블을 생성합니다
MaxReviewMember AS (
SELECT
MEMBER_ID
FROM
MemberReviewCount
WHERE
REVIEW_COUNT = (SELECT MAX(REVIEW_COUNT) FROM MemberReviewCount)
)
- 레스토랑 리뷰 테이블과 멤버 정보 테이블, MaxReviewMember 테이블을 Member_ID를 기준으로 JOIN 합니다.
FROM
REST_REVIEW RR
JOIN MEMBER_PROFILE MP ON RR.MEMBER_ID = MP.MEMBER_ID
JOIN MaxReviewMember MRM ON RR.MEMBER_ID = MRM.MEMBER_ID
- 리뷰 작성일을 기준으로 오름차순, 리뷰 텍스트를 기준으로 오름차순 정렬합니다.
없어진 기록 찾기
SELECT
O.ANIMAL_ID,
O.NAME
FROM ANIMAL_INS I JOIN ANIMAL_OUTS O ON I.ANIMAL_ID(+) = O.ANIMAL_ID
WHERE I.ANIMAL_ID IS NULL
ORDER BY O.ANIMAL_ID, O.NAME;
- 동물 보호소에 들어온 동물과 입양 보낸 동물들을 ANIMAL_ID를 기준으로 LEFT JOIN합니다.
LEFT JOIN하는 이유는 입양 들어온 기록은 존재하는데 입양 보낸 기록이 없을 때를 찾기 위함입니다.
FROM ANIMAL_INS I JOIN ANIMAL_OUTS O ON I.ANIMAL_ID(+) = O.ANIMAL_ID
- 없어진 동물의 기록을 찾아야하기 때문에 I.ANIMAL_ID가 NULL인 데이터를 조회합니다.
WHERE I.ANIMAL_ID IS NULL
- 동물 ID와 이름을 기준으로 오름차순 정렬합니다.
있었는데요 없었습니다
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 ASC
- 동물 보호소에 들어온 동물과 입양 보낸 동물들을 ANIMAL_ID를 기준으로 JOIN합니다.
FROM ANIMAL_INS I JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID
- 보호 시작일이 입양일보다 더 늦은 레코드를 조회하기 위해 조건을 걸어줍니다.
WHERE I.DATETIME > O.DATETIME
- 보호 시작일이 빠른 순으로 조회합니다.(오름차순으로 정렬합니다)
오랜기간 보호한 동물(1)
SELECT
I.NAME,
I.DATETIME
FROM ANIMAL_INS I JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID(+)
WHERE O.ANIMAL_ID IS NULL
ORDER BY I.DATETIME ASC
FETCH NEXT 3 ROWS ONLY
- 동물 보호소에 들어온 동물과 입양 보낸 동물들을 ANIMAL_ID를 기준으로 RIGHT JOIN합니다.
RIGHT JOIN하는 이유는 입양을 아직 못간 동물을 찾기 위합니다.
FROM ANIMAL_INS I JOIN ANIMAL_OUTS O ON I.ANIMAL_ID = O.ANIMAL_ID(+)
- 입양을 못간 동물을 조회하기 위해 조건을 걸어줍니다.
WHERE O.ANIMAL_ID IS NULL
- 보호 시작일을 기준으로 오름차순으로 정렬하고 상위 3개의 레코드를 조회하기 위해 FETCH NEXT 절을 사용합니다.
ORDER BY I.DATETIME ASC
FETCH NEXT 3 ROWS ONLY
보호소에서 중성화한 동물
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 LIKE 'Spayed%' OR O.SEX_UPON_OUTCOME LIKE 'Neutered%')
ORDER BY I.ANIMAL_ID
- 동물 보호소에 들어온 동물과 입양 보낸 동물들을 ANIMAL_ID를 기준으로 JOIN합니다.
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 LIKE 'Spayed%' OR O.SEX_UPON_OUTCOME LIKE 'Neutered%')
- 동물 ID를 기준으로 오름차순 정렬합니다.
상품 별 오프라인 매출 구하기
SELECT
P.PRODUCT_CODE,
P.PRICE * S.TOTAL_SALES AS SALES
FROM PRODUCT P JOIN (
SELECT PRODUCT_ID, SUM(SALES_AMOUNT) AS TOTAL_SALES
FROM OFFLINE_SALE
GROUP BY PRODUCT_ID
) S ON P.PRODUCT_ID = S.PRODUCT_ID
ORDER BY SALES DESC, P.PRODUCT_CODE ASC
- 오프라인에서 상품별 판매량을 구하기 위해 상품 ID를 기준으로 그룹화한 테이블을 S로 생성합니다.
(
SELECT PRODUCT_ID, SUM(SALES_AMOUNT) AS TOTAL_SALES
FROM OFFLINE_SALE
GROUP BY PRODUCT_ID
) S
- 상품 테이블과 S를 PRODUCT_ID를 기준으로 JOIN합니다.
FROM PRODUCT P JOIN (...) S ON P.PRODUCT_ID = S.PRODUCT_ID
- 매출을 구하기 위해 다음과 같이 필드를 생성합니다.
P.PRICE * S.TOTAL_SALES AS SALES
- 총 매출량을 기준으로 내림차순, 상품 코드를 기준으로 오름차순 정렬합니다.
상품을 구매한 회원 비율 구하기
SELECT
TO_CHAR(O.SALES_DATE, 'YYYY') AS YEAR,
EXTRACT(MONTH FROM O.SALES_DATE) AS MONTH,
COUNT(DISTINCT U.USER_ID) AS PUCHASED_USERS,
ROUND(COUNT(DISTINCT U.USER_ID)/(SELECT COUNT(*) FROM USER_INFO WHERE TO_CHAR(joined, 'YYYY') = '2021'), 1) AS PUCHASED_RATIO
FROM USER_INFO U JOIN ONLINE_SALE O ON U.USER_ID = O.USER_ID
WHERE TO_CHAR(U.JOINED, 'YYYY') = '2021'
GROUP BY TO_CHAR(O.SALES_DATE, 'YYYY'), EXTRACT(MONTH FROM O.SALES_DATE)
ORDER BY TO_CHAR(O.SALES_DATE, 'YYYY'), EXTRACT(MONTH FROM O.SALES_DATE)
- 상품을 구매한 회원을 찾기 위해 USER_INFO 테이블과 ONLINE_SALE 테이블을 JOIN 합니다.
FROM USER_INFO U JOIN ONLINE_SALE O ON U.USER_ID = O.USER_ID
- 그 중 2021년에 가입한 회원을 구하기 위해 조건을 걸어줍니다.
WHERE TO_CHAR(U.JOINED, 'YYYY') = '2021'
- 연, 월별로 (구매 회원수 / 가입 회원) 을 구해야하기에 연, 월을 기준으로 그룹화를 진행합니다.
GROUP BY TO_CHAR(O.SALES_DATE, 'YYYY'), EXTRACT(MONTH FROM O.SALES_DATE)
- 상품을 구매한 회원의 비율을 소수점 두번째 자리에서 반올림해야 하기에 다음과 같이 컬럼을 구성합니다.
2021년에 가입한 회원의 전체 수를 구하기 위해 서브 쿼리를 이용합니다.
ROUND(COUNT(DISTINCT U.USER_ID)/(SELECT COUNT(*) FROM USER_INFO WHERE TO_CHAR(joined, 'YYYY') = '2021'), 1) AS PUCHASED_RATIO
- 연과 월을 기준으로 오름차순 정렬합니다.
FrontEnd 개발자 찾기
SELECT
D.ID,
D.EMAIL,
D.FIRST_NAME,
D.LAST_NAME
FROM
DEVELOPERS D
WHERE
EXISTS (
SELECT 1
FROM SKILLCODES S
WHERE S.CATEGORY = 'FRONT END'
AND (D.SKILL_CODE & S.CODE) <> 0
)
ORDER BY
D.ID ASC;
- 서브 쿼리를 사용합니다.
SKILL이 FRONT END이고 DEVELOPERS의 SKILL_CODE와 비트 연산을 통해 0보다 큰(공통 비트가 존재하는) 컬럼을 조회합니다.
FROM
DEVELOPERS D
WHERE
EXISTS (
SELECT 1
FROM SKILLCODES S
WHERE S.CATEGORY = 'FRONT END'
AND (D.SKILL_CODE & S.CODE) <> 0
)