✍🏻 programmers Lv2. MySQL 7문제(24.10.21)

programmers Lv2. MySQL 7문제(24.10.21)

50) 가격대 별 상품 개수 구하기

• PRODUCT 테이블에서 만원 단위의 가격대 별로 상품 개수를 출력하는 SQL 문을 작성. 이때 컬럼명은 각각 컬럼명은 PRICE_GROUP, PRODUCTS로 지정해주시고 가격대 정보는 각 구간의 최소금액(10,000원 이상 ~ 20,000 미만인 구간인 경우 10,000)으로 표시.

SELECT ( CASE
            WHEN PRICE < 10000 THEN 0
            ELSE TRUNCATE(PRICE, -4)
        END) AS PRICE_GROUP, COUNT(*) AS PRODUCTS
FROM PRODUCT
GROUP BY PRICE_GROUP
ORDER BY PRICE_GROUP ASC;

• 가격이 10,000보다 작은 제품 모두 0으로 그룹화 : WHEN PRICE < 10000 THEN 0

• 가격이 10,000 이상인 경우에는 TRUNCATE(PRICE, -4)를 사용해 가격을 10,000 단위로 자름.

• TRUNCATE는 10,000원 단위로 버림 처리를 해줌.

---

51) 물고기 종류 별 잡은 수 구하기

• FISH_NAME_INFO에서 물고기의 종류 별 물고기의 이름과 잡은 수를 출력하는 SQL문을 작성. 물고기의 이름 컬럼명은 FISH_NAME, 잡은 수 컬럼명은 FISH_COUNT로 하고
  결과는 잡은 수 기준으로 내림차순 정렬

SELECT COUNT(*) AS FISH_COUNT, B.FISH_NAME
FROM FISH_INFO A 
JOIN FISH_NAME_INFO B ON A.FISH_TYPE = B.FISH_TYPE
GROUP BY FISH_NAME
ORDER BY 1 DESC;

• 틀렸던 부분 : 당연히 FISH_NAME으로 그룹화를 해야되는데 이름으로 그룹화된 물고기 수로 계속 그룹화를 시도했었음. 갯수를 먼저 그룹화 시키는 말도 안되는..것을 계속 시도했던 것.

---

52) ROOT 아이템 구하기

• ROOT 아이템을 찾아 아이템 ID(ITEM_ID), 아이템 명(ITEM_NAME)을 출력하는 SQL문을 작성

SELECT A.ITEM_ID, A.ITEM_NAME
FROM ITEM_INFO A 
JOIN ITEM_TREE B ON A.ITEM_ID = B.ITEM_ID
WHERE B.PARENT_ITEM_ID IS NULL;

---

53) 연도 별 평균 미세먼지 농도 조회하기

• AIR_POLLUTION 테이블에서 수원 지역의 연도 별 평균 미세먼지 오염도와 평균 초미세먼지 오염도를 조회하는 SQL문을 작성. 이때, 평균 미세먼지 오염도와 평균 초미세먼지 오염도의 컬럼명은 각각 PM10, PM2.5로 해 주시고, 값은 소수 셋째 자리에서 반올림. 결과는 연도를 기준으로 오름차순 정렬

SELECT YEAR(YM) AS 'YEAR', ROUND(AVG(PM_VAL1),2) AS 'PM10', ROUND(AVG(PM_VAL2),2) AS 'PM2.5'
FROM AIR_POLLUTION
WHERE LOCATION2 LIKE '수원' 
GROUP BY YEAR
ORDER BY YEAR;

• 연도별 평균 계산 값을 구하기 때문에 그룹화 해야 함!

---

54) 월별 잡은 물고기 수 구하기

• 월별 잡은 물고기의 수와 월을 출력하는 SQL문을 작성. 잡은 물고기 수 컬럼명은 FISH_COUNT, 월 컬럼명은 MONTH로 하고 결과는 월을 기준으로 오름차순 정렬

SELECT COUNT(*) AS 'FISH_COUNT',MONTH(TIME) AS 'MONTH'
FROM FISH_INFO
GROUP BY MONTH
ORDER BY MONTH;

---

55) 조건에 부합하는 중고거래 상태 조회하기

• USED_GOODS_BOARD 테이블에서 2022년 10월 5일에 등록된 중고거래 게시물의 게시글 ID, 작성자 ID, 게시글 제목, 가격, 거래상태를 조회하는 SQL문을 작성. 거래상태가 SALE 이면 판매중, RESERVED이면 예약중, DONE이면 거래완료 분류하여 출력해주시고, 결과는 게시글 ID를 기준으로 내림차순 정렬.

SELECT BOARD_ID, WRITER_ID, TITLE, PRICE, 
    (CASE 
        WHEN STATUS = 'SALE' THEN '판매중'
        WHEN STATUS = 'RESERVED' THEN '예약중'
        WHEN STATUS = 'DONE' THEN '거래완료'
        END)  AS 'STATUS'
FROM USED_GOODS_BOARD
WHERE CREATED_DATE LIKE '2022-10-05'
ORDER BY BOARD_ID DESC;

---

56) 재구매가 일어나 상품과 회원 리스트 구하기

• ONLINE_SALE 테이블에서 동일한 회원이 동일한 상품을 재구매한 데이터를 구하여, 재구매한 회원 ID와 재구매한 상품 ID를 출력하는 SQL문을 작성. 결과는 회원 ID를 기준으로 오름차순 정렬해주시고 회원 ID가 같다면 상품 ID를 기준으로 내림차순 정렬.

SELECT USER_ID, PRODUCT_ID
FROM ONLINE_SALE
GROUP BY USER_ID, PRODUCT_ID
HAVING COUNT(*) >=2
ORDER BY USER_ID ASC, PRODUCT_ID DESC;

• 재구매하는 것을 어떻게 표현할까? 했는데 USER_ID 와 PRODUCT_ID가 회원 아이디, 제품 아이디로 설정되고 해당 필드를 그룹화하면 묶이게 된다.

• COUNT(*)는 각 그룹의 행의 수를 계산하고 그 값이 2이상인 경우를 출력한 것이다.

---