[PROGRAMMERS] JOIN

[Notification]
DBMS : MySQL

1. JOIN LV.2

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

문제 링크

1.1 정답

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

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

문제 링크

2.1 정답

SELECT 
    B.PRODUCT_CODE, 
    (A.AMOUNT * B.PRICE) AS SALES 
FROM 
    (
        SELECT 
            PRODUCT_ID, 
            SUM(SALES_AMOUNT) AS AMOUNT 
        FROM 
            OFFLINE_SALE 
        GROUP BY 
            PRODUCT_ID
    ) AS A, 
    PRODUCT AS B 
WHERE 
    A.PRODUCT_ID = B.PRODUCT_ID 
ORDER BY 
    (A.AMOUNT * B.PRICE) DESC, 
    B.PRODUCT_CODE ASC;

2. JOIN LV.3

1. 없어진 기록 찾기

문제 링크

1.1 정답

SELECT 
    ANIMAL_ID, 
    NAME 
FROM 
    ANIMAL_OUTS
WHERE 
    ANIMAL_ID NOT IN (
        SELECT 
            ANIMAL_ID 
        FROM 
            ANIMAL_INS
    ) 
ORDER BY 
    ANIMAL_ID

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

문제 링크

2.1 정답

SELECT 
    A.ANIMAL_ID, 
    A.NAME 
FROM 
    ANIMAL_INS AS A
INNER JOIN 
    ANIMAL_OUTS AS B
ON 
    A.ANIMAL_ID = B.ANIMAL_ID 
    AND A.DATETIME > B.DATETIME
ORDER BY 
    A.DATETIME

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

문제 링크

3.1 정답

SELECT 
    NAME, 
    DATETIME 
FROM 
    ANIMAL_INS
WHERE 
    ANIMAL_ID NOT IN (
        SELECT 
            ANIMAL_ID 
        FROM 
            ANIMAL_OUTS
    )
ORDER BY 
    DATETIME ASC
LIMIT 
    3

3. JOIN LV.4

1. 보호소에서 중성화된 동물

문제 링크

1.1 정답

SELECT 
    A.ANIMAL_ID, 
    A.ANIMAL_TYPE, 
    A.NAME 
FROM 
    (
        SELECT 
            ANIMAL_ID, 
            ANIMAL_TYPE, 
            NAME 
        FROM 
            ANIMAL_INS 
        WHERE 
            SEX_UPON_INTAKE LIKE "%Intact%"
    ) AS A, 
    ANIMAL_OUTS AS B 
WHERE 
    A.ANIMAL_ID = B.ANIMAL_ID 
    AND (B.SEX_UPON_OUTCOME LIKE "%Spayed%" 
    OR B.SEX_UPON_OUTCOME LIKE "%Neutered%")
ORDER BY 
    A.ANIMAL_ID;

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

문제 링크

2.1 정답

SELECT 
    A.PRODUCT_ID, 
    B.PRODUCT_NAME, 
    (A.AMOUNT * B.PRICE) AS TOTAL_SALES 
FROM 
    (
        SELECT 
            PRODUCT_ID, 
            SUM(AMOUNT) AS AMOUNT 
        FROM 
            FOOD_ORDER 
        WHERE 
            PRODUCE_DATE LIKE "2022-05%" 
        GROUP BY 
            PRODUCT_ID
    ) AS A, 
    FOOD_PRODUCT AS B 
WHERE 
    A.PRODUCT_ID = B.PRODUCT_ID 
ORDER BY 
    (A.AMOUNT * B.PRICE) DESC, 
    A.PRODUCT_ID ASC;

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

문제 링크

3.1 정답

SELECT 
    A.FLAVOR 
FROM 
    (
        SELECT 
            FLAVOR, 
            SUM(TOTAL_ORDER) AS HALF 
        FROM 
            FIRST_HALF 
        GROUP BY 
            FLAVOR
    ) AS A, 
    (
        SELECT 
            FLAVOR, 
            SUM(TOTAL_ORDER) AS JU 
        FROM 
            JULY 
        GROUP BY 
            FLAVOR
    ) AS B 
WHERE 
    A.FLAVOR = B.FLAVOR 
