프로그래머스 SQL JOIN

문제 및 답변

 

(Level 2) 조건에 맞는 도서와 저자 리스트출력하기

-- Oracle
SELECT BOOK_ID
     , AUTHOR_NAME
     , TO_CHAR(PUBLISHED_DATE, 'YYYY-MM-DD') 
    AS PUBLISHED_DATE
FROM BOOK 
JOIN AUTHOR
ON BOOK.AUTHOR_ID = AUTHOR.AUTHOR_ID
AND CATEGORY = '경제'
ORDER BY PUBLISHED_DATE ASC;

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

 

(Level 2) 상품 별 오프라인 매출 구하기

-- Oracle & Mysql
SELECT PROD.PRODUCT_CODE
     , SUM(OFS.SALES_AMOUNT * PROD.PRICE) AS SALES
FROM PRODUCT PROD JOIN OFFLINE_SALE OFS
ON PROD.PRODUCT_ID = OFS.PRODUCT_ID
GROUP BY PROD.PRODUCT_CODE
ORDER BY SALES DESC, PRODUCT_CODE

 

(Level 3) 없어진 기록 찾기

-- Oracle & Mysql
SELECT OUTS.ANIMAL_ID
     , OUTS.NAME
FROM ANIMAL_INS INS
RIGHT JOIN ANIMAL_OUTS OUTS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE INS.ANIMAL_ID IS NULL
ORDER BY OUTS.ANIMAL_ID;

 

(Level 3) 있었는데요 없었습니다

-- Oracle & Mysql
SELECT INS.ANIMAL_ID
     , INS.NAME
FROM ANIMAL_INS INS
JOIN ANIMAL_OUTS OUTS
ON INS.ANIMAL_ID = OUTS.ANIMAL_ID
WHERE INS.DATETIME > OUTS.DATETIME
ORDER BY INS.DATETIME;

 

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

-- Oracle
SELECT NAME
     , DATETIME
  FROM 
    (
      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
    )
WHERE ROWNUM <= 3;

-- Mysql
SELECT NAME
     , DATETIME
  FROM 
    (
      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
    ) AS SUBQUERY
LIMIT 3;

SubQuery로 아직 입양을 못 간 동물 중, 가장 오래 보호소에 있었던 이름과 보호 시작일을 조회한다.
그리고 보호 시작일 순으로 정렬한 결과값을 얻어낸다.
나온 결과값에서 3개만 조회하도록 제한하여 최종 결과를 얻어냈다.

 

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

-- ORACLE
SELECT PROFILE.MEMBER_NAME
     , REVIEW.REVIEW_TEXT
     , TO_CHAR(REVIEW.REVIEW_DATE, 'YYYY-MM-DD') AS REVIEW_DATE
FROM MEMBER_PROFILE PROFILE
JOIN REST_REVIEW REVIEW
ON PROFILE.MEMBER_ID = REVIEW.MEMBER_ID
WHERE PROFILE.MEMBER_ID =
(
    SELECT MEMBER_ID
    FROM (
    SELECT MEMBER_ID, COUNT(*) AS REVIEW_COUNT
    FROM REST_REVIEW
    GROUP BY MEMBER_ID
    ORDER BY REVIEW_COUNT DESC
  )
  WHERE ROWNUM = 1
)
ORDER BY REVIEW.REVIEW_DATE ASC, REVIEW.REVIEW_TEXT ASC;

-- Mysql
SELECT PROFILE.MEMBER_NAME
     , REVIEW.REVIEW_TEXT
     , DATE_FORMAT(REVIEW.REVIEW_DATE, '%Y-%m-%d') AS REVIEW_DATE
FROM MEMBER_PROFILE PROFILE
JOIN REST_REVIEW REVIEW
ON PROFILE.MEMBER_ID = REVIEW.MEMBER_ID
WHERE PROFILE.MEMBER_ID =
(
    SELECT MEMBER_ID
    FROM (
    SELECT MEMBER_ID, COUNT(*) AS REVIEW_COUNT
    FROM REST_REVIEW
    GROUP BY MEMBER_ID
    ORDER BY REVIEW_COUNT DESC
  ) AS subquery
  LIMIT 1
)
ORDER BY REVIEW.REVIEW_DATE ASC, REVIEW.REVIEW_TEXT ASC;

 

---

References By

https://school.programmers.co.kr/learn/courses/30/parts/17046

프로그래머스

 

https://chat.openai.com/