[22주차_SQL] SQL 풀이 모음

[baexxbin]

고다혜:
조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기

배수빈:
년, 월, 성별 별 상품 구매 회원 수 구하기

백제완:
상품을 구매한 회원 비율 구하기

이지영:
식품분류별 가장 비싼 식품의 정보 조회하기

손지민:
저자 별 카테고리 별 매출액 집계하기

## 문제: [문제명](링크)


### 설명


### 코드


### 아이디어

[yeongleej]

문제: 식품분류별 가장 비싼 식품의 정보 조회하기

설명

• 식품분류 별로
• 제일 비싼 식품의 분류, 가격, 이름 출력
• 단, 식품분류가 '과자', '국', '김치', '식용유'인 경우만 출력
• 식품 가격 기준으로 내림차순

코드

SELECT 
    CATEGORY, PRICE AS MAX_PRICE, PRODUCT_NAME
FROM FOOD_PRODUCT
WHERE (CATEGORY, PRICE) IN (
    SELECT CATEGORY, MAX(PRICE)
    FROM FOOD_PRODUCT
    WHERE CATEGORY IN('과자', '국', '김치', '식용유')
    GROUP BY CATEGORY
)
ORDER BY MAX_PRICE DESC

아이디어

• 식품분류 별로 조회
  • GROUP BY 활용
• 식품분류 별로 가장 비싼 금액 구하기

  SELECT
      CATEGORY, MAX(PRICE) AS MAX_PRICE, PRODUCT_NAME
  FROM FOOD_PRODUCT
  WHERE CATEGORY IN('과자', '국', '김치', '식용유')
  GROUP BY CATEGORY

  • 이때, 가장 비싼 금액에 해당하는 식품 이름도 구해야 하는데 위의 쿼리에서는 구할 수 없음
  • 따라서 위의 쿼리를 서브쿼리로 활용해서 최종값 구하기

[KodaHye]

문제: 조회수가 가장 많은 중고거래 게시판의 첨부파일 조회하기

설명

• USER_GOODS_BOARD(BOARD_ID, WRITER_ID, TITLE, CONTENTS, PRICE, CREATED_DATE, STATUS, VIEWS)
• USED_GOODS_FILE(FILE_ID, FILE_EXT, FILE_NAME, BOARD_ID)
• 조회수가 가장 많은 게시물의 첨부 파일 출력

코드

SELECT CONCAT("/home/grep/src/", a.BOARD_ID, "/", a.FILE_ID, a.FILE_NAME, a.FILE_EXT) `FILE_PATH`
FROM USED_GOODS_FILE a
JOIN (
    SELECT *
    FROM (
        SELECT BOARD_ID, RANK() OVER(ORDER BY VIEWS DESC) `RNK`
        FROM USED_GOODS_BOARD
    ) subquery
    WHERE RNK = 1 
) b ON a.BOARD_ID = b.BOARD_ID
ORDER BY FILE_ID DESC

아이디어

1. FROM 절의 subquery에서 RANK()를 통해 조회수가 가장 많은 게시물이 무엇인지 가져옴
2. USED_GOODS_FILE의 BOARD_ID와 조회수가 가장 높은 게시글의 BOARD_ID가 같은 것을 JOIN 해줌
3. 문제에서 요구하는대로 출력

[jmxx219]

문제: 저자 별 카테고리 별 매출액 집계하기

설명

• 2022년 1월의 도서 판매 데이터를 기준으로 저자 별, 카테고리 별 매출액(판매량 * 판매가) 을 구하여, 저자 ID, 저자명, 카테고리, 매출액 리스트를 출력
• 테이블
  • BOOK(BOOK_ID, CATEGORY, AUTHOR_ID, PRICE, PUBLISHED_DATE)
  • AUTHOR(AUTHOR_ID, AUTHOR_NAME)
  • BOOK_SALES(BOOK_ID, SALES_DATE, SALES)

코드

SELECT 
    a.AUTHOR_ID, 
    a.AUTHOR_NAME,
    b.CATEGORY,
    SUM(bs.SALES * b.PRICE) AS TOTAL_SALES
FROM 
    BOOK_SALES bs
    JOIN BOOK b ON bs.BOOK_ID = b.BOOK_ID
    JOIN AUTHOR a ON b.AUTHOR_ID = a.AUTHOR_ID
WHERE 
    bs.SALES_DATE BETWEEN '2022-01-01' AND '2022-01-31'
GROUP BY 
    a.AUTHOR_ID, b.CATEGORY
ORDER BY 
    a.AUTHOR_ID, b.CATEGORY DESC;

아이디어

1. 세 테이블을 조인한 후, 2022년 1월 판매 데이터 필터링
2. 각 도서별 매출액 계산(bs.SALES * b.PRICE)
3. 저자별, 카테고리별 그룹화한 후, 매출액 집계(SUM(bs.SALES * b.PRICE))
4. 정렬

[baexxbin]

문제: 년, 월, 성별 별 상품 구매 회원 수 구하기

설명

• 가입한 회원 정보 테이블 USER_INFO
• 온라인 상품 판매 정보 테이블 ONLINE_SALE
• 년, 월, 성별 별로 상품을 구매한 회원수를 집계하는 SQL문을 작성
  • 이때, 성별 정보가 없는 경우 결과에서 제외

코드

SELECT 
    YEAR(o.SALES_DATE) AS YEAR,
    MONTH(o.SALES_DATE) AS MONTH,
    u.GENDER,
    COUNT(DISTINCT o.USER_ID) AS USERS
FROM USER_INFO u
JOIN ONLINE_SALE o ON u.USER_ID = o.USER_ID
GROUP BY YEAR, MONTH, GENDER
HAVING GENDER IS NOT NULL
ORDER BY YEAR, MONTH, GENDER;

아이디어

• select : 구해야할 컬럼 선택
  • 회원수를 구하는 것이기에 중복된 값이 들어가지 않도록 DISTINCT
• 두 테이블 조인
• 성별 정보가 없는 경우 결과에서 제외 처리를 위해, 그룹으로 묶은 뒤 NOT NULL처리

[jewan100]

문제: 상품을 구매한 회원 비율 구하기

설명

• USER_INFO 테이블과 ONLINE_SALE 테이블
• 2021년에 가입한 전체 회원들 중 상품을 구매한 회원수와 상품을 구매한 회원의 비율(=2021년에 가입한 회원 중 상품을 구매한 회원수 / 2021년에 가입한 전체 회원 수)을 년, 월 별로 출력

코드

SELECT
    YEAR(SALES_DATE) `YEAR`
    , MONTH(SALES_DATE) `MONTH`
    , COUNT(DISTINCT(A.USER_ID)) `PURCHASED_USERS`
    , ROUND(COUNT(DISTINCT(A.USER_ID)) / (
        SELECT
            COUNT(USER_ID)
        FROM
            USER_INFO
        WHERE
            YEAR(JOINED) = 2021
      ), 1) `PUCHASED_RATIO`
FROM
    ONLINE_SALE A
    INNER JOIN USER_INFO B
        ON A.USER_ID = B.USER_ID
WHERE
    YEAR(B.JOINED) = 2021
GROUP BY
    YEAR
    , MONTH
ORDER BY
    YEAR
    , MONTH

아이디어

• YEAR : 년도를 출력
• MONTH : 월을 출력
• 물건을 구매한 사람들이 중복이 발생할 수 있으므로 DISTINCT를 이용해서 제외