ORDER BY 
    A.HALF + B.JU DESC 
LIMIT 3;

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

문제 링크

4.1 정답

SELECT 
    B.MEMBER_NAME, 
    A.REVIEW_TEXT, 
    DATE_FORMAT(A.REVIEW_DATE, "%Y-%m-%d") AS REVIEW_DATE 
FROM 
    REST_REVIEW AS A, 
    MEMBER_PROFILE AS B 
WHERE 
    A.MEMBER_ID = (
        SELECT 
            MEMBER_ID 
        FROM 
            REST_REVIEW 
        GROUP BY 
            MEMBER_ID 
        ORDER BY 
            COUNT(*) DESC 
        LIMIT 1
    ) 
    AND A.MEMBER_ID = B.MEMBER_ID 
ORDER BY 
    A.REVIEW_DATE ASC, 
    A.REVIEW_TEXT ASC;

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

문제 링크

5.1 정답

SELECT 
    A.CAR_ID, 
    A.CAR_TYPE, 
    ROUND((A.FEE * (100 - B.DISCOUNT_RATE) / 100), 0) AS FEE 
FROM 
    (
        SELECT 
            CAR_ID, 
            CAR_TYPE, 
            DAILY_FEE * 30 AS FEE 
        FROM 
            CAR_RENTAL_COMPANY_CAR 
        WHERE 
            (CAR_TYPE = "세단" OR CAR_TYPE = "SUV") 
            AND CAR_ID NOT IN (
                SELECT 
                    CAR_ID 
                FROM 
                    CAR_RENTAL_COMPANY_RENTAL_HISTORY 
                WHERE 
                    (DATE_FORMAT(START_DATE, "%Y-%m-%d") <= "2022-11-30") 
                    AND (DATE_FORMAT(END_DATE, "%Y-%m-%d") >= "2022-11-01")
            )
    ) AS A, 
    CAR_RENTAL_COMPANY_DISCOUNT_PLAN AS B 
WHERE 
    A.CAR_TYPE = B.CAR_TYPE 
    AND B.DURATION_TYPE = "30일 이상" 
    AND ROUND((A.FEE * (100 - B.DISCOUNT_RATE) / 100), 0) >= 500000 
    AND ROUND((A.FEE * (100 - B.DISCOUNT_RATE) / 100), 0) < 2000000 
ORDER BY 
    ROUND((A.FEE * (100 - B.DISCOUNT_RATE) / 100), 0) DESC, 
    CAR_TYPE ASC, 
    CAR_ID DESC;

6. FrontEnd 개발자 찾기

문제 링크

6.1 정답

SELECT 
    B.ID, 
    B.EMAIL, 
    B.FIRST_NAME, 
    B.LAST_NAME 
FROM 
    (
        SELECT 
            SUM(CODE) AS "key" 
        FROM 
            SKILLCODES 
        WHERE 
            CATEGORY = "Front End" 
        GROUP BY 
            CATEGORY
    ) AS A, 
    DEVELOPERS AS B 
WHERE 
    B.SKILL_CODE & A.key > 0 
ORDER BY 
    B.ID ASC;

4. JOIN LV.5

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

문제 링크

1.1 정답

SELECT 
    YEAR(SALES_DATE) AS YEAR, 
    MONTH(SALES_DATE) AS MONTH, 
    COUNT(DISTINCT USER_ID) AS PURCHASED_USERS, 
    ROUND((COUNT(DISTINCT USER_ID) / B.count), 1) AS PURCHASED_RATIO
FROM 
    ONLINE_SALE AS A,
    (SELECT 
        COUNT(USER_ID) AS count 
     FROM 
        USER_INFO 
     WHERE 
        YEAR(JOINED) = 2021
    ) AS B
WHERE 
    USER_ID IN (
        SELECT 
            USER_ID 
        FROM 
            USER_INFO 
        WHERE 
            YEAR(JOINED) = 2021
    )
GROUP BY 
    YEAR(SALES_DATE), 
    MONTH(SALES_DATE)
ORDER BY 
    YEAR(SALES_DATE), 
    MONTH(SALES_DATE);